SQL CTE (Common Table Expression)

https://school.programmers.co.kr/learn/courses/30/lessons/301649

프로그래머스

 

특정 컬럼으로 정렬 후,

4배인것에서 힌트를 얻어 퍼센테이지로 랭크를 매김

 

 

 

 

 

• WITH AS (CTE):
  • 용도: 복잡한 쿼리를 간결하게 작성하고 재사용할 수 있게 해줍니다.
  • 구조:
    WITH CTE_NAME AS ( SELECT ... FROM ... ) SELECT ... FROM CTE_NAME;
    CTE는 일반적으로 중간 계산을 저장하고, 메인 쿼리에서 그 결과를 사용할 수 있도록 합니다. 여러 개의 CTE를 사용할 수도 있습니다.
  •  
• ROW_NUMBER() OVER (ORDER BY ...):
  • 용도: 데이터를 정렬된 순서에 따라 각 행에 고유한 번호를 부여합니다.
  • 구조:
    ROW_NUMBER() OVER (ORDER BY 컬럼명 DESC/ASC)
    ROW_NUMBER() 함수는 지정된 정렬 기준에 따라 각 행에 순번을 매깁니다.
  예시: SELECT ROW_NUMBER() OVER (ORDER BY Salary DESC) AS Rank, EmployeeID FROM Employees;
• COUNT() OVER ():
  • 용도: 전체 데이터 세트의 크기(총 행 수)를 구할 때 사용됩니다.
  • 구조:
     

    COUNT(*) OVER ()
    COUNT()는 일반적으로 집계 함수로 사용되지만, OVER()와 함께 쓰면 현재 선택된 모든 행의 전체 개수를 반환합니다.

 

 

 

-- common table expression 사용

WITH RANKED_ECOLI AS (
    SELECT ID, 
           ROW_NUMBER() OVER (ORDER BY SIZE_OF_COLONY DESC) AS ROW_RANK,
           COUNT(*) OVER () AS TOTAL_COUNT
    FROM ECOLI_DATA
)
, LABELED_ECOLI AS (
    SELECT ID,
           CASE 
               WHEN ROW_RANK <= TOTAL_COUNT * 0.25 THEN 'CRITICAL'
               WHEN ROW_RANK <= TOTAL_COUNT * 0.50 THEN 'HIGH'
               WHEN ROW_RANK <= TOTAL_COUNT * 0.75 THEN 'MEDIUM'
               ELSE 'LOW'
           END AS COLONY_NAME
    FROM RANKED_ECOLI
)
SELECT ID, COLONY_NAME
FROM LABELED_ECOLI
ORDER BY ID;