- 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.
推薦閱讀
- Word 2003、Excel 2003、PowerPoint 2003上機指導與練習
- Mastering Proxmox(Third Edition)
- 21天學通JavaScript
- Dreamweaver CS3網頁制作融會貫通
- 錯覺:AI 如何通過數據挖掘誤導我們
- 計算機原理
- Mastering D3.js
- VMware Performance and Capacity Management(Second Edition)
- Mastering Elastic Stack
- Windows 7寶典
- Splunk Operational Intelligence Cookbook
- 深度學習與目標檢測
- 云計算和大數據的應用
- Ansible 2 Cloud Automation Cookbook
- Linux常用命令簡明手冊