时间戳

首页 > 解决方案 > 应用 SUM（其中 date1 和 date2 之间的日期）

sql - 应用 SUM（其中 date1 和 date2 之间的日期）

问题描述

我的桌子目前看起来像这样：

+---------+---------------+------------+------------------+
| 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 天的值。我已经尝试在整个论坛中查找，但答案还不够。你们能帮帮我吗？或者将我链接到问题已经解决的线程。

非常感谢。

标签： sqlsql-servertsqlsum

解决方案

如果日期是连续的，事情应该没问题

然后让它们连续。将您的真实数据（分组为每天一行）加入您的日历表（制作一个，或使用递归 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 只是一个包含单列日期的表格，每天一个，没有时间，延伸到过去/未来 n 千天

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

还想指出，您的查询将您的数据按行划分为组。如果要在行日期后最多 180 天执行求和，则需要提取一年的数据，以便在第 180 行（6 月）上，您可以使用 12 月的数据来求和（从 6 月开始，12 月为 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

推荐阅读