Window function 정리 Work/PostgreSQL2020. 7. 1. 13:15
- 윈도우 함수 : 현재 행과 관련된 결과집합의 여러가지 연산을 수행. 집계함수와 유사하나 단일 행으로 그룹화 되지 않는다.
- 일반적인 windows fuction : https://www.postgresql.org/docs/9.3/functions-window.html
PostgreSQL: Documentation: 9.3: Window Functions
Window functions provide the ability to perform calculations across sets of rows that are related to the current query row. See Section 3.5 for an introduction to this feature. The built-in window functions are listed in Table 9-49. Note that these functio
www.postgresql.org
- 조금 난이도 있는 window function 활용 : https://www.cybertec-postgresql.com/en/calculating-differences-between-rows-in-sql/
Calculating differences between rows in SQL - Cybertec
Calculating differences between rows | How can one calculate the difference between the current and the previous row? | timeseries data
www.cybertec-postgresql.com
위 페이지의 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)
