FILTER 子句 – DuckDB - DuckDB 数据库

搜索快捷键 cmd + k | ctrl + k

文档 / SQL / 查询语法

FILTER 子句

FILTER 子句可以根据需要跟在 SELECT 语句中的聚合函数后面。它将过滤掉输入到聚合函数中的数据行，其方式与 WHERE 子句过滤行的方式相同，但仅限于特定的聚合函数。

这种情况在多种场景下非常有用，包括在评估具有不同过滤器的多个聚合时，以及在创建数据集的透视视图（pivoted view）时。与下面讨论的更传统的 CASE WHEN 方法相比，FILTER 为透视数据提供了更简洁的语法。

某些聚合函数不会过滤掉 NULL 值，因此使用 FILTER 子句可以返回有效结果，而 CASE WHEN 方法有时则不行。这种情况会出现在 first 和 last 函数中，在非聚合透视操作（即仅将数据重定向到列中而非重新聚合）中，这些函数非常有用。当使用 list 和 array_agg 函数时，FILTER 还能改善对 NULL 值的处理；因为 CASE WHEN 方法会将 NULL 值包含在列表结果中，而 FILTER 子句则会将它们移除。

示例

返回以下内容

总行数
i <= 5 的行数
i 为奇数的行数

SELECT
    count() AS total_rows,
    count() FILTER (i <= 5) AS lte_five,
    count() FILTER (i % 2 = 1) AS odds
FROM generate_series(1, 10) tbl(i);

total_rows	lte_five	odds
10	5	5

仅仅计算满足条件的行数也可以在没有 FILTER 子句的情况下完成，即使用布尔值 sum 聚合函数，例如 sum(i <= 5)。

可以使用不同的聚合函数，也允许使用多个 WHERE 表达式

SELECT
    sum(i) FILTER (i <= 5) AS lte_five_sum,
    median(i) FILTER (i % 2 = 1) AS odds_median,
    median(i) FILTER (i % 2 = 1 AND i <= 5) AS odds_lte_five_median
FROM generate_series(1, 10) tbl(i);

lte_five_sum	odds_median	odds_lte_five_median
15	5.0	3.0

FILTER 子句还可以用于将数据从行透视到列。这是一种静态透视，因为在 SQL 中必须在运行时之前定义列。不过，这种类型的语句可以在宿主编程语言中动态生成，从而利用 DuckDB 的 SQL 引擎进行快速、超大规模内存的透视操作。

首先生成一个示例数据集

CREATE TEMP TABLE stacked_data AS
    SELECT
        i,
        CASE WHEN i <= rows * 0.25  THEN 2022
             WHEN i <= rows * 0.5   THEN 2023
             WHEN i <= rows * 0.75  THEN 2024
             WHEN i <= rows * 0.875 THEN 2025
             ELSE NULL
             END AS year
    FROM (
        SELECT
            i,
            count(*) OVER () AS rows
        FROM generate_series(1, 100_000_000) tbl(i)
    ) tbl;

按年份“透视”数据（将每一年移动到单独的列中）

SELECT
    count(i) FILTER (year = 2022) AS "2022",
    count(i) FILTER (year = 2023) AS "2023",
    count(i) FILTER (year = 2024) AS "2024",
    count(i) FILTER (year = 2025) AS "2025",
    count(i) FILTER (year IS NULL) AS "NULLs"
FROM stacked_data;

此语法产生的结果与上述 FILTER 子句的结果相同

SELECT
    count(CASE WHEN year = 2022 THEN i END) AS "2022",
    count(CASE WHEN year = 2023 THEN i END) AS "2023",
    count(CASE WHEN year = 2024 THEN i END) AS "2024",
    count(CASE WHEN year = 2025 THEN i END) AS "2025",
    count(CASE WHEN year IS NULL THEN i END) AS "NULLs"
FROM stacked_data;

2022	2023	2024	2025	NULL 值
25000000	25000000	25000000	12500000	12500000

然而，当使用不忽略 NULL 值的聚合函数时，CASE WHEN 方法将无法按预期工作。first 函数就属于此类，因此在这种情况下更推荐使用 FILTER。

按年份“透视”数据（将每一年移动到单独的列中）

SELECT
    first(i) FILTER (year = 2022) AS "2022",
    first(i) FILTER (year = 2023) AS "2023",
    first(i) FILTER (year = 2024) AS "2024",
    first(i) FILTER (year = 2025) AS "2025",
    first(i) FILTER (year IS NULL) AS "NULLs"
FROM stacked_data;

2022	2023	2024	2025	NULL 值
1474561	25804801	50749441	76431361	87500001

每当 CASE WHEN 子句的第一次评估返回 NULL 时，这都会产生 NULL 值

SELECT
    first(CASE WHEN year = 2022 THEN i END) AS "2022",
    first(CASE WHEN year = 2023 THEN i END) AS "2023",
    first(CASE WHEN year = 2024 THEN i END) AS "2024",
    first(CASE WHEN year = 2025 THEN i END) AS "2025",
    first(CASE WHEN year IS NULL THEN i END) AS "NULLs"
FROM stacked_data;

2022	2023	2024	2025	NULL 值
1228801	NULL	NULL	NULL	NULL

聚合函数语法（包含 `FILTER` 子句）

关于此页面

行为准则商标使用指南

示例

聚合函数语法（包含 FILTER 子句）

关于此页面

本文中

聚合函数语法（包含 `FILTER` 子句）