postgresql - SQL 中每个 uuid 的固定时间戳范围

Question

我想生成一个表，其中包含最近 n 周时间戳的数据(在本例中，n=3)和所有数据，即使它为 null。

我正在使用以下代码

   with raw_weekly_data as (SELECT
   distinct d.uuid,
   date_trunc('week',a.start_timestamp) as tstamp,
   avg(price) as price
   FROM
   a join d on a.uuid = d.uuid
   where start_timestamp between date_trunc('week',now()) -           interval '3 week' and date_trunc('week',now())
   group by 1,2,3
   order by 1)

   ,tstamp as (SELECT
   distinct tstamp
   FROM
   raw_weekly_data
   )

   SELECT
   t.tstamp,
   r.*
   from raw_weekly_data r right join tstamp t on r.tstamp =       t.tstamp
   order by uuid

我想要这样的东西:

week  |  uuid  | price
w1    |  1     | 10
w2    |  1     | 2
w3    |  1     |
w1    |  2     | 20
w2    |  2     |
w3    |  2     |
w1    |  3     | 10
w2    |  3     | 10
w3    |  3     | 20

但是所有的空结果都没有显示。这里最好的方法是什么？

week  |  uuid  | price
w1    |  1     | 10
w2    |  1     | 2
w1    |  2     | 20
w1    |  3     | 10
w2    |  3     | 10
w3    |  3     | 20

Answer 1

形成所有星期的笛卡尔积和 UUID，然后 LEFT JOIN 到实际平均价格，每个 (week, uuid)。喜欢:

SELECT *
FROM   generate_series (date_trunc('week', now() - interval '3 week')
                                         , now() - interval '1 week'
                      , interval '1 week') tstamp
CROSS  JOIN (SELECT DISTINCT uuid FROM a) a
LEFT   JOIN (
   SELECT d.uuid
        , date_trunc('week', a.start_timestamp) AS tstamp
        , avg(price) AS price  -- d.price?
   FROM   a
   JOIN   d USING (uuid)
   WHERE  a.start_timestamp >= date_trunc('week',now()) - interval '3 week'
   AND    a.start_timestamp <  date_trunc('week',now())
   ) ad USING (uuid, tstamp)
GROUP  BY 1, 2
ORDER  BY 1, 2

通过这种方式，您可以获得过去三周和 UUID 的所有组合，并按平均价格扩展 - 如果该组合应该存在的话。
基于一些有根据的猜测来填补缺失的信息..