SQL -具有多个日期的条件窗口

Question

Table A

| Customer | Transaction_Date | Complaint_Date |
|----------|------------------|----------------|
| A        | 1                | 2              |
| A        | 1                | 3              |
| A        | 2                | 3              |
| B        | 1                | 2              |
| B        | 1                | 2              |
| B        | 2                | 3              |
| C        | 2                | 3              |

对于上面的表格，我想计算每个客户在过去x天内的投诉数量，并考虑到投诉发生的时间。即，只有在交易日之前发生的投诉才应计入

i.e

| Customer | Transaction_Date | Complaint_Date | Count |
|----------|------------------|----------------|-------|
| A        | 1                | 2              | 0     |
| A        | 1                | 3              | 0     |
| A        | 2                | 3              | 1     |
| B        | 1                | 2              | 0     |
| B        | 1                | 2              | 0     |
| B        | 2                | 3              | 2     |
| C        | 2                | 3              | 0     |

目前，我正在计算交易(记录)的数量，将客户、交易日期和投诉日期划分为一个分区，如果投诉日期小于交易日期且交易日期在x天范围内，则连接回客户和交易日期。然后，我计算x天窗口内的最终总和，按客户分区，因为上面的结果是每个交易日期和客户的计数

Answer 1

我认为您可以通过使用一个简单的where条件来做到这一点。假设日期字段以日期格式存储，计算最近10天(这是在SQL server中)：

select Customer,count(*)
from table 
where Complaint_date<transaction_date
      and Complaint_date between GETDATE()-10 and GETDATE()
Group by Customer

希望这能有所帮助。

编辑:您可以尝试在此处执行自连接：

;with tableA as
(select 'A' as customer,1 as Transaction_date,2 as Complaint_date union all
select 'A',1,3 union all
select 'A',2,3 union all
select 'B',1,2 union all
select 'B',1,2 union all
select 'B',2,3 union all
select 'C',2,3)

select a.Customer,a.transaction_date,count(b.Complaint_date) as cnt
from tableA a
left join tableA b on a.customer=b.customer
      and a.transaction_date>=b.Complaint_date
Group by a.Customer,a.transaction_date

这将生成以下输出：

Customer Transaction_Date Cnt
A        1                0
A        2                1
B        1                0
B        2                2
C        2                0

如果您想要与答案中提到的输出相同的输出，可以将此输出连接回原始表：

;with tableA as
(select 'A' as customer,1 as Transaction_date,2 as Complaint_date union all
select 'A',1,3 union all
select 'A',2,3 union all
select 'B',1,2 union all
select 'B',1,2 union all
select 'B',2,3 union all
select 'C',2,3)

select x.*,y.cnt
from tableA x
inner join
(select a.Customer,a.transaction_date,count(b.Complaint_date) as cnt
from tableA a
left join tableA b on a.customer=b.customer
      and a.transaction_date>=b.Complaint_date
Group by a.Customer,a.transaction_date) y
ON x.customer=y.customer and x.transaction_date=y.Transaction_date