[AI School-TIL] SQL(BigQuery) 6일차_23.02.03

 

[데이터 집계하기 2]

• rollup : 전체 집계를 구한 다음에, 각 단계에 따른 소계를 해주는 기능

select
  country,
  count(id) as count_user
from `thelook_ecommerce.users`
group by rollup(country);

 

 

#연습문제 11-1
#회원(users) 테이블에서 연령대 별 성별의 소계 및 합계를 조회
#일의 자리를 버림 (trunc는 내림)
select 
  trunc(age, -1) || '대' as age_group,
  gender,
  count(id) as user_count
from `thelook_ecommerce.users`
group by rollup(age_group, gender)
order by age_group, gender

 

• window 함수란?

- 일단 함수이다. (input -> 처리 -> output)

-현재 행과 관련있는 테이블 행들에 대해 계산을 수행함

-행 그룹의 값을 계산하고 각 행마다 하나의 결과를 반환함

-계산을 수행하는 범위를 window라고 함 (첫번째부터 현재 행까지가 계산의 범위)

대표사진 삭제

사진 설명을 입력하세요.

cf) 다른 집계 함수는 범위를 정하려면 반드시 group by를 해야 함.

 

함수

OVER (

[ PARTITION BY 컬럼 ]

[ ORDER BY 컬럼 ]

[ WINDOWING 절 ]

)

select
     id,
     value,
     sum(value) over(order by id) as total
from `weniv.value_list`

 

복사를 떠서 하나 만들어 놓고, id로 정렬을 시키고,

현재 행을 기준으로 해서 그 순간마다의 집계를 진행한다.

 

• rank : 순위를 매길 때 사용

select
     id,
     first_name,
     last_name,
     country,
     age,
     rank() over(order by age) as rank
from `thelook_ecommerce.users`
where id between 1 and 20
order by age

#국가별로 쪼개서 집계하려면 partition by 사용
select
     id,
     first_name,
     last_name,
     country,
     age,
     rank() over(partition by country order by age) as rank
from `thelook_ecommerce.users`
where id between 1 and 20
order by age

 

• dense_rank() : 동일 순위가 있을 때, 다음 순위는 건너뛰지 않고 순차로 번호를 부여함.

select 
     id,
     first_name,
     last_name,
     country,
     age,
     dense_rank() over(partition by country order by age) as rank
from `thelook_ecommerce.users`
where id between 1 and 20
order by country, age

 

• row_number() : 일련 번호로, 1부터 하나씩 증가하는 번호를 부여

select 
     id,
     first_name,
     last_name,
     country,
     age,
     row_number() over(order by age) as row_number
from `thelook_ecommerce.users`
where id between 1 and 20

 

• 연습문제

#연습문제 11-2
#상품정보(products) 테이블에서 각 브랜드 내에서 상품 가격별 순위(brand_rank)를 조회하시오.
#-조회 항목 : id, 브랜드(brand), 상품가격(cost), 각 브랜드 내 상품 가격이 높은 순위(brand_rank)
#-정렬 조건 : 브랜드 오름차순, 상품가격(cost) 내림차순
#-RANK 함수를 이용해서
select 
     id,
     brand,
     cost,
     rank() over(partition by brand order by cost desc) as rank
from `thelook_ecommerce.products`
order by brand, rank

#연습문제 11-3
select 
  refresh_date,
  combined_key,
  deaths,
  dense_rank() over(order by deaths desc) as deaths_rank
from `dataflix_covid_dataset.world_covid`
where refresh_date = '2020-06-01'
order by deaths desc

 

• 탐색 함수 (바로 전과 비교가 필요할 때 사용)
• lag : 이전 행 필드를 읽음
• lead : 다음 행 필드를 읽음

select 
  id,
  first_name,
  last_name,
  country,
  lag(id) over(
    partition by country 
    order by id
  ) as id_prev,
from `thelook_ecommerce.users`
where id between 1 and 20
order by id

 

#이벤트 페이지에 다시 접속한 시간 구하기
select 
  id,
  user_id,
  created_at,
  lag(created_at) over(
    order by created_at
  ) as created_at_prev,
  datetime_diff(datetime(created_at), datetime(lag(created_at) over(order by created_at)), second) as prev_visit_second
from `thelook_ecommerce.events`
where user_id = 5781
order by created_at

 

• FIRST_VALUE, LAST_VALUE : FIRST_VALUE는 그룹 내 첫번째 값을 구하고, LAST_VALUE는 마지막 값을 구한다. (전체 그룹에 대한 마지막 값을 구하려면 ROWS 옵션을 주어야 함)

select 
  id,
  email,
  created_at,
  first_value(id) over( order by id ) as first_id,
  last_value(id) over( 
    order by id 
    rows between UNBOUNDED PRECEDING and CURRENT ROW
  ) as last_id,
  last_Value(id) over(
    order by id
    rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING
  ) as last_id_unbounded
from `thelook_ecommerce.users`
where id between 1 and 10
order by id

 

• ROW, UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING
• NTH_VALUE

 

Q. window의 장점은 ?

한 필드에 계산을 할 때, 어떤 기준으로 할지를 필드 안에서 결정을 할 수 있어서 group by보다 편리함.

각각 다른 기준의 값들을 매 행마다 뽑아낼 수 있다.

각각 집계해서 with로 선언했던 것보다 편리하다.

# 각 제품 브랜드별 
select 
  id,
  name,
  brand,
  category,
  retail_price,
  NTH_VALUE(id, 1) OVER (PARTITION BY brand ORDER BY retail_price desc) as first_expensive_price_product,
  NTH_VALUE(id, 2) OVER (PARTITION BY brand ORDER BY cost desc) as first_expensive_cost_product,
  NTH_VALUE(id, 3) OVER (PARTITION BY brand ORDER BY id desc) as last_registered_product
from `thelook_ecommerce.products`
where brand IS NOT NULL

 

 

• PERCENT_RANK()
• CUME_DIST() - 누적분포 :n보다 값이 작은 행의 갯수

내용을 입력하세요.

 

• NTILE() : 나눠서 넣기 (어떤 순서로 나눠서 넣을지도 order by로 바로 결정해서 넣을 수 있다)

-예를 들어, 고객 매출순으로 5등급으로 나누겠다.

select
  id,
  first_name,
  last_name,
  country,
  age,
  ntile(4) over(order by age)
from `thelook_ecommerce.users`
where id between 1 and 20
order by age

 

• sum은 over()안에 아무것도 안 넣어도 작동이 된다.
• min, max : 경계에 있는 가격대를 알고 싶을 때 사용... 첫 번째 구간의 min이나 두 번째 구간의 max를 구해서...
• 범위 정하기...

ex) 날짜 기준으로 최근 3일만 조회하고 싶다.

1

2

3

4

5

current row, following

preceding, current row

 

• 데이터 수정 (insert, delete, update)
• insert into : 테이블에 새 레코드를 삽입할 때 사용한다.

-필드명을 넣지 않으면, 필드 순서대로 모든 데이터를 맞춰줘야 한다.

INSERT INTO weniv_product VALUES (12, 'ballpen', 500);

 

• update : 조건에 맞는 기존 레코드를 수정할 수 있다.

where 조건문 안 넣으면 모든 필드가 수정된다 ㅠㅠ 주의하자 !!!

-> 실수를 방지하기 위해서 장치를 많이 둔다...

(복구할 수 있는 장치 또는 빠르게 인지할 수 있는 장치를 만들어 놓음)

개발DB, 운영DB로 나뉘어져 있는데,

보통 운영DB가 중요함 !! (쿼리창 컬러 세팅 다르게 하기)

UPDATE weniv_product 
SET cost = 210000 
WHERE id = 1;

 

여러 레코드를 업데이트할 수 있다.

UPDATE weniv_product 
SET cost = cost + 500 
WHERE cost < 1000;

 

• delete : 기존 레코드를 삭제합니다.

실무에서는 권한이 안 주어지는 경우가 많고, 웬만하면 정말 신중하게 사용

추천방법 : select *로 나온 거에 선택을 해서, 마우스 오른쪽 눌러서 delete Row를 클릭해서 쓰기

(실수를 방지하기 위해서)

DBMS에서 where문 없는 delete는 거부하는 경우가 있음 (where에 1-1를 넣어서 처리 가능...)

delete from weniv_product
where id = 11

 

여러 레코드 삭제

delete from weniv_product
where id > 5

 

• 테이블 생성 및 수정
• create_database : 데이터베이스를 생성할 때 사용 (생성 권한이 있어야 가능)

캐릭터셋을 잘 지정해서 만드는 게 중요 (utf-8을 기본으로 하는 것을 추천하나, utf8mb4를 추천 (이모지까지 있음))

 

• create_table

긴 텍스트는 text형으로 지정하는 게 좋음.

 

• alter table : 컬럼 관련
• alter table 테이블명 add ___ : 컬럼명 추가하기
• alter table 테이블명 rename column ___ to ___ : 컬럼명 수정하기

 

• drop database
• drop table

 

 

• 실무 데이터 분석 예시

extract(dayofweek from created_at)

format_date('%a', created_at) 으로 하면 요일명으로 나옴