SQL INSERT, UPDATE, DELETE와 순위함수 NTILE, RANK

📑

 

INSERT

INSERT INTO 테이블명 (컬럼명1, 컬럼명2 ...) VALUES (값1, 값2 ...)

 

한 번에 여러 개를 넣고 싶은 경우에는 다음과 같이 써주면 된다.

INSERT INTO 테이블명 (컬럼명1, 컬럼명2 ...) VALUES (값1, 값2 ...), (값3, 값4 ...), ...

 

AUTO_INCREMENT 제약이 붙은 컬럼은 입력을 생략해도 알아서 채워진다.

예를 들어 아래의 경우에 id를 생략해도 자동으로 그 다음 번호인 2, 3, 4 ...로 채워지게 된다.

INSERT INTO product (id, 상품명, 가격) VALUES (1, "펜", 500)

⇓

INSERT INTO product (상품명, 가격) VALUES ("필통",5000)

 

AUTO_INCREMENT 제약이 붙지 않은 컬럼을 생략하면 NULL로 입력된다.

 

 

UPDATE

UPDATE 테이블명 
SET 컬럼1 = 값, 컬럼2 = 값
WHERE 조건식

UPDATE 테이블명 
SET 가격 = 가격 + 200
WHERE 가격 <= 3000

 

*JOIN한 테이블도 UPDATE 명령 가능.

 

 

DELETE

DELETE FROM 테이블명 WHERE 조건식

 

*JOIN한 테이블도 DELETE 명령 가능.

 

---

 

순위함수(분석함수)

결과에 순번, 순위를 매기는 함수

RANK, NTILE, DENSE_RANK, ROW_NUMBER 등이 있다.

 

1. SELECT NTILE(나누고싶은 그룹 개수) OVER (PARTITION BY 컬럼1 ORDER BY 컬럼2)

- 행 데이터를 그룹별로 나누어 차례대로 행 번호를 부여하는 SQL의 분석함수.

 

2. SELECT RANK() OVER(ORDER BY 컬럼명 FROM 테이블명;

- 동일한 값에 대해서는 같은 순위를 부여. 다음 순위는 해당 개수만큼 건너뛰고 반환.

*DENSE_RANK : 동일한 값에 중복 순위를 부여하고, 다음 순위는 중복 순위와 상관없이 다음번호를 반환.

 

3. SELECT ROW_NUMBER() OVER(ORDER BY 컬럼명) FROM 테이블명;

- 중복값도 다른 순위를 부여.

 

* PARTITION BY를 생략하면 전체 행에 대해 그룹화가 수행됨. PARTITION BY를 지정하면 해당 컬럼 내에서 그룹화를 진행하여 행 번호를 부여.

 

# RANK 사용 예시

# 회원별 가장 많이 구매한 카테고리 조회
SELECT USER_ID, CTGRY_CD, CTGRY_NM, QY
FROM
    (
    SELECT 
        U.USER_ID, 
        C.CTGRY_CD,
        C.CTGRY_NM,
        SUM(D.ORD_QY) AS QY,
        RANK() OVER (PARTITION BY U.USER_ID ORDER BY SUM(D.ORD_QY) DESC) AS rnk
    FROM T_USER U 
    JOIN T_ORD O ON U.USER_ID = O.USER_ID 
    JOIN T_ORD_DETAIL D ON O.ORD_NO = D.ORD_NO 
    JOIN T_GOODS G ON D.GOODS_ID = G.GOODS_ID
    JOIN T_GOODS_CTGRY C ON G.CTGRY_CD = C.CTGRY_CD
    GROUP BY U.USER_ID, C.CTGRY_CD
   ) AS A 
 WHERE rnk=1
 ORDER BY QY DESC ;

 

# ROW_NUMBER 사용 예시

# 회원별 가장 많이 구매한 카테고리 조회
SELECT USER_ID, CTGRY_CD, CTGRY_NM, QY
FROM
    (
    SELECT 
        U.USER_ID, 
        C.CTGRY_CD,
        C.CTGRY_NM,
        SUM(D.ORD_QY) AS QY,
        ROW_NUMBER() OVER (PARTITION BY U.USER_ID ORDER BY SUM(D.ORD_QY) DESC) AS row
    FROM T_USER U 
    JOIN T_ORD O ON U.USER_ID = O.USER_ID 
    JOIN T_ORD_DETAIL D ON O.ORD_NO = D.ORD_NO 
    JOIN T_GOODS G ON D.GOODS_ID = G.GOODS_ID
    JOIN T_GOODS_CTGRY C ON G.CTGRY_CD = C.CTGRY_CD
    GROUP BY U.USER_ID, C.CTGRY_CD
   ) AS A 
 WHERE row=1
 ORDER BY QY DESC ;

 

---

 

SQL CODE KATA

Ⅰ. 문제 전문은 링크 참고. 부모의 형질을 모두 보유한 대장균의 ID(ID), 대장균의 형질(GENOTYPE), 부모 대장균의 형질(PARENT_GENOTYPE)을 출력하는 SQL 문을 작성해주세요. 이때 결과는 ID에 대해 오름차순 정렬해주세요. (school.programmers.co.kr)

SELECT A.ID, A.GENOTYPE, B.GENOTYPE AS PARENT_GENOTYPE
FROM ECOLI_DATA A JOIN ECOLI_DATA B ON A.PARENT_ID = B.ID
WHERE (A.GENOTYPE & B.GENOTYPE) = B.GENOTYPE
ORDER BY ID;

 

이건 진짜 문제 이해부터가 너무 어려웠다.

풀긴 풀었는데 지금도 이해가 잘 안 가서 코파일럿씨에게 도움 요청.. ㅋㅋㅋ ㅠㅠ

WHERE (A.GENOTYPE & B.GENOTYPE) = B.GENOTYPE 절은 비트 연산자를 사용하여 A.GENOTYPE과 B.GENOTYPE 간의 관계를 정의하는 조건입니다. 이 조건을 이해하기 위해서는 비트 연산자와 그 의미를 알아야 합니다.

비트 연산자와 그 의미
비트 AND 연산자 (&): 두 숫자의 각 비트를 비교하여 두 비트가 모두 1인 경우에만 1을 반환합니다. 예를 들어, 5 & 3 = 1 (0101 & 0011 = 0001) 6 & 3 = 2 (0110 & 0011 = 0010)

조건 해석
(A.GENOTYPE & B.GENOTYPE): A.GENOTYPE와 B.GENOTYPE의 각 비트를 AND 연산합니다.= B.GENOTYPE: 연산 결과가 B.GENOTYPE과 같아야 합니다.
즉, A.GENOTYPE의 비트에서 B.GENOTYPE의 비트 패턴이 전부 포함되어 있어야 한다는 의미입니다. 이는 B.GENOTYPE이 A.GENOTYPE의 부분 집합임을 나타냅니다.

예를 들어, B.GENOTYPE이 3(0011)이고 A.GENOTYPE이 7(0111)인 경우:
7 & 3 = 3 (0111 & 0011 = 0011)
따라서, 조건이 참이 되어 이 행이 결과에 포함됩니다. 이 조건을 통해 A와 B의 GENOTYPE 간 특정 비트 패턴이 일치하는 행을 필터링할 수 있습니다.

 

 

Ⅱ. 문제 전문은 링크 참고.  대장균 개체의 크기를 내림차순으로 정렬했을 때 상위 0% ~ 25% 를 'CRITICAL', 26% ~ 50% 를 'HIGH', 51% ~ 75% 를 'MEDIUM', 76% ~ 100% 를 'LOW' 라고 분류합니다. 대장균 개체의 ID(ID) 와 분류된 이름(COLONY_NAME)을 출력하는 SQL 문을 작성해주세요. 이때 결과는 개체의 ID 에 대해 오름차순 정렬해주세요 . 단, 총 데이터의 수는 4의 배수이며 같은 사이즈의 대장균 개체가 서로 다른 이름으로 분류되는 경우는 없습니다. (school.programmers.co.kr)

 

진짜 모르겠다..

SELECT ID, 
    CASE WHEN rank_size <= ((SELECT COUNT(*) FROM ECOLI_DATA) * 0.25) THEN 'CRITICAL'
         WHEN rank_size <= ((SELECT COUNT(*) FROM ECOLI_DATA) * 0.50) THEN 'HIGH'
         WHEN rank_size <= ((SELECT COUNT(*) FROM ECOLI_DATA) * 0.75) THEN 'MEDIUM'
         ELSE 'LOW'
    END AS COLONY_NAME
FROM
(
    SELECT ID, RANK() OVER (ORDER BY SIZE_OF_COLONY DESC) AS rank_size
    FROM ECOLI_DATA
) AS aa
ORDER BY 1;

SELECT E1.ID,
    CASE
        WHEN (NUM/(SELECT COUNT(*) FROM ECOLI_DATA))*100 <= 25
        THEN 'CRITICAL'
        WHEN (NUM/(SELECT COUNT(*) FROM ECOLI_DATA))*100 <= 50
        THEN 'HIGH'
        WHEN (NUM/(SELECT COUNT(*) FROM ECOLI_DATA))*100 <= 75
        THEN 'MEDIUM'
        ELSE 'LOW'
    END AS 'COLONY_NAME'
FROM ECOLI_DATA E1
JOIN (SELECT ROW_NUMBER() OVER (ORDER BY SIZE_OF_COLONY DESC) AS NUM,
      ID, SIZE_OF_COLONY
      FROM ECOLI_DATA) AS E2
ON E1.ID = E2.ID
ORDER BY E1.ID;

WITH groupsbysize AS (
SELECT *
, NTILE(4) OVER (ORDER BY sizeofcolony DESC) AS percentile
FROM ecoli_data
WHERE 1=1
)

SELECT id
, CASE WHEN percentile = 1 THEN 'CRITICAL'
WHEN percentile = 2 THEN 'HIGH'
WHEN percentile = 3 THEN 'MEDIUM'
ELSE 'LOW'
END AS colonyname
FROM groupsby_size
ORDER BY id;