我有一个表，其中分别存储了客户注册和购买的时间。
select * from customers_table
cust_id cust_reg_date cust_purchase_date purchase_made
1 1/10/21 1/28/21 laptop
1 1/10/21 1/31/21 car
1 1/10/21 2/9/21 shirt
2 2/5/21 2/26/21 pajamas
2 2/5/21 3/5/21 phone
2 2/5/21 4/25/21 laptop
3 2/15/21 4/10/21 dirt bike
3 2/15/21 5/10/21 towel
3 2/15/21 6/1/21 pen
我得到了这个片段，它记录了周从cust_reg_date开始，客户才开始购买!
select *, case when cust_purchase_date between cust_reg_date + 1 and cust_reg_date + 10 then 'Week 1 Purchase'
when cust_purchase_date between cust_reg_date + 11 and cust_reg_date + 20 then 'Week 2 Purchase'
when cust_purchase_date between cust_reg_date + 21 and cust_reg_date + 30 then 'Week 3 Purchase'
.... end as week_purchase
cust_id cust_reg_date cust_purchase_date purchase_made week_purchase
1 1/10/21 1/28/21 laptop Week 2 Purchase
1 1/10/21 1/31/21 car Week 3 Purchase
1 1/10/21 2/26/21 shirt Week 5 Purchase
2 2/5/21 2/21/21 pajamas Week 2 Purchase
2 2/5/21 3/5/21 phone Week 3 Purchase
2 2/5/21 4/25/21 laptop Week 5 Purchase
3 3/15/21 4/10/21 dirt bike Week 3 Purchase
3 3/15/21 5/10/21 towel Week 6 Purchase ....
如何显示没有购买的缺失周，如下所示:
cust_id cust_reg_date cust_purchase_date purchase_made week_purchase
1 1/10/21 - - Week 1 Purchase <--- Requested
1 1/10/21 1/28/21 laptop Week 2 Purchase
1 1/10/21 1/31/21 car Week 3 Purchase
1 1/10/21 - - Week 4 Purchase <--- Requested
1 1/10/21 2/26/21 shirt Week 5 Purchase
2 2/5/21 - - Week 1 Purchase <--- Requested
2 2/5/21 2/21/21 pajamas Week 2 Purchase
2 2/5/21 3/5/21 phone Week 3 Purchase
2 2/5/21 - - Week 4 Purchase <--- Requested
2 2/5/21 4/25/21 laptop Week 5 Purchase
3 3/15/21 - - Week 1 Purchase <--- Requested
3 3/15/21 - - Week 2 Purchase <--- Requested
3 3/15/21 4/10/21 dirt bike Week 3 Purchase
3 3/15/21 - - Week 4 Purchase <--- Requested
3 3/15/21 - - Week 5 Purchase <--- Requested
3 3/15/21 5/10/21 towel Week 6 Purchase
首先，我使用recursive查询生成所有周(从第1周到第10周)。然后我制作一个CROSS JOIN来获得每个客户的所有生成周数。最后，我使用LEFT JOIN将all_weeks_by_cust子查询与customers_table表连接:
WITH RECURSIVE weeks(start, finish, week) AS (
SELECT 1, 10, 1
UNION ALL
SELECT finish + 1, finish + 10, week + 1
FROM weeks
WHERE week < 10),
all_cust AS (SELECT cust_id, MIN(cust_reg_date) AS cust_reg_date
FROM customers_table
GROUP BY cust_id),
all_weeks_by_cust AS (SELECT *
FROM weeks
CROSS JOIN all_cust)
SELECT cw.cust_id, cw.cust_reg_date, ct.cust_purchase_date, ct.purchase_made,
'Week ' || cw.week || ' Purchase' AS week_purchase
FROM all_weeks_by_cust cw
LEFT JOIN customers_table ct ON cw.cust_id = ct.cust_id AND ct.cust_purchase_date BETWEEN (ct.cust_reg_date + cw.start * INTERVAL '1 day') AND (ct.cust_reg_date + cw.finish * INTERVAL '1 day')
ORDER BY cw.cust_id, cw.week;
对于客户1，使用来自问题的示例数据:
week_purchase