Window function 정리 Work/PostgreSQL2020. 7. 1. 13:15
- 윈도우 함수 : 현재 행과 관련된 결과집합의 여러가지 연산을 수행. 집계함수와 유사하나 단일 행으로 그룹화 되지 않는다.
- 일반적인 windows fuction : https://www.postgresql.org/docs/9.3/functions-window.html
- 조금 난이도 있는 window function 활용 : https://www.cybertec-postgresql.com/en/calculating-differences-between-rows-in-sql/
위 페이지의 sample code
cypex=# CREATE TABLE t_oil
(
region text,
country text,
year int,
production int,
consumption int
);
cypex=# COPY t_oil
FROM PROGRAM 'curl https://www.cybertec-postgresql.com/secret/oil_ext.txt';
COPY 644
cypex=# SELECT country, year, production,
lag(production, 1) OVER (ORDER BY year)
FROM t_oil
WHERE country = 'USA'
LIMIT 10;
country | year | production | lag
---------+------+------------+-------
USA | 1965 | 9014 |
USA | 1966 | 9579 | 9014
USA | 1967 | 10219 | 9579
USA | 1968 | 10600 | 10219
USA | 1969 | 10828 | 10600
USA | 1970 | 11297 | 10828
USA | 1971 | 11156 | 11297
USA | 1972 | 11185 | 11156
USA | 1973 | 10946 | 11185
USA | 1974 | 10461 | 10946
(10 rows)
cypex=# SELECT country, year,
production - lag(production, 1) OVER (ORDER BY year) AS diff
FROM t_oil
WHERE country = 'USA'
LIMIT 10;
country | year | diff
---------+------+------
USA | 1965 |
USA | 1966 | 565
USA | 1967 | 640
USA | 1968 | 381
USA | 1969 | 228
USA | 1970 | 469
USA | 1971 | -141
USA | 1972 | 29
USA | 1973 | -239
USA | 1974 | -485
(10 rows)
cypex=# SELECT t_oil.country,
t_oil.year,
t_oil.production,
t_oil.production - first_value(t_oil.production) OVER (ORDER BY t_oil.year) AS diff_first
FROM t_oil
WHERE t_oil.country = 'USA'::text
LIMIT 10;
country | year | production | diff_first
---------+------+------------+------------
USA | 1965 | 9014 | 0
USA | 1966 | 9579 | 565
USA | 1967 | 10219 | 1205
USA | 1968 | 10600 | 1586
USA | 1969 | 10828 | 1814
USA | 1970 | 11297 | 2283
USA | 1971 | 11156 | 2142
USA | 1972 | 11185 | 2171
USA | 1973 | 10946 | 1932
USA | 1974 | 10461 | 1447
(10 rows)
cypex=# SELECT country, year, production,
lag(production) OVER (PARTITION BY country ORDER BY year) AS diff
FROM t_oil
WHERE country IN ('Canada', 'Mexico')
AND year < 1970;
country | year | production | diff
---------+------+------------+------
Canada | 1965 | 920 |
Canada | 1966 | 1012 | 920
Canada | 1967 | 1106 | 1012
Canada | 1968 | 1194 | 1106
Canada | 1969 | 1306 | 1194
Mexico | 1965 | 362 |
Mexico | 1966 | 370 | 362
Mexico | 1967 | 411 | 370
Mexico | 1968 | 439 | 411
Mexico | 1969 | 461 | 439
(10 rows)
cypex=# SELECT country, year, production,
avg(production) OVER (ORDER BY year
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS mov
FROM t_oil
WHERE country IN ('Saudi Arabien')
AND year BETWEEN 1975 AND 1990;
country | year | production | mov
---------------+------+------------+-----------------------
Saudi Arabien | 1975 | 7216 | 8465.6666666666666667
Saudi Arabien | 1976 | 8762 | 8487.7500000000000000
Saudi Arabien | 1977 | 9419 | 8758.4000000000000000
Saudi Arabien | 1978 | 8554 | 9369.2000000000000000
Saudi Arabien | 1979 | 9841 | 9668.0000000000000000
Saudi Arabien | 1980 | 10270 | 9176.4000000000000000
Saudi Arabien | 1981 | 10256 | 8455.8000000000000000
Saudi Arabien | 1982 | 6961 | 7394.4000000000000000
Saudi Arabien | 1983 | 4951 | 6060.6000000000000000
Saudi Arabien | 1984 | 4534 | 5051.0000000000000000
Saudi Arabien | 1985 | 3601 | 4578.6000000000000000
Saudi Arabien | 1986 | 5208 | 4732.4000000000000000
Saudi Arabien | 1987 | 4599 | 4952.6000000000000000
Saudi Arabien | 1988 | 5720 | 5653.4000000000000000
Saudi Arabien | 1989 | 5635 | 5764.7500000000000000
Saudi Arabien | 1990 | 7105 | 6153.3333333333333333
(16 rows)
cypex=# SELECT t_oil, lag(t_oil) OVER (ORDER BY year)
FROM t_oil
WHERE country = 'Mexico'
AND year IN (1980, 1981);
t_oil | lag
-----------------------------------------+-----------------------------------------
("North America",Mexico,1980,2129,1048) |
("North America",Mexico,1981,2553,1172) | ("North America",Mexico,1980,2129,1048)
(2 rows)
cypex=# SELECT t_oil = lag(t_oil) OVER (ORDER BY year)
FROM t_oil
WHERE country = 'Mexico'
AND year IN (1980, 1981);
?column?
----------
f
(2 rows)