SQL查询 - 查找超过累积比例的行

Question

假设我有一张数据表，长这样：

ItemNo    |    ItemCount   |    Proportion
------------------------------------------
1              3                0.15 
2              2                0.10
3              3                0.15
4              0                0.00
5              2                0.10
6              1                0.05
7              5                0.25
8              4                0.20

换句话说，总共有20个项目，每个ItemNo的累积比例之和为100％。表格行的排序在此处非常重要。

是否可能执行SQL查询而无需循环或游标来返回首个超过累积比例的ItemNo？

换句话说，如果我想检查的“比例”是35％，则超过该比例的第一行是ItemNo 3，因为0.15 + 0.10 + 0.15 = 0.40

同样，如果我想找到超过75％的第一行，则是ItemNo 7，因为直到该行所有Proportion的总和都小于0.75。

- Widor

Answer 1

select top 1
  t1.ItemNo
from
  MyTable t1
where
  ((select sum(t2.Proportion) from MyTable t2 where t2.ItemNo <= t1.ItemNo) >= 0.35)
order by
  t1.ItemNo

- njr101

Answer 2

一个经典的窗口函数：

SELECT * 
FROM   (
    SELECT ItemNo
          ,ItemCount
          ,sum(Proportion) OVER (ORDER BY ItemNo) AS running_sum
    FROM   tbl) y
WHERE  running_sum > 0.35
LIMIT  1;

适用于PostgreSQL等数据库。

或者，按照你似乎使用的tSQL符号：
SELECT TOP 1 *
FROM (
SELECT ItemNo
,ItemCount
,sum(Proportion) OVER (ORDER BY ItemNo) AS running_sum
FROM tbl) y
WHERE running_sum > 0.35;

如下评论所述，不适用于tSQL。

- Erwin Brandstetter

---

在Sql Server中使用语法上是否有微妙的差别？当我使用你的第二个例子时，会出现“Incorrect syntax near 'order'.”的错误提示。 - Widor

我可能错了，但我不认为在SQL Server中可以使用OVER子句生成累计总数，只能用于排名或分区内求和。请参见https://dev59.com/-XRA5IYBdhLWcg3wsgHq#861073。这就是我选择子查询的原因。 - njr101

@njreed.myopenid.com：噢，看起来你是对的，它不能与tSQL一起使用。好链接。我把tSQL版本划掉了。请记得在下一个问题中披露你的关系数据库管理系统。 :) - Erwin Brandstetter

@Erwin 感谢您的澄清。没有指定RDBMS是有意为之，以便获得通用的SQL解决方案，而无需依赖于特定供应商的功能。 - Widor