sql - PostgreSQL:使用 Generate_Series() 计算 0

Question

我正在尝试计算过去 7 天的记录，包括没有任何记录或 0 的记录。这是我当前的查询。

WITH calendar as (
SELECT  d
FROM generate_series(date_trunc('day',CURRENT_DATE - '7 day'::interval - '7 hour'::interval),date_trunc('day', CURRENT_DATE - INTERVAL '7 hour'), '1 day'::interval) d 
)

SELECT 
        COUNT(mc.id), 
        mc.name AS ord_name, 
        c.d::date AS ord_date
    FROM test_table mc
    LEFT JOIN calendar c
    ON c.d = mc.occured_at::date
    WHERE date_trunc('day', occured_at - interval '7 hour') > 
    (CURRENT_DATE + INTERVAL '7 hour') - INTERVAL '7 days'
    GROUP BY 
        name, 
        c.d
    ORDER BY 
        c.d;

我的查询结果 DB Fiddle Link

所以我正在使用 generate_series() 来获取我想要的日期。我减去 7 小时，因为从技术上讲，一天将从早上 7 点开始，到第二天早上 6:59 结束。我使用 LEFT JOIN 来比较我从 calendar 获得的日期和我的表的日期。

示例数据:test_table

| id  |  name  |       occured_at     |
|-----|--------|----------------------|
| 1   |  ord1  |2019-02-23 07:00:00+00|
| 2   |  ord2  |2019-02-23 12:30:00+00|
| 3   |  ord1  |2019-02-24 06:58:00+00|
| 4   |  ord2  |2019-02-25 07:00:00+00|
| 5   |  ord2  |2019-02-25 07:01:00+00|
| 6   |  ord1  |2019-02-26 06:59:00+00|
| 7   |  ord1  |2019-02-26 07:00:00+00|
| 8   |  ord1  |2019-02-26 12:30:00+00|
| 9   |  ord2  |2019-02-27 06:58:00+00|
| 10  |  ord1  |2019-02-28 07:01:00+00|
| 11  |  ord1  |2019-02-28 07:00:00+00|
| 12  |  ord1  |2019-03-01 06:59:00+00|

预期结果:

|count |ord_name |ord_date  |
|------|---------|----------|
| 1    |  ord1   |2019-02-23|
| 2    |  ord2   |2019-02-23|
| 0    |  ord1   |2019-02-24|
| 0    |  ord2   |2019-02-24|
| 1    |  ord1   |2019-02-25|
| 2    |  ord2   |2019-02-25|
| 2    |  ord1   |2019-02-26|
| 1    |  ord2   |2019-02-26|
| 0    |  ord1   |2019-02-27|
| 0    |  ord2   |2019-02-27|
| 3    |  ord1   |2019-02-28|
| 0    |  ord2   |2019-02-28|
| 0    |  ord1   |2019-03-01|
| 0    |  ord2   |2019-03-01|

Answer 1

使用cross join 生成所有行，然后使用left join 引入具有匹配值的行:

WITH calendar as (
      SELECT d
      FROM generate_series(date_trunc('day', CURRENT_DATE - '7 day'::interval - '7 hour'::interval),
                           date_trunc('day', CURRENT_DATE - INTERVAL '7 hour'),
                          '1 day'::interval
                          ) d 
     )

SELECT n.name AS ord_name, 
       c.d::date AS ord_date
       COUNT(mc.id), 
FROM (SELECT DISTINCT mc.name test_table mc) n CROSS JOIN
     calendar c LEFT JOIN
     test_table mc
     ON mc.occured_at >= c.d - interval '7 hour' and
        mc.occured_at < c.d + interval '1 day' - interval '7 hour'
GROUP BY n.name, c.d
ORDER BY c.d, n.name;