SQL 코드카타 모르는 거

최대값 최소값

https://wonin.tistory.com/479

[MySQL] max, min값을 가진 row 를 select 하기

 

=null로 해도 되나 isnull이 맞나

16. 경기도에 위치한 식품창고 목록 출력하기

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

문제점: if문 안에 freezer_yn=null로 하니 틀렸다고 함.

해결방법: isnull(freezer_yn)으로 변경함

ㄹ

 

 

where문에 date_format(date(joined), '%Y')로 쓰면 왜 안되는거지?

<시행착오>

<정답>

 

like로 다수 조건 작성하는 방법

like ('%Neutered% or '%Spayed%') 로 하면 안됨

(x like y) or (x like z) 로 작성해야함

<시행착오>

 

<정답>

 

<고치고 싶은 것>

where문에 자꾸만 

animal_type = 'Cat' or animal_type = 'Dog' 이런 식으로 작성함.

in을 사용할 수도 있다는 것

 

 

시간 추출하기: 입양 시각 구하기(1)

 

<이상한 점>

시간 포맷팅 문자를 %h 소문자로 하면 1~12 까지의 데이터만 나오고,

 %H 대문자로하면 7-19까지의 데이터가 다 나오네? 이상하다..

date_format은 포맷을 변경할 때 사용하는거지 특정 년도나 시간을 뽑아내는 걸로는 의미가 없나..? 

근데 그런 용도로 사용되는걸 수업때 배웠는데..

 

 

<오라클 정답과 비교>

#내가 작성한 코드
select HOUR, count(hour) as count
from
(
select date_format(datetime, '%H') HOUR
from animal_outs
) a
where HOUR between 9 and 19
group by 1
order by 1

#오라클 정답
SELECT to_number(to_char(DATETIME,'hh24')) as HOUR, count(*) as COUNT
from ANIMAL_OUTS 
where to_char(DATETIME,'hh24:mi') between '09:00' and '19:59'
group by to_char(DATETIME,'hh24') 
order by HOUR;

 

※ 참고하기 좋은 글

https://m.blog.naver.com/smj9030/222420176566

[SQL/Oracle] 입양 시각 구하기(1) - 날짜 추출, 날짜 포맷

 

<to_char 함수 이해안 감>

to_char() 함수는 날짜, 숫자 등의 값을 문자열로 변환하는 함수

날짜 포맷(형식)의 예	의미	문자 표시의 예 : 2020년 12월 23일(수) 오후 8시 12분 44초 기준
YYYY-MM-DD	연(4자리)-월(2자리)-일(2자리)	2020-12-23
YYYY 또는 RRRR	연도(4자리)	2020
YY 또는 RR	연도(2자리)	20
MM	월(2자리)	12
Mon	월(월이름 약칭)	12월 또는 DEC
Month	월(월이름 전체)	12월 또는 DECEMBER
DD	일(2자리)	23
DDTH	일(몇째 날)	23TH
DAY	일(요일)	수
HH24	시(24시간 기준)	20
HH	시(12시간 기준)	08
MI	분(2자리)	12
SS	초(2자리)	44

 

5월 조건을 푸는 방법

월이 나오면 무조건 date_format부터 쓰려고 하지말고, 

문자 패턴을 찾아서 풀면 서브쿼리를 쓰지 않아도 된다.

 

where문에 여러가지 조건 적을 때, 조건 구분방법

<시행착오>

 

포함조건 여러가지 작성하고 싶으면?

<시행착오>

 

<정답>

 

limit이 뭐지?

limit: 지정한 갯수만큼 자료를 보고싶을 때

https://blog.naver.com/50after/220939872934

SQL 갯수제한 - select limit 사용법

 

포함&미포함 조건

like, not like 외 regex를 사용해도 되는구나

SELECT 
    I.ANIMAL_ID,
    I.ANIMAL_TYPE,
    I.NAME
FROM ANIMAL_INS I
    JOIN ANIMAL_OUTS O
        ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE I.SEX_UPON_INTAKE REGEXP ('Intact')
    AND O.SEX_UPON_OUTCOME REGEXP ('Spayed|Neutered')
ORDER BY 1

 

having sum이 뭐지? from에 조건을 작성해도 되나?

 

특정 컬럼을 그룹화한 결과에 조건을 거는 having

 

※ where과 having의 차이점

where은 그룹화하기 전이고, having은 그룹화 후에 조건을 줌

https://velog.io/@eclat12450/SQL-%EC%A7%91%EA%B3%84%ED%95%A8%EC%88%98SUM-MAX-MIN..-GROUPBY%EC%99%80-HAVING

SQL : 집계함수(SUM, MAX, MIN..) GROUPBY와 HAVING

 

HAVING COUNT

<My solution>

SELECT USER_ID,
       NICKNAME,
       CONCAT(CITY,' ', STREET_ADDRESS1, ' ',STREET_ADDRESS2) AS '전체주소',
       CONCAT(SUBSTR(TLNO,1,3),'-',SUBSTR(TLNO,4,4),'-',SUBSTR(TLNO,8,4)) AS '전화번호'
FROM
(
SELECT U.USER_ID,
       COUNT(U.USER_ID) COUNT_USER_ID,
       U.NICKNAME,
       U.CITY,
       U.STREET_ADDRESS1,
       U.STREET_ADDRESS2,
       TLNO
FROM USED_GOODS_BOARD B LEFT JOIN USED_GOODS_USER U ON B.WRITER_ID=U.USER_ID
GROUP BY U.USER_ID ) A
WHERE COUNT_USER_ID >= 3
ORDER BY USER_ID DESC

<다른 정답>

SELECT USER_ID, 
        NICKNAME, 
        CONCAT(CITY,' ',STREET_ADDRESS1,' ',STREET_ADDRESS2) '전체주소',
        CONCAT(SUBSTR(TLNO,1,3),'-',SUBSTR(TLNO,4,4),'-',SUBSTR(TLNO,8,4)) '전화번호'
FROM USED_GOODS_USER
WHERE USER_ID IN (
    SELECT WRITER_ID
    FROM USED_GOODS_BOARD
    GROUP BY WRITER_ID
    HAVING COUNT(*) >=3
)
ORDER BY USER_ID DESC;

이 답안은 나랑 완전 다르게 JOIN문을 사용하지도 않았고,

WHERE문에서 서브쿼리를 써줬고,

조건도 HAVING으로 줬다

 

HAVING과 WHERE의 차이

HAVING절은 WHERE절과 비슷하지만 그룹 전체, 즉 그룹을 나타내는 결과 집합의 행에만 적용된다

반면, WHERE절은 개별 행에 적용된다

 

HAVING절은 그룹을 필터링하는데 사용된다, 즉, 그룹화 또는 집계가 발생한 후 레코드를 필터링하는데 사용된다

WHERE절은 행을 필터링하는데 사용된다

 

집계함수는 HAVING절과 함께 사용할 수 있다

WHERE절은 HAVING절에 포함된 하위 쿼리에 있지 않으면 집계함수와 함께 사용할 수 없다

(집계함수란, COUNT, MIN, MAX, SUM, AVG 등)

 

HAVING절은 GROUP BY 절 뒤에 사용한다

WHERE절은 GROUP BY 절 앞에 사용한다

 

<출처>

https://velog.io/@ljs7463/SQL-having-%EA%B3%BC-where-%EC%B0%A8%EC%9D%B4

SQL having 과 where 차이

 

MONTH

<나의 답안>

SELECT DISTINCT(RH.CAR_ID)
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY RH LEFT JOIN CAR_RENTAL_COMPANY_CAR C ON RH.CAR_ID=C.CAR_ID
WHERE CAR_TYPE = '세단' AND START_DATE LIKE '2022-10%'
ORDER BY 1 DESC

<참고답안>

SELECT DISTINCT(RH.CAR_ID)
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY RH LEFT JOIN CAR_RENTAL_COMPANY_CAR C ON RH.CAR_ID=C.CAR_ID
WHERE CAR_TYPE = '세단' AND MONTH(START_DATE) = 10
ORDER BY 1 DESC

 

이해가 안가는 문제

 

<다른 답안>

SELECT 
    CAR_ID,
    MAX(
        CASE WHEN 
        '2022-10-16' BETWEEN DATE_FORMAT(START_DATE, '%Y-%m-%d') AND DATE_FORMAT(END_DATE, '%Y-%m-%d') 
        THEN '대여중'
        ELSE '대여 가능'
        END
    ) AS AVAILABILITY 
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
GROUP BY CAR_ID
ORDER BY 1 DESC

MAX(CASE WHEN ...) AS AVAILABILITY:

The CASE WHEN statement is used to determine the availability based on the specified date range.

The result is then aggregated using MAX to get the maximum availability status for each CAR_ID.

 

각각의 CAR_ID에 대해 최대 AVAILABILITY 상태를 가져오는 집계를 한다는데 여기서 최대값을 어떻게 정의하는건지 이해가 안간다

 

SELECT
CAR_ID,
CASE WHEN CAR_ID IN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE START_DATE <= "2022-10-16" AND END_DATE >= "2022-10-16"
    ) THEN "대여중"
    ELSE "대여 가능"
    END AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC;

CASE WHEN 조건을 IN 서브쿼리문과 함께 써준 점이 인상적이다.

위 방식이 뭔가 더 멋져보이지만 이게 더 내가 할 수 있는 방식에 가깝다

CAR_ID중에서 대여중인걸 남기려고 생각했었기 때문에

 

집계조건 필터링 & 제외조건 2개를 어떻게 같이 써주지?

 

<My solution (오답)>

SELECT MONTH(START_DATE) AS MONTH,
       CAR_ID,
       COUNT(*) RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE CAR_ID IN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE MONTH(START_DATE) BETWEEN 8 AND 10
    GROUP BY CAR_ID
    HAVING COUNT(*) >= 5)
GROUP BY 1, 2
ORDER BY 1, 2 DESC

 

<My solution (정답)>

아래 다른 답안 참고하여 WHERE절에 AND로 8-10월 사이 조건을 하나 더 줬더니 

결과는 똑같은데 정답이 되었다. 뭐지?

SELECT MONTH(START_DATE) AS MONTH,
       CAR_ID,
       COUNT(*) RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE CAR_ID IN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE MONTH(START_DATE) BETWEEN 8 AND 10
    GROUP BY CAR_ID
    HAVING COUNT(*) >= 5) AND MONTH(START_DATE) BETWEEN 8 AND 10
GROUP BY 1, 2
ORDER BY 1, 2 DESC

 

<다른 답안>

특정월의 대여횟수가 0 이상인 조건도 반영되어있어서 가장 이상적인 답안으로 생각된다.

SELECT MONTH(START_DATE) AS MONTH
     , CAR_ID
     , COUNT(HISTORY_ID) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE CAR_ID IN (SELECT CAR_ID
                 FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                 WHERE MONTH(START_DATE) IN (8, 9, 10)
                 GROUP BY CAR_ID
                 HAVING COUNT(HISTORY_ID) >= 5)
      AND MONTH(START_DATE) IN (8, 9, 10)
GROUP BY MONTH, CAR_ID
HAVING COUNT(HISTORY_ID) >= 1
ORDER BY MONTH
       , CAR_ID DESC;

 

뭐가 문제인지 전혀모르겠는데..?

윈도우 함수를 써야하는 것 같다..!

아마 음식종류별 즐겨찾기수가 가장 많은이 MAX로는 안되나보다! 그런것 같다

각 음식 종류별 최대 즐겨찾기수 이니. 음식종류를 고정해서 그안에서 최대값을 찾는걸 해야하는데

GROUP BY로 묶어서 MAX하는건 안되는모양인데?

 

My solution (정답)

SELECT FOOD_TYPE,
       REST_ID,
       REST_NAME,
       FAVORITES
FROM
(
SELECT REST_ID,
       REST_NAME,
       FOOD_TYPE,
       FAVORITES,
       VIEWS,
       RANK() OVER (PARTITION BY FOOD_TYPE ORDER BY FAVORITES DESC) RN
FROM REST_INFO
ORDER BY FOOD_TYPE, RN ASC ) A
WHERE RN = 1
ORDER BY FOOD_TYPE DESC

 

Q. window_function으로 max집계함수를 쓸 수 있나?

내가 rank로 최대값을 구해준게 맞는 방법이겠다

아래 글을 보면 window function으로 max를 쓰는게 max만 골라내주는게 아니라 

그룹에 대해 max값을 찾아서 max값을 일괄 표기해주는거일 뿐이기 때문

https://drill.apache.org/docs/aggregate-window-functions/

 

<다른 답안1>

max값을 뽑아서 포함되는 조건을 사용해준건 참고할만 하지만

favorite수치가 중복이 있을 수도 있고 한 경우를 생각해보면 온전한 방법은 아닐 것 같다

ELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO
WHERE FAVORITES IN (SELECT MAX(FAVORITES) FROM REST_INFO GROUP BY FOOD_TYPE)
GROUP BY FOOD_TYPE
ORDER BY FOOD_TYPE DESC

 

<다른 답안2>

하나의 테이블로도 조인을 사용할 수 있구나!

엑셀식의 사고방식이 적용된 듯

SELECT A.FOOD_TYPE, A.REST_ID, A.REST_NAME, B.FAVORITES
FROM REST_INFO AS A
INNER JOIN (SELECT FOOD_TYPE, MAX(FAVORITES) AS FAVORITES
      FROM REST_INFO
      GROUP BY FOOD_TYPE) as B
ON A.FOOD_TYPE = B.FOOD_TYPE AND A.FAVORITES = B.FAVORITES
ORDER BY 1 DESC;

 

 

왜 안되지?

COUNT(*)를 빼니까 동작하네..

SELECT *
FROM PLACES
WHERE HOST_ID IN (SELECT HOST_ID
    FROM PLACES
    GROUP BY HOST_ID
    HAVING COUNT(*) >=2
    )

 

없는 시간대도 빠짐없이 행출력이 되게 하려면?

https://velog.io/@mero/MySQL-%EB%82%A0%EC%A7%9C%EB%B3%84%EB%A1%9C-group-by%ED%96%88%EC%9D%84-%EB%95%8C-%EC%97%86%EB%8A%94-%EB%82%A0%EC%A7%9C%EB%8F%84-%EC%B6%9C%EB%A0%A5%ED%95%98%EA%B8%B0

[MySQL] 날짜별로 group by했을 때 없는 날짜도 출력하기

with문 이해하기

https://www.mysqltutorial.org/mysql-basics/mysql-recursive-cte/

MySQL Recursive CTE

https://schatz37.tistory.com/46

[SQL] with 절을 효율적으로 사용하기

재귀함수는 또 뭐람?

https://velog.io/@cyanred9/SQL-Recursive

[SQL] Recursive 표현으로 쿼리 만들기

https://devkingdom.tistory.com/319

[MSSQL] WITH 절을 이용해 CTE(Common Table Expression) 표현하기

https://hongik-prsn.tistory.com/78

MSSQL 계층형 쿼리, 트리구조로 실적 구하기(WITH, 재귀 CTE)

https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-RECURSIVE-%EC%9E%AC%EA%B7%80-%EC%BF%BC%EB%A6%AC

[MYSQL] 📚 RECURSIVE (재귀 쿼리)