能否按日期分组统计并组内求和？生产进度追踪SQL实现咨询

嘿，这两个问题我都熟，给你详细拆解下：

当然可以！这是数据库查询里非常基础且常用的操作，用GROUP BY配合聚合函数SUM()就能轻松实现。

举个简单的例子：假设你有一张记录物料流转的表production_flow，包含flow_date（流转日期）、state（状态）、count（变动数量）这几个字段，如果你想按日期分组，统计每天所有物料的总变动量，可以用这条SQL：

SELECT flow_date, SUM(count) AS total_daily_change
FROM production_flow
GROUP BY flow_date
ORDER BY flow_date;

如果需要更精细的统计——比如按日期+状态分组，统计每天每个状态下的物料变动总和，只需要把state也加入GROUP BY即可：

SELECT flow_date, state, SUM(count) AS total_change
FROM production_flow
GROUP BY flow_date, state
ORDER BY flow_date, state;

要实现你描述的那种动态追踪各状态物料数量的需求，核心是记录每次状态流转的变动，再通过累计计算得到每日的状态快照。我给你一套具体的实现方案：

第一步：数据库表设计 #

建议建两张表来存储数据：

1. 初始状态表（material_initial_state） #

用来记录物料的初始状态，结构如下：

比如你说的初始10个物料在state 0，就插入一条记录：

INSERT INTO material_initial_state (initial_date, state, count)
VALUES ('2018-02-10', 0, 10);

2. 状态流转记录表（material_flow） #

用来记录每次物料状态流转的变动，结构如下：

对应你描述的几次流转，插入这些记录：

-- 2018-02-11：4个从state 0到state 1
INSERT INTO material_flow (flow_date, from_state, to_state, count)
VALUES ('2018-02-11', 0, 1, 4);

-- 2018-02-12：2个从state 1到state 2
INSERT INTO material_flow (flow_date, from_state, to_state, count)
VALUES ('2018-02-12', 1, 2, 2);

-- 2018-02-13：3个从state 0到state 1（按示例补充）
INSERT INTO material_flow (flow_date, from_state, to_state, count)
VALUES ('2018-02-13', 0, 1, 3);

第二步：查询每日状态快照的SQL实现 #

用递归CTE和窗口函数可以轻松计算出每天各状态的物料数量，以下是PostgreSQL的示例代码（如果是MySQL等其他数据库，只需要调整日期生成的部分即可）：

WITH all_state_changes AS (
    -- 引入初始状态的变动（相当于第一天的“流入”）
    SELECT initial_date AS record_date, state, count AS change
    FROM material_initial_state
    UNION ALL
    -- 记录状态流转的“流入”（目标状态增加）
    SELECT flow_date, to_state, count AS change
    FROM material_flow
    UNION ALL
    -- 记录状态流转的“流出”（原状态减少）
    SELECT flow_date, from_state, -count AS change
    FROM material_flow
),
-- 按日期和状态汇总当天的总变动
daily_state_changes AS (
    SELECT record_date, state, SUM(change) AS daily_total
    FROM all_state_changes
    GROUP BY record_date, state
),
-- 生成完整的日期序列，避免遗漏中间日期
date_sequence AS (
    SELECT generate_series(
        (SELECT MIN(record_date) FROM daily_state_changes),
        (SELECT MAX(record_date) FROM daily_state_changes),
        '1 day'::interval
    )::date AS record_date
),
-- 填充每个日期下所有状态的变动（无变动则为0）
full_daily_changes AS (
    SELECT
        ds.record_date,
        s.state,
        COALESCE(dsc.daily_total, 0) AS daily_total
    FROM date_sequence ds
    CROSS JOIN (SELECT DISTINCT state FROM daily_state_changes) s
    LEFT JOIN daily_state_changes dsc
        ON ds.record_date = dsc.record_date AND s.state = dsc.state
),
-- 计算每个状态的累计数量（即当天的实际数量）
running_totals AS (
    SELECT
        record_date,
        state,
        SUM(daily_total) OVER (PARTITION BY state ORDER BY record_date) AS current_count
    FROM full_daily_changes
)
-- 最终按日期展示各状态的物料数量
SELECT
    record_date,
    MAX(CASE WHEN state = 0 THEN current_count END) AS state_0_count,
    MAX(CASE WHEN state = 1 THEN current_count END) AS state_1_count,
    MAX(CASE WHEN state = 2 THEN current_count END) AS state_2_count
FROM running_totals
GROUP BY record_date
ORDER BY record_date;

执行这条SQL后，你就能得到如下格式的结果：

完全符合你想要的追踪效果！

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