SQL Developer - 데이터 분석(4)

데이터

1. 남성이 구매하는 아이템 조회

• 남성인지 알수 있는 테이블 USERTBL - GENDER
• ORDERTBL - USERID 조인

1) 남성은 몇 명?

SELECT
    COUNT(*)
FROM
    usertbl
WHERE
    gender = 'M';

 

2) 남성이 구매한 아이템 아이디? 조회 (usertbl, ordertbl 테이블 조인)

SELECT
    u.userid,
    o.itemid
FROM
         usertbl u
    JOIN ordertbl o ON u.userid = o.userid
WHERE
    u.gender = 'M';

3) 7,20 아이템명과 매출액 조회

SELECT
    i.item_name,
    SUM(gmv) AS gmv
FROM
         usertbl u
    JOIN ordertbl o ON u.userid = o.userid
    JOIN item     i ON o.itemid = i.num
WHERE
    u.gender = 'M'
GROUP BY
    i.item_name;

2. 내부조인과 외부조인

SELECT
    *
FROM
    ordertbl
WHERE
    userid IS NULL; --24명

SELECT
    *
FROM
    usertbl
WHERE
    userid IS NULL; --0명

 

 

1) 내부조인

• ordertbl과 usertbl 조인할 때
• gender, age_band, sum(gmv)
• group by gender, age_band
• 24명에 대한 정보는 빠지고 조회됨.

SELECT
    u.gender,
    u.age_band,
    SUM(gmv) AS gmv
FROM
         ordertbl o
    JOIN usertbl u ON o.userid = u.userid
GROUP BY
    u.gender,
    u.age_band
ORDER BY
    u.gender,
    u.age_band;

2) 외부조인

• 9행이 출력 : 비회원 구매 매출액

SELECT
    u.gender,
    u.age_band,
    SUM(gmv) AS gmv
FROM
    ordertbl o
    LEFT OUTER JOIN usertbl  u ON o.userid = u.userid
GROUP BY
    u.gender,
    u.age_band
ORDER BY
    u.gender,
    u.age_band;

 

3. 날짜 조회

1) ordertbl 날짜 컬럼 조회

SELECT
    dt
FROM
    ordertbl;

 

2) number ==> 문자열로 변경

SELECT
    dt,
    to_char(dt) AS yyyymmdd
FROM
    ordertbl;

 

3) 년, 월, 일 구별 : 문자로 변경했기 때문에 잘라내기 가능

SELECT
    dt,
    substr(to_char(dt), 1, 4) AS yyyy,
    substr(to_char(dt), 5, 2) AS mm,
    substr(to_char(dt), 7, 2) AS dd
FROM
    ordertbl;

 

-- 한꺼번에 연결

SELECT
    dt,
    substr(to_char(dt), 1, 4)
    || '-'
    || substr(to_char(dt), 5, 2)
    || '-'
    || substr(to_char(dt), 7, 2) AS yyyymmdd
FROM
    ordertbl;

 

4. null 인 경우 다른 값으로 변경

1) ordertbl userid가 null 인 경우 0으로 변경

SELECT
    userid,
    nvl(userid, '0')
FROM
    ordertbl
WHERE
    userid IS NULL;

 

2) userid가 null이면 'NA'변경

-- NA  : 오류

number 타입인데 문자열로 바꿀 수 없음

SELECT
    userid,
    nvl(userid, 'NA') -- 01722. 00000 -  "invalid number"
FROM
    ordertbl
WHERE
    userid IS NULL;

-- 수정 후

SELECT
    userid,
    nvl(to_char(userid), 'NA')
FROM
    ordertbl
WHERE
    userid IS NULL;

 

3) 결과 값이 null 인경우 'NA'로 출력

SELECT
    nvl(u.gender, 'NA')   AS gender,
    nvl(u.age_band, 'NA') AS age_band,
    SUM(gmv)              AS gmv
FROM
    ordertbl o
    LEFT OUTER JOIN usertbl  u ON o.userid = u.userid
GROUP BY
    u.gender,
    u.age_band
ORDER BY
    u.gender,
    u.age_band;

 

5. case ~ when ~ else

1) F : 여성, M: 남성으로 출력되게 하고 싶음

SELECT
    gender,
    CASE gender
        WHEN 'M' THEN
            '남성'
        WHEN 'F' THEN
            '여성'
        ELSE
            'NA'
    END AS 성별
FROM
    usertbl;

 

2) 20~24 : 20s, 25~29 : 20s, 30~34,35~39 : 30s, 40~44: 40s, else : NA

SELECT
    nvl(u.gender, 'NA') AS gender,
    CASE u.age_band
        WHEN '20~24' THEN
            '20s'
        WHEN '25~29' THEN
            '20s'
        WHEN '30~34' THEN
            '30s'
        WHEN '35~39' THEN
            '30s'
        WHEN '40~44' THEN
            '40s'
        ELSE
            'NA'
    END                 AS age_gorup,
    SUM(gmv)            AS gmv
FROM
    ordertbl o
    LEFT OUTER JOIN usertbl  u ON o.userid = u.userid
GROUP BY
    u.gender,
    u.age_band
ORDER BY
    u.gender,
    u.age_band;