초보자를 위한 SQL 200제 -한 달에 책 한 권 끝내기- Day5

024~031

024 + INTERVAL '3' YEAR

SELECT product_sku_idx,created_date + interval'3'year
FROM thegifting_personal_matche.cart_items_log;

024 NULL값 대신 다른 데이터 출력하기( NVL,NVL2)

SELECT product_title,short_desc,NVL(discount_policy_idx,0)
 FROM thegifting_personal_matche.personal_store_products;

null값이 "0"으로 변경/실제로 데이터가 0으로 변경되는 것은 아니고 출력되는 쿼리에서만 0으로 출력되어 보이는 것

SELECT order_idx,ebut_sku_code,order_total_price - cart_discount_price ,
 NVL(cart_discount_price,0) AS'결제금액'
 FROM thegifting_personal_matche.erp_order_hst
 WHERE ebut_sku_code LIKE '%ITW-101%'
 order by 결제금액 ASC;

025 최대값 출력하기( MAX)

SELECT order_idx,ebut_sku_code,order_total_price - cart_discount_price ,
 MAX(NVL(cart_discount_price,0)) AS'결제금액'
 FROM thegifting_personal_matche.erp_order_hst
 WHERE ebut_sku_code LIKE '%ITW-101%'
 order by 결제금액 ASC;

026 최소값 출력하기( MIN)

SELECT order_idx,ebut_sku_code,order_total_price - cart_discount_price ,
 MIN(NVL(cart_discount_price,0)) AS'결제금액'
 FROM thegifting_personal_matche.erp_order_hst
 WHERE ebut_sku_code LIKE '%ITW-101%'
 order by 결제금액 ASC;

027 평균값 출력하기( AVG)

SELECT order_idx,ebut_sku_code,order_total_price - cart_discount_price ,
ROUND( AVG(NVL(cart_discount_price,0)),0) AS'결제금액'
 FROM thegifting_personal_matche.erp_order_hst
 WHERE ebut_sku_code LIKE '%ITW-101%'
 order by 결제금액 ASC;

028 토탈값 출력하기( SUM)

SELECT order_idx,ebut_sku_code,order_total_price - cart_discount_price ,
ROUND( SUM(NVL(cart_discount_price,0)),0) AS'결제금액'
 FROM thegifting_personal_matche.erp_order_hst
 WHERE ebut_sku_code LIKE '%ITW-101%'
 order by 결제금액 ASC;

029 데이터 분석 함수로 순위 출력하기( RANK)1

SELECT title as '상품명',RANK()OVER(order by score_choice DESC) as '상품 추천 순위'
FROM thegifting_personal_matche.v_recommend_disable_stock;

030 데이터 분석 함수로 순위 출력하기(DENSE_ RANK)2

순위가 1위인 상품이 여러개 일때,  다음순위로 출력되지 않고 2위로 출력되게 하는 함수

SELECT title as '상품명',dense_rank()OVER(order by score_choice DESC) as '상품 추천 순위'
FROM thegifting_personal_matche.v_recommend_disable_stock;

031 데이터 분석 함수로 등급 출력하기(NTILE)

NTILE(N ->N개의 등급을 나누기) OVER(등급나누는 기준)

SELECT title as '상품명', NTILE(4)OVER(order by stock_recommend desc) as'등급' 
FROM thegifting_personal_matche.v_recommend_disable_stock;