sql - 我如何请求 Postgres 获取特定时间序列中不同日期的计数？

Question

我有一个这样的访问表:

time                   | user_id
--------------------------------
2018-05-01 00:00:00+02 | 56
2018-05-01 00:00:00+02 | 64
2018-05-01 00:00:00+02 | 56
2018-05-02 00:00:00+02 | 27
2018-05-02 00:00:00+02 | 64
...

我想请求 Postgres 数据库获取每个日期的活跃用户数量。如果用户在过去 30 天内在 10 个不同的日期进行过访问，则该用户是活跃的。例如，对于日期 2018-05-22 的数量，查询将是:

select count(*) from (
    select
        user_id,
        count(distinct time::date) as cnt
    from visit
    where
        time::date > '2018-05-22'::date - interval '30 days'
    group by user_id
    having count(distinct time::date) >= 10
    order by cnt desc
) t

结果是一个数字。它工作正常。为了从某个时间序列中获取每个日期的数量，我应该在此查询中修改什么？所需的结果应该是这样的:

date       | quantity
---------------------
2018-05-01 | 38
2018-05-02 | 26
2018-05-03 | 35
2018-05-04 | 44
...

Answer 1

首先，创建一个日历表，其中包含您需要的所有日期。

比如说，从 '1900-01-01' 到 '2099-12-31'？

然后它基本上是一个JOIN...

SELECT
    calendar_date, count(*)
FROM
(
    SELECT
        CALENDAR_TABLE.calendar_date,
        visit.user_id,
        COUNT(DISTINCT visit.time::date) as cnt
    FROM
        CALENDAR_TABLE
    INNER JOIN
        visit
            ON  visit.time >= CALENDAR_TABLE.calendar_date - interval '30 days'
            AND visit.time <  CALENDAR_TABLE.calendar_date + interval '01 days'
    WHERE
        CALENDAR_TABLE.calendar_date BETWEEN '2018-05-01' AND '2018-05-22'
    GROUP BY
        CALENDAR_TABLE.calendar_date,
        visit.user_id
    HAVING
        COUNT(DISTINCT visit.time::date) >= 10
) t
GROUP BY
    calendar_date

或许……

SELECT
    calendar_date, count(*)
FROM
(
    SELECT
        CALENDAR_TABLE.calendar_date,
        visit.user_id,
        COUNT(*) as cnt
    FROM
        CALENDAR_TABLE
    INNER JOIN
    (
        SELECT
           user_id,
           time::date   AS user_date
        FROM
           visit
        GROUP BY
           user_id,
           time::date
    )
        visit
            ON  visit.user_date >= CALENDAR_TABLE.calendar_date - interval '30 days'
            AND visit.user_date <  CALENDAR_TABLE.calendar_date + interval '01 days'
    WHERE
        CALENDAR_TABLE.calendar_date BETWEEN '2018-05-01' AND '2018-05-22'
    GROUP BY
        CALENDAR_TABLE.calendar_date,
        visit.user_id
    HAVING
        COUNT(*) >= 10
) t
GROUP BY
    calendar_date

这可能会减少内存开销，但可能会使连接和过滤变慢......