应用SUM(日期介于日期1和日期2之间)

Question

我的表当前如下所示：

+---------+---------------+------------+------------------+
| Segment |    Product    |  Pre_Date  |    ON_Prepaid    |
+---------+---------------+------------+------------------+
| RB      | 01. Auto Loan | 2020-01-01 | 10645976180.0000 |
| RB      | 01. Auto Loan | 2020-01-02 |  4489547174.0000 |
| RB      | 01. Auto Loan | 2020-01-03 |  1853117000.0000 |
| RB      | 01. Auto Loan | 2020-01-04 |  9350258448.0000 |
+---------+---------------+------------+------------------+

我试图对7天内的"ON_Prepaid"值求和，比如说从"2020-01-01"到"2020-01:07"。以下是我尝试过的

drop table if exists ##Prepay_summary_cash
select *,
[1W_Prepaid] = sum(ON_Prepaid) over (partition by SEGMENT, PRODUCT order by PRE_DATE rows between 1 following and 7 following), 
[2W_Prepaid] = sum(ON_Prepaid) over (partition by SEGMENT, PRODUCT order by PRE_DATE rows between 8 following and 14 following),
[3W_Prepaid] = sum(ON_Prepaid) over (partition by SEGMENT, PRODUCT order by PRE_DATE rows between 15 following and 21 following),
[1M_Prepaid] = sum(ON_Prepaid) over (partition by SEGMENT, PRODUCT order by PRE_DATE rows between 22 following and 30 following),
[1.5M_Prepaid] = sum(ON_Prepaid) over (partition by SEGMENT, PRODUCT order by PRE_DATE rows between 31 following and 45 following),
[2M_Prepaid] = sum(ON_Prepaid) over (partition by SEGMENT, PRODUCT order by PRE_DATE rows between 46 following and 60 following),
[3M_Prepaid] = sum(ON_Prepaid) over (partition by SEGMENT, PRODUCT order by PRE_DATE rows between 61 following and 90 following),
[6M_Prepaid] = sum(ON_Prepaid) over (partition by SEGMENT, PRODUCT order by PRE_DATE rows between 91 following and 181 following)
into ##Prepay_summary_cash 
from ##Prepay1

如果日期是连续的，事情应该是好的；然而，"Pre_Date"中有一些缺失的日子(你知道银行周日不工作，等等(。

所以我正在尝试类似的东西

[1W] = SUM(ON_Prepaid) over (where Pre_date between dateadd(d,1,Pre_date) and dateadd(d,7,Pre_date))

诸如此类的事情。因此，如果2020-01-05本身没有记录，结果应该只对2020年1月1日、2日、3日、4日、6日、7日的日期求和，而不是1、2、3、4、6、7、8(8是因为"后面的第7行"(。例如，我在30天左右的时间里有缺失的记录，那么这30天应该加起来为0。因此，45天应该只返回15天的值。我试着在论坛上到处查找，但答案都不够。你们能帮帮我吗？或者将我链接到一个问题已经解决的线程。

非常感谢。

Answer 1

如果日期是连续的，情况应该会很好

然后使它们连续。将您的真实数据(分组为每天一行(左键连接到您的日历表上(制作一个日历表，或使用递归cte从X生成360个日期的列表(，您的查询将计算出

WITH d as 
( 
SELECT * 
FROM 
(
SELECT * 
FROM cal 
CROSS JOIN 
(SELECT DISTINCT segment s, product p FROM ##Prepay1) x
) c
LEFT JOIN ##Prepay1 p 
ON 
c.d = p.pre_date AND 
c.segment = p.segment AND 
c.product = p.product
WHERE 
c.d BETWEEN '2020-01-01' AND '2021-01-01' -- date range on c.d not c.pre_date
)
--use d.d/s/p not d.pre_date/segment/product in your query (sometimes the latter are null)
select *,
[1W_Prepaid] = sum(ON_Prepaid) over (partition by s, s order by d.d rows between 1 following and 7 following), 
...

CAL只是一个包含单列日期的表，每天一个，没有时间，可以扩展到过去/未来的1000天

希望注意的是，月份有可变的天数，所以6M有点用词不当。。最好将月份称为180D、90D等

还需要指出的是，您的查询会按行将数据划分为多个组。如果您想在该行日期后180天内进行汇总，您需要提取一年的数据，以便在第180行(6月(上可以汇总12月的数据(12月为6月后的6个月(

如果您想将查询限制为仅显示到6月(但包括6月后6个月的汇总数据(，则需要将其重新包装到子查询中。你不能"其中在jan和jun之间"；在进行求和的查询中，因为where子句在window子句之前完成(这样做将在求和之前删除dec数据(

其他一些数据库让这变得更容易，Oracle和Postgres跃然纸上；它们可以在其他行的值与当前行的值相距一定距离的范围内执行求和。SQL server只支持基于行的索引而不是其值的距离(基于值的距离支持仅限于"具有相同值的行"，而不是"具有比当前行高或低的值n的行"(。我想可以通过交叉应用或选择中的协调子来满足这一要求，尽管我会仔细检查性能。。

SELECT *, 
(SELECT SUM(tt.a) FROM x tt WHERE t.x = tt.x AND tt.y = t.y AND tt.z BETWEEN DATEADD(d, 1, t.z) AND DATEADD(d, 7, t.z) AS 1W
FROM
x t