TIL #9. SQL 날짜, 시간, 랜덤 추출

 

SQL 공부

[MySQL] 날짜 형식 변경 : DATE_FORMAT

SELECT DATE_FORMAT ('2024-11-28 09:45', '%Y년 %m월 %d일')  >>  2024년 11월 28일

SELECT 
    DATE_FORMAT(DATETIME, '%Y-%m-%d') AS 날짜
FROM table

참고)

[MySQL] DATE_FORMAT: 날짜/시간 표기 방식 지정하기

 

시간 더하기 : DATE_ADD

DATE_ADD(기준 날짜, INTERVAL 뺄 수치)

-- 현재 시간에 1초 더하기
SELECT DATE_ADD(NOW(), INTERVAL 1 SECOND);

-- 현재 시간에 1분 빼기
SELECT DATE_ADD(NOW(), INTERVAL -1 MINUTE);

-- 1시간, 1일, 1달, 1년도 동일하게 HOUR, DAY, MONTH, YEAR 넣으면 됨

 

시간 빼기 : DATE_SUB

DATE_SUB(기준 날짜, INTERVAL 뺄 수치)

-- 현재 시간에 1시간 빼기
SELECT DATE_SUB(NOW(), INTERVAL 1 HOUR);

-- 현재 시간에 1시간 더하기
SELECT DATE_SUB(NOW(), INTERVAL -1 HOUR);

 

시간 차이 구하기 : DATEDIFF, TIMESTAMPDIFF

DATEDIFF(날짜1, 날짜2);

SELECT DATEDIFF('2024-12-31','2024-01-01') +1
-- 결과 : 365
-- 1년이 총 몇일인지 구하려면 저렇게 1 더해야 함
-- 오늘-어제로 하면 날짜 차이는 하루나니까 1로 나오는데, 총 며칠인지 구하려면 +1하는 논리

 

TIMESTAMPDIFF(단위, 날짜1, 날짜2);

SELECT *, 
       TIMESTAMPDIFF(YEAR, hire_date, SYSDATE) AS 근무기간
FROM doctors;

*단위

SECOND : 초
MINUTE : 분
HOUR : 시
DAY : 일
WEEK : 주
MONTH : 월
QUARTER : 분기
YEAR : 연

참고)

[MySQL] 날짜 차이 가져오기 (DATEDIFF, TIMESTAMPDIFF 함수)

 

 

집계함수

함수	설명
COUNT	데이터의 행 수를 반환
COUNT(DISTINCT 컬럼)	고유한 값을 갖는 데이터의 행 수 반환

 

예제 코드

-- 데이터의 행 수 세기
SELECT COUNT(*) AS row_count
FROM employees;

-- NULL 포함여부에 따른  COUNT 차이
SELECT COUNT(*) AS total_rows, -- NULL 포함
       COUNT(salary) AS non_null_salaries -- NULL 제외
FROM employees;

-- 부서별 고유한 직원 수 계산
SELECT department, COUNT(DISTINCT employee_id) AS unique_employees
FROM employees
GROUP BY department;

-- 특정 열의 값 합산
SELECT SUM(salary) AS total_salary
FROM employees;

-- 열 값의 평균 구하기
SELECT AVG(salary) AS average_salary
FROM employees;

-- 열 값 중 최대값과 최소값 찾기
SELECT MAX(age) AS max_age,
       MIN(age) AS min_age
FROM employees;

-- 날짜 데이터를 활용한 MIN/MAX
SELECT MIN(hire_date) AS earliest_hire,
       MAX(hire_date) AS latest_hire
FROM employees;

-- 여러 집계함수 동시 사용
SELECT COUNT(*) AS row_count,
       AVG(salary) AS average_salary,
       MAX(age) AS max_age,
       MIN(age) AS min_age,
       SUM(salary) AS total_salary
FROM employees;

 

MySQL은 특이하게 HAVING에서 alias쓸 수 있음

원래 SELECT 전 단계 애들은 alias를 못 쓰는건데 특이하게 HAVING에서 사용 가능함

DBMS 종류	HAVING에서 별칭 사용
MySQL	가능
Oracle	불가능
PostgreSQL	가능
SQL Server	불가능

 

WHERE와 HAVING 차이점 요약

• WHERE
  • 그룹화 이전에 데이터를 필터링
  • 개별 행을 기준으로 조건을 적용
• HAVING
  • 그룹화 이후에 데이터를 필터링, 즉 그룹화된 결과를 기준으로 조건을 적용
• 차이 요약
  • 데이터 → WHERE (개별 행 필터링) → GROUP BY (그룹화) → HAVING (그룹화된 결과 필터링)

 

RAND() 함수로 데이터 샘플링

RAND 함수는 0 이상 1 미만의 난수(예: 0.456)를 생성합니다. ORDER BY와 함께 사용하면 각 행마다 랜덤한 값을 생성한 후 이를 기준으로 정렬합니다. 이후 LIMIT으로 원하는 갯수만큼 샘플링하는 효과를 얻을 수 있습니다~!

단, 메모리 부하 올 수 있으니 10만 행 이하의 데이터셋에서 사용 권장!

-- 랜덤하게 5개의 행 샘플링
SELECT * 
FROM employees 
ORDER BY RAND() 
LIMIT 5;

-- 나이가 30 이상인 직원 중 랜덤하게 3명 추출
SELECT * 
FROM employees 
WHERE age >= 30 
ORDER BY RAND() 
LIMIT 3;

 

예시)

 

SQL 코드 작성 순서

select from join on where groupby having orderby limit

SQL 코드 실행 순서

from on join where groupby having select orderby limit

 

 

오답노트

문제. doctors 테이블에서 각 의사의 근무 기간을 계산하는 쿼리를 작성해주세요

id	name	major	hire_date
1	르탄이	피부과	2018-05-10
2	배캠이	성형외과	2019-06-15
3	구구이	안과	2020-07-20

-- 오답
SELECT *, 
(SYSDATE - hire_date) AS 근무기간
FROM doctors
-- 


-- 정답

ㅇㅇㅇ

 

데이터 분석 관련 아티클 읽기

오늘 아래 아티클을 읽고 팀원들과 의견을 나누었다

비개발자를 위한 엑셀로 이해하는 SQL: ④ GROUP BY와 HAVING | 요즘IT

 

• 요약 : 데이터를 유형 별로 묶어 특징을 살필 때 엑셀에서는 여러 단계의 처리를 통해서만 알 수 있었는데 SQL에서는 GROUP BY와 HAVING을 통해 쉽게 처리할 수 있다.

 

• 주요 포인트

1. GROUP BY
   • 1번째 : 어떤 기준으로 그룹핑할지
   • GROUP BY 1,2와 같이 열의 순서를 이용하는 건 비추. 쿼리가 길어지면 알아보기 어려움.
   • 2번쨰 : 그룹핑해서 보고 싶은 값이 뭔지(총계, 평균, 최솟값 등)
   • 그룹핑한 후에는 그룹대로 묶여서 개별 데이터를 나열할 수 없음
2. HAVING
   • GROUP BY를 통해 그룹핑한 후 조건을 걸어야 할 때 필요한 구문
   • WHERE과 동일하게 논리 연산자(같다, 같지 않다), 비교(초과, 미만, 이상, 이하), 범위 또는 목록, 문자열 등을 조건으로 가질 수 있고 콤마를 통해 여러 조건을 달 수 있다.

 

• 핵심 개념
  • WHERE는 그룹핑하기 전에 조건문 걸 때 쓰는 거고, HAVING은 그룹핑한 후에 조건문 걸 때 쓰는 구문
  • 쿼리를 작성하는 순서 SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY
  • 쿼리가 실행되는 순서 FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

 

• 용어 정리
  • 집계 함수 : SUM, AVG, MAX, MIN 집계 함수는 GROUP BY와 함께 사용되는 것이 일반적. 데이터 전체를 대상으로 총합을 구하는 등 사용할 수도 있지만 그 때는 임의로 데이터 전체를 하나의 그룹으로 묶었다는 개념으로 적용되는 것
  • 집계 함수 : AVG, MAX, MIN, SUM 등
    • 일반적으로 집계 함수는 group by와 함께 사용되는 경우가 많습니다.
    • 더불어 집계 함수가 group by 와 사용되지 않고, 테이블 전체에 적용할 수도 있습니다.
    • 하지만 이 경우에는 테이블 전체를 묵시적으로 group by 대상으로 생각하고 집계 함수를 적용하므로 Select 절에는 집계 함수 외에는 다른 컬럼이 올 수가 없습니다.

 

• 개인 인사이트

GROUP BY를 하면 WHERE을 못 쓰고 HAVING만 쓸 수 있다고 착각하고 있었는데, WHERE와 HAVING 둘 다 사용 가능한 것을 알게 되었다. 쿼리 실행 순서를 기억하면서 앞으로 코드 작성해야겠다. 개념 이해가 더 잘 되어서 앞으로 더 잘 활용할 수 있을 듯.

GROUP BY 공부하는 김에 문제 풀면서 계속 헷갈리던 집계 함수 부분도 찾아봄. 어떤 때는 GROUP BY 안 쓰면 에러나고 어떤 때는 에러가 안 났는데 그 이유가 테이블 전체를 그룹핑했다고 가정하고 코드가 작동한 거였다는 사실을 알게 됨. GROUP BY를 따로 안 쓰고 테이블 전체를 대상으로 집계를 할거면 SELECT절에 다른 일반 컬럼 없이 집계 함수만 써야 하고, 그런 경우가 아닐 경우에는 GROUP BY를 명시해야겠다.