SQL如何获取表中前序值 实现空值前向填充与时间点品类补全

以下方案基于标准SQL编写，适配MySQL8.0+、PostgreSQL、SparkSQL、BigQuery等支持窗口函数的主流数据库引擎，默认业务表名为sales_record，且同一品类同一时间点不存在多条重复统计记录。

实现逻辑拆分 #

• 首先提取表中所有出现过的时间点，与固定的两个品类（orange、apple）做笛卡尔积，生成每个时间点必含两个品类的基础数据集，解决品类记录缺失的问题
• 将基础数据集与原表左关联，拿到实际存在的Qty、total字段值，不存在的记录自动保留为null
• 利用窗口函数向前查找同品类上一个非空值填充当前空字段，没有任何前置有效值的初始时间点，字段值填充为0

支持IGNORE NULLS语法的最优写法（推荐） #

大部分现代SQL引擎支持IGNORE NULLS窗口函数选项，执行效率更高，代码如下：

WITH
-- 提取全量不重复时间点
all_time AS (
    SELECT DISTINCT `time` FROM sales_record
),
-- 定义需要覆盖的品类列表
all_type AS (
    SELECT 'orange' AS type UNION ALL
    SELECT 'apple' AS type
),
-- 生成 时间*品类 全量基础维度
base_dim AS (
    SELECT t.`time`, tp.type
    FROM all_time t
    CROSS JOIN all_type tp
),
-- 关联原表取实际统计值
joined_data AS (
    SELECT
        d.`time`,
        d.type,
        r.qty,
        r.total
    FROM base_dim d
    LEFT JOIN sales_record r
        ON d.`time` = r.`time`
        AND d.type = r.type
)
-- 向前填充空值，无前置值时填0
SELECT
    `time`,
    type,
    COALESCE(
        LAST_VALUE(qty IGNORE NULLS) OVER (
            PARTITION BY type
            ORDER BY `time`
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ),
        0
    ) AS qty,
    COALESCE(
        LAST_VALUE(total IGNORE NULLS) OVER (
            PARTITION BY type
            ORDER BY `time`
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ),
        0
    ) AS total
FROM joined_data
ORDER BY `time`, type;

低版本兼容写法（无IGNORE NULLS支持时使用） #

如果你的数据库版本不支持窗口函数的IGNORE NULLS选项（如8.0.20以下版本MySQL、SQL Server），可以用关联子查询查找最近非空值的方式实现，替换上述代码的最后一步查询即可：

SELECT
    d.`time`,
    d.type,
    COALESCE(
        (
            SELECT r.qty
            FROM sales_record r
            WHERE r.type = d.type
              AND r.`time` <= d.`time`
              AND r.qty IS NOT NULL
            ORDER BY r.`time` DESC
            LIMIT 1
        ),
        0
    ) AS qty,
    COALESCE(
        (
            SELECT r.total
            FROM sales_record r
            WHERE r.type = d.type
              AND r.`time` <= d.`time`
              AND r.total IS NOT NULL
            ORDER BY r.`time` DESC
            LIMIT 1
        ),
        0
    ) AS total
FROM base_dim d
ORDER BY d.`time`, d.type;

注意事项 #

• 如果你需要补全连续时间（比如原表时间有断档，需要把缺失的日期/小时也补上），可以把all_timeCTE替换为对应数据库生成连续时间序列的逻辑（比如PostgreSQL用generate_series、MySQL用递归CTE），后续填充逻辑无需改动
• 如果后续需要新增统计品类，直接在all_typeCTE中添加对应的UNION ALL行即可
• 如果原表存在同一品类同一时间多条记录的情况，需要先对原表做聚合去重，再参与后续关联，避免结果出现重复行

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