⌘+k ctrl+k
1.3 (稳定版)
搜索快捷键 cmd + k | ctrl + k
FILTER 子句

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

这在多种情况下都很有用，包括在评估具有不同过滤条件的多个聚合函数时，以及在创建数据集的透视视图时。FILTER 相较于下文讨论的更传统的 CASE WHEN 方法，为数据透视提供了更简洁的语法。

某些聚合函数也不会过滤掉 NULL 值，因此使用 FILTER 子句有时会返回有效结果，而 CASE WHEN 方法则不会。这在使用 first 和 last 函数时会发生，这两个函数在非聚合透视操作中很受欢迎，其目标是简单地将数据重新排列成列而不是重新聚合。FILTER 在使用 list 和 array_agg 函数时也能改善 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 子句）

关于此页面