使用generate系列填充缺失的值

Question

我有这样的数据-

First   Last    Client          iso_week  count
Aaron   Cook    AVALON          2018_01   1
Aaron   Cook    AVALON          2018_02   1
Aaron   Cook    AVALON          2018_04   2
Angela  Myers   New Western     2018_03   3

我如何使用generate_series获得这个输出-

First   Last    Client          iso_week  count
Aaron   Cook    AVALON          2018_01   1
Aaron   Cook    AVALON          2018_02   1
Aaron   Cook    AVALON          2018_03   0
Aaron   Cook    AVALON          2018_04   2
Angela  Myers   New Western     2018_01   0
Angela  Myers   New Western     2018_02   0
Angela  Myers   New Western     2018_03   3
Angela  Myers   New Western     2018_04   0

我需要在中间加零，在那里，所有的人都缺了几个月。例如，Aaron Cook丢失了March 2018数据，这就是为什么为2018_03创建了一个记录，并将零添加到该记录的计数中。

Answer 1

您可以使用generate_series将数据与0计数进行堆栈，并在最后选择的后面对其进行sum处理。

with cte as

(select frst, lst, client, iso_week, cnt
 from t
 union
 select frst, lst, client, left(iso_week,5)||trim(to_char(generate_series(1,12),'00')), 0
 from t)
                                                  
 select frst, lst, client, iso_week, sum(cnt) as cnt
 from cte
 group by frst, lst, client, iso_week;

后来我意识到我可以更容易地实现它。

 select frst, lst, client, iso_week, cnt
 from t
 union all
 select frst, lst, client, left(iso_week,5)||trim(to_char(generate_series(1,12),'00')), 0
 from t
 except
 select frst, lst, client, iso_week, 0
 from t;

这两种方法都以牺牲性能为代价提供简单性。如果性能成为一个问题，我建议在连接distinct frst, lst, client, left(iso_week,5)的输出之前将generate_series转储到另一个临时表中。如前所述，您正在将数据的大小乘以至少12。尽管如此，如果您正在处理的已经聚合的数据只有几百或数千行，那么性能应该不会太差。

Answer 2

您可以使用cross join生成行，使用left join输入数据：

select c.client, c.first, c.last, yw.iso_week,
       coalesce(t.count, 0)
from (select '2018_' || to_char(ts.wek, '00') as iso_week
      from generate_series(1, 5) gs(wk)
     ) yw cross join
     (select distinct client, first, last from t) c left join
     t
     on t.iso_week = yw.iso_week and t.client = c.client

Answer 3

可以将递归CTE的结果加入到原始数据上：

with recursive d_ranges(w) as (
    select to_date('2018 01', 'YYYY MM')::text
    union all
    select (to_date(w, 'YYYY-MM-DD') + interval '1 month')::text from d_ranges where to_date(w, 'YYYY-MM-DD') < to_date('2018 04', 'YYYY MM')
),
d_users as (
   select distinct u.fst, u.lst, u.client from users u
)
select s.*, case when u.cnt is null then 0 else u.cnt end 
from (select u.*, to_char(to_date(r.w, 'YYYY-MM-DD'), 'YYYY_MM') w 
      from d_users u cross join d_ranges r) s 
left join users u on u.iso_week = s.w and u.fst = s.fst and u.lst = s.lst and u.client = s.client
order by s.fst, s.lst, s.client, s.w;