코테준비 | WHERE 절 서브쿼리 / EXISTS / 소수점 변환 / 두 날짜의 차이 (PostgreSQL)

☑️ Patient Support Analysis (Part 1)

[문제] EXISTS 활용

---

[문제 풀이]

내 코드

-- WITH문 풀이법 
WITH sub AS (
    SELECT policy_holder_id
    FROM callers
    GROUP BY policy_holder_id
    HAVING COUNT(DISTINCT case_id)>=3
)
SELECT COUNT(DISTINCT policy_holder_id) AS policy_holder_count
FROM sub

-- WHERE저 다중커럼 서브쿼리 풀이법
SELECT COUNT(DISTINCT policy_holder_id) AS policy_holder_count
FROM callers
WHERE policy_holder_id IN ( SELECT DISTINCT policy_holder_id 
                            FROM callers 
                            GROUP BY policy_holder_id 
                            HAVING COUNT(DISTINCT case_id)>=3)

수정 코드 : WHERE 절 EXISTS 활용

SELECT COUNT(DISTINCT policy_holder_id) AS policy_holder_count
FROM callers c1
WHERE EXISTS (SELECT 1
              FROM callers c2
              WHERE c1.policy_holder_id = c2.policy_holder_id
              GROUP BY c2.policy_holder_id
              HAVING COUNT(DISTINCT c2.case_id) >= 3
          )

 

이슈 및 해결 과정

• 아이디어
  • WITH문 : ID 별 3번이상 콜한 회원 아이디 cte 생성 후 갯수 집계 
  • 다중컬럼 서브쿼리 :  ID 별 3번이상 콜한 회원 아이디를 WHERE 절에서 필터링한 후 바로 집계
• 이슈 : 다중컬럼 풀이는 서브쿼리 값을 아우터쿼리와 행 수준에서 하나씩 비교하기 때문에 비 효율적인 쿼리임 
• 해결
  • `EXISTS` 함수 활용하여 해결 
  • 해당 문제에서는 ID 가 3번이상 콜했는지 `여부` 만 알면됨
  • EXISTS (~ ) 는 서브쿼리가 값을 반환하면 TRUE 아니면 FALSE 임  
  • 따라서, 아이디를 INNER JOIN 해준 후  서브쿼리 결과로 반환되는것만 살림 

한줄 포인트

• 다중컬럼 서브쿼리 필요시 EXISTS 함수 적용을 생각해 볼수 있음 
• WHERE EXISTS ( 서브쿼리 ) 에서 조인키를 활용하면 필터링 조건에 맞는 값만 빠르게 필터링 가능 

☑️Patient Support Analysis (Part 2)

[문제] 소수점 처리

---

[문제 풀이]

내 코드

SELECT ROUND(SUM(CASE WHEN call_category IS NULL OR call_category='n/a' THEN 1 END)*100.0
              /COUNT(*),1) AS ncategorised_call_pct
FROM callers

 

 

이슈 및 해결 과정

• POSTGRESQL 은 MYSQL 과 다르게 숫자형식을 맞춰줘야 소수점이 출력됨
• 숫자 자료형 변환법
  • 실수를 포함한 연산  : `1.0` `100.00` 을 곱해줌 
  • 함수 사용
  • CAST(값 AS FLOAT) 
  • CONVERT (FLOAT,2)

--CAST(숫자 AS FLOAT) 로 실수형 변환 
SELECT CAST(2 AS FLOAT) / 3 AS divide;

--CONVERT(FLOAT,2) 로 실수형 변환 
SELECT CONVERT(FLOAT, 2) / 3 AS divide;

 

☑️ Server Utilization Time

[문제] : 시각 차이를 원하는 값으로 반환하기 

---

[문제 풀이]

내 코드

WITH organized AS(
    SELECT *
    FROM server_utilization
    ORDER BY server_id,status_time
), lead_time AS (
    SELECT *,LEAD(status_time,1) OVER (PARTITION BY server_id) AS end_time 
    FROM organized
), diff AS(
    SELECT server_id
          ,status_time AS start_time, end_time
          ,end_time-status_time AS diff
          ,EXTRACT(EPOCH FROM (end_time - status_time))/3600 AS date_diff
    FROM lead_time 
    WHERE session_status = 'start'
)
SELECT ROUND(SUM(date_diff)/24,0) AS total_uptime_days
from diff

 

 

이슈 및 해결 과정

• sever_id 별 start - stop 가동 시간 구하기
• 아이디어 
  • db가 어떻게 저장되어 있을지 모르니 server_id, status_time 기준으로 정렬
  • server_id 기준 시각 당겨오기 ( stop_time 만들기)
  • 시각 차이 구하기
• 문제상황
  • 해당 문제는 full-time (24시간) 을 1일로 침 
  • date_part('day',end_time- status_time ) 결과의 경과 시각은 날짜값만 나옴 (시간 생략)  
• 문제해결 
  • `extract ( epoch from (diff))` 로 날짜 차이를 초 단위로 반환 → 3600 나눠서 `시간` 단위로 가공
  •  diff 의 합집계 값을 24시간으로 나눠서 `일` 로 변환 

PostgreSQL 날짜 /시간 함수 리마인드

• timestamp 타입 연산  
  • 2024/03/18 1:00:00 - 2024/03/20 2:00:00 => interval(=json) 타입으로 반환됨 `days`: 2 `hours` :1
  • 2024/03/18 1:00:00  + interval '1 hour' =>  timestamp 타입으로 반환됨 2024/03/18 2:00:00  
• 경과 기간 원하는 타입으로 반환하기
  • EXTRACT ( 단위 FROM (diff)) 

#해당 단위만 추출
SELECT EXTRACT(YEAR FROM TIMESTAMP '2024-03-18 14:35:50');
SELECT EXTRACT(MONTH FROM TIMESTAMP '2024-03-18 14:35:50'); 
SELECT EXTRACT(DAY FROM TIMESTAMP '2024-03-18 14:35:50');
SELECT EXTRACT(HOUR FROM TIMESTAMP '2024-03-18 14:35:50'); 
SELECT EXTRACT(MINUTE FROM TIMESTAMP '2024-03-18 14:35:50'); 
SELECT EXTRACT(SECOND FROM TIMESTAMP '2024-03-18 14:35:50');

#EPOCH : 1970-01-01 00:00:00 UTC(Unix Epoch Time)부터의 경과 시간을 초 단위로 반환합니다.
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2024-03-18 14:35:50');

 

한줄 포인트

• 시간 경과의 차이를 집계 한 후 원하는 타입으로 출력할때
• EXTRACT ( EPOCH FROM (DIFF)) 에서 /3600로 시간 변환 -> 24로 날짜로 변환