nose딩

SELECT A.ID,
    CASE WHEN A.SIZE = 1 THEN 'CRITICAL'
        WHEN A.SIZE = 2 THEN 'HIGH'
        WHEN A.SIZE = 3 THEN 'MEDIUM'
        WHEN A.SIZE = 4 THEN 'LOW'
    END AS COLONY_NAME
FROM 
(
    SELECT ID, 
    NTILE(4) OVER (ORDER BY SIZE_OF_COLONY DESC) AS SIZE
    FROM ECOLI_DATA
) A
ORDER BY ID

*NTILE( N ) OVER (ORDER BY 컬럼)

-> 특정한 컬럼의 값을 N개의 등급으로 오름차순 / 내림차순으로 분류가 필요한 상황에서 사용

-> 예시: 직원 테이블에서 급여 내림차순 분류

+ PARTITION BY

-> *NTILE( N ) OVER ( PARTITION BY 컬럼1 ORDER BY 컬럼2)

-> 예시: 직원 테이블에서 직급별로 급여 내림차순 분류

*해시 사용

import java.util.HashSet;
class Solution {
    public boolean solution(String[] phone_book) {
        HashSet<String> hs = new HashSet<>();
        for(String s : phone_book) hs.add(s);
        for(String s : phone_book)
        {
            for(int i=1; i<s.length(); i++)
            {
                if(hs.contains(s.substring(0,i))) return false;
            }
        }
        return true;
    }
}

*해시 사용 x, 정렬 사용 

import java.util.Arrays;
class Solution {
    public boolean solution(String[] phone_book) {
        Arrays.sort(phone_book);
        for(int i =0; i<phone_book.length-1; i++)
        {
            if(phone_book[i+1].startsWith(phone_book[i])) return false;
        }
        return true;
    }
}

SELECT A.ID, COALESCE(B.CHILD_COUNT,0) AS CHILD_COUNT
FROM ECOLI_DATA A LEFT JOIN
(
    SELECT PARENT_ID, COUNT(PARENT_ID) CHILD_COUNT
    FROM ECOLI_DATA
    GROUP BY PARENT_ID
)B
ON A.ID = B.PARENT_ID
ORDER BY A.ID

* COALESCE -> NULL 값 치환할 때 사용

SELECT ROUTE, 
    CONCAT(ROUND(SUM(D_BETWEEN_DIST),1),'km') TOTAL_DISTANCE, 
    CONCAT(ROUND(AVG(D_BETWEEN_DIST),2),'km') AVERAGE_DISTANCE
FROM SUBWAY_DISTANCE
GROUP BY ROUTE
ORDER BY ROUND(SUM(D_BETWEEN_DIST),1) DESC

* 정렬 시 TOTAL_DISTANCE 사용 불가 -> CONCAT을 사용해 km이 합쳐졌기 때문에 정렬이 제대로 되지 않기 때문 

SELECT A.QUARTER, COUNT(*) ECOLI_COUNT
FROM
(
SELECT CASE WHEN MONTH(DIFFERENTIATION_DATE) IN ('1','2','3') THEN '1Q'
    WHEN MONTH(DIFFERENTIATION_DATE) IN ('4','5','6') THEN '2Q'
    WHEN MONTH(DIFFERENTIATION_DATE) IN ('7','8','9') THEN '3Q'
    ELSE '4Q'
    END AS QUARTER
FROM ECOLI_DATA
) A
GROUP BY A.QUARTER
ORDER BY A.QUARTER

* Quarter 사용

SELECT CONCAT(QUARTER(DIFFERENTIATION_DATE),'Q') QUARTER, COUNT(*) ECOLI_COUNT
FROM ECOLI_DATA
GROUP BY QUARTER
ORDER BY QUARTER

 SELECT region Region,
  count(distinct(case when category = 'Furniture' then order_id end)) Furniture,
  count(distinct(case when category = 'Office Supplies' then order_id end)) 'Office Supplies',
  count(distinct(case when category = 'Technology' then order_id end)) Technology
from records
group by region
order by region

*컬럼명 공백 포함하고 싶을때는 '' 이용

SELECT CAR_ID, ROUND(AVG(END_DATE-START_DATE+1),1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING ROUND(AVG(END_DATE-START_DATE+1),1) >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC

* 날짜 계산은 +1

SELECT CAR_ID, 
    CASE WHEN CAR_ID IN
    (
        SELECT CAR_ID 
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
        WHERE '20221016' BETWEEN TO_CHAR(START_DATE,'YYYYMMDD') AND TO_CHAR(END_DATE,'YYYYMMDD')
    )
    THEN '대여중'
    ELSE '대여 가능'
    END AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC