编号等差分组的SQL实现

发表于 更新于 分类于 ， 阅读次数： 评论数：

假设我们有这样一组数据在数据库中的存储为如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 表结构
CREATE TABLE t1 (
    sno INT,
    score INT
);

-- 数据
INSERT INTO t1 (sno, score)
VALUES
    (1, 1),    (2, 0),     (3, 2),    (4, 0),
    (5, 0),    (6, 3),    (7, 3),    (8, 2),
    (9, 0),    (10, 0),    (11, 5),    (12, 4),
    (13, 4),    (14, 2),    (15, 2),    (16, 5),
    (17, 0),    (18, 2),    (19, 3),    (20, 1);

我们要将数据1-2，3-4···两两一组进行对分数求平均，显示在对应数据后面。我们可以这样

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 查询代码
WITH PlayerRanges AS (
  SELECT
    sno,
    CEIL(sno / 2) AS range_group
  FROM
    t1
)
SELECT
  t.*,
  AVG(t.score) OVER (PARTITION BY pr.range_group) AS avg_score
FROM
  t1 t
JOIN
  PlayerRanges pr ON t.sno = pr.sno
ORDER BY t.sno ASC

其中PlayerRanges表的作用是获取同一组的相同特征，例如这里是CEIL(sno / 2)指将编号除以2后向上取整，这样就两两一组了。然后主表关联该表，并使用窗口函数对辅助表的分组元素进行分组即可。

需要注意的是在这个场景下OVER ()不能写OEDER BY子句。原因是窗口函数的行为取决于 ORDER BY 子句的方向（升序或降序）以及窗口函数类型。下面为不同情况下窗口函数如何考虑之前和之后的记录。

  1. 无 ORDER BY 子句：
    • 在没有 ORDER BY 子句的情况下，窗口函数默认考虑整个分区中的所有记录，而不关心它们的顺序。
  2. 有 ORDER BY 子句，升序（ASC）：
    • 当在窗口函数中使用 ORDER BY 子句，且指定为升序（ASC）时，窗口函数将考虑当前记录及其之前的记录。也就是说，窗口函数会在当前记录及其之前的记录上进行计算。
  3. 有 ORDER BY 子句，降序（DESC）：
    • 如果 ORDER BY 子句指定为降序（DESC），窗口函数将考虑当前记录及其之后的记录。这意味着窗口函数会在当前记录及其之后的记录上进行计算。

如果 SQL 代码中，ORDER BY t.sno ASC 指定了升序排序，窗口函数 AVG(t.score) OVER (PARTITION BY pr.range_group ORDER BY t.sno ASC) 会考虑当前记录及其之前的记录。这意味着计算平均分数时，窗口函数会将当前记录及其之前的记录按照 t.sno 的升序排列后进行计算。