FIRST_VALUE

返回表达式针对窗口帧中第一行求值所得的值。

此内置窗口函数以与聚合窗口函数相同的方式为每一行计算窗口帧。它为每一行返回表达式针对窗口帧中第一行求值所得的值。

另请参阅： [SELECT] [LAST_VALUE] [NTH_VALUE]

---

语法

SELECT FIRST_VALUE(expr) OVER (
    [PARTITION BY column]
    ORDER BY column
    [ROWS BETWEEN frame_start AND frame_end]
) FROM <multipartIdentifier>;

参数

expr 针对窗口帧中第一行求值的表达式。

PARTITION BY column 可选。将结果集划分为多个分区。FIRST_VALUE 函数分别在每个分区内应用。

ORDER BY column 指定处理行的顺序。

ROWS BETWEEN frame_start AND frame_end 可选。定义窗口帧。常用选项包括：

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（默认）
• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

返回值

返回表达式针对窗口帧中第一行求值所得的值。

---

示例

查找第一个发布版本作为参照

-- Find first and latest release for context
SELECT
    tag_name,
    name,
    published_at,
    FIRST_VALUE(tag_name) OVER (
        ORDER BY published_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as first_release,
    LAST_VALUE(tag_name) OVER (
        ORDER BY published_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as latest_release
FROM github.repos.releases
WHERE owner = 'stackql'
  AND repo = 'stackql';

提示

默认情况下，窗口帧从分区开头延伸到当前行。要包含分区中的所有行，请使用 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

有关更多信息，请参阅 https://sqlite.org/windowfunctions.html#built-in_window_functions。