Today I Learned
sql_select & max,min,count & group by 공부 본문
프로그래머스 스쿨 sql 고득점 Kit 문제
https://school.programmers.co.kr/learn/challenges?tab=sql_practice_kit
select
#1. 문제
ONLINE_SALE 테이블과 OFFLINE_SALE 테이블에서 2022년 3월의 오프라인/온라인 상품 판매 데이터의 판매 날짜, 상품ID, 유저ID, 판매량을 출력하는 SQL문을 작성해주세요. OFFLINE_SALE 테이블의 판매 데이터의 USER_ID 값은 NULL 로 표시해주세요. 결과는 판매일을 기준으로 오름차순 정렬해주시고 판매일이 같다면 상품 ID를 기준으로 오름차순, 상품ID까지 같다면 유저 ID를 기준으로 오름차순 정렬해주세요
SELECT TO_CHAR(SALES_DATE, 'YYYY-MM-DD'), PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ( (SELECT SALES_DATE
, PRODUCT_ID
, USER_ID
, SALES_AMOUNT
FROM ONLINE_SALE
WHERE TO_CHAR(SALES_DATE, 'YYYYMM') = '202203')
UNION ALL
(SELECT SALES_DATE
, PRODUCT_ID
, NULL AS USER_ID
, SALES_AMOUNT
FROM OFFLINE_SALE WHERE TO_CHAR(SALES_DATE, 'YYYYMM') = '202203')
)
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID- to_char : 숫자 값을 문자열로 변환하는 함수 (참고 블로그 : 숫자 형식으로 변환하기(TO_CHAR) (tistory.com))
- union all : 합집합같은 개념. 중복값도 다 표시해줌
- OFFLINE_SALE 테이블의 판매 데이터의 USER_ID 값은 NULL 로 표시 : NULL AS USER_ID로 함, 여기 잘 이해안됨
#2.
오라클에서 != , <> , ^= : 특정 값이 아닌 조건을 걸 경우 세가지가 쓰이는데 <> 를 제일 많이 쓴대
(참고 블로그 : 오라클 != <> ^= 차이 정리글 (tistory.com))
(not like를 쓴 사람도 있던데...)
#3. 문제
동물 보호소에 가장 먼저 들어온 동물의 이름을 조회하는 SQL 문을 작성해주세요
/*서브쿼리 사용하기 */
SELECT name from animal_ins where datetime = (select min(datetime) from animal_ins)- oracle에선 limit을 사용하지 않음 (참고 블로그 : limit과 rownum 차이 등등 [Oracle] 오라클 limit 사용하는 방법 (페이징, rownum) (tistory.com))
#4. 문제 -- 어려움
REST_INFO와 REST_REVIEW 테이블에서 서울에 위치한 식당들의 식당 ID, 식당 이름, 음식 종류, 즐겨찾기수, 주소, 리뷰 평균 점수를 조회하는 SQL문을 작성해주세요. 이때 리뷰 평균점수는 소수점 세 번째 자리에서 반올림 해주시고 결과는 평균점수를 기준으로 내림차순 정렬해주시고, 평균점수가 같다면 즐겨찾기수를 기준으로 내림차순 정렬해주세요.
SELECT lpad(a.rest_id,5,0), a.rest_name, a.food_type, a.favorites, a.address, round(avg(b.review_score),2) as score
from rest_info a inner join rest_review b
on a.rest_id = b.rest_id
where a.address like '서울%'
group by lpad(a.rest_id,5,0), a.rest_name, a.food_type, a.favorites, a.address
order by score desc, a.favorites desc
/*또는 where substr(a.address,2,1) = '서울' */- lpad()함수를 쓴 이유는 데이터가 1,2,3 으로 저장되어있어서 그런것
- LPAD("값","총문자길이","채움문자") (참고 블로그 : https://gent.tistory.com/190 )
- ROUND("값","자리수") (참고 블로그 : https://gent.tistory.com/241)
- 처음에 group by절을 쓰지않아 문제를 틀렸었다. not a single-group group function 이라고 떴는데, 이 에러는 그룹함수를 사용했는데 group by를 사용하지 않아 생긴 에러임 (참고 블로그 : https://gent.tistory.com/241)
- group by 에 여러개 쓰는 이유는 group by 로 기준이되는 컬럼들과 연산이 되는 컬럼만 조회돼야 오류가 안나서 그런거 아닐까 (참고 q&a : https://www.inflearn.com/questions/27971)
MAX, MIN, COUNT
#5. 문제
USER_INFO 테이블에서 2021년에 가입한 회원 중 나이가 20세 이상 29세 이하인 회원이 몇 명인지 출력하는 SQL문을 작성해주세요.
/*부등호 사용하기*/
SELECT count(user_id) from user_info where to_char(joined, 'YYYY') = '2021' and age>=20 and age <=29
/*between 사용하기*/
SELECT count(user_id) from user_info where to_char(joined, 'YYYY') = '2021' and age between 20 and 29- 문제가 '가입한 회원중 나이가 ~ ' 라서 group by -having 절을 사용할 생각을 함. 아마 이 개념을 잘 모르는 듯.. 하지만 그냥 조건이 2개였음
#6. 문제
동물 보호소에 들어온 동물의 이름은 몇 개인지 조회하는 SQL 문을 작성해주세요. 이때 이름이 NULL인 경우는 집계하지 않으며 중복되는 이름은 하나로 칩니다.
/*내가 한 방법*/
SELECT count(distinct name) as count from animal_ins
SELECT count(distinct name) as count from animal_ins where name is not null
/*다른사람이 한 방법*/
SELECT COUNT(name)
FROM (SELECT DISTINCT name
FROM ANIMAL_INS);- COUNT는 *가 아니면 null을 세지 않는다.
group by
#7. 문제
동물 보호소에 들어온 동물 이름 중 두 번 이상 쓰인 이름과 해당 이름이 쓰인 횟수를 조회하는 SQL문을 작성해주세요. 이때 결과는 이름이 없는 동물은 집계에서 제외하며, 결과는 이름 순으로 조회해주세요.
/*정답*/
SELECT name, count(name) from animal_ins group by name having count(name) > 1 order by name
/*틀림*/
SELECT name, count(name) from animal_ins group by name having count(*) > 1 order by name
/*COUNT(*) 로 할 경우 WHERE NAME IS NOT NULL 넣어주기 -- 정답*/
SELECT NAME, COUNT(NAME) AS COUNT
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY NAME
HAVING COUNT(*)>=2
ORDER BY NAME;- 왜 틀렸을까 생각해보니 *로 해서 이름이 없는 동물까지 집계한거 아닐까 생각해본다. 위에서 배웠듯이 COUNT는 *가 아니면 null 을 세지 않기 때문에, 문제에 따라 name 을 넣어주니 정답!
#8. 문제
보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 09:00부터 19:59까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.
/*처음 한 방식*/
SELECT to_char(datetime,'HH24') as hour, count(datetime) as count
from animal_outs group by to_char(datetime,'HH24')
having to_char(datetime,'HH24') >=9 and to_char(datetime,'HH24') <=19
order by hour
/*더 간단하게 */
SELECT to_char(datetime,'HH24') as hour, count(datetime) as count
from animal_outs group by to_char(datetime,'HH24')
having to_char(datetime,'HH24') between 9 and 19 order by hour- to_char(datetime,'HH24') 과 to_char(datetime,'HH') 차이점 : HH24로 하면 0~24로 시간이 표시되고, 그냥 HH로 하면 0~12로 표시 되는 듯! (참고 블로그 : 날짜 지정 포맷 형식 'YYYY-MM-DD HH24:MI:SS' (tistory.com))
#9. 문제 -- 어려움
보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.
(참고 블로그 : [Oracle] 오라클 고급쿼리 – 계층적 쿼리 (level) (tistory.com) )
(참고 블로그2 CONNECT BY LEVEL 사용법 : 오라클 계층쿼리 CONNECT BY LEVEL 사용법 | momo (tistory.com) )
(참고 블로그3 : [Oracle] CONNECT BY 계층형 쿼리가 어려울 때 (tistory.com) )
/*첫번째*/
select b.HOUR,count(a.HOUR) cnt
from (select to_char(datetime,'HH24') HOUR from animal_outs) a
, (select level-1 HOUR from dual connect by level <= 24) b
where b.HOUR=a.HOUR(+)
group by b.HOUR
order by b.HOUR
/*두번째*/
SELECT A.HOUR
, COUNT(B.ANIMAL_TYPE) AS COUNT
FROM (
SELECT DISTINCT (LEVEL-1) AS HOUR
FROM ANIMAL_OUTS
CONNECT BY LEVEL <= 24
) A
LEFT JOIN ANIMAL_OUTS B
ON TO_CHAR(DATETIME,'HH24') = A.HOUR
GROUP BY HOUR
ORDER BY HOUR
#10. 문제
PRODUCT 테이블에서 만원 단위의 가격대 별로 상품 개수를 출력하는 SQL 문을 작성해주세요. 이때 컬럼명은 각각 컬럼명은 PRICE_GROUP, PRODUCTS로 지정해주시고 가격대 정보는 각 구간의 최소금액(10,000원 이상 ~ 20,000 미만인 구간인 경우 10,000)으로 표시해주세요. 결과는 가격대를 기준으로 오름차순 정렬해주세요.
/*첫번째*/
SELECT substr(price,1,1) *10000 as price_group, count(product_id) as products
from product
group by substr(price,1,1) *10000
order by price_group
/*두번째*/
SELECT
floor(price/10000)*10000 as price_group
, count(product_id)
from product
group by floor(price/10000)
order by floor(price/10000) asc
;- SUBSTR() 함수 : substr('문자열', '위치', '길이' ) (참고 블로그 : [오라클] SUBSTR, SUBSTRB 함수 사용방법 (문자열, 자르기, 바이트, Left) (tistory.com) )
- floor()함수 : 소수점을 가장 가까운 정수로 내림하여 값을 변환 (참고 블로그 : [Oracle] 오라클의 내림 함수 - FLOOR() (tistory.com))
- 이문제에서 이해가 안되는게 예시에 9000원짜리가 있는데 첫번째 답을 적용했을 경우 90000원이 되는거 아님? 최소금액으로 하면 0 이되야하는데! 근데 실행결과에서 10000부터 나오고 정답맞댕,,