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

Understanding hypothetical aggregates

Hypothetical aggregates are pretty similar to standard ordered sets. However, they help to answer a different kind of question: what would be the result if a value was there? As you can see, this is not about values inside the database but about the result if a certain value was actually there.

The only hypothetical function provided by PostgreSQL is rank:

test=# SELECT region, 
rank(9000) WITHIN GROUP
(ORDER BY production DESC NULLS LAST)
FROM t_oil
GROUP BY ROLLUP (1);
region | rank
---------------+------
Middle East | 21
North America | 27
| 47
(3 rows)

It tells us: If somebody produced, 9000 barrels per day, it would be ranked the 27th best year in North America and 21st in the Middle East.

In my example, I used NULLS LAST. When data is sorted, nulls are usually at the end. However, if sort order is reversed, nulls should still be at the end of the list. NULLS LAST ensures exactly that.
主站蜘蛛池模板: 邓州市| 鞍山市| 常州市| 平顺县| 万宁市| 凯里市| 康定县| 竹山县| 石楼县| 库尔勒市| 宜良县| 西贡区| 婺源县| 南安市| 黄龙县| 岗巴县| 叙永县| 金川县| 通化市| 界首市| 绿春县| 甘南县| 安溪县| 英德市| 宁强县| 新源县| 中阳县| 明溪县| 德州市| 驻马店市| 古交市| 古交市| 重庆市| 米易县| 新泰市| 石城县| 永年县| 兰考县| 右玉县| 鲁甸县| 临海市|