sql - Presto - 插入缺失的时间戳

Question

编辑：在下面的帖子中做了一些澄清

我正在尝试解决我有一个缺少时间戳的表的问题。假设有一个这样的表：

时间戳	数值字段
2021-10-24 16:59:00.000	101
2021-10-24 16:57:00.000	101

我想尝试做两件事：

1. 填充时间戳为 2021-10-24 16:58:00.000 的第三条记录。

2. 除此之外，如果领先和滞后记录匹配，我想将 NumericField 字段填充为 101，就像在这个例子中一样。结果将是：

时间戳	数值字段
2021-10-24 16:59:00.000	101
2021-10-24 16:58:00.000	101
2021-10-24 16:57:00.000	101

如果超前和滞后 NumericField 记录不匹配，则生成的 NumericField 将导致 null。示例是：

时间戳	数值字段
2021-10-24 16:59:00.000	101
2021-10-24 16:58:00.000	无效的
2021-10-24 16:57:00.000	100

我发布这个问题的原因是 Presto 不支持递归 CTE，我找不到任何好的资源来帮助我解决这个问题。

标签： sqlpresto

Answer 1

我会尝试使用lag来查找以前的值，然后sequence使用步骤生成日期数组，将interval '1' minute其取消嵌套并将结果与​​原始表合并：

WITH dataset (Timestamp, NumericField) AS (
    VALUES (timestamp '2021-10-24 16:59:00.000', 101),
        (timestamp '2021-10-24 16:57:00.000', 101),
        (timestamp '2021-10-24 16:55:00.000', 99)
)
SELECT date as Timestamp,
    val as NumericField
FROM (
        SELECT array_except(
                sequence(prev_ts, Timestamp, interval '1' minute),
                array [ prev_ts, timestamp ] -- exclude border values
            ) as dates,
            case
                NumericField
                when prev_num then prev_num
            end as val
        FROM (
                SELECT *,
                    lag(Timestamp) over(order by Timestamp) prev_ts,
                    lag(NumericField) over(order by Timestamp) prev_num
                FROM dataset
            )
    ) seq
    CROSS JOIN UNNEST(dates) AS t (date)
UNION
SELECT *
FROM dataset
ORDER BY timestamp

输出：

时间戳	数值字段
2021-10-24 16:55:00.000	99
2021-10-24 16:56:00.000
2021-10-24 16:57:00.000	101
2021-10-24 16:58:00.000	101
2021-10-24 16:59:00.000	101