엑셀보다 쉽고 빠른 SQL 5주 차 - 2
2. 조회한 데이터가 상식적이지 않은 값 가지고 있을 때
- 조건문으로 값의 범위 지정: 가장 큰 값, 가장 작은 값의 범위를 상식적인 수준에서 지정한다.
- 예시
SELECT customer_id,
name,
age,
CASE WHEN age<15 THEN 15
WHEN age>80 THEN 80
ELSE age END '수정한 나이'
FROM customers;
3. Pivot Table: MySQL 기준
-
| 구분 column (B 부분) | |
| 집계 기준 (A 부분) |
데이터 |
- 2개 이상의 기준으로 데이터 집계할 때, 보기 좋게 배열하여 보여주는 것
- A 부분: 행(row), 즉 그룹화할 기준
- B 부분: 열(column)
- 행(row): 각각의 레코드(record). 데이터가 세로로 층층히 쌓임
- 열(column): 각 column이 가로로 나열됨
1) 서브쿼리 사용하지 않은 경우
- 예시
SELECT region, -- 행(row)
SUM(CASE WHEN gender = 'male' THEN order_count ELSE 0 END) AS male_orders, -- 열(column)
SUM(CASE WHEN gender = 'female' THEN order_count ELSE 0 END) AS female_orders -- 열(column)
FROM orders
GROUP BY region;
2) 서브쿼리 있는 경우
- 예시
SELECT age, -- 행(row)
MAX(IF(gender='male', order_count, 0)) male, -- 열(column)
MAX(IF(gender='female', order_count, 0)) female -- 열(column)
FROM
(
SELECT b.gender,
CASE WHEN age BETWEEN 10 AND 19 THEN 10
WHEN age BETWEEN 20 AND 29 THEN 20
WHEN age BETWEEN 30 AND 39 THEN 30
WHEN age BETWEEN 40 AND 49 THEN 40
WHEN age BETWEEN 50 AND 59 THEN 50 END age,
COUNT(1) order_count
FROM food_orders a
INNER JOIN customers b
ON a.customer_id=b.customer_id
WHERE b.age BETWEEN 10 AND 59
GROUP BY 1, 2
) t
GROUP BY 1
ORDER BY 1 DESC;
4. Window Function
- 각 행의 관계를 정의하기 위한 함수
-
WINDOW_FUNCTION(ARGUMENT) OVER (PARTITION BY 그룹 기준 컬럼 ORDER BY 정렬 기준)
window_fuction: SUM, RANK 등 기능 이름
argument: 함수에 따라 작성하거나 생략
partition by: 그룹 나누기 위한 기준이 되는 column, group by와 유사하다
order by: 정렬할 기준이 되는 column
1) RANK
-
RANK() OVER (PARTITION BY column1 ORDER BY column2)
column1 기준으로 그룹 나누고, column2 기준으로 정렬한다.
- 특정 기준으로 순위 매겨준다.
- 예시
SELECT cuisine_type,
restaurant_name,
RANK() OVER (PARTITION BY cuisine_type ORDER BY order_count DESC),
order_count
FROM
(
SELECT cuisine_type, restaurant_name, COUNT(1) order_count
FROM food_orders
GROUP BY 1, 2
) a;
2) SUM
(1) 합계
-
SELECT order_date,
SUM(price) OVER (PARTITION BY order_date) AS total_price
FROM payments;
- 지정된 그룹에 대한 전체 합을 구한다.
위의 경우, order_date 별로 합계 구함
(2) 누적합
-
SELECT order_id,
price,
SUM(price) OVER (PARTITION BY order_date ORDER BY order_id) AS cum_sum
FROM payments;
- 데이터를 순차적으로 더하는 누적합을 구한다.
- ORDER BY가 필수이다.
- 위의 경우, order_date 별로 order_id 순서대로 누적합을 구한다.
- 예시
SELECT cuisine_type,
restaurant_name,
cnt_order,
SUM(cnt_order) OVER (PARTITION BY cuisine_type) sum_cuisine, -- 합
SUM(cun_order) OVER (PARTITION BY cuisine_type ORDER BY cnt_order) cum_cuisine -- 누적합
FROM
(
SELECT cuisine_type,
restaurant_name,
COUNT(1) cut_order
FROM food_orders
GROUP BY 1, 2
) a
ORDER BY cuisine_type , cnt_order;
SQL 퀘스트 8
문 31
문제내용
doctors 테이블에서 각 전공 별 의사 수를 계산하는 쿼리를 작성해주세요!
답안:
SELECT major,
COUNT(1)
FROM doctors
GROUP BY major;
- 헷갈린 점: GROUP BY에 사용한 column을 SELECT에 꼭 쓸 필요가 없다고 생각했다.
문제 내용에서 의사 수 계산을 요구했으므로 COUNT만 쓰면 되지 않을까 생각했다.
- 개선점: GROUP BY에 사용된 column을 SELECT 문에서도 쓰는 게 일반적이다.
문 32
doctors 테이블에서 현재 날짜 기준으로 5년 이상 근무(hire_date)한 의사 수를 계산하는 쿼리를 작성해주세요!
답안:
SELECT COUNT(*) AS num_of_doctors
FROM doctors
WHERE hire_date <= DATE_SUB(CURDATE(), INTERVAL 5 YEAR);
-- 오늘 날짜에서 5년을 뺀 날짜보다 이전에 고용된 의사. 즉, 5년 이상 일한 의사
1. DATE_SUB
- DATE_SUB(기준 날짜, INTERVAL 숫자값 interval)
- 시간단위(interval):
YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE 등
예시: 5 MONTH → 기준 날짜에서 5달 빼기
- 기준 날짜에서 내가 지정한 특정 기간을 뺀 값을 반환하는 함수
- 예시
SELECT DATE_SUB('2025-04-30', INTERVAL 10 DAY);
출력값 2025-04-20
2. DATE_ADD
- DATE_ADD(기준 날짜, INTERVAL 숫자값 interval)
- 기준 날짜에서 내가 지정한 특정 기간을 더한 값을 반환하는 함수
아티클 스터디: SQL의 가독성을 높이는 다섯 가지 사소한 습관
아티클
https://yozm.wishket.com/magazine/detail/1519/
[주제]
SQL의 가독성을 높이는 다섯 가지 사소한 습관
- 예약어는 대문자로
- 행갈이를 자주 하자
- 행갈이를 더 자주 하자
- 주석을 쓰자
- Alias를 잘 쓰자
[아티클 요약]
SQL 문법은 파이썬에 비해 자유로운 편이다. 강제 되지 않더라도 가독성 있게 코드를 작성하면 이점이 있다. 가독성을 높이는 다섯 가지 팁은 다음과 같다.
1. 예약어를 대문자로 쓴다. 코드 구분이 확실히 쉬워진다.
SELECT column1, column2
FROM table1
2. 행갈이를 자주 하자. SELECT, FROM, GROUP BY 등 절이 바뀔 때마다 행을 바꾸는 것이다. 하나의 라인에 모든 코드를 다 쓰면 나중에 코드를 찾을 때 필요 없는 코드까지 다 읽어야 하는 문제가 있다.
SELECT column1,
column2
FROM table1
WHERE column1 BETWEEN 1 AND 10
ORDER BY column2;
3. 행갈이를 더 자주 한다. AND, OR 등이 사용될 때에도 행을 바꾼다. 쿼리를 쓸 때 코드의 일부분을 제외하거나 포함하는 일이 있다. 이때 행갈이를 해두면 필요한 부분을 빠르게 찾아 주석 처리하여 로직에서 제외할 수 있다.
SELECT -- column1,
column2
FROM table1
WHERE -- column1 = 'lemon'
AND column2 = 'female'
/* ORDER BY age,
nationality */;
- SQL 주석 처리하는 법
1줄 주석으로 만들기: 주석 시작하는 부분에 --
여러 줄 주석으로 만들기: 주석 시작과 끝에 /* */
4. 주석을 쓴다. 코드를 작성한 의도를 남겨두는 것이다. 서브 쿼리, CASE, IF, WHERE 절의 조건 등에 주로 사용한다. 다음의 내용을 포함하면 좋다.
- 서브쿼리의 의미
- 조건 해석에 오해 있을 수 있는 경우
- 조건에 추가 설명이 필요한 경우
- 조건이 왜 필요한지, 어떻게 동작하는지 작성해 다음에 이해하기 쉽게 한다.
SELECT COUNT(1) AS num_of_customers
FROM payments
WHERE date <= DATE_SUB(CURDATE(), INTERVAL 8 YEAR);
-- 현재 날짜 기준으로 구매한지(date) 8년 이상 된 고객 수를 계산
5. Alias를 잘 쓴다. 코드의 내용을 잘 함축한 별명을 짓는 것이 좋다. 별명을 대강 작성하면 코드를 다시 볼 때 이해하는 데 어려움을 느낄 수 있다.
SELECT MAX(restaurant_name) AS max_restaurant_name,
cuisine_type
FROM food_orders
WHERE price >= 50000
GROUP BY cuisine_type;
가장 중요한 것은 사내 구성원과 합의한 규칙이다. 팀 내부에서 이미 통용되는 규칙을 따르는 것이 필요하다. 내 코드를 다른 사람이 보는 경우를 염두에 두고 코드를 작성하자.
[인사이트]
- 코드를 나 혼자만 보는 것이 아니기에 코드의 가독성이 중요하다고 한다. 이전의 아티클 스터디에서 팀원들과 커뮤니케이션 스킬의 중요성에 대해 얘기한 적이 있다. 코드를 가독성 있게 작성하는 것은 소통을 원활하게 한다. 그래서 이 또한 넓게 보면 커뮤니케이션 스킬에 포함될 수 있지 않을까 생각했다.
- 별명을 쓰는 것을 좋아하지 않아서 대충 짓곤 했다. 그런데 별명을 대충 지으면 실무에서 번거로운 상황이 발생할 수 있다고 한다. 코드의 의미를 잘 전달하는 간략한 이름 짓기 습관을 들여야겠다.
//