GROUP BY :
컬럼2에는 그룹별로 보고 싶은 컬럼을 쓰면 된다.
PARTITION BY 컬럼2 ORDER BY 컬럼3 은 두개다 있어도 되고 없어도된다.
SELECT Id
, Name
, Salary
, Department
, MAX(Salary) OVER (PARTITION BY DepartmentId) AS MaxSalary
FROM Employee
여기에
max_sal 열이 추가된다.
- 원래의 table에 그룹으로 보여준 열이 추가되어 보여준다 라고 생각
SELECT Id
, Name
, kg
, Line
, SUM(kg) OVER (ORDER BY Line) AS CumSum
FROM Elevator
SUM(kg) OVER (ORDER BY DepartmentId) -> 누적합
SELECT Id
, Name
, kg
, Line
, SUM(kg) OVER (ORDER BY Line PARTITION BY Id) AS CumSum
FROM Elevator
AVG()
BIT_AND()
BIT_OR()
BIT_XOR()
COUNT()
JSON_ARRAYAGG()
JSON_OBJECTAGG()
MAX()
MIN()
STDDEV_POP(), STDDEV(), STD()
STDDEV_SAMP()
SUM()
VAR_POP(), VARIANCE()
VAR_SAMP()
SELECT
val,
ROW_NUMBER() OVER w AS 'row_number',
RANK() OVER w AS 'rank',
DENSE_RANK() OVER w AS 'dense_rank'
FROM numbers
WINDOW w AS (ORDER BY val);
SELECT
t, val,
LAG(val) OVER w AS 'lag',
LEAD(val) OVER w AS 'lead',
val - LAG(val) OVER w AS 'lag diff',
val - LEAD(val) OVER w AS 'lead diff'
FROM series
WINDOW w AS (ORDER BY t);
SELECT
n,
LAG(n, 1, 0) OVER w AS 'lag',
LEAD(n, 1, 0) OVER w AS 'lead',
n + LAG(n, 1, 0) OVER w AS 'next_n',
n + LEAD(n, 1, 0) OVER w AS 'next_next_n'
FROM fib
WINDOW w AS (ORDER BY n);
- LAG(컬럼) OVER (PARTITION BY 컬럼 ORDER BY 컬럼)
- LAG(컬럼, 칸수) OVER (PARTITION BY 컬럼 ORDER BY 컬럼) -> 한칸씩이 아니라 내가 넣은 칸수 개수대로 밀고싶다.
- LAG(컬럼, 칸수, Default값) OVER (PARTITION BY 컬럼 ORDER BY 컬럼) -> null데이터가 아니라 default값을 넣고 싶다.
- LEAD(컬럼) OVER (PARTITION BY 컬럼 ORDER BY 컬럼)
- LEAD(컬럼, 칸수) OVER (PARTITION BY 컬럼 ORDER BY 컬럼)
- LEAD(컬럼, 칸수, Default값) OVER (PARTITION BY 컬럼 ORDER BY 컬럼)