没有工会的每个报告日期的客户总数

Question

我想要显示运行此报告，其中显示每个报告日期的客户总数。下面是我需要数据的样子：

​

​

我的原始数据集如下所示(请参阅查询)：以便计算客户数量。我需要使用开始日期和结束日期:如果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)

Answer 1

可以将日期范围作为单独的表/子查询提供，连接到实际数据并执行分组：

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;

输出：

​

​