在資料中顯示缺失的周

我有一張表，分別存盤客戶的注冊時間和購買時間。

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

uj5u.com熱心網友回復：

首先，我使用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 的輸出以及問題中的示例資料：

您需要生成與客戶注冊和上次購買之間的最大差異一樣多的周數。在我的示例中，我使用了 10 周，因此客戶 3 的最后一次購買不在報告中，因為他在第 11 周進行了此次購買。

標籤：sql PostgreSQL

上一篇：聚合查詢結果

下一篇：在postgresql中將int轉換為字串？