如果未找到 PostgresSQL 的值，则使用默认值填充行

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

演示