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

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.

主站蜘蛛池模板: 梁山县| 濉溪县| 额尔古纳市| 尼玛县| 南通市| 天长市| 金川县| 囊谦县| 庄浪县| 曲沃县| 富源县| 洛宁县| 攀枝花市| 莱芜市| 从江县| 周宁县| 从江县| 晋城| 雷州市| 平山县| 铁岭县| 独山县| 于田县| 凤冈县| 丰镇市| 南京市| 永昌县| 沙田区| 繁昌县| 象州县| 都兰县| 建阳市| 江永县| 抚顺市| 武陟县| 乐东| 锡林浩特市| 广宗县| 怀宁县| 岑溪市| 嘉峪关市|