- Mastering PostgreSQL 9.6
- Hans Jurgen Schonig
- 205字
- 2021-07-09 19:57:24
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.
推薦閱讀
- Instant Raspberry Pi Gaming
- 輕松學(xué)Java
- Maya極速引擎:材質(zhì)篇
- 計算機網(wǎng)絡(luò)技術(shù)基礎(chǔ)
- 數(shù)據(jù)通信與計算機網(wǎng)絡(luò)
- 分?jǐn)?shù)階系統(tǒng)分析與控制研究
- 網(wǎng)絡(luò)安全管理實踐
- LAMP網(wǎng)站開發(fā)黃金組合Linux+Apache+MySQL+PHP
- Building a BeagleBone Black Super Cluster
- 中文版AutoCAD 2013高手速成
- 3ds Max造型表現(xiàn)藝術(shù)
- 工業(yè)機器人集成應(yīng)用
- Learn Microsoft Azure
- 大數(shù)據(jù)素質(zhì)讀本
- Java Deep Learning Projects