Spark SQL中如何为非连续quantity值复制datetime行？

原表数据（表名：workflow_tracking） #

核心逻辑 #

每行的datetime需要覆盖上一行quantity + 1到当前行quantity的所有连续数值，第一行则从1开始到当前quantity。

不同数据库实现方法 #

1. PostgreSQL #

利用generate_series生成连续序列，结合窗口函数LAG获取上一行的quantity值：

WITH ranked_data AS (
    SELECT 
        datetime,
        quantity,
        LAG(quantity, 1, 0) OVER (ORDER BY datetime) AS prev_quantity
    FROM workflow_tracking
)
SELECT 
    datetime,
    generate_series(prev_quantity + 1, quantity) AS quantity
FROM ranked_data
ORDER BY quantity;

2. MySQL 8.0+ #

使用递归CTE生成连续序列（MySQL无内置序列生成函数）：

WITH RECURSIVE ranked_data AS (
    SELECT 
        datetime,
        quantity,
        LAG(quantity, 1, 0) OVER (ORDER BY datetime) AS prev_quantity,
        ROW_NUMBER() OVER (ORDER BY datetime) AS rn
    FROM workflow_tracking
),
sequence_gen AS (
    SELECT 
        datetime,
        prev_quantity + 1 AS current_qty,
        quantity AS max_qty,
        rn
    FROM ranked_data
    UNION ALL
    SELECT 
        datetime,
        current_qty + 1,
        max_qty,
        rn
    FROM sequence_gen
    WHERE current_qty < max_qty
)
SELECT datetime, current_qty AS quantity
FROM sequence_gen
ORDER BY current_qty;

3. SQL Server #

通过递归生成数字表，再与原表关联筛选：

WITH ranked_data AS (
    SELECT 
        datetime,
        quantity,
        LAG(quantity, 1, 0) OVER (ORDER BY datetime) AS prev_quantity
    FROM workflow_tracking
),
numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n <= (SELECT MAX(quantity) FROM workflow_tracking)
)
SELECT 
    rd.datetime,
    n.n AS quantity
FROM ranked_data rd
JOIN numbers n ON n.n BETWEEN rd.prev_quantity + 1 AND rd.quantity
ORDER BY n.n;

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