SQL Developer - 데이터 분석(5)

데이터

 

1. TOP3 카테고리와 그 외 상품의 매출액 비교

SELECT
    item_type,
    SUM(gmv) AS gmv
FROM
    (
        SELECT
            CASE
                WHEN c.cate1 IN ( '스커트', '티셔츠', '원피스' ) THEN
                    'TOP3'
                ELSE
                    '기타'
            END      AS item_type,
            SUM(gmv) AS gmv
        FROM
                 ordertbl o
            JOIN item     i ON o.itemid = i.num
            JOIN category c ON i.category_id = c.num
        GROUP BY
            c.cate1,
            c.cate2,
            c.cate3
        ORDER BY
            gmv DESC
    )
GROUP BY
    item_type;

 

2. 아이템 컨셉을 지정 후 추출

• 시크, 깜찍, 청순, 기본 키워드별 상품 분류 후 매출 조회
• ordertbl : item_name, item_concept, gmv
• item_concept : item_name에 해당 키워드가 존재하는 경우 
  • 청순 --> 청순컨셉
  • 깜찍 --> 깜찍컨셉
  • 시크 --> 시크컨셉
  • 기본 --> 기본컨셉
  • 아무것도 없다면 --> 미분류

1) 키워드 별 상품 분류

SELECT
    item_name,
    CASE
        WHEN item_name LIKE '%깜찍%' THEN
            '깜찍컨셉'
        WHEN item_name LIKE '%청순%' THEN
            '청순컨셉'
        WHEN item_name LIKE '%시크%' THEN
            '시크컨셉'
        WHEN item_name LIKE '%기본%' THEN
            '기본컨셉'
        ELSE
            '미분류'
    END      AS item_concept,
    SUM(gmv) AS gmv
FROM
         ordertbl o
    JOIN item i ON o.itemid = i.num
GROUP BY
    item_name
ORDER BY
    gmv DESC;

2) item_concept 별 매출 구하기

SELECT
    item_concept,
    SUM(gmv) AS gmv
FROM
    (
        SELECT
            item_name,
            CASE
                WHEN item_name LIKE '%깜찍%' THEN
                    '깜찍컨셉'
                WHEN item_name LIKE '%청순%' THEN
                    '청순컨셉'
                WHEN item_name LIKE '%시크%' THEN
                    '시크컨셉'
                WHEN item_name LIKE '%기본%' THEN
                    '기본컨셉'
                ELSE
                    '미분류'
            END      AS item_concept,
            SUM(gmv) AS gmv
        FROM
                 ordertbl o
            JOIN item i ON o.itemid = i.num
        GROUP BY
            item_name
        ORDER BY
            gmv DESC
    )
GROUP BY
    item_concept
ORDER BY
    gmv DESC;

 

3. 날짜 관련 함수

• 문자열로 바꾸면 형식을 줄 수 있음 : 자르기, 채우기 등등
•  +,- 가능

SELECT
    sysdate,
    current_date,
    current_timestamp
FROM
    dual;

1) 연도만 조회

SELECT
    to_char(sysdate, 'yyyy')
FROM
    dual;

2) 월만 조회

SELECT
    to_char(sysdate, 'mm')
FROM
    dual;

3) 일만 조회

SELECT
    to_char(sysdate, 'dd')
FROM
    dual;

 

4. 현재 날짜에서 다른 날짜 구하기

1) 2개월 후 

SELECT
    add_months(sysdate, 2)
FROM
    dual;

2) 2개월 전

SELECT
    add_months(sysdate, - 2)
FROM
    dual;

3)  +20일

SELECT
    sysdate + 20
FROM
    dual;

 

5. 최근 1년 동안의 매출액

1) gmv_trend 날짜 형식 확인

SELECT
    *
FROM
    gmv_trend;

 

2) 원하는 날짜 형식으로 맞춰서 추출

SELECT
    yyyy,
    mm,
    to_date(concat(yyyy, mm), 'yyyymm')
FROM
    gmv_trend;

 

3) 최근 1년동안의 매출액

• 날짜는 대소 비교 가능
• 날짜가 2021년 3월에 끊겨서 아무것도 나오지 않음

SELECT
    *
FROM
    gmv_trend
WHERE
    to_date(concat(yyyy, mm), 'yyyymm') >= add_months(sysdate, 12);

6. 할인율, 이익률, 판매가 계산 : ordertbl

• 쇼핑몰 관점에서 출력

SELECT
    dt,
    orderid,
    userid,
    itemid,
    price,
    unitsold, -- 제품을 몇개씩 판매했는지
    gmv,
    product_profit,
    discount,
    discount / gmv       AS discount_rate,
    gmv - discount       AS paid_amount,
    product_profit / gmv AS product_margin,
    total_profit / gmv   AS total_margin
FROM
    ordertbl;

 

7. 인당 평균 구매 수량 = 총 판매수량 / 총 고객 수 (ordertbl과 item join)

SELECT
    i.item_name,
    SUM(unitsold)                                    AS unitsold,
    COUNT(DISTINCT userid)                           AS user_cnt,
    round(SUM(unitsold) / COUNT(DISTINCT userid), 2) AS avg_unitsold_per_customer
FROM
         ordertbl o
    JOIN item i ON o.itemid = i.num
GROUP BY
    i.item_name
ORDER BY
    avg_unitsold_per_customer DESC;

 

8. 인당 평균 구매 금액 = 총 구매 금액 / 총 고객 수

SELECT
    i.item_name,
    SUM(unitsold)                                    AS unitsold,
    COUNT(DISTINCT userid)                           AS user_cnt,
    round(SUM(unitsold) / COUNT(DISTINCT userid), 2) AS avg_unitsold_per_customer,
    round(SUM(gmv) / COUNT(DISTINCT userid), 2)      AS avg_gmv_per_customer
FROM
         ordertbl o
    JOIN item i ON o.itemid = i.num
GROUP BY
    i.item_name
ORDER BY
    avg_unitsold_per_customer DESC;

 

9. 인당 구매 금액이 높은 성별 / 연령대 조회

SELECT
    gender,
    age_band,
    SUM(gmv)                                      AS gmv,
    COUNT(DISTINCT o.userid)                      AS user_cnt,
    round(SUM(gmv) / COUNT(DISTINCT o.userid), 2) AS avg_gmv_per_customer
FROM
         ordertbl o
    JOIN usertbl u ON o.userid = u.userid
GROUP BY
    gender,
    age_band
ORDER BY
    avg_gmv_per_customer DESC;

 

10. 하루 동안 2개 이상의 상품(동일한 제품 제외)을 구매한 고객은 주로 어떤 상품을 구매했을까?

1) 2개씩 구매한 아이디와 건수 조회

SELECT
    userid,
    COUNT(DISTINCT itemid) AS item_cnt
FROM
    ordertbl
GROUP BY
    userid
HAVING
    COUNT(DISTINCT itemid) > 1;

 

2) 최종

SELECT
    cate3,
    i.item_name,
    price,
    COUNT(DISTINCT o.userid) AS user_cnt,
    SUM(gmv)                 AS gmv
FROM
         ordertbl o
    JOIN item     i ON o.itemid = i.num
    JOIN category c ON i.category_id = c.num
    JOIN (
        SELECT
            o.userid,
            COUNT(DISTINCT itemid) AS item_cnt
        FROM
                 ordertbl o
            JOIN usertbl u ON o.userid = u.userid
        GROUP BY
            o.userid
        HAVING
            COUNT(DISTINCT itemid) > 1
    )        user_list ON o.userid = user_list.userid
GROUP BY
    cate3,
    i.item_name,
    price
ORDER BY
    user_cnt DESC;

 

12. 하루동안 2개 이상의 제품을 구매한 고객의 성연령 비율 조회

SELECT
    gender,
    age_band,
    COUNT(DISTINCT o.userid)               AS user_cnt,
    COUNT(DISTINCT
        CASE
            WHEN user_list.userid IS NOT NULL THEN
                user_list.userid
            ELSE
                NULL
        END
    )                                      AS user_id_cnt,
    round(COUNT(DISTINCT
        CASE
            WHEN user_list.userid IS NOT NULL THEN
                user_list.userid
            ELSE
                NULL
        END
    ) / COUNT(DISTINCT o.userid) * 100, 1) AS user_percent
FROM
         ordertbl o
    JOIN usertbl u ON o.userid = u.userid
    LEFT JOIN (
        SELECT
            o.userid,
            COUNT(DISTINCT itemid) AS item_cnt
        FROM
                 ordertbl o
            JOIN usertbl u ON o.userid = u.userid
        GROUP BY
            o.userid
        HAVING
            COUNT(DISTINCT itemid) > 1
    )       user_list ON o.userid = user_list.userid
GROUP BY
    gender,
    age_band
ORDER BY
    gender,
    age_band;