小編給大家分享一下postgresql中如何實現(xiàn)group by range，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

創(chuàng)新互聯(lián)建站是一家專業(yè)提供義馬企業(yè)網(wǎng)站建設(shè),專注與做網(wǎng)站、成都網(wǎng)站設(shè)計、H5響應(yīng)式網(wǎng)站、小程序制作等業(yè)務(wù)。10年已為義馬眾多企業(yè)、政府機構(gòu)等服務(wù)。創(chuàng)新互聯(lián)專業(yè)的建站公司優(yōu)惠進行中。

1. 建立測試表

testdb=# CREATE TEMP TABLE team (
id serial,
name text,
birth_year integer,
salary integer
);

2. 插入記錄

testdb=# INSERT INTO team (name, birth_year, salary)
VALUES ('Gabriel', 1970, 44000),
('Tom', 1972, 36000),
('Bill', 1978, 39500),
('Bob', 1980, 29000),
('Roger', 1976, 26800),
('Lucas', 1965, 56900),
('Jerome', 1984, 33500),
('Andrew', 1992, 41600),
('John', 1991, 40000),
('Paul', 1964, 39400),
('Richard', 1986, 23000),
('Joseph', 1988, 87000),
('Jason', 1990, 55000);

3. 查詢結(jié)果

testdb=# WITH series AS (  
SELECT generate_series(1950, 2000, 10) AS time_start -- 1950 = min, 2010 = max, 10 = 10 year interval
), range AS (
SELECT time_start, (time_start + 9) AS time_end FROM series -- 9 = interval (10 years) minus 1
)
SELECT time_start, time_end,
(SELECT count(*) FROM team WHERE birth_year BETWEEN time_start AND time_end) as team_members,
round((SELECT AVG(salary) FROM team WHERE birth_year BETWEEN time_start AND time_end), 2) as salary_avg,
(SELECT MIN(salary) FROM team WHERE birth_year BETWEEN time_start AND time_end) as salary_min,
(SELECT MAX(salary) FROM team WHERE birth_year BETWEEN time_start AND time_end) as salary_max
FROM range;

輸出結(jié)果：

time_start | time_end | team_members | salary_avg | salary_min | salary_max
------------+----------+--------------+------------+------------+------------
1950 |     1959 |            0 |            |            |          
1960 |     1969 |            2 |   48150.00 |      39400 |      56900
1970 |     1979 |            4 |   36575.00 |      26800 |      44000
1980 |     1989 |            4 |   43125.00 |      23000 |      87000
1990 |     1999 |            3 |   45533.33 |      40000 |      55000
2000 |     2009 |            0 |            |            |          
(6 rows)

testdb=#

以上是“postgresql中如何實現(xiàn)group by range”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學習更多知識，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！