UTC时间转换为YYYY-MM格式及月度累计求和SQL需求

问题分析 #

你的原SQL因按delivered_on::date和total_amount分组，再配合按日期排序的窗口函数，最终得到的是每日累计结果。要获取2021年1月整月的累计值，需调整分组逻辑与聚合方式。

修正后的SQL（获取1月当月总累计） #

SELECT
  DATE_TRUNC('month', delivered_on) AS month,
  SUM(total_amount) AS monthly_total_amount,
  COUNT(*) AS monthly_order_count
FROM orders
WHERE delivered_on BETWEEN '2021-01-01' AND '2021-01-31 23:59:59'
GROUP BY month;

关键调整说明 #

• 用DATE_TRUNC('month', delivered_on)直接将日期截断到月份维度，确保按整月分组
• 移除原SQL中冗余的DISTINCT和窗口函数，直接通过SUM()和COUNT()完成整月数据聚合
• 调整WHERE条件的结束时间为2021-01-31 23:59:59，避免UTC时间转换为日期时可能出现的漏统计问题（若业务逻辑仅需按截断后的日期范围筛选，也可保留原WHERE条件）

如果需要的是从年初到1月的累计值（而非1月当月总和），可使用嵌套窗口函数实现：

SELECT
  DATE_TRUNC('month', delivered_on) AS month,
  SUM(total_amount) AS monthly_total,
  SUM(SUM(total_amount)) OVER (ORDER BY DATE_TRUNC('month', delivered_on)) AS cumulative_total
FROM orders
WHERE delivered_on <= '2021-01-31 23:59:59'
GROUP BY month
ORDER BY month;

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