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

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 second aggregate will take care of more recent data.

Note that 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.

主站蜘蛛池模板: 黄梅县| 日土县| 太湖县| 休宁县| 德钦县| 三明市| 贡觉县| 晋城| 郯城县| 太原市| 嵊泗县| 宾川县| 湖南省| 马龙县| 东乌| 鞍山市| 荥阳市| 云安县| 万源市| 裕民县| 筠连县| 垫江县| 新安县| 民县| 息烽县| 屯门区| 基隆市| 平遥县| 金寨县| 清流县| 民勤县| 姚安县| 潞西市| 博白县| 浦城县| 连山| 信丰县| 福安市| 松潘县| 高青县| 东台市|