[SQL] 문제 풀이 모음4

🔒 문제

CREATE TABLE attendance (
    employee_id INT,
    attendance_date DATE,
    status VARCHAR(10),
    PRIMARY KEY (employee_id, attendance_date)
);

INSERT INTO attendance (employee_id, attendance_date, status)
VALUES
(1, '2025-03-05', 'present'),
(1, '2025-03-06', 'absent'),
(1, '2025-03-07', 'absent'),
(1, '2025-03-08', 'present'),
(2, '2025-03-05', 'present'),
(2, '2025-03-06', 'present'),
(2, '2025-03-07', 'present'),
(2, '2025-03-08', 'absent'),
(3, '2025-03-04', 'present'),
(3, '2025-03-05', 'absent'),
(3, '2025-03-06', 'absent'),
(3, '2025-03-07', 'present'),
(4, '2025-03-01', 'absent'),
(4, '2025-03-02', 'absent'),
(4, '2025-03-03', 'present'),
(4, '2025-03-04', 'present'),
(4, '2025-03-05', 'absent'),
(4, '2025-03-06', 'absent'),
(4, '2025-03-07', 'present'),
(5, '2025-03-01', 'present'),
(5, '2025-03-02', 'present'),
(5, '2025-03-03', 'present'),
(5, '2025-03-04', 'absent'),
(5, '2025-03-05', 'absent'),
(5, '2025-03-06', 'present');

문제:
각 직원에 대해 연속적인 결근(absent)이 2일 이상인 직원들의 정보와 그 연속 결근 기간을 찾아 출력하시오.
직원들이 결근한 날짜를 기준으로 연속적인 결근 기간을 계산해야 합니다. 예를 들어, 직원 1번은 2025-03-02와 2025-03-03이 연속적으로 결근이므로, 그 기간은 2일로 계산해야 합니다.

 

🔓 내가 푼 답

 

with tb as (

select employee_id,

attendance_date,

dense_rank () over(partition by employee_id order by attendance_date),

datediff('2999-12-31',attendance_date),

dense_rank() over(partition by employee_id order by attendance_date) +

datediff('2999-12-31',attendance_date) as sum_days

from attendance where status='absent'

)

select t.employee_id,t.attendance_date

from tb t join (select employee_id,sum_days from tb group by employee_id,sum_days having count(*)>=2) b

on t.employee_id=b.employee_id

and t.sum_days=b.sum_days;

 

 

 풀이>

select employee_id,

attendance_date,

dense_rank () over(partition by employee_id order by attendance_date),

datediff('2999-12-31',attendance_date),

dense_rank() over(partition by employee_id order by attendance_date) +

datediff('2999-12-31',attendance_date) as sum_days

from attendance where status='absent'

 

 

(사원별 출근일을 정렬해서 순위를 구한 값=연속된 정수값)과 (특정 아무 날짜 값과 attendance_date 날짜 차이를 구한 값=정수값)을 더해 그 값이 같으면 연속된 날짜로 판단하고 문제를 풀었다.

dense_rank 윈도우 함수(동일값 순위 같음)를 사용해서 employee_id별 attendance_date로 정렬한 값으로 순위를 구하고

특정 날짜('2999-12-31')와 attendance_date 날짜 차이를 구해서 정수값을 구한 뒤

위 두개의 값을 더한다.

그 값이 같으면 연속된 날짜라고 판단했다.

 

2번째 풀이

WITH tb AS (

SELECT

employee_id,

attendance_date,

LAG(attendance_date) OVER (PARTITION BY employee_id ORDER BY attendance_date) AS prev_attendance_date

FROM attendance

WHERE status = 'absent'

),

consecutive_absences AS (

SELECT

employee_id,

attendance_date,

-- attendance_date와 prev_attendance_date 날짜 차이가 1이면 연속 결근(0)

CASE

WHEN DATEDIFF(attendance_date, prev_attendance_date) = 1 THEN 0 -- 연속 결근

ELSE 1 -- 연속 결근이 아님

END AS new_group

FROM tb

),

grouped_absences AS (

SELECT

employee_id,

attendance_date,

SUM(new_group) OVER (PARTITION BY employee_id ORDER BY attendance_date rows unbounded PRECEDING) AS absence_group

FROM consecutive_absences

)

SELECT

employee_id,

MIN(attendance_date) AS start_date,

MAX(attendance_date) AS end_date,

COUNT(*) AS consecutive_absence_days

FROM grouped_absences

GROUP BY employee_id, absence_group

HAVING COUNT(*) >= 2 -- 연속 결근 기간이 2일 이상인 경우

ORDER BY employee_id, start_date;

 

 

 

풀이 > 

tb : LAG함수를 사용해 attendance_date의 이전날짜를 구한다

 

 

 

consecutive_absences : attendance_date,prev_attendance_date의 날짜 차이를 구해 1이면 0(연속 결근), 아닌 경우 1로 처리한다.

 

 

grouped_absences: 각 사원의 연속된 결근을 누적합으로 계산하여, 사원별로 연속 결근 그룹을 형성합니다. (범위: UNBOUNDED PRECEDING)

 

 

✅ 사용 문법 정리

윈도우함수() OVER(PARTITION BY ORDER BY ROWS {범위} )

BOUND	DEFINITION
UNBOUNDED PRECEDING	unbounded precedeing and currnet row	현재 행부터 맨 처음 행까지
N PRECEDING	N preceding and currnet row	n번째 앞~현재 행까지
CURRNET ROW	current row
N FOLLOWING	current row and N following	현재 행 ~ 뒤의 n번째 행까지
UNBOUNDED FOLLOWING	current row and unbounded following	현재 행~맨 뒤 행까지