1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
|
---------------------------------------------------------------------------
--
-- advanced.sql-
-- Tutorial on advanced PostgreSQL features
--
--
-- Copyright (c) 1994, Regents of the University of California
--
-- src/tutorial/advanced.source
--
---------------------------------------------------------------------------
-----------------------------
-- Window Functions
-----------------------------
-- a sample table
CREATE TABLE empsalary (
depname text,
empno bigint,
salary int,
enroll_date date
);
INSERT INTO empsalary VALUES
('develop', 10, 5200, '2007-08-01'),
('sales', 1, 5000, '2006-10-01'),
('personnel', 5, 3500, '2007-12-10'),
('sales', 4, 4800, '2007-08-08'),
('personnel', 2, 3900, '2006-12-23'),
('develop', 7, 4200, '2008-01-01'),
('develop', 9, 4500, '2008-01-01'),
('sales', 3, 4800, '2007-08-01'),
('develop', 8, 6000, '2006-10-01'),
('develop', 11, 5200, '2007-08-15');
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname)
FROM empsalary;
SELECT depname, empno, salary,
row_number() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;
SELECT salary, sum(salary) OVER () FROM empsalary;
SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
SELECT depname, empno, salary, enroll_date
FROM
(SELECT depname, empno, salary, enroll_date,
row_number() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
FROM empsalary
) AS ss
WHERE pos < 3;
SELECT sum(salary) OVER w, avg(salary) OVER w
FROM empsalary
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
DROP TABLE empsalary;
-----------------------------
-- Inheritance:
-- A table can inherit from zero or more tables. A query can reference
-- either all rows of a table or all rows of a table plus all of its
-- descendants.
-----------------------------
-- For example, the capitals table inherits from cities table. (It inherits
-- all data fields from cities.)
CREATE TABLE cities (
name text,
population float8,
elevation int -- (in ft)
);
CREATE TABLE capitals (
state char(2)
) INHERITS (cities);
-- Now, let's populate the tables.
INSERT INTO cities VALUES ('San Francisco', 7.24E+5, 63);
INSERT INTO cities VALUES ('Las Vegas', 2.583E+5, 2174);
INSERT INTO cities VALUES ('Mariposa', 1200, 1953);
INSERT INTO capitals VALUES ('Sacramento', 3.694E+5, 30, 'CA');
INSERT INTO capitals VALUES ('Madison', 1.913E+5, 845, 'WI');
SELECT * FROM cities;
SELECT * FROM capitals;
-- You can find all cities, including capitals, that
-- are located at an elevation of 500 ft or higher by:
SELECT c.name, c.elevation
FROM cities c
WHERE c.elevation > 500;
-- To scan rows of the parent table only, use ONLY:
SELECT name, elevation
FROM ONLY cities
WHERE elevation > 500;
-- clean up (you must remove the children first)
DROP TABLE capitals;
DROP TABLE cities;
|
