SQL子查询使用主查询中的逐项分组

Question

我有一个带有created 时间戳和id 标识符的表。

我每周可以获得唯一的 id 数量:

SELECT date_trunc('week', created)::date AS week, count(distinct id) 
FROM my_table
GROUP BY week ORDER BY week;

现在我想通过唯一的 id 每周累积 created 的数量，如下所示:

SELECT date_trunc('week', created)::date AS week, count(distinct id),
       (SELECT count(distinct id)
        FROM my_table
        WHERE date_trunc('week', created)::date <= week) as acc
FROM my_table
GROUP BY week ORDER BY week;

但这不起作用，因为 week 在子选择中不可访问(错误:列“week”不存在)。

我该如何解决？

我正在使用 PostgreSQL

Answer 1

使用累积聚合。但是，我认为您不需要 distinct，所以:

SELECT date_trunc('week', created)::date AS week, count(*) as cnt,
       SUM(COUNT(*)) OVER (ORDER BY MIN(created)) as running_cnt
FROM my_table
GROUP BY week
ORDER BY week;

在任何情况下，当您描述问题时，您可以将 cnt 更改为使用 count(distinct)。您的子查询根本没有使用 distinct。