[SQL] 프로그래머스 SQL

-- 코드를 입력하세요
-- 리뷰를 가장 많이 작성한 회원의 리뷰를 조회하는 SQL
-- 회원 이름, 리뷰 텍스트, 리뷰 작성일 출력하는데 리뷰 작성일 기준 오름차순
SELECT MEMBER_NAME, REVIEW_TEXT,TO_CHAR(REVIEW_DATE,'yyyy-mm-dd')
FROM MEMBER_PROFILE MP, rest_review r
where MP.MEMBER_ID=r.MEMBER_ID
and r.member_ID IN(select member_id
 from REST_REVIEW 
 group by member_id
having count(member_id)
=(select max(mycount)
 from (select member_id, count(member_id) mycount
       from rest_review
       group by member_id)))
ORDER BY REVIEW_DATE ASC;

 

SELECT A.MEMBER_NAME, B.REVIEW_TEXT, B.REVIEW_DATE
FROM MEMBER_PROFILE A INNER JOIN REST_REVIEW B
ON A.MEMBER_ID = B.MEMBER_ID
WHERE A.MEMBER_ID IN 
(      
    # 랭크가 1위인 MEMBER_ID 뽑기
    SELECT D.MEMBER_ID
    FROM
    (   
        # MEMBER_ID 카운트의 랭크 매기기
        SELECT C.MEMBER_ID, RANK() OVER (ORDER BY C.COUNTS DESC) AS RANKS
        FROM
        (   
            # MEMBER_ID 당 카운트 세기
            SELECT MEMBER_ID, COUNT(MEMBER_ID) AS "COUNTS"
            FROM REST_REVIEW
            GROUP BY MEMBER_ID
        ) C
    ) D
    WHERE D.RANKS = 1
) 
ORDER BY B.REVIEW_DATE

 

to_char

to_date

차이

https://deftkang.tistory.com/86

[Oracle] 날짜 검색방법(자료형변환 Between사용방법)및 주의사항

 

 

https://gent.tistory.com/282

[Oracle] 오라클 BETWEEN ~ AND 범위 검색 사용법 (날짜, 문자)

 

 

sum과 count 헷갈리지 않도록

select product_code, sum(allamount*price) as sales
from product prod,
(select product_id, count(sales_amount) allamount
from offline_sale
group by product_id) al
where prod.product_id=al.product_id
group by product_code
order by sales desc, product_code asc;

 

행으로 합치기 - Listagg

WITH price_group AS(
SELECT count(*)  as "10000"
from product
where price between 10000 and 20000
UNION ALL    
SELECT count(*) as "20000"
from product
where price between 20000 and 30000)
select * from price_group;

 

 

마지막 줄 읽기

SELECT * FROM student ORDER BY ROWID DESC LIMIT 1;

 

오라클 올림 round

버림 trunc

SELECT TRUNC(PRICE, -4) AS PRICE_GROUP, COUNT(PRODUCT_ID) AS PRODUCTS
FROM PRODUCT
GROUP BY TRUNC(PRICE, -4)
ORDER BY PRICE_GROUP ASC;

 

+) CASE 문

CASE WHEN BETWEEN AND THEN END "ALIAS 이름"

SELECT
    CASE 
        WHEN PRICE BETWEEN 0 AND 9999
    THEN 0
        WHEN PRICE BETWEEN 10000 AND 19999
    THEN 10000
        WHEN PRICE BETWEEN 20000 AND 29999
    THEN 20000
        WHEN PRICE BETWEEN 30000 AND 39999
    THEN 30000
    WHEN PRICE BETWEEN 40000 AND 49999
    THEN 40000
    WHEN PRICE BETWEEN 50000 AND 59999
    THEN 50000
    WHEN PRICE BETWEEN 60000 AND 69999
    THEN 60000
    WHEN PRICE BETWEEN 70000 AND 79999
    THEN 70000
    WHEN PRICE BETWEEN 80000 AND 89999
    THEN 80000
    END PRICE_GROUP,
    COUNT(*) AS PRODUCTS
    FROM PRODUCT
    GROUP BY  CASE 
        WHEN PRICE BETWEEN 0 AND 9999
    THEN 0
        WHEN PRICE BETWEEN 10000 AND 19999
    THEN 10000
        WHEN PRICE BETWEEN 20000 AND 29999
    THEN 20000
        WHEN PRICE BETWEEN 30000 AND 39999
    THEN 30000
    WHEN PRICE BETWEEN 40000 AND 49999
    THEN 40000
    WHEN PRICE BETWEEN 50000 AND 59999
    THEN 50000
    WHEN PRICE BETWEEN 60000 AND 69999
    THEN 60000
    WHEN PRICE BETWEEN 70000 AND 79999
    THEN 70000
    WHEN PRICE BETWEEN 80000 AND 89999
    THEN 80000
    END 
    ORDER BY PRICE_GROUP;

 

 

https://school.programmers.co.kr/learn/courses/30/lessons/131123?language=oracle 

프로그래머스

 

full group by의 함정

select rest_id, food_type, max(favorites)
from rest_info
group by food_type;

: 즐겨찾기수는 제대로 가져오지만, 다른 칼럼의 경우 모두 상단의 필드를 가져온다 -> 잘못된 group by

 

select rest_id, food_type, favorites,max(favorites)
from rest_info
group by food_type;

00001	한식	734	734
00002	일식	112	230
00003	양식	102	102
00008	분식	151	151
00015	중식	20	20

: favorites 수가 일치하지 않는 것을 확인가능

 

따라서, rest_id만을 가지고 찾는것은 불가능

-> 조인을 이용 -> food_type과 max(favorites) 모두 확인

select r1.food_type, r1.rest_id, r1.rest_name, r1.favorites
from rest_info r1 inner join(select food_type,max(favorites) as maxfavor
from rest_info group by food_type) r2
on r1.favorites=r2.maxfavor and r1.food_type = r2.food_type
order by r1.food_type desc;

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

프로그래머스

 

-> 연관 문제https://school.programmers.co.kr/learn/courses/30/lessons/131537?language=oracle

프로그래머스

SELECT TO_CHAR(SALES_DATE,'YYYY-MM-DD') AS SALES_DATE, PRODUCT_ID, USER_ID, sum(SALES_AMOUNT) AS AMOUNT
FROM ONLINE_SALE
WHERE TO_CHAR(SALES_DATE,'YYYY-MM')  = '2022-03'
GROUP BY SALES_DATE, PRODUCT_ID, USER_ID
UNION ALL
SELECT TO_CHAR(SALES_DATE,'YYYY-MM-DD'), PRODUCT_ID, NULL, sum(SALES_AMOUNT)
FROM OFFLINE_SALE
WHERE TO_CHAR(SALES_DATE,'YYYY-MM')  = '2022-03'
GROUP BY SALES_DATE, PRODUCT_ID
ORDER BY 1,2,3;

 

---

-- 코드를 입력하세요
select f1.category, mprice, product_name
from food_product f1, 
(select max(price) mprice, category 
 from food_product 
 group by category) f2
 where mprice=f1.price
 and f2.category=f1.category
 and f1.category IN('과자', '국', '김치', '식용유')
 order by mprice desc;

---

where절 서브쿼리

: 다중행 서브쿼리

 

-- 코드를 입력하세요
select id, name, host_id
from places
where host_id IN (SELECT host_id
from places
group by host_id
having count(host_id)>=2)
order by id;

 

 

칼럼안의 한글은 '한글'

날짜는 date일경우 To_char로 바꿔서

varchar2일경우 to_date로 바꿔서

-- 코드를 입력하세요
-- 출고여부 : 출고대기 출고예정 출고 완료
--,	As 출고여부
SELECT ORDER_ID ,PRODUCT_ID, TO_CHAR(OUT_DATE, 'yyyy-mm-dd'),
case
when TO_char(out_date,'mm-dd')<='05-01' then '출고완료'
when out_date is null then '출고미정'
when TO_char(out_date,'mm-dd')>'05-01' then '출고대기'
end 출고여부
from food_order
order by order_id;

 

 

intersect

minus

등등

SELECT      CART_ID
FROM        CART_PRODUCTS 
WHERE       NAME = 'Milk'
INTERSECT   -- 위 아래 SELECT 반환이 동일할 경우 중복된 값을 반환
SELECT      CART_ID
FROM        CART_PRODUCTS 
WHERE       NAME = 'Yogurt';

 

조인 이용

-- 코드를 입력하세요
SELECT c1.cart_id
from cart_products c1 inner join cart_products c2
on c1.cart_id=c2.cart_id
and c1.name='Milk'
and c2.name='Yogurt'
group by c1.cart_id
order by c1.cart_id;

 

날짜는 항상 한쪽 자료형으로 변경

-- 코드를 입력하세요
-- 2022년 05월 생산한, 식품ID, 식품 이름, 총매출
-- 총매출 기준 내림차순, 총매출이 같으면 식품 ID 오름차순
SELECT prod.product_id, product_name, allcount*price as total_sales
From food_product prod,
(select product_id, sum(amount) allcount
 from food_order
 where TO_CHAR(product_date, 'YYYY-MM')='2022-05'
 group by product_id) ord
where ord.product_id = prod.product_id
order by total_sales desc, product_id;

 

 

평균 함수의 경우 널값이 존재하는지 여부를 파악해 -> 0으로 치환해야한다.

NVL("해당 칼럼명", "치환값")

https://school.programmers.co.kr/learn/courses/30/lessons/131118?language=mysql 

프로그래머스

 

mysql의 경우 full group by 가능

-- 코드를 입력하세요
-- 서울에 위치한 평균점수 세번째에서 반올림
-- 평균점수 기준 내림차순, 즐겨찾기 수 기준 내림차순
SELECT info.rest_id, rest_name, food_type, favorites, address, round(
    avg(review_score),2) average
from rest_info info, rest_review review
where info.address like '서울%'
and info.rest_id=review.rest_id
group by rest_id
Order by average desc, favorites desc;

 

 

oracle

-- 코드를 입력하세요
-- 서울에 위치한 평균점수 세번째에서 반올림
-- 평균점수 기준 내림차순, 즐겨찾기 수 기준 내림차순
SELECT info.rest_id, rest_name, food_type, favorites, address, round(
    avg(review_score),2) average
from rest_info info, rest_review review
where info.address like '서울%'
and info.rest_id=review.rest_id
group by info.rest_id, rest_name, food_type, favorites, address
order by average desc, favorites desc;

https://school.programmers.co.kr/learn/courses/30/lessons/131532?language=oracle 

프로그래머스

-- 코드를 입력하세요
SELECT TO_CHAR(sales_date,'YYYY'), TO_CHAR(sales_date,'MM') , gender, count(distinct sale.user_id) 
from online_sale sale, USER_Info id
where sale.user_id = id.user_id
and gender is not null
group by TO_CHAR(sales_date,'YYYY'), TO_CHAR(sales_date,'MM') , gender
order by 1,2,3;

 

 

1. 상품을 구매한 회원 비율 구하기https://school.programmers.co.kr/learn/courses/30/lessons/131534

프로그래머스

 

 

-- 코드를 입력하세요
-- 2021년에 가입한 전체 회원 중 상품 구매 회원수와 상품 구매 회원 비율
-- 년, 월 별로 출력
-- 소수점 두번째에서 반올림
-- 년 기준 오름차순, 월 기준 오름차순

SELECT TO_CHAR(sales_date, 'YYYY'), TO_CHAR(sales_date, 'MM'), count(distinct onsale.user_id), round(count(distinct onsale.user_id)/
(SELECT count(USER_ID) FROM USER_INFO WHERE TO_CHAR(JOINED,'YYYY')= 2021),1) as puchased_ratio
from user_info info, online_sale onsale
where to_char(joined, 'YYYY') = 2021
and info.user_id = onsale.user_id
group by TO_CHAR(sales_date, 'YYYY'), TO_CHAR(sales_date, 'MM')
order by 1,2;