正在运行作为子查询一部分的SQL列的合计

Question

这里有很多帖子描述了如何进行运行汇总，但我面临的情况是，运行汇总需要是使用子查询计算的列的汇总(这意味着我当前的ORDER BY导致查询失败(

我有一个表格，显示每个时间段的金额，类似于这样：

TimePeriod   Amount
2022-03-31   396
2022-03-31   16
2022-03-31   84
2021-12-31   842
2021-12-31   57
2021-09-30   652
2021-09-30   25
2021-09-30   173

在我的查询中，我需要找到每个时间段的总百分比。我所做的是：

SELECT 
TimePeriod,
SUM(Amount) AS 'Total Per Period', 
CAST( ROUND( SUM(Amount)/(SELECT SUM(Amount) FROM MyDatabase.MyTable),3) AS DECIMAL(12,3)) AS 'Percentage of Total'
FROM
MyDatabase.MyTable
GROUP BY
TimePeriod
ORDER BY
TimePeriod DESC

这给了我一个正确的输出，就像这样：

TimePeriod   Total per Period   Percentage of total
2022-03-31   496                0.221
2021-12-31   899                0.400
2021-09-30   850                0.379

我想做的是添加一个"百分比"列的运行总数，类似于：

TimePeriod   Total per Period   Percentage of total   Running total percentage
2022-03-31   496                0.221                 0.221
2021-12-31   899                0.400                 0.621
2021-09-30   850                0.379                 1.000

我试图做的是首先将它添加到第一个SELECT子句中，但这不起作用，因为它是一个只存在于我的查询中的列。然后我试着做了一个选择，就像这样：

SELECT
TimePeriod,
'Total Per Period',
'Percentage of Total',
SUM('Percentage of Total') OVER (ORDER BY TimePeriod)
FROM
(SELECT 
TimePeriod,
SUM(Amount) AS 'Total Per Period', 
CAST( ROUND( SUM(Amount)/(SELECT SUM(Amount) FROM MyDatabase.MyTable),3) AS DECIMAL(12,3)) AS 'Percentage of Total'
FROM
MyDatabase.MyTable
GROUP BY
TimePeriod
ORDER BY
TimePeriod DESC)

这会抛出一个错误，指出子查询中不允许使用最后一个ORDER BY。相反，删除ORDER BY表示语法不正确。我猜问题是我有一个子查询引用了一个子查询结果，但我不确定如何解决这个问题。我的查询中似乎缺少什么？

Answer 1

您有一些语法错误，以及一些需要改进的地方：

• 如另一个答案中所述，派生表需要一个别名
• 如果需要，请使用[]引用列名(最好一开始就没有这样的列名(
• 在派生表或子查询中不能有ORDER BY，这样做也没有意义
• 您可以将SELECT SUM子查询替换为SUM(SUM) OVER ()窗口函数
• 如果TimePeriod可能有重复项，则运行的总窗口函数必须有ROWS UNBOUNDED PRECEDING。它也更快
• 因为您是按TimePeriod DESC排序的，所以以与主ORDER BY相同的顺序进行运行总计可能会更快，但使用ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING会得到相同的结果。好处是查询计划中少了一个排序

SELECT
TimePeriod,
[Total Per Period],
[Percentage of Total],
SUM([Percentage of Total]) OVER (ORDER BY TimePeriod DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM
(SELECT 
TimePeriod,
SUM(Amount) AS [Total Per Period],
CAST( ROUND( SUM(Amount) / SUM(SUM(Amount)) OVER () , 3) AS DECIMAL(12,3)) AS [Percentage of Total]
FROM
MyDatabase.MyTable
GROUP BY
TimePeriod
) t
ORDER BY
TimePeriod DESC;

一个进一步的改进是将整个事情合并为一个级别：

SELECT
TimePeriod,
SUM(Amount) AS [Total Per Period],
CAST( ROUND(
SUM(Amount) /
SUM(SUM(Amount)) OVER ()
, 3) AS DECIMAL(12,3)) AS [Percentage of Total],
CAST( ROUND(
SUM(SUM(Amount)) OVER (ORDER BY TimePeriod DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) /
SUM(SUM(Amount)) OVER ()
, 3) AS DECIMAL(12,3))
FROM
MyDatabase.MyTable
GROUP BY
TimePeriod
ORDER BY
TimePeriod DESC;

请注意，由于四舍五入，结果可能略有不同。

SQL Fiddle

Answer 2

您可能需要使用[]来包含列名，而不是'，后者意味着字符串值，我们还需要给子查询一个别名。

SELECT
TimePeriod,
[Total Per Period],
[Percentage of Total],
SUM([Percentage of Total]) OVER (ORDER BY TimePeriod)
FROM
(SELECT 
TimePeriod,
SUM(Amount) AS 'Total Per Period', 
CAST( ROUND( SUM(Amount)/(SELECT SUM(Amount) FROM MyDatabase.MyTable),3) AS DECIMAL(12,3)) AS 'Percentage of Total'
FROM
MyDatabase.MyTable
GROUP BY
TimePeriod) t1
ORDER BY
TimePeriod DESC

Answer 3

这是我对您问题的解决方案。我在Oracle中解决它。

with flo as (
select
timeperiod, 
sum(amount) as amount,
cast((sum(amount) / (select sum(amount) from da ))as decimal(10,6)) *100.0 as percent_of_total
from  yourtable
group by timeperiod
)
select
timeperiod,
amount,
percent_of_total,
sum(percent_of_total) over (order by timeperiod desc ) as running
from flo;