官术网_书友最值得收藏!

Combining grouping sets with the FILTER clause

In real-world applications, grouping sets can often be combined with FILTER clauses. The idea behind the FILTER clause is to be able to run partial aggregates.

Here is an example:

test=# SELECT region, 
avg(production) AS all, avg(production) FILTER (WHERE year < 1990) AS old, avg(production) FILTER (WHERE year >= 1990) AS new FROM t_oil GROUP BY ROLLUP (region);

region | all | old | new
---------------+----------------+----------------+----------------
Middle East | 1992.603686635 | 1747.325892857 | 2254.233333333
North America | 4541.362318840 | 4471.653333333 | 4624.349206349
| 2607.513986013 | 2430.685618729 | 2801.183150183 (3 rows)

The idea here is that not all columns will use the same data for aggregation. The FILTER clauses allow you to selectively pass data to those aggregates. In my example, the second aggregate will only consider data before 1990, while the third aggregate will take care of more recent data.

If it is possible to move conditions to a WHERE clause, it is always more desirable, as less data has to be fetched from the table. FILTER is only useful if the data left by the WHERE clause is not needed by each aggregate.

FILTER works for all kinds of aggregates and offers a simple way to pivot your data. Also, FILTER is faster than mimicking the same behavior with CASE WHEN ... THEN NULL ... ELSE END. You can find some real performance comparison here: https://www.cybertec-postgresql.com/en/postgresql-9-4-aggregation-filters-they-do-pay-off/.

主站蜘蛛池模板: 兴隆县| 定安县| 出国| 香格里拉县| 外汇| 隆德县| 灵台县| 安多县| 涟源市| 孟津县| 长阳| 乌兰浩特市| 若羌县| 榆林市| 皋兰县| 苏州市| 武胜县| 沁水县| 永安市| 阳西县| 中阳县| 桃源县| 山丹县| 出国| 拜泉县| 视频| 通化市| 治多县| 灯塔市| 青阳县| 宣恩县| 阳江市| 上饶县| 泰宁县| 阳曲县| 亳州市| 杭锦后旗| 漠河县| 循化| 温泉县| 阿克|