DuckDB last函数未返回预期值，SQL执行结果不稳定求助

你的SQL出现“时对时错”的核心原因是未明确指定排序规则的聚合函数（first()/last()）以及无关联条件的相关子查询，数据库的执行计划或默认行顺序变化会导致结果波动，具体问题点如下：

• first(UTCTradeDate)、last(UTCTradeDate)这类函数没有指定排序依据，数据库会按照存储的物理顺序返回结果，而物理顺序可能因执行计划、数据存储变化（比如VACUUM、索引使用）而改变，导致结果不稳定。
• 所有引用without_gaps字段的子查询（比如SELECT CASE WHEN CAST(without_gaps.UTCDateTime AS TIME) > ... FROM per_sec）没有关联条件，会对整个per_sec表做聚合，而非针对当前行的TimeKey做计算，逻辑完全错误。
• last(Ticker) FILTER(...)同样未指定排序，last的结果依赖无保证的行顺序，必然导致结果不稳定。

修正后的SQL逻辑 #

我们需要用窗口函数替代相关子查询，明确指定排序规则，确保结果的确定性：

INSERT INTO per_sec_without_gaps
WITH per_sec_ordered AS (
    -- 先对per_sec按时间排序，确保聚合的确定性
    SELECT 
        UTCDateTime,
        UTCTradeDate,
        LocalDateTime,
        LocalTradeDate,
        Ticker,
        BidPrice,
        AskPrice,
        BidQuantity,
        AskQuantity,
        -- 计算per_sec的最小时间（按TIME类型）
        MIN(CAST(UTCDateTime AS TIME)) OVER() AS min_utc_time,
        MIN(CAST(LocalDateTime AS TIME)) OVER() AS min_local_time,
        -- 用LAST_VALUE获取截止到当前时间的非空值，明确排序
        LAST_VALUE(Ticker) OVER(ORDER BY UTCDateTime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS last_ticker,
        LAST_VALUE(BidPrice) OVER(ORDER BY UTCDateTime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS last_bid_price,
        LAST_VALUE(AskPrice) OVER(ORDER BY UTCDateTime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS last_ask_price,
        LAST_VALUE(BidQuantity) OVER(ORDER BY UTCDateTime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS last_bid_qty,
        LAST_VALUE(AskQuantity) OVER(ORDER BY UTCDateTime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS last_ask_qty,
        -- 获取per_sec的首/末UTCTradeDate（按时间排序）
        FIRST_VALUE(UTCTradeDate) OVER(ORDER BY UTCDateTime) AS first_utc_trade_date,
        LAST_VALUE(UTCTradeDate) OVER(ORDER BY UTCDateTime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_utc_trade_date,
        -- 获取per_sec的首/末LocalTradeDate（按时间排序）
        FIRST_VALUE(LocalTradeDate) OVER(ORDER BY LocalDateTime) AS first_local_trade_date,
        LAST_VALUE(LocalTradeDate) OVER(ORDER BY LocalDateTime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_local_trade_date
    FROM per_sec
)
SELECT
    row_number() OVER(ORDER BY wg.UTCDateTime) AS Index, -- 明确排序，确保Index唯一且有序
    wg.UTCDateTime AS TimeKey,
    -- 根据当前行的UTC时间判断取首/末交易日期
    CASE 
        WHEN CAST(wg.UTCDateTime AS TIME) > (SELECT min_utc_time FROM per_sec_ordered LIMIT 1)
        THEN (SELECT first_utc_trade_date FROM per_sec_ordered LIMIT 1)
        ELSE (SELECT last_utc_trade_date FROM per_sec_ordered LIMIT 1)
    END,
    CAST(wg.UTCDateTime AS TIME),
    -- 本地时间的交易日期判断
    CASE 
        WHEN CAST(wg.LocalDateTime AS TIME) > (SELECT min_local_time FROM per_sec_ordered LIMIT 1)
        THEN (SELECT first_local_trade_date FROM per_sec_ordered LIMIT 1)
        ELSE (SELECT last_local_trade_date FROM per_sec_ordered LIMIT 1)
    END,
    CAST(wg.LocalDateTime AS TIME),
    -- 关联到当前TimeKey的最新非空值
    (SELECT last_ticker FROM per_sec_ordered WHERE UTCDateTime <= wg.TimeKey ORDER BY UTCDateTime DESC LIMIT 1),
    (SELECT last_bid_price FROM per_sec_ordered WHERE UTCDateTime <= wg.TimeKey ORDER BY UTCDateTime DESC LIMIT 1),
    (SELECT last_ask_price FROM per_sec_ordered WHERE UTCDateTime <= wg.TimeKey ORDER BY UTCDateTime DESC LIMIT 1),
    NULL,
    (SELECT last_bid_qty FROM per_sec_ordered WHERE UTCDateTime <= wg.TimeKey ORDER BY UTCDateTime DESC LIMIT 1),
    (SELECT last_ask_qty FROM per_sec_ordered WHERE UTCDateTime <= wg.TimeKey ORDER BY UTCDateTime DESC LIMIT 1),
    NULL
FROM without_gaps wg
ORDER BY wg.UTCDateTime; -- 明确排序，确保插入顺序稳定

关键优化点说明 #

1. 明确排序规则：所有FIRST_VALUE、LAST_VALUE以及最终的row_number()都指定了ORDER BY，彻底避免因行顺序不确定导致的结果波动。
2. CTE预计算：用per_sec_ordered预计算所有需要的聚合值和窗口函数结果，减少重复计算，提升效率。
3. 关联逻辑修正：针对without_gaps的每一行TimeKey，通过WHERE UTCDateTime <= wg.TimeKey关联到per_sec中的数据，确保逻辑正确。
4. 确定性聚合：通过LIMIT 1配合ORDER BY DESC获取截止到当前时间的最新值，替代原有的无排序last()函数。

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