- Mastering PostgreSQL 10
- Hans Jürgen Sch?nig
- 163字
- 2021-06-30 19:04:02
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.
推薦閱讀
- Splunk 7 Essentials(Third Edition)
- 人工智能超越人類
- Mastering Spark for Data Science
- Spark編程基礎(Scala版)
- Ansible Quick Start Guide
- 手把手教你玩轉RPA:基于UiPath和Blue Prism
- 機器人智能運動規劃技術
- 21天學通C++
- Maya 2012從入門到精通
- B2B2C網上商城開發指南
- 自動生產線的拆裝與調試
- 計算機網絡安全
- Salesforce for Beginners
- Microsoft System Center Confi guration Manager
- 大數據技術基礎:基于Hadoop與Spark