如何对此变量进行分区，使其累积总数在过滤时保持不变

Question

我正在使用SQL Server 2012，并且针对表运行以下T-SQL查询。

SELECT ID
       ,StayDate
       ,Dateadd(day, -datepart(day,StayDate) + 1, StayDate) as 'MonthOfStay'
       ,DailyRateAmount
       ,SUM(DailyRateAmount) OVER (PARTITION BY ID) AS [CummulativeRateAmt]
FROM Table1
WHERE ID = 625

上述查询的输出如下：

ID     StayDate     MonthOfStay  DailyRateAmt CummulativeRateAmt
625    2018-09-28   2018-09-01    100              400
625    2018-09-29   2018-09-01    100              400
625    2018-09-30   2018-09-01    100              400
625    2018-10-01   2018-10-01    100              400

当我向此查询添加StayDate筛选器时，它会为我提供以下输出：

SELECT ID
       ,StayDate
       ,Dateadd(day, -datepart(day,StayDate) + 1, StayDate) as 'MonthOfStay'
       ,DailyRateAmount
       ,SUM(DailyRateAmount) OVER (PARTITION BY ID) AS [CummulativeRateAmt]
FROM Table1
WHERE ID = 625
AND StayDate between '2018-09-28' and '2018-09-30'

输出：

ID     StayDate     MonthOfStay  DailyRateAmt CummulativeRateAmt
625    2018-09-28   2018-09-01    100              300
625    2018-09-29   2018-09-01    100              300
625    2018-09-30   2018-09-01    100              300

我正在寻找此输出，其中CummulativeRateAmt列值显示每个ID的所有StayDates的总费率金额：

ID     StayDate     MonthOfStay  DailyRateAmt CummulativeRateAmt
625    2018-09-28   2018-09-01    100              400
625    2018-09-29   2018-09-01    100              400
625    2018-09-30   2018-09-01    100              400

我知道我可以嵌套上面的第一个查询，然后在嵌套后过滤StayDates。

但是，是否有一种分区语法可以让我无需嵌套我的T-SQL查询即可执行此操作？

Answer 1

您可以使用

CTE或Sub-query，然后筛选记录

WITH CTE AS (
 SELECT ID
       ,StayDate
       ,Dateadd(day, -datepart(day,StayDate) + 1, StayDate) as 'MonthOfStay'
       ,DailyRateAmount
       ,SUM(DailyRateAmount) OVER (PARTITION BY ID) AS [CummulativeRateAmt]
 FROM table t
 WHERE ID = 625
)
SELECT * FROM CTE 
WHERE StayDate between '2018-09-28' and '2018-09-30'

您也可以通过带有过滤 ID 的CROSS JOIN来获取总和

SELECT  ID, StayDate,
        Dateadd(day, -datepart(day,StayDate) + 1, StayDate) as 'MonthOfStay',
        DailyRateAmount,
        tt.CummulativeRateAmt
FROM table t CROSS JOIN (
      SELECT SUM(DailyRateAmount) CummulativeRateAmt FROM table WHERE ID = 625) tt
WHERE ID = 625 AND
       StayDate between '2018-09-28' and '2018-09-30'

Answer 2

尽管我建议使用 CTE 或子查询使用更明确的方法(遵循预期的逻辑更容易(，但您可以使用 TOP WITH TIES 执行所需的操作：

SELECT TOP (1) WITH TIES ID, StayDate,
       Dateadd(day, -datepart(day,StayDate) + 1, StayDate) as MonthOfStay,
       DailyRateAmount
       SUM(DailyRateAmount) OVER (PARTITION BY ID) AS CumulativeRateAmt
FROM Table1
WHERE ID = 625
ORDER BY (CASE WHEN StayDate between '2018-09-28' and '2018-09-30' THEN 1 ELSE 2 END);