为何同一SQL查询中FIRST_VALUE与LAST_VALUE返回行数不一致？

核心问题出在**窗口函数的默认框架（Frame）**上，这是用窗口函数时很容易踩的坑。

先看你用FIRST_VALUE+DESC的逻辑 #

当你写FIRST_VALUE(day) OVER (PARTITION BY column_2 ORDER BY day DESC)时：

• 按column_2分区后，内部按day降序排列，最新的行排在每个分区最前面
• 窗口函数的默认框架是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，也就是覆盖从分区第一行（最新行）到当前行的范围
• 所以不管当前行是分区里的哪一行，FIRST_VALUE取的都是分区第一行的day值，同一个column_2下所有行的day_last_value都是同一个值
• 再加上DISTINCT，同一个column_2下的所有行生成的聚合值（day_last_value、xxx_last_value等）完全一致，最终每个column_2只会保留一行

再看你用LAST_VALUE+ASC的问题 #

当你换成LAST_VALUE(day) OVER (PARTITION BY column_2 ORDER BY day ASC)时：

• 分区内部按day升序排列，最新的行在分区最后面
• 但默认框架还是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，只包含从分区开头到当前行的范围
• 这时候LAST_VALUE取的是当前行的day值，而不是整个分区最后一行（最新行）的day值
• 同一个column_2下的不同行，xxx/yyy/zzz可能有不同的历史值，即使加了DISTINCT，这些不同的列值组合会被保留，导致行数远超每个column_2一行的预期

正确的LAST_VALUE写法 #

要让LAST_VALUE取到整个分区的最后一行，必须显式指定窗口框架覆盖整个分区：

DECLARE _timestamp TIMESTAMP;
DECLARE _timestamp_start TIMESTAMP;

SET _timestamp = TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), HOUR), INTERVAL 1 MINUTE);
SET _timestamp_start = TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), HOUR), INTERVAL 24 HOUR);

SELECT DISTINCT
    LAST_VALUE(day) OVER (PARTITION BY column_2 ORDER BY day ASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as day_last_value,
    column_2,
    column_3,
    column_4,
    LAST_VALUE(xxx) OVER (PARTITION BY column_2 ORDER BY day ASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as xxx_last_value,
    LAST_VALUE(yyy) OVER (PARTITION BY column_2 ORDER BY day ASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as yyy_last_value,
    LAST_VALUE(zzz) OVER (PARTITION BY column_2 ORDER BY day ASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as zzz_last_value,
  FROM 
    table1
  WHERE 
    day BETWEEN DATE(_timestamp_start) AND DATE(_timestamp)

指定框架后，LAST_VALUE会遍历整个分区取到最后一行（最新day）对应的值，这时再用DISTINCT，结果就和FIRST_VALUE+DESC的写法一致了。

更高效的替代写法 #

其实用ROW_NUMBER()窗口函数逻辑更清晰，还能避免DISTINCT的额外开销：

DECLARE _timestamp TIMESTAMP;
DECLARE _timestamp_start TIMESTAMP;

SET _timestamp = TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), HOUR), INTERVAL 1 MINUTE);
SET _timestamp_start = TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), HOUR), INTERVAL 24 HOUR);

WITH ranked_data AS (
  SELECT
    day,
    column_2,
    column_3,
    column_4,
    xxx,
    yyy,
    zzz,
    ROW_NUMBER() OVER (PARTITION BY column_2 ORDER BY day DESC) as rn
  FROM table1
  WHERE day BETWEEN DATE(_timestamp_start) AND DATE(_timestamp)
)
SELECT
  day as day_last_value,
  column_2,
  column_3,
  column_4,
  xxx as xxx_last_value,
  yyy as yyy_last_value,
  zzz as zzz_last_value
FROM ranked_data
WHERE rn = 1;

这种写法直接标记每个分区的最新行，筛选行号为1的记录即可，数据量较大时性能更优。

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