带分组依据的 SQL 查询

Question

我正在使用 postgresql 并想知道我是否可以在一个请求中执行此操作:我有一个包含四列的表:id、start、end 和 user_id在一个请求中，我做了一个

SELECT sum(finish - start) as duration, user_id group by user_id

现在，如果我也将获取的行的 ID 保存在某处，那就太好了，因为我稍后在代码中需要它们这可能吗？非常感谢

Answer 1

你应该看看Window Functions and Partitions或者可能是 array_agg。

假设这个例子:

CREATE TABLE mytable (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    start INTEGER,
    finish INTEGER
);
INSERT INTO mytable(id, user_id, start, finish) VALUES (1, 1, 5, 10);  -- duration: 5
INSERT INTO mytable(id, user_id, start, finish) VALUES (2, 2, 10, 30); -- duration: 20
INSERT INTO mytable(id, user_id, start, finish) VALUES (3, 1, 15, 20); -- duration: 5

如您所知，SELECT SUM(finish - start), user_id FROM mytable GROUP BY user_id 将返回:

10  | 1
20  | 2

我想您不想要的是此查询的输出(因为如果您在 GROUP BY 中使用具有唯一非空约束的列，您可能还不如不使用聚合，我假设 id 是:SELECT id, SUM(finish - start), user_id FROM mytable GROUP BY user_id, id。

使用窗口函数，您可以使用与当前行相关的其他行的数据。

以下查询:

SELECT id, SUM(finish - start) OVER (PARTITION BY user_id) AS duration, user_id
    FROM mytable ORDER BY user_id

产生这些结果:

1 | 10 | 1
3 | 10 | 1
2 | 20 | 2

您现在将根据使用的 id 获得一行，但是 SUM 适用于窗口框架内的整组行(此处，所有具有相同user_id 作为当前行)。

就您的应用程序而言，您可能需要逐行读取并将 id 的相关值存储在某处，直到 user_id 发生变化。

或者，您可以使用 array_agg将所有 ID 聚合到一个数组中:

SELECT array_agg(id), SUM(finish - start) AS duration, user_id
    FROM mytable GROUP BY user_id ORDER BY user_id, duration


{1, 3}  | 10 | 1
{2}     | 20 | 2

例如，如果您希望将其放在以空格分隔的字符串中，请使用 array_to_string在此之上:

SELECT array_to_string(array_agg(id), ' '), SUM(finish - start) AS duration, user_id
    FROM mytable GROUP BY user_id ORDER BY user_id, duration