search

GreatAlgorithm-Study / AlgorithmStudy

🌟알고리즘 대장정🌟
6 stars 4 forks source link

[10주차_SQL] SQL 풀이 모음 #134

Closed yeahdy closed 4 days ago

yeahdy commented 1 week ago

추천문제와 전주의 본인 순번 전 사람이 풀었던 문제 입니다.

## 문제: [문제명](링크)

### 설명

### 코드

### 아이디어
icegosimperson commented 1 week ago

문제: Second Highest Salary

설명

Employee 테이블에서 두 번째로 높은 고유한 급여를 찾기 Salary 중복 제거

코드

1) 처음 작성한 코드

SELECT Salary AS SecondHighestSalary
FROM (
    SELECT Salary, RANK() OVER (ORDER BY Salary DESC) AS rk
    FROM Employee
) AS RankSalary
WHERE rk = 2;

틀린 이유 : null값 반환X

2) 정답 코드

SELECT MAX(Salary) as SecondHighestSalary
FROM (SELECT salary, dense_rank() over(order by salary desc) as rk
FROM Employee
) Employee
where rk = 2;

3) 다른 사람 풀이

SELECT (
    SELECT DISTINCT Salary 
    FROM Employee
    ORDER BY Salary DESC
    LIMIT 1 OFFSET 1
) AS SecondHighestSalary;
SELECT MAX(salary) AS SecondHighestSalary 
FROM Employee 
WHERE salary < (SELECT MAX(salary)FROM Employee);

아이디어

LIMIT 4 OFFSET 2; -- 3, 4, 5, 6행 출력

LIMIT [시작행], [출력할 행의 개수]; -- 콤마(,) 입력해서 OFFSET 생략 LIMIT 1,3; -- 2번째 행부터 3개 행만 출력

KodaHye commented 1 week ago

문제: 언어별 개발자 분류하기

설명

_SKILLCODE가 400 (=b'110010000')이라면, 이는 SKILLCODES 테이블에서 CODE가 256 (=b'100000000'), 128 (=b'10000000'), 16 (=b'10000') 에 해당하는 스킬을 가졌다는 것을 의미함

코드

WITH FE AS (
    SELECT SUM(CODE) CODE
    FROM SKILLCODES
    WHERE CATEGORY = "Front End"
)
, C AS (
    SELECT CODE
    FROM SKILLCODES
    WHERE NAME = "C#"
)
, PYTHON AS (
    SELECT CODE
    FROM SKILLCODES
    WHERE NAME = "Python"
)
, DEVELOPER_GRADE AS (
    SELECT 
        CASE 
            WHEN a.SKILL_CODE & b.CODE != 0 AND a.SKILL_CODE & d.CODE != 0 THEN 'A'
            WHEN a.SKILL_CODE & c.CODE != 0 THEN 'B'
            WHEN a.SKILL_CODE & b.CODE != 0 THEN 'C'
        END AS GRADE,
        a.ID, a.EMAIL
    FROM DEVELOPERS a, FE b, C c, Python d
)

SELECT * 
FROM DEVELOPER_GRADE a
WHERE a.GRADE IS NOT NULL
ORDER BY a.GRADE, a.ID

아이디어

추가로, CASE문을 통해 GRADE컬럼을 추가했을 때, WHERE GRADE IS NOT NULL로 실행하면 (1054, "Unknown column 'GRADE' in 'where clause'") 오류가 발생하면서 안되지만, HAVING GRADE IS NOT NULL을 사용하면 결과 값이 나오더라고요! WHERE, HAVING 모두 필터링 역할을 하는데, WHERE절은 SELECT 하기 전에 실행되고, HAVING은 SELECT 이후에 실행되기 때문에 그런 것 같습니다!

yeahdy commented 1 week ago

문제: 조건에 맞는 개발자 찾기

설명

Python이나 C# 스킬을 가진 개발자의 ID, 이메일, 이름, 성을 조회 DEVELOPERS테이블에서 SKILL_CODE가 400 (=b'110010000') 이라면, 이는 SKILLCODES테이블에서 CODE가 256 (=b'100000000'), 128 (=b'10000000'), 16 (=b'10000') 에 해당하는 스킬을 가졌다는 것을 의미

코드

SELECT 
    d.ID, d.EMAIL, d.FIRST_NAME, d.LAST_NAME
FROM DEVELOPERS d
INNER JOIN SKILLCODES s
ON d.SKILL_CODE & s.CODE > 0
WHERE s.NAME IN ('Python','C#')
GROUP BY d.ID, d.EMAIL, d.FIRST_NAME, d.LAST_NAME
ORDER BY d.ID;

아이디어

yeongleej commented 1 week ago

문제: Human Traffic of Stadium

설명

코드

WITH CTE AS(
    SELECT *, ID- ROW_NUMBER() OVER() AS ID_DIFF
    FROM STADIUM
    WHERE PEOPLE > 99
)

SELECT ID, VISIT_DATE, PEOPLE
FROM CTE
WHERE ID_DIFF IN (SELECT ID_DIFF FROM CTE GROUP BY ID_DIFF HAVING COUNT(ID_DIFF) >= 3)
ORDER BY VISIT_DATE

아이디어

yeahdy commented 1 week ago

문제: 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

설명

코드

SELECT 
    c.CAR_ID,
    c.CAR_TYPE,
    FLOOR((c.DAILY_FEE*30) * (1-(dp.DISCOUNT_RATE/100))) FEE
FROM CAR_RENTAL_COMPANY_CAR c
LEFT JOIN (
    SELECT CAR_ID, MIN(START_DATE) START_DATE, MAX(END_DATE) END_DATE
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY rh
    GROUP BY CAR_ID
) rh
ON c.CAR_ID = rh.CAR_ID
INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN dp
ON c.CAR_TYPE = dp.CAR_TYPE
AND dp.DURATION_TYPE LIKE '30%'
AND c.CAR_TYPE IN ('세단','SUV')
WHERE '2022-11-01' > rh.END_DATE OR '2022-11-30' < rh.START_DATE
HAVING FEE BETWEEN 500000 AND 1999999
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC;

아이디어

WHERE 와 INNER ON 필터링 모두 가능한데, 성능상 차이가 있을까?

그럼 WHERE 와 LEFT JOIN ON 필터링은 성능상 차이가 있을까?

icegosimperson commented 1 week ago

문제: 상품을 구매한 회원 비율 구하기

설명

baexxbin commented 6 days ago

문제: 1280. Students and Examinations

설명

코드

-- CROSS JOIN 이용
SELECT st.student_id, st.student_name ,sb.subject_name, 
        COUNT(e.student_id) AS attended_exams
FROM Students st
CROSS JOIN Subjects sb
LEFT JOIN Examinations e
    ON e.student_id = st.student_id
    AND e.subject_name = sb.subject_name
GROUP BY st.student_id, st.student_name, sb.subject_name
ORDER BY st.student_id, st.student_name, sb.subject_name

아이디어

그리고..! 처음에 그냥 CROSS JOIN없이 JOIN으로만 했는데도 답으로 인정은 됐는데, 이건 데이터가 충분하지 못해서 그런거지 않을까 생각됩니닷..!

yeongleej commented 6 days ago

문제: 특정 형질을 가지는 대장균 찾기

설명

ID 1 : GENOTYPE 8 -> 1000₍₂₎ ID 2 : GENOTYPE 15 -> 1111₍₂₎ ID 3 : GENOTYPE 1 -> 1₍₂₎ ID 4 : GENOTYPE 13 -> 1101₍₂₎

각 대장균 별 보유한 형질을 다음과 같다 ID 1 : 4 ID 2 : 1, 2, 3, 4 ID 3 : 1 ID 4 : 1, 3, 4

코드

1. conv() 함수 활용

SELECT COUNT(*) AS COUNT
FROM ECOLI_DATA E, 
     (SELECT CONV(GENOTYPE, 10, 2) MOD 10 AS ONE,
             (CONV(GENOTYPE, 10, 2) MOD 100) DIV 10 AS TWO,
             (CONV(GENOTYPE, 10, 2) MOD 1000) DIV 100 AS THREE,
              ID
      FROM ECOLI_DATA) G
WHERE E.ID = G.ID AND G.TWO = 0 AND (G.ONE = 1 OR G.THREE = 1);

2. 비트연산 활용

SELECT COUNT(ID) AS COUNT
FROM ECOLI_DATA
WHERE (GENOTYPE & 2 != 2) AND (GENOTYPE & 1 = 1 OR GENOTYPE & 4 = 4)
SELECT COUNT(ID) AS COUNT
FROM ECOLI_DATA
WHERE (GENOTYPE & 2 != 2) AND (GENOTYPE & 5 > 5)

9주차 때, 예진님이 풀이하신 방법입니다!

아이디어

1. conv() 함수 활용

2. 비트연산 활용

KodaHye commented 6 days ago

문제: 분기별 분화된 대장균의 개체 수 구하기

설명

코드

처음 작성한 코드

WITH CTE AS (
    SELECT 
        QUARTER(DIFFERENTIATION_DATE) `QUARTER`
      , COUNT(QUARTER(DIFFERENTIATION_DATE)) `ECOLI_COUNT`
    FROM ECOLI_DATA
    GROUP BY QUARTER
)

SELECT CONCAT(a.QUARTER, 'Q') `QUARTER`, a.ECOLI_COUNT
FROM CTE a


수정한 코드

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

아이디어

처음 SQL의 실행 순서와 GROUP BY에서는 Alais를 사용할 수 있다는 것을 고려하지 못하고, 임시 테이블을 생성하고 결과 출력하였고, 이후 아래 코드로 수정함

baexxbin commented 6 days ago

문제: 입양 시각 구하기(2)

설명

코드 및 아이디어

SELECT A.HOUR, IFNULL(B.COUNT, 0) AS COUNT FROM TM A LEFT JOIN ( SELECT HOUR(DATETIME) AS HOUR, COUNT(HOUR(DATETIME)) AS COUNT FROM ANIMAL_OUTS GROUP BY HOUR(DATETIME) ) B ON A.HOUR = B.HOUR


> 재귀 방법은 다혜님이 잘 설명해주셔서 넘어가겠습니다!

- 세션 변수 사용 방식
```sql
SET @HOUR := -1;

SELECT (@HOUR := @HOUR+1) as HOUR,
(SELECT COUNT(*) FROM ANIMAL_OUTS WHERE HOUR(DATETIME) =@HOUR) AS COUNT
FROM ANIMAL_OUTS
WHERE @HOUR < 23

CREATE PROCEDURE GetAnimalCountByHour() BEGIN -- 프로시저 내부에서 사용할 변수 선언 DECLARE i INT DEFAULT 0; -- i, 반복문의 카운터 역할, 0으로 초기화
DECLARE hour_count INT; -- 각 시간대의 레코드 개수 저장

-- 시간별 결과를 저장할 임시 테이블 생성
CREATE TEMPORARY TABLE HourlyCounts (HOUR INT, COUNT INT);

-- 0시부터 23시까지 반복
WHILE i < 24 DO
    -- 특정 시간대의 COUNT 값을 계산
    SELECT COUNT(*) INTO hour_count 
    FROM ANIMAL_OUTS 
    WHERE HOUR(DATETIME) = i;

    -- 결과를 임시 테이블에 저장
    INSERT INTO HourlyCounts (HOUR, COUNT) VALUES (i, hour_count);

    -- 시간 증가
    SET i = i + 1;
END WHILE;

-- 결과 출력
SELECT * FROM HourlyCounts;

-- 임시 테이블 삭제
DROP TEMPORARY TABLE HourlyCounts;

END$$

DELIMITER ;

CALL GetAnimalCountByHour();


- `프로시저`
   - 미리 정의된 작업 집합, 데이터베이스에서 특정 작업을 수행하기 위해 저장된 SQL코드 블록
   - 작성 방식
      - `CREATE PROCEDURE` : 프로시저 정의
      - `CALL` : 프로시저 실행
      - `BEGIN ... END` : 프로시저 내부에 포함될 작업 정의

> 프로시저는 보통 저장 프로시저를 말하고, 이는 여러 테이블이나 특정작업을 반복해야할때 유용하게 사용한다고 합니다.

> 참고로 프로그래머스 환경에서는 프로시저가 안돌아가서..! 사용은 못하지만, 개념만 대충 알고 한번도 사용안해봤던 프로시저의 개념을 짚고 넘어갈 수 있었던 기회였습니다!
Jewan1120 commented 6 days ago

문제: Investments in 2016

설명

아이디어

Jewan1120 commented 6 days ago

문제: 오프라인/온라인 판매 데이터 통합하기

설명

코드

SELECT
    DATE_FORMAT(SALES_DATE, "%Y-%m-%d") SALES_DATE,
    PRODUCT_ID,
    USER_ID,
    SALES_AMOUNT
FROM
    ONLINE_SALE
WHERE
    SALES_DATE LIKE '2022-03%'
UNION ALL
SELECT
    DATE_FORMAT(SALES_DATE, "%Y-%m-%d") SALES_DATE,
    PRODUCT_ID,
    NULL,
    SALES_AMOUNT
FROM
    OFFLINE_SALE        
WHERE
    SALES_DATE LIKE '2022-03%'
ORDER BY
    SALES_DATE,
    PRODUCT_ID,
    USER_ID

아이디어

SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM
    (SELECT SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
     FROM ONLINE_SALE
     WHERE DATE_FORMAT(SALES_DATE, '%Y-%m') = '2022-03'
     UNION ALL
     SELECT SALES_DATE, PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT
     FROM OFFLINE_SALE
     WHERE DATE_FORMAT(SALES_DATE, '%Y-%m') = '2022-03'
    ) A
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID