[연습] SQL Lv.1~3

PART 1.

count(distinct())

where... like  / where... substr

 

Q. user 테이블에서 "김"씨로 시작하는 이용자들 수를 세어 보시오.

R. 출력예시 name_cnt: "김"씨 성을 가지고 있는 교육생의 수

 

풀이1 : where...like 사용

1. count(distinct(컬럼명))

    근데 왜 distinct(name) 컬럼으로 카운트하면 개수가 틀리게 나올까?

2. where 컬럼 like "%"

select count(distinct(user_id)) name_cnt
from users
where name like "김%"

 

풀이2 : where...substr 사용

1. count(distinct(컬럼명))

    근데 왜 distinct(name) 컬럼으로 카운트하면 개수가 틀리게 나올까?

2.where substr(컬럼, 몇번째, 몇글자) = '김'

select count(distinct(user_id)) name_cnt
from users
where substr(name,1,1)='김'

 

정리

count(1)은 전체 행렬의 데이터 개수를 나타내므로, count(distinct(컬럼))을 해야 특정 컬럼의 데이터 개수 확인

 

---

PART 2.

select date(컬럼)

           round(avg(컬럼))

group by

 

Q. 이용자들이 날짜별로 획득한 포인트가 늘어나는지 줄어드는지 확인R. 출력예시

created_at: 익명화된 유저들의 아이디

average_points: 유저가 획득한 날짜별 평균 포인트, 반올림 필수

 

풀이 

1. 날짜형

    date(컬럼)

2. 반올림한 평균 포인트

    round(avg(컬럼))

3. 날짜별

    grounp by(select에서 소환한 컬럼)

select date(created_at) created_at,
       round(avg(point)) average_points
from point_users
group by date(created_at)

 

정리

가장 최근 날짜의 경우, max(date)로 표기

반올림 round()

---

PART 3.

left join

coalesce(컬럼명, null을 대체할 값)

order by ... desc

 

Q. 이용자별 획득 포인트 메일로 보내기

R. 출력예시

user_id: 익명화된 유저들의 아이디

email: 유저들의 이메일

point: 유저가 획득한 포인트

- users 테이블에는 있지만 point_users에는 없는 user는 포인트가 없으므로 0으로 처리

- 포인트 기준 내림차순 정렬

 

풀이 

1. 하나의 기준으로 테이블 연결

    left join

2. 해당 값이 null 이면 다른 값을 지정하는 함수

    coalesce

3. 차순 정리 

    order by desc

select u.user_id,
       u.email,
       coalesce(point,0) point
from users u left join point_users p on u.user_id=p.user_id
order by point desc

 

정리

inner join은 각 테이블의 공통 항목만 불러오는 것 (A∩B)

따라서, 한 테이블의 모든 데이터를 살리고 싶다면, left join을 해야 함

null데이터의 경우, coalesce(컬럼, 대체값) 가능정렬은 order by를 씀