[AI School-TIL] SQL(BigQuery) 7일차_23.02.10

 

• 배웠던 내용 총정리

 

-distinct : 중복 제거 (group by하지 않아도 연도별 중복 제거를 해서 볼 수 있음)

-where : 조건을 부여해서 원하는 데이터만 구해 옴 (필터처럼)

-연산

연산자 사용 가능 논리 연산 (and, or, not) -> ()괄호를 묶어서 사용 가능 between A and B : A와 B를 포함한 사이의 값 in A : A 안에 값과 일치하는 값을 조회 (서브쿼리를 써서 조회하기 원하는 브랜드를 불러올 때 사용) like '비교문자' : %(와일드카드), 대소문자를 안 가림, 언더바는 글자의 수를 비교 is null : null이라는 아무 것도 없는 값 (0은 값이 있는 것). is를 통해서만 연산 (=, !=은 사용 안 됨)

-집계함수, group by

count : count(*)은 null값 포함, count(컬럼명)은 null값 포함하지 않음 sum : 합계 avg max min group by : 데이터를 그룹화해서 조회할 때, group by로 쪼개야 함

-having, order by

having : group by한 집계에서 한번 더 필터를 걸 때 사용 -where과 차이점은 ? where에서 추려놓은 상태에서 select가 됨 -having은 select까지 해서 나온 결과값에 다시 필터 작업을 할 때 -having을 잘 쓰면 연산 비용을 줄일 수 있다. order by : 정렬 -asc 오름차순, desc 내림차순 -일차정렬, 이차정렬 가능 -정렬 후, limit으로 상위 몇개, 하위 몇개만 select하게

 

작성 순서 1) from 2) select * 3) where 4) group by -> select 앞에 붙이기 (암묵적인 국룰 !) 5) having 6) select 7) order by 8) limit

 

-숫자, 문자열 함수

concat : 문자열 붙이기 (||과 같음) replace instr : 문자열 위치 ifnull : cast : 형변환 date, datetime : 날짜 타입

-날짜 함수

date(), datetime() : 날짜, 시간 extract() format_date() date_diff(나중날짜, 이전날짜, 비교단위) date_sub

-조건분기

case when then else end -> 연도별, 분기별 가입자수 조회할 때 if

-join

두 개의 테이블을 합치는 것 join vs left join : join은 교집합, left join은 왼쪽 테이블 다 나오고 key가 겹치는 오른쪽 테이블의 필드와 레코드를 불러온다. cross join left join 2개 이상 붙이기 ERD : 각 테이블 구성요소와 테이블끼리 연관 관계(1대 N, N대 1, N대 N) 를 표시해줌 -user와 product간에 중계 테이블이 order테이블 -user와 event(방문 로그) : 1대 N

 

 

#cross join (join과 on을 사용하지 않음)
#왼쪽 오른쪽 테이블 다 붙인 다음에, id가 같지 않은 레코드는 다 날려서 join문과 같은 효과를 냄
select *
from user, order
where user.id = order.user_id

 

-union : 위 아래로 합치기

-with / sub query

서브쿼리 : 다른 sql문 안에 중첩된 select문이다. (join 대신에) -가상의 테이블을 만들어서 from절에서 서브쿼리 쓸 수 있다. -from에서 사용한 필드를 select절 안에서 서브쿼리로 활용할 수 있다 (user 10만, order 10만 레코드가 있으면... 10만 * 10만의 연산을 하게 됨 -> index최적화를 해서 연산 줄이기) with __ as ( )

-rollup : 소계를 구함 (group by안에서 사용)

cf) grouping 함수 null값 확인할 때 사용

 

-window 함수

현재 행과 관련이 있는 한 행마다 따로 계산을 해주는 함수 그룹 내 순위 관련 : rank, dense_rank, row_number -rank() over (order by age) as A 그룹 내 집계 관련 : sum, max, min, avg, count -주문 내역 카테고리마다 집계를 표시하도록 그룹 내 행 순서 : lag, lead, first_value, last_value, nth_value ntile(n) row / range(row말고 값으로 봐야할 때 사용...?)

 

• 매출 분석 쿼리

https://paullabworkspace.notion.site/30-c548878b0c0444b7b647b87110009f56

30강 - 실무 데이터 분석(매출 분석) | Notion

 

#2022년 전체 주문건, 총 매출, 평균 매출
select 
  count(id) as total_order_count,
  sum(sale_price) as total_sale_price,
  avg(sale_price) as avg_sale_price
from `thelook_ecommerce.order_items`
where extract(year from created_at) = 2022

#2022년 월별 주문건, 총 매출, 평균 매출
select 
  extract(month from created_at) as month,
  round(count(id), 2) as total_order_count,
  round(sum(sale_price),2) as total_sale_price,
  round(avg(sale_price),2) as avg_sale_price
from `thelook_ecommerce.order_items`
where extract(year from created_at) = 2022
group by month
order by month

#2022연도까지의 모든연도의 주문문건수, 총 판매금액, 평균 판매금액을 조회
select 
  extract(month from created_at) as month,
  round(count(id), 2) as total_order_count,
  round(sum(sale_price),2) as total_sale_price,
  round(avg(sale_price),2) as avg_sale_price
from `thelook_ecommerce.order_items`
where extract(year from created_at) <= 2022
group by month
order by month

#2022년도와 2022년도 이하 모든연도의 월별 주문건수, 판매금액합계, 평균판매금액
with month_2022 as (
  select 
    extract(month from created_at) as month,
    round(count(id), 2) as total_order_count,
    round(sum(sale_price),2) as total_sale_price,
    round(avg(sale_price),2) as avg_sale_price
  from `thelook_ecommerce.order_items`
  where extract(year from created_at) = 2022
  group by month
  order by month
), month_all as (
  select 
    extract(month from created_at) as month,
    round(count(id), 2) as total_order_count,
    round(sum(sale_price),2) as total_sale_price,
    round(avg(sale_price),2) as avg_sale_price
  from `thelook_ecommerce.order_items`
  where extract(year from created_at) <= 2022
  group by month
  order by month
)
select 
  t1.month,
  t1.total_order_count as order_count_2022,
  t2.total_order_count as order_count_total,
  t1.total_sale_price as sum_sale_price_2022,
  t2.total_sale_price as sum_sale_price_total,
  t1.avg_sale_price as avg_sale_price_2022,
  t2.avg_sale_price as avg_sale_price_total
from month_2022 t1 
join month_all t2 on t1.month = t2.month
order by month

 

#2022년 분기별(계절별) 주문건, 총 매출, 평균 매출
select 
  extract(quarter from created_at) as quarter,
  round(count(id), 2) as total_order_count,
  round(sum(sale_price),2) as total_sale_price,
  round(avg(sale_price),2) as avg_sale_price
from `thelook_ecommerce.order_items`
where extract(year from created_at) = 2022
group by quarter
order by quarter

#2022년도 이하 모든 연도의 분기별(계절별) 주문건, 총 매출, 평균 매출
select 
  extract(quarter from created_at) as quarter,
  round(count(id), 2) as total_order_count,
  round(sum(sale_price),2) as total_sale_price,
  round(avg(sale_price),2) as avg_sale_price
from `thelook_ecommerce.order_items`
where extract(year from created_at) <= 2022
group by quarter
order by quarter

#2022년도와 2022년도 이하 모든 연도의 분기별 주문건수, 판매금액합계, 평균판매금액
with quarter_2022 as (
  select 
    extract(quarter from created_at) as quarter,
    round(count(id), 2) as total_order_count,
    round(sum(sale_price),2) as total_sale_price,
    round(avg(sale_price),2) as avg_sale_price
  from `thelook_ecommerce.order_items`
  where extract(year from created_at) = 2022
  group by quarter
  order by quarter
), quarter_all as (
  select 
    extract(quarter from created_at) as quarter,
    round(count(id), 2) as total_order_count,
    round(sum(sale_price),2) as total_sale_price,
    round(avg(sale_price),2) as avg_sale_price
  from `thelook_ecommerce.order_items`
  where extract(year from created_at) <= 2022
  group by quarter
  order by quarter
)
select 
  t1.quarter,
  t1.total_order_count as order_count_2022,
  t2.total_order_count as order_count_total,
  t1.total_sale_price as sum_sale_price_2022,
  t2.total_sale_price as sum_sale_price_total,
  t1.avg_sale_price as avg_sale_price_2022,
  t2.avg_sale_price as avg_sale_price_total
from quarter_2022 t1 
join quarter_all t2 on t1.quarter = t2.quarter
order by quarter

 

• 실행계획 기능