如何用SQL按年周统计持有条目总量？是否需借助PHP？

算力阿峰

2026-8-9

问题描述

我可以通过以下SQL查询获取所有持有条目的总量：

select
  SUM(quantity) AS sumQuantity
from
  data

现在我想统计每一周的持有总量，但尝试了下面的查询后，只能得到每周的增减量。请问能不能只用SQL实现这个需求，还是必须借助PHP？

select
  SUM(quantity) AS sumQuantity,
  CONCAT(
    YEAR(entry_date),
    WEEK(entry_date)
  ) AS yearWeek
from
  data
group by
  yearWeek
order by
  yearWeek desc

解决方案

完全可以只用SQL实现，核心是计算累计总和（滚动求和）——你当前的查询只是按周汇总了当周的quantity变化，而持有总量是从初始到该周的累计值。

根据不同的SQL数据库，实现方式略有差异，以下是主流数据库的写法：

MySQL 8.0+ / PostgreSQL / SQL Server 2012+

使用窗口函数SUM() OVER()来计算累计值：

WITH weekly_changes AS (
  SELECT
    CONCAT(YEAR(entry_date), '-', WEEK(entry_date)) AS yearWeek,
    SUM(quantity) AS weekly_change
  FROM data
  GROUP BY yearWeek
  ORDER BY yearWeek
)
SELECT
  yearWeek,
  SUM(weekly_change) OVER(ORDER BY yearWeek) AS total_quantity
FROM weekly_changes;

先用CTE按周汇总当周的增减量
再通过窗口函数对每周的变化量做累计求和，得到截止到该周的持有总量

MySQL 5.x（不支持窗口函数）

可以用自连接来实现累计求和：

SELECT
  t1.yearWeek,
  SUM(t2.weekly_change) AS total_quantity
FROM (
  SELECT
    CONCAT(YEAR(entry_date), WEEK(entry_date)) AS yearWeek,
    SUM(quantity) AS weekly_change
  FROM data
  GROUP BY yearWeek
) t1
JOIN (
  SELECT
    CONCAT(YEAR(entry_date), WEEK(entry_date)) AS yearWeek,
    SUM(quantity) AS weekly_change
  FROM data
  GROUP BY yearWeek
) t2 ON t2.yearWeek <= t1.yearWeek
GROUP BY t1.yearWeek
ORDER BY t1.yearWeek;