sql - 如果找不到 PostgresQL 值，则使用默认值填充行

Question

我想返回 user_activity 的最后 7 天，但对于那些空白的日子，我想添加 0 作为值

假设我有这张表

actions |  id   | date
------------------------
67      |  123  | 2019-07-7
90      |  123  | 2019-07-9
100     |  123  | 2019-07-10
50      |  123  | 2019-07-13
30      |  123  | 2019-07-15

这应该是过去 7 天的预期输出

  actions |  id   | date
    ------------------------
    90      |  123  | 2019-07-9
    100     |  123  | 2019-07-10
    0       |  123  | 2019-07-11  <--- padded
    0       |  123  | 2019-07-12  <--- padded
    50      |  123  | 2019-07-13
    0       |  123  | 2019-07-14  <--- padded
    30      |  123  | 2019-07-15

这是我到目前为止的查询，我只能得到最近 7 天的查询但不确定添加到默认值是否是积极的

SELECT *
FROM user_activity
WHERE action_day > CURRENT_DATE - INTERVAL '7 days' 
ORDER BY uid, action_day

Answer 1

您可以使用 generate_series 加入您的表格。首先，您需要有一种方法可以将行用于不同的 ID。然后该集合可以与主表正确连接。

WITH days
AS (SELECT id,dt
    FROM (
        SELECT DISTINCT id FROM user_activity
        ) AS ids
  CROSS JOIN generate_series(
        CURRENT_DATE - interval '7 days', 
    CURRENT_DATE,  interval '1 day') AS dt
    )
SELECT  
     coalesce(u.actions,0)
    ,d.id
    ,d.dt
FROM days d LEFT JOIN user_activity u ON u.id = d.id AND u.action_day = d.dt

DEMO