SQL:包括日期在内的多个日期的累计行数

Question

我有以下格式的客户购买表:

---------------------------------------------
| customer_id | purchase_id | purchase_date |
---------------------------------------------
| 001         | 001         | 01-01-2018    |
| 001         | 002         | 02-01-2018    |
| 001         | 005         | 09-01-2018    |
| 002         | 003         | 04-01-2018    |
| 002         | 004         | 08-01-2018    |
| 002         | 006         | 10-01-2018    |
---------------------------------------------

我想编写一个 SQL 查询来计算每个客户每天的累计购买次数(即客户在表中有多少行，purchase_date 到并包括该日期)。

所需的输出如下:

-------------------------------------------
| customer_id | date        | # purchases |
-------------------------------------------
| 001         | 01-01-2018  | 1           |
| 001         | 02-01-2018  | 2           |
| 001         | 03-01-2018  | 2           |
| 001         | 04-01-2018  | 2           |
| 001         | 05-01-2018  | 2           |
| 001         | 06-01-2018  | 2           |
| 001         | 07-01-2018  | 2           |
| 001         | 08-01-2018  | 2           |
| 001         | 09-01-2018  | 3           |
| 001         | 10-01-2018  | 3           |
| 002         | 01-01-2018  | 0           |
| 002         | 02-01-2018  | 0           |
| 002         | 03-01-2018  | 0           |
| 002         | 04-01-2018  | 1           |
| 002         | 05-01-2018  | 1           |
| 002         | 06-01-2018  | 1           |
| 002         | 07-01-2018  | 1           |
| 002         | 08-01-2018  | 2           |
| 002         | 09-01-2018  | 2           |
| 002         | 10-01-2018  | 3           |
-------------------------------------------

获得此输出的最有效方法是什么？

注意:运行 SQL Server 2016

Answer 1

窗口函数很好地解决了这个问题。

select customer_id, 
purchase_date, 
lead(purchase_date) over (partition by customer_id order by purchase_id) as next_purchase,
count(*) over (partition by customer_id order by purchase_id) as purchases
from purchases
order by customer_id,
purchase_date

这将输出以下结果:

-------------------------------------------------------------
| customer_id | purchase_date | next_purchase | purchases   |
-------------------------------------------------------------
| 001         | 01-01-2018    | 02-01-2018    | 1           |
| 001         | 02-01-2018    | 09-01-2018    | 2           |
| 001         | 09-01-2018    | null          | 3           |
| 002         | 04-01-2018    | 08-01-2018    | 1           |
| 002         | 08-01-2018    | 10-01-2018    | 2           |
| 002         | 10-01-2018    | null          | 3           |
-------------------------------------------------------------

如果您想填补空白，请使用 CTE 生成您可以加入的日期列表。根据您所需的输出，我假设您希望在购买表中的最短和最长日期之间进行报告。另外，我假设您每月报告一次。

--Return First of Month for minimum and maximum purchase date since we're reporting by month
declare @start_date date = dateadd(m, -1, dateadd(d, 1, eomonth((select min(purchase_date) from purchases))));
declare @end_date date = dateadd(m, -1, dateadd(d, 1, eomonth((select max(purchase_date) from purchases))));

--Use recursive CTE to create stream of dates for each month between start and end date
with dates as (
    select @start_date as report_date
    union all
    select dateadd(m, 1, report_date)
    from dates
    where dateadd(m, 1, report_date) <= @end_date
)

select 
p.customer_id,
d.report_date,
p.purchases
from 
    (
        select customer_id, 
        purchase_date, 
        lead(purchase_date) over (partition by customer_id order by purchase_id) as next_purchase,
        count(*) over (partition by customer_id order by purchase_id) as purchases
        from purchases
        order by customer_id,
        purchase_date
    ) p
join dates d
    on d.report_date >= p.purchase_date
    and (d.report_date < p.next_purchase or d.next_purchase is null)

最终输出:

---------------------------------------------
| customer_id | purchase_date | purchases   |
---------------------------------------------
| 001         | 01-01-2018    | 1           |
| 001         | 02-01-2018    | 2           |
| 001         | 03-01-2018    | 2           |
| 001         | 04-01-2018    | 2           |
| 001         | 05-01-2018    | 2           |
| 001         | 06-01-2018    | 2           |
| 001         | 07-01-2018    | 2           |
| 001         | 08-01-2018    | 2           |
| 001         | 09-01-2018    | 3           |
| 001         | 10-01-2018    | 3           |
| 002         | 04-01-2018    | 1           |
| 002         | 05-01-2018    | 1           |
| 002         | 06-01-2018    | 1           |
| 002         | 07-01-2018    | 1           |
| 002         | 08-01-2018    | 2           |
| 002         | 09-01-2018    | 2           |
| 002         | 10-01-2018    | 3           |
---------------------------------------------

我们的输出表之间的唯一区别是我的方法不会开始向客户展示，直到他们至少进行了一次购买。此外，此方法假设您的购买表每个客户每月有 1 条记录。由于不太可能，您应该首先按月进行团购。