每個報告日期的客戶總數（不包括工會）

我想顯示運行此報告，其中顯示每個報告日期的客戶總數。這是我需要資料的樣子：

我的原始資料集如下所示（請參閱查詢）：為了計算客戶數量。我需要使用開始和結束日期：如果 Start_Date>reporting_date 和 End_Date<=reporting_date 則算作客戶。

我能夠開發一個腳本，但它只給了我一個報告日期的客戶總數。

select '2022-10-31' reporting_date, count(case when Start_Date>'2022-10-31' and End_Date<='2022-10-31' then Customer_ID end)

from (values ('2022-10-14','2022-8-19','0010Y654012P6KuQAK')
           , ('2022-3-15','2022-9-14','0011v65402PoSpVAAV')
           , ('2021-1-11','2022-10-11','0010Y654012P6DuQAK')
           , ('2022-12-1','2022-5-14','0011v65402u7muLAAQ')
           , ('2021-1-30','2022-3-14','0010Y654012P6DuQAK')
           , ('2022-10-31','2022-2-14','0010Y654012P6PJQA0')
           , ('2021-10-31','US','0010Y654012P6PJQA0')
           , ('2021-5-31','2022-5-14','0011v65402x8cjqAAA')
           , ('2022-6-2','2022-1-13','0010Y654016OqkJQAS')
           , ('2022-1-1','2022-11-11','0010Y654016OqIaQAK')

    ) a(Start_Date ,End_Date ,Customer_ID)

有沒有一種方法可以通過交叉加入或其他變通方法來修改每個報告日期的客戶總數，而無需進行很多聯合

select '2022-10-31' reporting_date, count(case when Start_Date>'2022-10-31' and End_Date<='2022-10-31' then Customer_ID end)

from (values ('2022-10-14','2022-8-19','0010Y654012P6KuQAK')
           , ('2022-3-15','2022-9-14','0011v65402PoSpVAAV')
           , ('2021-1-11','2022-10-11','0010Y654012P6DuQAK')
           , ('2022-12-1','2022-5-14','0011v65402u7muLAAQ')
           , ('2021-1-30','2022-3-14','0010Y654012P6DuQAK')
           , ('2022-10-31','2022-2-14','0010Y654012P6PJQA0')
           , ('2021-10-31','US','0010Y654012P6PJQA0')
           , ('2021-5-31','2022-5-14','0011v65402x8cjqAAA')
           , ('2022-6-2','2022-1-13','0010Y654016OqkJQAS')
           , ('2022-1-1','2022-11-11','0010Y654016OqIaQAK')

    ) a(Start_Date ,End_Date ,Customer_ID)

UNION ALL 

select '2022-9-30' reporting_date, count(case when Start_Date>'2022-9-301' and End_Date<='2022-9-30' then Customer_ID end)

from (values ('2022-10-14','2022-8-19','0010Y654012P6KuQAK')
           , ('2022-3-15','2022-9-14','0011v65402PoSpVAAV')
           , ('2021-1-11','2022-10-11','0010Y654012P6DuQAK')
           , ('2022-12-1','2022-5-14','0011v65402u7muLAAQ')
           , ('2021-1-30','2022-3-14','0010Y654012P6DuQAK')
           , ('2022-10-31','2022-2-14','0010Y654012P6PJQA0')
           , ('2021-10-31','US','0010Y654012P6PJQA0')
           , ('2021-5-31','2022-5-14','0011v65402x8cjqAAA')
           , ('2022-6-2','2022-1-13','0010Y654016OqkJQAS')
           , ('2022-1-1','2022-11-11','0010Y654016OqIaQAK')

    ) a(Start_Date ,End_Date ,Customer_ID)

     

uj5u.com熱心網友回復：

可以將日期范圍作為單獨的表/子查詢提供，連接到實際資料并執行分組：

select s.start_d, s.end_d, COUNT(Customer_ID) AS total
FROM (SELECT '2022-10-31'::DATE, '2022-10-31'::DATE
      UNION SELECT '2022-09-30', '2022-09-30') 
    AS s(start_d, end_d)
LEFT JOIN (values ('2022-10-14','2022-8-19','0010Y654012P6KuQAK')
           , ('2022-3-15','2022-9-14','0011v65402PoSpVAAV')
           , ('2021-1-11','2022-10-11','0010Y654012P6DuQAK')
           , ('2022-12-1','2022-5-14','0011v65402u7muLAAQ')
           , ('2021-1-30','2022-3-14','0010Y654012P6DuQAK')
           , ('2022-10-31','2022-2-14','0010Y654012P6PJQA0')
           , ('2021-10-31','2021-10-31','0010Y654012P6PJQA0')
           , ('2021-5-31','2022-5-14','0011v65402x8cjqAAA')
           , ('2022-6-2','2022-1-13','0010Y654016OqkJQAS')
           , ('2022-1-1','2022-11-11','0010Y654016OqIaQAK')

    ) a(Start_Date ,End_Date ,Customer_ID)
  ON a.Start_Date>s.start_d and a.End_Date<=s.end_d
GROUP BY s.start_d, s.end_d;

輸出：

標籤：sqlPostgreSQL雪花云数据平台

上一篇：如何在Postgreshstore列中找到成對的所有組合？

下一篇：PostgreSQL的MySQLISNULLSQL代碼等效性