英文:
Count number of raise by group from a history table
问题
我有以下的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);
我需要按组计算提高的次数。如果组改变，下一个提高将属于新的组，直到设置新的组。如何选择按组计算提高次数。在这种情况下，预期的输出应为：
| entity_id | group | raise_number |
| --------- | -------- | -------------- |
| 1 | 1435215 | 3 |
| 1 | 2639656 | 2 |
我提出了以下查询：
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;
输出：
> 查询错误: 错误: 列 "t.entity_id" 必须出现在 GROUP BY 子句中或用于聚合函数中
我如何检索每个组的提高次数？
解释：
前两行指示当前组435215。
然后我们有3行，将提高次数增加到3（与组435215关联的提高）。
然后我们更改为组2639656。
因为我们更改了组，所以计算2次提高（与组2639656关联的提高）。
英文:
I have the following SQL structure :
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);
I need to count the number of raise by group. If a group change, the next raise would be for that new group until a new group will be set. How can I select raise_number by group. In this case the expected output should be :
| entity_id | group | raise_number |
|---|---|---|
| 1 | 1435215 | 3 |
| 1 | 2639656 | 2 |
I came up using the following query :
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;
Output :
> Query Error: error: column "t.entity_id" must appear in the GROUP BY
> clause or be used in an aggregate function
How can I retreive the number of raise per group ?
Demo: https://www.db-fiddle.com/f/fcHarhr2smyooFKVvWvJ8T/7
Explanation :
The first 2 row indicate the current group 435215.
Then we have 3 row which increment the raise number to 3 (raise linked to group 435215).
After we change the group to 2639656.
As we changed the group, we count 2 raise (raise linked to group 2639656).
答案1
得分: 1
根据这个答案，我们使用先前的assignedtogroup进行向前填充空值，并简单计算非空的raise_number数量。<br/>注意：如评论中所提到的，raise_number = 0是一个初始值，在计数中应被忽略。
使用 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)
FROM test
) T(raise_number, assignedtogroup, grp)
)
选择 filledGroup, COUNT(raise_number)
从 ForwardFilled
其中 raise_number <> 0
按 filledgroup 排序
英文:
Following this answer, we forward fill the nulls with the previous assignedtogroup and simply count the non null raise_number.<br/>Note: as mentioned in the comments raise_number = 0 is an initial value and must be ignored in the count.
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)
FROM test
) T(raise_number, assignedtogroup, grp)
)
SELECT filledGroup, COUNT(raise_number)
FROM ForwardFilled
WHERE raise_number <> 0
GROUP BY filledGroup
ORDER BY filledgroup
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论