如何按entity_id与assignedtogroup分组统计raise次数？

现有表结构 #

CREATE TABLE history(
  entity_id INT,
  raise_number INT,
  assignedtogroup INT,
  time BIGINT
);
INSERT INTO history (entity_id, raise_number, assignedtogroup, time)
VALUES
(452885, 0, 435189, 1622817568259),
(452885, NULL, 435189, 1622817568259),
(452885, 1, NULL, 1623082677268),
(452885, NULL, 10086, 1623233125267),
(452885, NULL, 10086, 1623233125267),
...

已实现的SQL（按分组统计raise次数） #

WITH ForwardFilled(filledgroup, raise_number) AS (
    SELECT MAX(assignedtogroup) OVER (PARTITION BY grp), raise_number
    FROM(
        SELECT raise_number, assignedtogroup,
              COUNT(assignedtogroup) OVER (ORDER BY time ASC) AS grp
        FROM history
    ) T(raise_number, assignedtogroup, grp)
)
SELECT filledGroup, COUNT(raise_number)
FROM ForwardFilled
WHERE raise_number <> 0
GROUP BY filledGroup
ORDER BY filledgroup

现有输出结果 #

修改需求 #

需按entity_id和assignedtogroup分组统计raise次数，结果表包含entity_id、filledgroup、count三列。

修改后的SQL语句 #

WITH ForwardFilled(entity_id, filledgroup, raise_number) AS (
    SELECT entity_id, MAX(assignedtogroup) OVER (PARTITION BY entity_id, grp), raise_number
    FROM(
        SELECT entity_id, raise_number, assignedtogroup,
              COUNT(assignedtogroup) OVER (PARTITION BY entity_id ORDER BY time ASC) AS grp
        FROM history
    ) T(entity_id, raise_number, assignedtogroup, grp)
)
SELECT entity_id, filledGroup, COUNT(raise_number) AS count
FROM ForwardFilled
WHERE raise_number <> 0
GROUP BY entity_id, filledGroup
ORDER BY entity_id, filledgroup

关键调整说明 #

1. 所有窗口函数添加PARTITION BY entity_id，保证每个entity_id独立进行分组填充逻辑
2. CTE中保留entity_id字段，最终统计时同时按entity_id和filledgroup分组
3. 结果集新增entity_id列，满足需求格式要求

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