1. SQL5주차 완강 및 복습, 아티클스터디(데이터 분석가가 되어보니 중요한 것들)

2. 나만의 언어로 학습

아티클 스터디

• 데이터 분석가는 올바른 의사결정을 하기위해 돕는 사람으로 본다.
• 도메인 지식(상업, 상품 및 서비스에 대한 지식)을 길러 생각의 프레임 워크(정보에 대한 시각)을 길러 작업 시간을 줄이고 명확한 커뮤니케이션, 제대로 된 해석을 이끌어 내야한다. 도메인 지식은 회사에서 시도한 방법과 결과 등의 정보를 축적해 쌓아가는것이 좋다.

SQL

• if <> not given 을 통하여 평균에서 없는값 제외하기
• null은 x와 같은 의미
• left join사용시 빈데이터 빼고 뽑고싶을때 'where 테이블 이름 is not null'
• 다른값을 대신해서 사용하는 방법
  lf(rating>=1, rating , 대체값) 2. null값일때 coalesce(age, 대체값)
• 값이 상식적이지 않을때 조건문으로 값의 범위를 지정하기
  select name, age,
  case when age<15 then 15
  when age>=80 then 80
  else age end re_age
  from customers
• pivot table이란 2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는것
  데이터 값을 깔금하게 하기위해서 max를 사용하여 정리
  max(if(hh='15', cnt_order, 0)) "15"
• window_function - 함수로 그룹 내의 연산을 쉽게 만듬
  window_function(argument) over(partition by 그룹 칼럼 order by 정렬기준)
• Rank 특정 기준으로 순위를 매기는 함수
  rank() over(partition by 그룹 기준 칼럼 order by 정렬기준 칼럼) ranking
• sum 특정 기준으로 합을 구하는 함수
  sum(합할칼럼) over(partition 그룹 기준 칼럼) sum_칼럼이름
  누적수를 구할때는 sum(합할칼럼) over(partition by 그룹 기준 칼럼 order by 정렬칼럼)
• 문자를 날짜로 변환하는 포맷 함수 date - date(칼럼이름)
  date(date) date_type,
  date_format(date(date), '%Y') "년",
  date_format(date(date), '%m') "월",
  date_format(date(date), '%d') "일",
  date_format(date(date), '%w') "요일" (일요일=0)
• YEAR(칼럼)으로 년도로 변환가능, MONTH로 월, DAY로 일 가능
• CURDATE() between 시간칼럼 and 시간칼럼으로 시간상 진행중인 시간대 특정가능
• DATEDIFF(종료시간칼럼,시작시간칼럼) AS working_days로 지속기간 일수 계산 가능

3. 오늘 해본 문제들 코드모음

SQL

[실습] pivot table 뷰 만들어보기(성별, 연령별 주문건수- 나이는10-59사이, 연령순 내림차순)
select age,
max(if(gender='male', cnt_order, 0)) "male",
max(if(gender='female', cnt_order, 0)) "female"
from
(
select gender,
case when age between 10 and 19 then 10
when age between 20 and 29 then 20
when age between 30 and 39 then 30
when age between 40 and 49 then 40
when age between 50 and 59 then 50 end age,
count(1) cnt_order
from food_orders f inner join customers c on f.customer_id=c.customer_id
where age between 10 and 59
group by 1,2
) a
group by 1
order by 1 desc

[실습] Rank함수를 이용하여 음식타입별 순위를 3위까지만 데이터 추출
select cuisine_type, restaurant_name,cnt_order,
rank() over(partition by cuisine_type order by cnt_order desc) ranking
from
(
select cuisine_type, restaurant_name,cnt_order,
rank() over(partition by cuisine_type order by cnt_order desc) ranking
from
(
select cuisine_type , restaurant_name,
count(1) cnt_order
from food_orders
group by 1, 2
) a
) b
where ranking<=3

[실습] 주문건이 해당하는 음식 타입이 차지하는 비율을 구하고, 주문건 낮은 순으로정렬시 누적합
select cuisine_type, restaurant_name, cnt_order,
sum(cnt_order) over(partition by cuisine_type) sum_cuisine,
sum(cnt_order) over(partition by cuisine_type order by cnt_order) cum_cuisine
from
(
select cuisine_type , restaurant_name,
count(1) cnt_order
from food_orders
group by 1, 2
)a
order by cuisine_type, cnt_order

[실습] 년도별로 3월의 주문건수 구하기
select date_format(date(date),'%Y')"년",
date_format(date(date), '%m') "월",
date_format(date(date),'%Y%m')"년월",
count(1) "주문건수"
from food_orders f inner join payments p on f.order_id=p.order_id
where date_format(date(date), '%m') = '03'
group by 1, 2, 3
order by 1

[숙제] 음식 타입별, 연령별 주문건수 pivot view 만들기(연령은 10~59세 사이)
select cuisine_type,
max(if(age=10, cnt_order, 0))"10대",
max(if(age=20, cnt_order, 0)) "20대",
max(if(age=30, cnt_order,0))" 30대",
max(if(age=40, cnt_order, 0)) "40대",
max(if(age=50, cnt_order, 0)) "50대"
from
(
select f.cuisine_type,
case when age between 10 and 19 then 10
when age between 20 and 29 then 20
when age between 30 and 39 then 30
when age between 40 and 49 then 40
when age between 50 and 59 then 50 end age,
count(1) cnt_order
from food_orders f inner join customers c on f.customer_id=c.customer_id
where age between 10 and 59
group by 1, 2
)a
group by 1

5주차 퀘스트

17. team_projects 테이블에서 AWS 예산(aws_cost)이 40000 이상 들어간 프로젝트들의 이름을 선택하는 쿼리를 작성해주세요!

select name
from team_projects
where aws_cost>=40000

18. team_projects 테이블에서 2022년에 시작된 프로젝트를 선택하는 쿼리를 작성해주세요! 단, start_date < ‘2023-01-01’ 조건을 사용하지 말고 쿼리를 작성해주세요!
    (내가 생각한 답)
    select
    from team_projects
    where start_date between '2022-01-01' and '2022-12-31'
    (실제 정답)
    select
    from team_projects
    where YEAR(star_date)=2022

19. team_projects 테이블에서 현재 진행중인 프로젝트를 선택하는 쿼리를 작성해주세요. 단, 지금 시점의 날짜를 하드코딩해서 쿼리하지 말아주세요!
    select *
    from team_projects
    where CURDATE() between start_date and end_dat

20. team_projects 테이블에서 각 프로젝트의 지속 기간을 일 수로 계산하는 쿼리를 작성해주세요!
    select name, DATEDIFF(end_date,start_date) AS working_day
    from team_projects

4. SQLD 자격증 강의 수강