了解 PostgreSQL 中的 FILTER（附带示例）

立即开始增强您的 PostgreSQL。

作者：Timescale 团队

在 PostgreSQL 和 TimescaleDB 中，FILTER 子句可用于通过添加 WHERE 子句来扩展聚合函数，例如 sum()、avg() 和 count()。当您想在查询中执行多个聚合时，这尤其有用。

当 FILTER 与聚合函数一起使用时，只有其 WHERE 子句的计算结果为真的输入行才会在聚合子句中使用。聚合函数正在处理的数据由 WHERE 子句的条件“过滤”。

语法

<aggregate_function>(<expression>) FILTER(WHERE <condition>)

如果将聚合函数与窗口函数调用一起使用，则语法如下

<aggregate_function>(<expression>) FILTER(WHERE <condition>) OVER(<window_definition)

FILTER 子句只能与作为聚合的窗口函数一起使用。

示例

让我们看一些示例以更好地理解 FILTER 子句。对于第一组示例，我们将使用名为 car_sales 的表中的数据。

sales_year	sales_month	make	model	kind	quantity	revenue
2021	1	福特	F100	皮卡	40	2500000
2021	1	福特	Mustang	汽车	9	1010000
2021	1	雷诺	Fuego	汽车	20	9000000
2021	2	雷诺	Fuego	汽车	50	23000000
2021	2	福特	F100	皮卡	20	1200000
2021	2	福特	Mustang	汽车	10	1050000
2021	3	雷诺	梅甘娜	汽车	50	20000000
2021	3	雷诺	科雷傲	汽车	15	1004000
2021	3	福特	Mustang	汽车	20	2080000
2021	4	雷诺	梅甘娜	汽车	50	20000000
2021	4	雷诺	科雷傲	汽车	15	1004000
2021	4	福特	Mustang	汽车	25	2520000

使用 FILTER 替换 CASE 语句

在 9.4 版之前，PostgreSQL 没有 FILTER。在此版本之前，数据库开发人员通常使用 CASE 语句和 WHEN 子句来获取现在可以使用 FILTER 获取的结果。这种方法不太直接。

让我们从一个简单的查询开始，获取每个汽车制造商的最低和最高收入。以下是用 FILTER 子句获取结果的查询

SELECT
	min(revenue) FILTER (WHERE make = ‘Ford’) min_ford,
	max(revenue) FILTER (WHERE make = ‘Ford’) max_ford,
	min(revenue) FILTER (WHERE make = ‘Renault’) min_renault,
	max(revenue) FILTER (WHERE make = ‘Renault’) max_renault
FROM
	car_sales;

我们使用 AS 关键字为这些总和分别创建了别名，以便区分结果，否则结果集中的每一列都将简单地标记为 min 或 max。以下是结果

福特最低价	福特最高价	雷诺最低价	雷诺最高价
1010000	2520000	1004000	23000000

要用 CASE 语句获得相同的结果，您必须使用以下查询

SELECT
	min(CASE WHEN make = ‘Ford’ THEN revenue ELSE null END) min_ford,
	max(CASE WHEN make = ‘Ford’ THEN revenue ELSE null END) max_ford,
	min(CASE WHEN make = ‘Renault’ THEN revenue ELSE null END) min_renault,
	max(CASE WHEN make = ‘Renault’ THEN revenue ELSE null END) max_renault
FROM
	car_sales;

对于这种情况， FILTER 更容易理解。

使用 FILTER 透视表

我们可以使用 FILTER 子句将行透视为表，您经常需要这样做才能生成报表。假设我们想知道每个月售出的汽车总数。我们可以使用 GROUP BY 通过以下查询来做到这一点

SELECT sales_month, sum(quantity) FROM car_sales GROUP BY sales_month;

结果如下

sales_month	总和
4	90
3	85
1	69
2	80

或者我们可以使用 FILTER 通过以下查询透视这些结果

SELECT
	sum(quantity) FILTER (WHERE sales_month = 1) jan_quantity,
	sum(quantity) FILTER (WHERE sales_month = 2) feb_quantity,
	sum(quantity) FILTER (WHERE sales_month = 3) mar_quantity,
	sum(quantity) FILTER (WHERE sales_month = 4) apr_quantity
FROM
	car_sales;

结果

一月数量	二月数量	三月数量	四月数量
69	80	85	90

将 FILTER 子句与窗口函数结合使用

对于下一个示例，我们将使用一个不同的数据集，其中包含来自两个城市的温度和降水量数据。

日期	城市	温度	降水量
2021-09-04	迈阿密	68.36	0.00
2021-09-05	迈阿密	72.50	0.00
2021-09-01	迈阿密	65.30	0.28
2021-09-02	迈阿密	64.40	0.79
2021-09-03	迈阿密	71.60	0.47
2021-09-04	亚特兰大	67.28	0.00
2021-09-05	亚特兰大	70.80	0.00
2021-09-01	亚特兰大	63.14	0.20
2021-09-02	亚特兰大	62.60	0.59
2021-09-03	亚特兰大	62.60	0.39

在下一个查询中，我们将获取每个城市温度的三日移动平均值。为此，我们将使用 OVER 子句定义一个窗口，并按城市对其进行分区。为了说明 FILTER 如何与窗口子句一起使用，我们只会在窗口中任何一天（包括当前行加上前两行）的温度超过 70 度时返回最高温度。换句话说，它将为我们提供过去三天（包括一天中任何时间温度超过 70 度）的最高温度。

以下是查询

SELECT city, day, temperature,
    MAX(temperature)
    FILTER (WHERE temperature > 70)
    OVER (
      PARTITION BY city
      ORDER BY day ASC
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) max_3day
FROM city_data
ORDER BY city, day;

以下是结果

城市	日期	温度	3 天最高温
亚特兰大	2021-09-01	63.14
亚特兰大	2021-09-02	62.60
亚特兰大	2021-09-03	62.60
亚特兰大	2021-09-04	67.28
亚特兰大	2021-09-05	70.80	70.80
迈阿密	2021-09-01	65.30
迈阿密	2021-09-02	64.40
迈阿密	2021-09-03	71.60	71.60
迈阿密	2021-09-04	68.36	71.60
迈阿密	2021-09-05	72.50	72.50

后续步骤

要详细了解 FILTER 子句及其在 PostgreSQL 中的使用方法，您可以查阅 PostgreSQL 关于聚合表达式和窗口函数调用的文档。

如果您想在 PostgreSQL（但速度更快）数据库中体验 FILTER，同时享受自动数据分区和列式压缩带来的性能提升和存储空间减少，请立即创建一个免费的 Timescale 帐户。