库存SQL需求：新增与前序同库存记录均价差值计算列

现有库存交易表（假设表名为stock_transactions），字段包括stock_code、date、amount、quantity、type（0代表入库entry，1代表出库exit）：

• 已实现逻辑：同库存连续entry行计算均价作为avg_unit_price；非连续entry行及exit行保留原unit_price（即amount/quantity）作为avg_unit_price
• 需新增diff_from_prev_entries列，规则：
  • exit行：当前行avg_unit_price减去同库存最近的entry行的avg_unit_price
  • entry行：若同库存存在前序entry组，当前组avg_unit_price减去前序组的avg_unit_price；无则为NULL

采用窗口函数标记entry组并关联前序值，避免自连接的复杂关联逻辑，以下是可直接创建为视图的完整SQL：

CREATE VIEW stock_with_diff AS
WITH stock_with_avg AS (
    SELECT
        *,
        -- 计算原始单价
        amount / quantity AS unit_price,
        -- 标记连续entry组：当前为entry且上一行非entry时，组ID递增
        SUM(CASE WHEN type = 0 AND LAG(type, 1, -1) OVER (PARTITION BY stock_code ORDER BY date) != 0 THEN 1 ELSE 0 END) 
            OVER (PARTITION BY stock_code ORDER BY date) AS entry_group_id
    FROM stock_transactions
),
entry_group_avg AS (
    -- 计算每个连续entry组的均价
    SELECT
        stock_code,
        entry_group_id,
        AVG(unit_price) AS group_avg_price
    FROM stock_with_avg
    WHERE type = 0
    GROUP BY stock_code, entry_group_id
),
stock_with_final_avg AS (
    -- 合并得到最终的avg_unit_price
    SELECT
        s.*,
        CASE
            -- 连续entry行使用组均价
            WHEN s.type = 0 AND (SELECT COUNT(*) FROM stock_with_avg s2 WHERE s2.stock_code = s.stock_code AND s2.entry_group_id = s.entry_group_id) > 1
                THEN e.group_avg_price
            -- 非连续entry或exit行用原始单价
            ELSE s.unit_price
        END AS avg_unit_price
    FROM stock_with_avg s
    LEFT JOIN entry_group_avg e ON s.stock_code = e.stock_code AND s.entry_group_id = e.entry_group_id
),
stock_with_prev_group_avg AS (
    -- 获取每个entry组的前序组均价
    SELECT
        s.*,
        LAG(e.group_avg_price) OVER (PARTITION BY s.stock_code ORDER BY s.entry_group_id) AS prev_group_avg
    FROM stock_with_final_avg s
    LEFT JOIN entry_group_avg e ON s.stock_code = e.stock_code AND s.entry_group_id = e.entry_group_id
)
SELECT
    stock_code,
    date,
    amount,
    quantity,
    type,
    avg_unit_price,
    CASE
        -- 处理exit行：取同库存最近的entry均价做差
        WHEN type = 1 THEN
            avg_unit_price - LAST_VALUE(CASE WHEN type = 0 THEN avg_unit_price END IGNORE NULLS) 
                OVER (PARTITION BY stock_code ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        -- 处理entry行：取前序组均价做差，无前序则为NULL
        WHEN type = 0 THEN
            CASE WHEN prev_group_avg IS NOT NULL THEN avg_unit_price - prev_group_avg ELSE NULL END
    END AS diff_from_prev_entries
FROM stock_with_prev_group_avg;

关键逻辑说明 #

1. entry组标记：通过SUM()窗口函数生成entry_group_id，每次遇到新的entry起始行（上一行不是entry）时组ID递增，实现连续entry行的分组
2. exit行差值计算：用LAST_VALUE(...) IGNORE NULLS跳过exit行，直接定位同库存最近的entry行均价
3. entry组差值计算：用LAG()窗口函数获取当前entry组的前序组均价，同一组内的所有entry行共用该差值

示例验证 #

假设输入数据：

输出结果：

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