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

  • 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.

主站蜘蛛池模板: 忻州市| 吉木萨尔县| 老河口市| 辉县市| 上饶县| 丁青县| 东台市| 永胜县| 镇沅| 合水县| 固始县| 常宁市| 江津市| 华容县| 永泰县| 兴和县| 澎湖县| 靖远县| 外汇| 丹江口市| 吉林市| 托克逊县| 定边县| 图木舒克市| 武平县| 张北县| 乳山市| 三明市| 沙坪坝区| 东乌| 孟连| 霍邱县| 九龙城区| 呼玛县| 通河县| 北海市| 手机| 岳普湖县| 元阳县| 永德县| 当雄县|