[solvesql] 연습문제 (난이도 : 보통)

 

- 복수 국적 메달 수상한 선수 찾기

 

• 2000년 이후 - games 테이블 / year 컬럼
• 메달 수상 - records 테이블 / medal 컬럼
• 2개 이상 국적 - records 테이블 / team_id 컬럼
• 선수 이름 - athletes 테이블 / name 컬럼

 

select a.name
from records r
  join games g
    on r.game_id = g.id
  join athletes a
    on r.athlete_id = a.id
where g.year >= 2000 
  and r.medal is not null
group by a.id
having count(distinct r.team_id) > 1
order by a.name

 

 

- 일별 블로그 방문자 수 집계

 

select strftime('%Y-%m-%d', event_date_kst) as dt
  , count(distinct user_pseudo_id) as users
from ga
where strftime('%Y-%m-%d', event_date_kst) between '2021-08-02' and '2021-08-09'
group by strftime('%Y-%m-%d', event_date_kst)
order by strftime('%Y-%m-%d', event_date_kst)

 

count(컬럼) -> null을 제외하고 count하기 때문에, '이벤트가 하나라도 기록 된 경우 방문자로 집계합니다.' 라는 조건 자동 만족

 

 

- 할부는 몇 개월로 해드릴까요

 

select payment_installments
  , count(distinct order_id) as order_count
  , min(payment_value) as min_value
  , max(payment_value) as max_value
  , avg(payment_value) as avg_value
from olist_order_payments_dataset
where payment_type = 'credit_card'
group by payment_installments

 

 

- 가구 판매의 비중이 높았던 날 찾기

 

select order_date
  , count(distinct case when category = 'Furniture' then order_id end) as furniture
  , round(count(distinct case when category = 'Furniture' then order_id end) / (count(distinct order_id)+0.00)*100, 2) as furniture_pct -- 백분율
from records
group by order_date -- 일별 주문수 10개 이상인 날
having count(distinct order_id) >= 10
  and furniture_pct >= 40
order by furniture_pct desc, order_date;

 

* distinct order_id >> distinct 주의!!

* 소수점 표현하는 것이 핵심

 

데이터 타입변환 함수

1. CAST 함수

 

-- cast 함수 문법
select cast(컬럼 as 데이터 형식)

-- 예시
select cast('100.00' as int) -- double(실수)형 -> integer(정수)형으로 변환
select cast('2022-10-01' as date) -- 문자열 -> date형으로 변환

 

2. CONVERT 함수

 

-- 문법
select convert(데이터 형식[(길이)], 컬럼)

-- 예시
select convert(nvarchar(10), 컬럼) as 별칭 -- varchar로 변환
select convert(int, 컬럼) -- int로 변환

 

<숫자형>

 

int : 정수

float : 부동소수점

numeric & decimal : 실수

​

numeric / decimal( [전체길이(소수점이하포함)], [소수점이하길이] )

 

<타입 종류>

 

DOUBLE, REAL	소수점 아래 15자리까지 표현
FLOAT	소수점 아래 7자리까지 표현
BINARY	값을  binary로 변환
CHAR	값을 문자열로 변환
DATE	값을 yyyy-mm-dd의 date로 변환
DATETIME	값을 yyy-mm-dd hh:mm:ss의 datetime으로 변환
TIME	값을 hh:mm:ss의 time으로 변환
DECIMAL	값을 최대자릿수인(M), 소수점 이하 자릿수(D)로 지정하여 decimal로 변환
NCHAR	값을 nchar로 변환(char랑 비슷하지만, 국가별 문자 세트로 문자열 생성)
SIGNED ~	값을 signed(부호 있는 64비트 정수)로 변환합니다.
UNSIGNED ~	값을 signed(부호 없는 64비트 정수)로 변환합니다.

 

- 지역별 주문의 특징 ('~별 집계' 유형)

 

-- 첫 번째 방법
select region as Region
  , count(distinct case when category = 'Furniture' then order_id end) as 'Furniture'
  , count(distinct case when category = 'Office Supplies' then order_id end) as 'Office Supplies'
  , count(distinct case when category = 'Technology' then order_id end) as 'Technology'
from records
group by region
order by region

-- 두 번째 방법
select Region
  , sum(case when category = 'Furniture' then order_cnt end) as 'Furniture'
  , sum(case when category = 'Office Supplies' then order_cnt end) as 'Office Supplies'
  , sum(case when category = 'Technology' then order_cnt end) as 'Technology'
from 
(
  select region Region, category, count(distinct order_id) as order_cnt
  from records
  group by region, category
  order by region
) as r2 -- region, category 별 주문량
group by region
order by region;

 

* 'as 별칭' - 별칭에 띄어쓰기 있다면 따옴표(', ") 붙이는거 잊지 말기

 

[MySQL] table pivot을 하며 깨달은 CASE()과 GROUP BY()의 속성

 

select Region
  , case when category = 'Furniture' then order_cnt end as 'Furniture'
  , case when category = 'Office Supplies' then order_cnt end as 'Office Supplies'
  , case when category = 'Technology' then order_cnt end as 'Technology'
from 
(
  select region Region, category, count(distinct order_id) as order_cnt
  from records
  group by region, category
  order by region
) as r2 -- region, category 별 주문량
--group by region
order by region;

서브쿼리 r2 테이블 (region, category별 주문량 집계 테이블)

sum()을 해야 하는 이유 (> sum 하면 null은 자동으로 제외되고 처리) -> 이후 region으로 그룹화

 

- 작품이 없는 작가 찾기

 

select a1.artist_id as artist_id, name
from artists a1 -- moma에 등록된 작가
  left join artworks_artists a2
    on a1.artist_id = a2.artist_id
where death_year is not null -- 현재 살아있지 않음
  and artwork_id is null; -- moma에 등록된 작품이 없음
--order by artwork_id limit 1000