这里有很多帖子描述了如何进行运行总计,但我面临的情况是,运行总计需要是使用子查询计算的列(这意味着我当前的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 ,比如:

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,这样做也没有意义.
  • 可以用SUM(SUM) OVER ()窗口函数替换SELECT SUM子查询.
  • 如果TimePeriod可能有重复项,则运行的total window函数必须有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

Sql相关问答推荐

SUM(条件)在Oracle?

Group By子句返回太多行

用于动态查询情况的存储过程常识模式

通过 Select 值的顺序进行排序ClickHouse

为什么在postgres中,横向连接比相关子查询快?

如何在postgres函数中插入后返回布尔值?

直接加法(1+1)与聚合函数SUM(1+1)的区别是什么

返回UPSERT中的旧行值

将日期时间转换为日期格式

每小时 Select 1行

SQL Athena/prest判断值是否在嵌套的json数组中

如何向 mariadb 添加外键?

snowflake中的动态文件名生成

如何通过CROSS APPLY获取多级嵌套JSON属性的值?

更新表 A,然后将新值插入表 B(包含更新内容的历史日志(log))

如何根据另一列对行值进行分组?

当没有任何行存在时,将一个表中的行插入到另一个表中的更好方法

如何优化sql请求?

复制到比.从snowflake的外部stage表演中插入?

SQLite 中标识符周围的方括号是什么意思?