- Mastering PostgreSQL 10
- Hans Jürgen Sch?nig
- 367字
- 2021-06-30 19:04:02
Partitioning data
So far, the same result can also easily be achieved using a subselect. However, if you want more than just the overall average, subselects will turn your queries into nightmares. Suppose, you don't just want the overall average but the average of the country you are dealing with. A PARTITION BY clause is what you need:
test=# SELECT country, year, production, consumption,
avg(production) OVER (PARTITION BY country)
FROM t_oil; country | year | production | consumption | avg ----------+-------+------------+-------------+----------- Canada | 1965 | 920 | 1108 | 2123.2173 Canada | 2010 | 3332 | 2316 | 2123.2173 Canada | 2009 | 3202 | 2190 | 2123.2173 ... Iran | 1966 | 2132 | 148 | 3631.6956 Iran | 2010 | 4352 | 1874 | 3631.6956 Iran | 2009 | 4249 | 2012 | 3631.6956 ...
The point here is that each country will be assigned to the average of the country. The OVER clause defines the window we are looking at. In this case, the window is the country the row belongs to. In other words, the query returns the rows compared to all rows in this country.
Basically, a PARTITION BY clause takes any expression. Usually, most people will use a column to partition the data. Here is an example:
test=# SELECT year, production, avg(production) OVER (PARTITION BY year < 1990) FROM t_oil WHERE country = 'Canada' ORDER BY year;
year | production | avg -------+------------+----------------------- 1965 | 920 | 1631.6000000000000000 1966 | 1012 | 1631.6000000000000000 ... 1990 | 1967 | 2708.4761904761904762 1991 | 1983| 2708.4761904761904762 1992 | 2065| 2708.4761904761904762 ...
The point is that data is split using the expression. year < 1990 can return two values: true or false. Depending on the group a year is in, it will be assigned to the pre-1990 average or to the post-1990 average. PostgreSQL is really flexible here. Using functions to determine group membership is not uncommon in real-world applications.
- Mastering Hadoop 3
- Photoshop CS4經(jīng)典380例
- 腦動(dòng)力:PHP函數(shù)速查效率手冊
- Apache Spark Deep Learning Cookbook
- Docker High Performance(Second Edition)
- 水下無線傳感器網(wǎng)絡(luò)的通信與決策技術(shù)
- AI的25種可能
- Visual Studio 2010 (C#) Windows數(shù)據(jù)庫項(xiàng)目開發(fā)
- 生物3D打印:從醫(yī)療輔具制造到細(xì)胞打印
- 工業(yè)機(jī)器人實(shí)操進(jìn)階手冊
- MPC5554/5553微處理器揭秘
- JRuby語言實(shí)戰(zhàn)技術(shù)
- Building Google Cloud Platform Solutions
- 空間機(jī)器人智能感知技術(shù)
- Hands-On Business Intelligence with Qlik Sense