group by range

建立測試表

testdb=# CREATE TEMP TABLE team (

id serial,

name text,

birth_year integer,

salary integer

);

插入記錄

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);

查詢結果

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;

輸出結果：

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=#

本文轉自 pgmia 51CTO部落格，原文連結:http://blog.51cto.com/heyiyi/2070561