选择行直到运行总和达到特定值

Question

我有以下数据：

DECLARE @t TABLE (usr VARCHAR(100), dt DATE, amount INT);
INSERT INTO @t VALUES
('a', '2018-01-01', 100), -- 100
('a', '2018-02-01', 100), -- 200
('a', '2018-03-01', 100), -- 300
('a', '2018-04-01', 100), -- 400
('a', '2018-05-01', 100), -- 500
('b', '2018-01-01', 150), -- 150
('b', '2018-02-01', 150), -- 300
('b', '2018-03-01', 150), -- 450
('b', '2018-04-01', 150), -- 600
('b', '2018-05-01', 150); -- 750

我想选择行直到数量的累计总和达到指定值，比如300或301（用户变量或列）。但是有以下要求：

• 对于300，我想选择a的前3行和b的前2行
• 对于301，我想选择a的前4行和b的前3行

这应该很简单，但我找到的解决方案无法处理第二种情况。

- Salman A

---

金额是如何从100增加到150的？你是怎么得出300/301的？细节缺失。 - MEdwin

amount 是否保证为非负数？即运行总数永远不会下降？ - Martin Smith

@MartinSmith 很有趣。假设它总是正数，但如果能处理负值就更好了。 - Salman A

你需要定义如何处理它。它应该返回所有行，直到运行总数首次达到300或如果总数再次降至300以下，则返回后续行。 - Martin Smith

@MartinSmith 假设它是贪心的，即选择尽可能多的行。 - Salman A

Answer 1

DECLARE @t TABLE (usr VARCHAR(100), dt DATE, amount INT);
INSERT INTO @t VALUES
('a', '2018-01-01', 100), -- 100
('a', '2018-02-01', 100), -- 200
('a', '2018-03-01', 100), -- 300
('a', '2018-04-01', 100), -- 400
('a', '2018-05-01', 100), -- 500
('b', '2018-01-01', 150), -- 150
('b', '2018-02-01', 150), -- 300
('b', '2018-03-01', 150), -- 450
('b', '2018-04-01', 150), -- 600
('b', '2018-05-01', 150); -- 750

DECLARE @Total INT = 301;

WITH cte AS
(
    SELECT *, SUM(amount) OVER (PARTITION BY usr ORDER BY dt) AS RunTotal
    FROM @t
)
SELECT *
FROM   cte
WHERE  cte.RunTotal - cte.amount < @Total -- running total for previous row is less
                                          -- than @Total then include current row

- HoneyBadger

---

我在 order by dt 附近遇到了一个异常。 - Amit chauhan

在我的SQL Server 2017 Express（14.0.1000.169）上运行良好 - 你使用的是哪个版本？我认为窗口函数（OVER）是在SQL 2008中添加的，所以如果您使用的是非常旧的版本，可能无法使用它们？ - 3N1GM4

@3N1GM4 我正在使用 SSMS 2017。 - Amit chauhan

@Amitchauhan - 使用SSMS 2017并不意味着底层的数据库引擎就是那个版本。如果你运行select @@version，会得到什么结果？ - 3N1GM4

@3N1GM4，这是微软 SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 版本。也许这就是我遇到异常的原因。 - Amit chauhan

是的，我刚刚再次确认，这个功能在 SQL 2012 中已经全面添加了，所以这很可能是你遇到问题的原因。 - 3N1GM4

Answer 2

DECLARE @t TABLE (usr VARCHAR(100), dt DATE, amount INT);
INSERT INTO @t VALUES
('a', '2018-01-01', 100), -- 100
('a', '2018-02-01', 100), -- 200
('a', '2018-03-01', 100), -- 300
('a', '2018-04-01', 100), -- 400
('a', '2018-05-01', 100), -- 500
('b', '2018-01-01', 150), -- 150
('b', '2018-02-01', 150), -- 300
('b', '2018-03-01', 150), -- 450
('b', '2018-04-01', 150), -- 600
('b', '2018-05-01', 150); -- 750

declare @target int = 300;

with cte_RunningTotal as
(
    select  
        usr,
        dt,
        amount,
        sum(amount) over (partition by usr order by dt rows unbounded preceding) as runningTotal
    from @t 
)
select *
from cte_RunningTotal 
where runningTotal < @target + amount
order by usr, dt

- 3N1GM4