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

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.
主站蜘蛛池模板: 鲜城| 临邑县| 永济市| 霍州市| 漾濞| 上林县| 龙岩市| 五河县| 上饶市| 长丰县| 武鸣县| 冀州市| 陆川县| 富川| 阜南县| 宜阳县| 巴林右旗| 衡阳县| 仙游县| 桂平市| 卢氏县| 黄陵县| 亳州市| 龙口市| 冀州市| 呼玛县| 咸宁市| 蒙山县| 图木舒克市| 花莲县| 海林市| 封开县| 和田市| 保定市| 彭泽县| 苗栗县| 邹城市| 青田县| 德保县| 丰镇市| 万州区|