group by range

1. 建立测试表

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

1. 插入记录

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

1. 查询结果

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