作为子查询一部分的 SQL 列的运行总计

Question

这里有很多帖子描述了如何进行运行总计，但是我面对的是，运行总计需要是使用子问题计算的列的列（这意味着我的当前订单会导致查询失败）

我有一个显示每个时间段数量的表格，类似的时间：

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
    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)

这引发了错误，说出最后一个顺序不允许在子征服中。取而代之的是说语法不正确。我猜想问题是我有一个子查询参考子查询结果，但我不确定如何处理这个结果。我的查询似乎缺少什么？

Answer 1

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

• 如其他答案中所述，派生表需要一个别名
• 使用[]在必要时报价列名（最好没有此类首先是列名）。
• 您不能在派生的表或子查询内通过进行顺序，也不有意义。
• 您可以将选择Sum子查询（ sum（sum））上的（）窗口函数。
• 运行的总窗口函数必须具有行无界的，如果timePeriod可能具有重复。它也更快。
• 因为您是通过timePeriod desc订购的，所以以与Main 订单相同的顺序进行运行总数可能更快无限之后的可以符合相同的结果。好处是查询计划中的一种。

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小提琴

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;