如何查找列中所有值的总和达到指定值的行？在表

Question

鉴于数据与下面的模式：

CREATE TABLE purchases (timestamp DATETIME, quantity INT) 

我想找到的时间点（即该行的时间戳），其中在数量列中的值的总和通过一定的阈值值。

这是在MS SQL Server中，理想情况下我想避免使用游标，如果可能的话。

Answer 1

SELECT timestamp, SUM(quantity) 
FROM purchases 
GROUP BY timestamp 
HAVING SUM(quantity) > someValue 

或者如果它是一个运行总和

SELECT a1.timestamp 
FROM purchases a1, purchases a2 
WHERE a1.quantity >= a2.quantity or (a1.quantity=a2.quantity and a1.timestamp = a2.timestamp) 
GROUP BY a1.timestamp, a1.quantity 
having SUM(a2.quantity) >= someValue 
ORDER BY a1.timestamp ASC 
LIMIT 1 

Answer 2

你可以得到最小的时间戳，其中先前值的总和大于阈值：

select min(timestamp) 
from purchases p 
where (
    select sum(x.quantity) 
    from purchases x 
    where x.timestamp < p.timestamp 
) > @threshold 

然而，这不是一个非常有效的查询，所以最好使用游标。

Answer 3

在SQL Server 2005 +，你可以试试这个：

;WITH numbered AS (
    SELECT 
    timestamp, 
    quantity, 
    rownum = ROW_NUMBER() OVER (ORDER BY timestamp) 
    FROM purchases 
), 
recursive AS (
    SELECT 
    timestamp, 
    quantity, 
    rownum, 
    runningsum = quantity, 
    passed = CASE WHEN n.quantity < @threshold THEN 0 ELSE 1 END 
    FROM numbered 
    UNION ALL 
    SELECT 
    n.timestamp, 
    n.quantity, 
    n.rownum, 
    runningsum = n.quantity + r.runningsum, 
    passed = CASE WHEN n.quantity + r.runningsum < @threshold THEN 0 ELSE 1 END 
    FROM numbered n 
    INNER JOIN recursive r ON n.rownum = r.rownum + 1 
) 
SELECT MIN(timestamp) 
FROM recursive 
WHERE passed = 1 

基本上相同@Guffa的解决方案，只是利用热膨胀系数，以避免需要三角形的加入。