MySQL - Modes, 윈도우 함수

Modes

Viewing Modes

SELECT @@GLOBAL.sql_mode;

SELECT @@SESSION.sql_mode;

Setting Modes

SET GLOBAL sql_mode = 'modes';

SET SESSION sql_mode = 'modes';

[참고] sql mode 정보

---

윈도우 함수

[참고] 윈도우 함수 개념 및 구문 정보

윈도우란 ? 데이터 그룹

CREATE DATABASE window_funcs;

USE window_funcs;

CREATE TABLE employees (
    emp_no INT PRIMARY KEY AUTO_INCREMENT,
    department VARCHAR(20),
    salary INT
);
 
INSERT INTO employees (department, salary) VALUES
('engineering', 80000),
('engineering', 69000),
('engineering', 70000),
('engineering', 103000),
('engineering', 67000),
('engineering', 89000),
('engineering', 91000),
('sales', 59000),
('sales', 70000),
('sales', 159000),
('sales', 72000),
('sales', 60000),
('sales', 61000),
('sales', 61000),
('customer service', 38000),
('customer service', 45000),
('customer service', 61000),
('customer service', 40000),
('customer service', 31000),
('customer service', 56000),
('customer service', 55000);

OVER()

윈도우 함수가 작동하는 데이터의 범위를 지정하는 데 사용

집계함수 OVER();
ex) SELECT department, AVG(salary) OVER() FROM employees;

PARTITION BY

OVER(PARTITION BY column);
ex) SELECT department, salary, AVG(salary) OVER(PARTITION BY department) FROM employees;

ORDER BY

OVER(ORDER BY column);
ex) SELECT department, salary, AVG(salary) OVER(PARTITION BY department ORDER BY salary)
	FROM employees;

NTILE(number)

number 버킷으로 나누기

NTILE(number)
ex) SELECT emp_no, department, salary, NTILE(4) OVER(ORDER BY salary DESC) AS salary_quart
	FROM employees; // 4분할

FIRST_VALUE(expr)

FIRST_VALUE(expr)
ex) SELECT emp_no, department, salary, FIRST_VALUE(emp_no) OVER(ORDER BY salary DESC)
	FROM employees;

LAG(expr), LEAD(expr)

이전, 이후 행들의 정보

LAG/LEAD(column)
ex) SELECT
	emp_no,
    department,
    salary,
    salary - LAG/LEAD(salary) OVER(ORDER BY salary DESC) AS salary_diff_lag,
    salary - LEAD(salary) OVER(ORDER BY salary DESC) AS salary_diff_lead
FROM employees;

ROW_NUMBER(), RANK(), DENSE_RANK()

CREATE TABLE number_table (
	number INT
);

INSERT INTO number_table (number) VALUES (1),(1),(2),(3),(3),(3),(4),(4),(5);

ex)  SELECT
		number,
        ROW_NUMBER() OVER w AS 'row_number',
        RANK() OVER w AS 'rank',
        DENSE_RANK() OVER w AS 'dense_rank'
     FROM number_table WINDOW w AS (ORDER BY number);