这里的很多帖子都描述了如何进行运行总计,但我面临的情况是运行总计需要是使用子查询计算的列的总计(这意味着我当前的 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 表示语法不正确。我猜问题是我有一个引用子查询结果的子查询,但我不确定如何解决这个问题。我的查询中似乎缺少什么?
您有一些语法错误,以及一些需要改进的地方:
[]
引用列名(最好一开始就没有这样的列名)。ORDER BY
在派生表或子查询中使用,这样做也没有意义。SELECT SUM
子查询。SUM(SUM) OVER ()
ROWS UNBOUNDED PRECEDING
如果TimePeriod
可能有重复。它也更快。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;
请注意,由于四舍五入,结果可能略有不同。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句