根据总计检索组的详细行

Question

我有一张看起来像这样的表格：

+------+------+------------------+
| item | val  |    timestamp     |
+------+------+------------------+
|  1   | 3.66 | 16-05-2011 09:17 | 
|  1   | 2.56 | 16-05-2011 09:47 | 
|  2   | 4.23 | 16-05-2011 09:37 | 
|  3   | 6.89 | 16-05-2011 11:26 | 
|  3   | 1.12 | 16-05-2011 12:11 |
|  3   | 4.56 | 16-05-2011 13:23 |
|  4   | 1.10 | 16-05-2011 14:11 |
|  4   | 9.79 | 16-05-2011 14:23 |
|  5   | 1.58 | 16-05-2011 15:27 |
|  5   | 0.80 | 16-05-2011 15:29 |
|  6   | 3.80 | 16-05-2011 15:29 |
+------+------+------------------+

所以，当天所有项目的总计：2011 年 5 月 16 日 是：40.09

现在我想检索 此列表中的哪些项目占总数的 80%。 让我举个例子：

• 总计：40.09
• 总计的 80%：32.07

从我想要检索分组的总金额中具有更多百分比权重的项目开始占总计 80% 的项目列表：

+------+------+
| item | val  |
+------+------+
|  3   | 12.57|
|  4   | 10.89|
|  1   |  6.22|
+------+------+

如您所见，结果集中的元素是按项目代码分组的元素，并从总计中具有较大百分比权重的元素开始降序排列，直到达到80% 阈值。

从第 2 项开始，这些项目将从结果集中被丢弃，因为它们超过了 80% 的阈值，因为：

12.57 + 10.89 + 6.22 + 4.23 > 32.07 (80 % of the grand total )

这不是作业，这是我被绊倒的真实上下文，我需要通过单个查询来获得结果...

查询应在 MySQL、SQL Server、PostgreSQL 上运行，无需修改或只需少量更改。

Answer 1

您可以使用单个查询来完成此操作：

WITH Total_Sum(overallTotal) as (SELECT SUM(val) 
                                 FROM dataTable), 

     Summed_Items(id, total) as (SELECT id, SUM(val)
                                 FROM dataTable
                                 GROUP BY id),

     Ordered_Sums(id, total, ord) as (SELECT id, total, 
                                         ROW_NUMBER() OVER(ORDER BY total DESC)
                                      FROM Summed_Items),

     Percent_List(id, itemTotal, ord, overallTotal) as (
                  SELECT id, total, ord, total
                  FROM Ordered_Sums
                  WHERE ord = 1
                  UNION ALL
                  SELECT b.id, b.total, b.ord, b.total + a.overallTotal
                  FROM Percent_List as a
                  JOIN Ordered_Sums as b
                  ON b.ord = a.ord + 1
                  JOIN Total_Sum as c
                  ON (c.overallTotal * .8) > (a.overallTotal + b.total))

SELECT id, itemTotal
FROM Percent_List

这将产生以下结果：

id      itemTotal
3       12.57  
4       10.89  
1       6.22  

请注意，这不适用于 mySQL（无 CTE），并且需要更新的版本postgreSQL 的工作（否则不支持 OLAP 函数）。 SQLServer 应该能够按原样运行该语句（我认为 - 这是在 DB2 上编写和测试的）。否则，您可能尝试将其转换为相关表连接等，但即使可能（存储过程或在那么高级语言可能是您唯一的选择）。

Answer 2

我不知道有什么方法可以通过单个查询来完成此操作；您可能必须创建一个存储过程。该过程的步骤如下所示：

1. 使用 SUM 计算当天的总计
2. 获取按 val DESC 排序的当天的各个记录
3. 保持运行循环浏览各个记录时的总计；只要运行总计 < 0.8 * grandtotal，将当前记录添加到您的列表中