SQL查询为available_store_credits添加累计运行总额列的实现问题

错误原因 #

• 尝试1问题：同级查询中窗口函数执行优先级高于GROUP BY，直接在聚合查询中加窗口函数会统计未分组的原始行累计值，不符合需求；available_store_credits字段定义后漏写逗号，会触发语法报错；累计的是amount_in_cents原始值，未做除以100转元及四舍五入处理，和available_store_credits统计口径不一致。
• 尝试2问题：子查询嵌套逻辑冗余，行级计算的累计值按月份二次聚合会导致重复统计，最终输出数值偏大。

正确实现 #

优先推荐在完成所有聚合和表关联后再计算累计值，可避免FULL OUTER JOIN产生的空值、年月对齐问题，代码如下：

SELECT 
  total_orders,
  quantity,
  available_store_credits,
  -- 空值按0处理，按年月升序累加得到累计运行总额
  SUM(COALESCE(available_store_credits, 0)) OVER (ORDER BY COALESCE(orders_transactions.month, store_credit_results.month)) AS cumulative_available_credits
FROM
  (
  SELECT
    COUNT(orders.id) as total_orders,
    date_trunc('year', confirmed_at) as year,
    date_trunc('month', confirmed_at) as month,
    SUM( quantity ) as quantity
  FROM
    orders
    INNER JOIN (
      SELECT
        orders.id,
        sum(quantity) as quantity
      FROM
        orders
        INNER JOIN line_items ON line_items.order_id = orders.id
      WHERE
        orders.deleted_at IS NULL
        AND orders.status IN (
          'paid', 'packed', 'in_transit', 'delivered'
        )
      GROUP BY
        orders.id
    ) as order_quantity
      ON order_quantity.id = orders.id
  GROUP BY month, year) as orders_transactions
      
  FULL OUTER JOIN
  (
    SELECT
      date_trunc('year', created_at) as year,
      date_trunc('month', created_at) as month,
      SUM( ROUND( ( CASE WHEN amount_in_cents > 0 THEN amount_in_cents end) / 100, 2 )) AS store_credit_given,
      SUM( ROUND( amount_in_cents / 100, 2 )) AS available_store_credits
    FROM
      store_credit_transactions
    GROUP BY month, year
  ) as store_credit_results
    ON orders_transactions.month = store_credit_results.month
ORDER BY COALESCE(orders_transactions.month, store_credit_results.month)

如果仅需要统计有储值记录月份的累计，也可以把窗口函数放在储值聚合子查询的外层，实现如下：

SELECT 
  total_orders,
  quantity,
  available_store_credits,
  cum_amt
FROM
  (
  SELECT
    COUNT(orders.id) as total_orders,
    date_trunc('year', confirmed_at) as year,
    date_trunc('month', confirmed_at) as month,
    SUM( quantity ) as quantity
  FROM
    orders
    INNER JOIN (
      SELECT
        orders.id,
        sum(quantity) as quantity
      FROM
        orders
        INNER JOIN line_items ON line_items.order_id = orders.id
      WHERE
        orders.deleted_at IS NULL
        AND orders.status IN (
          'paid', 'packed', 'in_transit', 'delivered'
        )
      GROUP BY
        orders.id
    ) as order_quantity
      ON order_quantity.id = orders.id
  GROUP BY month, year) as orders_transactions
      
  FULL OUTER JOIN
  (
    SELECT 
      year,
      month,
      store_credit_given,
      available_store_credits,
      SUM(available_store_credits) OVER (ORDER BY year, month) AS cum_amt
    FROM (
      SELECT
        date_trunc('year', created_at) as year,
        date_trunc('month', created_at) as month,
        SUM( ROUND( ( CASE WHEN amount_in_cents > 0 THEN amount_in_cents end) / 100, 2 )) AS store_credit_given,
        SUM( ROUND( amount_in_cents / 100, 2 )) AS available_store_credits
      FROM
        store_credit_transactions
      GROUP BY month, year
    ) t
  ) as store_credit_results
    ON orders_transactions.month = store_credit_results.month

内容的提问来源于stack exchange，提问作者Hemant Patil