RANK() 1 2 3 4 5 5 5 5 8
중복된 값이 있는 경우 해당 값에 동일한 순위가 할당되고 후속 순위는 건너뜁니다.
RANK() OVER (PARTITION BY department ORDER BY salary DESC)
DENSE_RANK() 1 2 3 4 5 5 5 5 6
중복이 있는 경우 순위를 건너뛰지 않습니다
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC)
ROW_NUMBER() 1 2 3 4 5 6 7 8
동점을 고려하지 않고 단순히 행 번호를 순차적으로 증가
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC)
STDDEV 표준편자
VARIANCE 평균과의 차이 제곱의 평균
FLOOR() 주어진 숫자보다 작거나 같은 가장 큰 정수를 반환
TRUNCATE() 함수는 숫자를 지정된 소수 자릿수까지 자름( TRUNCATE(number, decimal_places))
ROUND() 함수는 숫자 값에 따라 숫자를 반올림하거나 내림
RUNNING_SUM : SUM(weight) over (order by turn)
| person_name | weight | turn | total |
| ----------- | ------ | ---- | ----- |
| Alice | 250 | 1 | 250 |
| Alex | 350 | 2 | 600 |
| John Cena | 400 | 3 | 1000 |
| Marie | 200 | 4 | 1200 |
| Bob | 175 | 5 | 1375 |
| Winston | 500 | 6 | 1875 |
CONCAT
더하기 및 빼기
DATE_ADD(CURDATE(), INTERVAL 10 DAY) MONTH YEAR 등
간격 : DATEDIFF('2024-12-25', '2024-01-01')
MONTH_BETWEEN : SELECT (YEAR(date2) - YEAR(date1)) * 12 + (MONTH(date2) - MONTH(date1)) AS months_between FROM (SELECT '2023-01-01' AS date1, '2024-07-18' AS date2) AS dates;
형지정SELECT DATE_FORMAT(CURDATE(), '%W, %M %d, %Y')
SELECT EXTRACT(YEAR FROM '2024-07-16') MONTH DAY날짜 일부 추출
UPPER / LOWER
NULL 처리 IFNULL 또는 COALESCE
날짜 형 변환 DATE_FORMAT() / YEAR() , MONTH()등
| 포맷문자 | 설명(예시) | 포맷문자 | 설명(예시) |
| %Y | 년도 (2021) | %m | 월 (01, 02, 11) |
| %y | 년도 (21) | %c | 월 (1, 8) |
| %d | 일(01, 19) | %M | 월 (January, August) |
| %e | 일(1, 19) | %b | 월(Jan, Aug) |
| %T | 시간 (12:30:00) | %W | 요일(Wednesday, friday) |
| %r | 시간 (12:30:00 AM) | %a | 요일(Wed, Fri) |
| %H | 24시간 시간(01, 14, 18) | %i | 분 (00) |
| %l | 12시간 시간 (01, 02, 06) | %S | 초 (00) |
CREATE TABLE sales (
id INT AUTO_INCREMENT PRIMARY KEY,
sale_date DATE,
amount DECIMAL(10, 2)
);
INSERT INTO sales (sale_date, amount) VALUES
('2024-01-10', 100.00),
('2024-01-15', 200.00),
('2024-02-10', 300.00),
('2024-02-15', 400.00),
('2024-03-10', 500.00),
('2024-03-15', 600.00),
('2024-04-10', 700.00),
('2024-04-15', 800.00);
1: 월별(MoM) 매출 변화
SELECT
month,
total_sales,
LAG(total_sales, 1) OVER (ORDER BY month) AS 전월매출,
(total_sales - LAG(total_sales, 1) OVER (ORDER BY month)) AS 당월_전월차이
FROM (
SELECT
DATE_FORMAT(sale_date, '%Y-%m') AS month,
SUM(amount) AS total_sales
FROM
sales
GROUP BY
DATE_FORMAT(sale_date, '%Y-%m')
) AS monthly_sales;
2.월별 매출 비율 변화
SELECT
month,
total_sales,
LAG(total_sales, 1) OVER (ORDER BY month) AS 전월매출,
ROUND((total_sales - LAG(total_sales, 1) OVER (ORDER BY month)) / LAG(total_sales, 1) OVER (ORDER BY month) * 100, 2) AS 전월대비증감율
FROM (
SELECT
DATE_FORMAT(sale_date, '%Y-%m') AS month,
SUM(amount) AS total_sales
FROM
sales
GROUP BY
DATE_FORMAT(sale_date, '%Y-%m')
) AS monthly_sales;
3.지난 3개월 동안의 월별 평균
SELECT
month,
total_sales,
ROUND(AVG(total_sales) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS "3개월평균"
FROM (
SELECT
DATE_FORMAT(sale_date, '%Y-%m') AS month,
SUM(amount) AS total_sales
FROM
sales
GROUP BY
DATE_FORMAT(sale_date, '%Y-%m')
) AS monthly_sales;
drop table customers
CREATE TABLE customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
join_date DATE
);
INSERT INTO customers (join_date) VALUES
('2024-01-05'),
('2024-02-10'),
('2024-02-15'),
('2024-03-20'),
('2024-03-25');
drop table orders
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT,
order_date DATE,
amount DECIMAL(10, 2),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
INSERT INTO orders (customer_id, order_date, amount) VALUES
(1, '2024-01-10', 100.00),
(1, '2024-01-15', 200.00),
(2, '2024-02-10', 300.00),
(3, '2024-02-15', 400.00),
(4, '2024-03-10', 500.00),
(5, '2024-03-15', 600.00),
(1, '2024-04-10', 700.00),
(2, '2024-04-15', 800.00);3.신규 및 기존 고객의 월 평균 매출을 계산
WITH monthly_sales AS (
SELECT
o.customer_id,
DATE_FORMAT(o.order_date, '%Y-%m') AS month,
SUM(o.amount) AS monthly_amount,
c.join_date
FROM
orders o
JOIN
customers c ON o.customer_id = c.customer_id
GROUP BY
o.customer_id, DATE_FORMAT(o.order_date, '%Y-%m')
),
customer_status AS (
SELECT
customer_id,
month,
monthly_amount,
CASE
WHEN DATE_FORMAT(join_date, '%Y-%m') = month THEN 'new'
ELSE 'existing'
END AS status
FROM
monthly_sales
)
SELECT
month,
status,
ROUND(AVG(monthly_amount), 2) AS average_sales
FROM
customer_status
GROUP BY
month, status
ORDER BY
month, status;
4.지난 6개월 동안의 판매 추세 계산
WITH monthly_sales AS (
SELECT
DATE_FORMAT(order_date, '%Y-%m') AS month,
SUM(amount) AS total_sales
FROM
orders
WHERE
order_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
GROUP BY
DATE_FORMAT(order_date, '%Y-%m')
)
SELECT
month,
total_sales,
LAG(total_sales, 1) OVER (ORDER BY month) AS previous_month_sales,
(CASE
WHEN total_sales > LAG(total_sales, 1) OVER (ORDER BY month) THEN '증가'
WHEN total_sales < LAG(total_sales, 1) OVER (ORDER BY month) THEN '감소'
ELSE '변화없음'
END) AS trend
FROM
monthly_sales;
CREATE TABLE Orders (
order_id INT,
order_date DATE,
product_id INT,
quantity INT
);
INSERT INTO Orders (order_id, order_date, product_id, quantity) VALUES
(1, '2023-01-15', 101, 10),
(2, '2023-02-20', 102, 20),
(3, '2023-03-10', 101, 30),
(4, '2023-04-05', 103, 25),
(5, '2023-05-20', 104, 15),
(6, '2023-06-18', 101, 40),
(7, '2023-07-12', 102, 50),
(8, '2023-08-25', 103, 35),
(9, '2023-09-17', 104, 45),
(10, '2023-10-03', 101, 20);
5.매출 통계 - 년도별로 가장 많이 팔린 상품의 ID와 그 판매량
SELECT
YEAR(order_date) AS year,
product_id,
SUM(quantity) AS total_quantity
FROM Orders
GROUP BY year, product_id
HAVING total_quantity = (
SELECT MAX(total_quantity)
FROM (
SELECT
YEAR(order_date) AS year,
product_id,
SUM(quantity) AS total_quantity
FROM Orders
GROUP BY year, product_id
) AS yearly_sales
WHERE yearly_sales.year = year
);
WITH AverageQuantity AS (
SELECT
product_id,
AVG(quantity) AS avg_quantity
FROM Inventory
GROUP BY product_id
),
MaxQuantity AS (
SELECT
product_id,
MAX(quantity) AS max_quantity
FROM Inventory
GROUP BY product_id
)
SELECT
aq.product_id
FROM AverageQuantity aq
JOIN MaxQuantity mq ON aq.product_id = mq.product_id
WHERE mq.max_quantity <= aq.avg_quantity;
CREATE TABLE Inventory (
warehouse_id INT,
product_id INT,
quantity INT
);
INSERT INTO Inventory (warehouse_id, product_id, quantity) VALUES
(1, 101, 100),
(1, 102, 200),
(1, 103, 150),
(2, 101, 50),
(2, 102, 250),
(2, 103, 100),
(3, 101, 70),
(3, 102, 80),
(3, 103, 120);
6.모든 창고의 재고 수량이 평균 이하인 상품
WITH AverageQuantity AS (
SELECT
product_id,
AVG(quantity) AS avg_quantity
FROM Inventory
GROUP BY product_id
),
MaxQuantity AS (
SELECT
product_id,
MAX(quantity) AS max_quantity
FROM Inventory
GROUP BY product_id
)
SELECT
aq.product_id
FROM AverageQuantity aq
JOIN MaxQuantity mq ON aq.product_id = mq.product_id
WHERE mq.max_quantity <= aq.avg_quantity;
CREATE TABLE Customers (
customer_id INT,
customer_name VARCHAR(100)
);
CREATE TABLE Orders (
order_id INT,
customer_id INT,
order_date DATE,
product_id INT,
quantity INT
);
INSERT INTO Customers (customer_id, customer_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');
INSERT INTO Orders (order_id, customer_id, order_date, product_id, quantity) VALUES
(1, 1, '2023-01-15', 101, 1),
(2, 1, '2023-02-20', 102, 2),
(3, 1, '2023-03-10', 103, 3),
(4, 2, '2023-04-05', 104, 1),
(5, 2, '2023-05-20', 105, 2),
(6, 3, '2023-06-18', 106, 1),
(7, 3, '2023-07-12', 107, 2),
(8, 3, '2023-08-25', 108, 3),
(9, 3, '2023-09-17', 109, 4),
(10, 2, '2023-10-03', 110, 1);
7.한 해 동안 3회 이상 구매한 고객의 ID와 첫 번째 구매 날짜
SELECT
o.customer_id,
MIN(o.order_date) AS first_order_date
FROM Orders o
JOIN (
SELECT
customer_id,
COUNT(order_id) AS order_count
FROM Orders
WHERE YEAR(order_date) = 2023
GROUP BY customer_id
HAVING order_count >= 3
) frequent_customers ON o.customer_id = frequent_customers.customer_id
GROUP BY o.customer_id;
CREATE TABLE Orders (
order_id INT,
customer_id INT,
order_date DATE,
product_id INT,
quantity INT,
price_per_unit DECIMAL(10, 2)
);
INSERT INTO Orders (order_id, customer_id, order_date, product_id, quantity, price_per_unit) VALUES
(1, 1, '2023-01-15', 101, 10, 20.00),
(2, 1, '2023-02-20', 102, 5, 30.00),
(3, 1, '2023-03-10', 103, 2, 25.00),
(4, 2, '2023-04-05', 104, 1, 100.00),
(5, 2, '2023-05-20', 105, 3, 45.00),
(6, 3, '2023-06-18', 106, 7, 15.00),
(7, 3, '2023-07-12', 107, 8, 12.00),
(8, 4, '2023-08-25', 108, 4, 50.00),
(9, 4, '2023-09-17', 109, 6, 35.00),
(10, 5, '2023-10-03', 110, 9, 10.00);
8.총 주문 금액이 가장 높은 상위 5명의 고객 ID와 그 총 주문 금액
SELECT
customer_id,
SUM(quantity * price_per_unit) AS total_spent
FROM Orders
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 5;
CREATE TABLE Orders (
order_id INT,
order_date DATE,
product_id INT,
quantity INT
);
INSERT INTO Orders (order_id, order_date, product_id, quantity) VALUES
(1, '2023-01-15', 101, 10),
(2, '2023-02-20', 101, 20),
(3, '2023-03-10', 101, 15),
(4, '2023-04-05', 102, 25),
(5, '2023-05-20', 102, 35),
(6, '2023-06-18', 102, 30),
(7, '2023-07-12', 103, 40),
(8, '2023-08-25', 103, 45),
(9, '2023-09-17', 103, 50),
(10, '2023-10-03', 104, 60);9.월별 주문량이 전월 대비 증가한 상품의 ID와 그 증가율
WITH MonthlySales AS (
SELECT
product_id,
DATE_FORMAT(order_date, '%Y-%m') AS month,
SUM(quantity) AS monthly_quantity
FROM Orders
GROUP BY product_id, month
),
MonthlyGrowth AS (
SELECT
product_id,
month,
monthly_quantity,
LAG(monthly_quantity) OVER (PARTITION BY product_id ORDER BY month) AS prev_month_quantity
FROM MonthlySales
)
SELECT
product_id,
month,
((monthly_quantity - prev_month_quantity) / prev_month_quantity) * 100 AS growth_rate
FROM MonthlyGrowth
WHERE prev_month_quantity IS NOT NULL AND monthly_quantity > prev_month_quantity;
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100),
manager_id INT
);
INSERT INTO employees (employee_id, employee_name, manager_id) VALUES
(1, 'CEO', NULL),
(2, 'Manager 1', 1),
(3, 'Manager 2', 1),
(4, 'Employee 1', 2),
(5, 'Employee 2', 2),
(6, 'Employee 3', 3);10. CONNECT BY 대신 사용 RECURSIVE
WITH RECURSIVE EmployeeHierarchy AS (
-- Anchor member: 최고 관리자 (manager_id가 NULL인 직원)
SELECT
employee_id,
employee_name,
manager_id,
employee_name AS path,
0 AS level
FROM
employees
WHERE
manager_id IS NULL
UNION ALL
-- Recursive member: 상위 직원과 매칭되는 하위 직원
SELECT
e.employee_id,
e.employee_name,
e.manager_id,
CONCAT(h.path, ' -> ', e.employee_name) AS path,
h.level + 1 AS level
FROM
employees e
INNER JOIN EmployeeHierarchy h ON e.manager_id = h.employee_id
)
SELECT * FROM EmployeeHierarchy
ORDER BY level, employee_id;