作為子查詢一部分的SQL列的運行總計

這里的很多帖子都描述了如何進行運行總計，但我遇到的情況是運行總計需要是使用子查詢計算的列的總計（這意味著我當前的 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 表示語法不正確。我猜問題是我有一個參考子查詢結果的子查詢，但我不確定如何解決這個問題。我的查詢中似乎缺少什么？

uj5u.com熱心網友回復：

您有一些語法錯誤，以及一些需要改進的地方：

• 如另一個答案中所述，派生表需要別名
• 如有必要，用于[]參考列名（最好一開始就沒有這樣的列名）。
• 您不能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;

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

SQL小提琴

uj5u.com熱心網友回復：

您可能需要使用[]包含列名而不是'表示字串值，我們還需要給子查詢一個別名。

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

uj5u.com熱心網友回復：

這是我對您的問題的解決方案。我在 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;

標籤：sql sql服务器 子查询 累积和

上一篇：無法決議XML

下一篇：按時間最近的記錄分組選擇