- 建立测试表
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=#