如何按分组统计history表中的raise次数？SQL查询报错修正

表结构与测试数据 #

CREATE TABLE history(
  entity_id INT,
  raise_number INT,
  assignedtogroup INT,
  time BIGINT
);
INSERT INTO history VALUES 
(1, 0, 435215, 1677235879584),
(1, null, 435215, 1677235879584),
(1, 1, null, 1678358448831),
(1, 2, null, 1678358851266),
(1, 3, null, 1678358861480),
(1, null, 2639656, 1678358930852),
(1, null, 2639656, 1678358930852),
(1, 4, null, 1678359050954),
(1, 5, null, 1678359059543);

需求说明 #

需要按分组统计raise次数：当分组变更后，后续的raise将归属新分组，直到再次变更分组。具体规则：

• 前2行表示当前分组为435215；
• 接下来3行的raise次数归属435215，累计3次；
• 之后分组变更为2639656；
• 分组变更后的2次raise归属2639656。

预期输出 #

尝试的查询语句及报错 #

编写的查询语句：

SELECT entity_id, assignedtogroup AS group, 
       COUNT(*) AS raise_number
FROM (
  SELECT entity_id, assignedtogroup, raise_number,
         LAG(assignedtogroup) OVER (PARTITION BY entity_id ORDER BY time) AS prev_group
  FROM history
) t
WHERE assignedtogroup IS NOT NULL AND (prev_group IS NULL OR prev_group = assignedtogroup)
GROUP BY assignedtogroup;

执行后报错：

Query Error: error: column "t.entity_id" must appear in the GROUP BY clause or be used in an aggregate function

修正方案 #

原查询存在两个核心问题：一是GROUP BY子句未包含SELECT中的非聚合列entity_id，违反SQL语法规范；二是逻辑错误，无法将raise_number非空的记录关联到对应的分组（这类记录的assignedtogroup为null）。

正确的思路是先为每条记录填充其所属的分组——通过向前填充最近的非nullassignedtogroup值，再统计每个分组下有效raise的数量。

修正后的SQL语句：

SELECT 
  entity_id,
  assignedtogroup AS "group",
  COUNT(raise_number) AS raise_number
FROM (
  SELECT 
    entity_id,
    -- 向前填充最近的非null分组值
    LAST_VALUE(assignedtogroup) OVER (
      PARTITION BY entity_id 
      ORDER BY time 
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS assignedtogroup,
    raise_number
  FROM history
) t
WHERE raise_number IS NOT NULL -- 仅统计存在raise的记录
GROUP BY entity_id, assignedtogroup
ORDER BY assignedtogroup;

逻辑说明 #

1. 内层查询使用LAST_VALUE窗口函数，按entity_id分区、time排序，为每条记录填充最近的非nullassignedtogroup值，解决raise记录分组字段为空的关联问题；
2. 外层过滤出raise_number非空的有效记录，按entity_id和assignedtogroup分组，统计每个分组的raise次数；
3. 最终输出符合需求的分组统计结果。

内容的提问来源于stack exchange，提问作者executable