[AI School-TIL] SQL(BigQuery) 2일차_23.01.20

[지난 시간 복습]

• group by

  # 국가별로 몇명인지
  select country, count(id)
  from `thelook_ecommerce.users`
  group by country;
  
  # 국가별, 도시별 몇명인지
  
  select country, city, count(id)  
  from `thelook_ecommerce.users`  
  group by country, city;

[오늘 진도]

#연습문제 4-1
#회원(users) 테이블에서 전체 유저의 평균연령을 조회하세요.
#2)무엇을 가져오는지
select avg(age) as avg_age
#1) 어디에서
from `thelook_ecommerce.users`;

#연습문제 4-2
#회원(users) 테이블에서 여성 유저의 평균연령을 조회하세요.
select avg(age)
from `thelook_ecommerce.users`
where gender = 'F';

### SQL 연습문제 4-3
#회원(users) 테이블에서 국가별 가입자수를 조회하세요.
select country, count(id) as country_user_count
from `thelook_ecommerce.users`
group by country;
#회원(users) 테이블에서 국가별 가입자수를 조회하세요. (오류가 나는 경우)
select country, name, count(id) as country_user_count
from `thelook_ecommerce.users`
group by country;

#그 단위로 한번 더 묶음해줘야 함
select country, name, count(id) as country_user_count
from `thelook_ecommerce.users`
group by country, name;

#그 단위로 한번 더 묶음해줘야 함
select country, city, count(id) as country_user_count
from `thelook_ecommerce.users`
group by country, city;
### SQL 연습문제 4-5
#회원(users) 테이블에서 
#가입기간(created_at)이 2020년도 1월인 유저의 
#국가별 가입자 수 (country_user_count)를 조회하세요.
#2020-01-01 00:00:00 ~ 2020-01-31 23:59:59
select 
  country,
  count(id) as country_user_count
from `thelook_ecommerce.users`
where created_at between '2022-01-01' and '2022-02-01'
group by country;

having : 최종 집계가 나온 데이터에서 필터를 거는 명령어

select
  country,
  count(id) as user_count
from `thelook_ecommerce.users`
group by country
having user_count >= 4000;

order by : 최종 집계가 나온 데이터에서 필터를 거는 명령어

#기본 오름차순 (asc)
select *
from `thelook_ecommerce.users`
order by id;

#국가, 최근 가입한날 순서로
select *
from `thelook_ecommerce.users`
order by country, created_at desc;

#나이 가장 어린 3명만 추리기
select *
from `thelook_ecommerce.users`
order by age asc
limit 3;

#국가별 가입자수 
#가입자수 많은 국가 2개만 추려
select country,
  count(id) as user_count
from `thelook_ecommerce.users`
group by country
order by user_count desc
limit 2;

#가입자수 많은 city는?
select country 
  city,
  count(id) as user_count
from `thelook_ecommerce.users`
group by country, city
order by user_count desc
limit 3;

• 생각하는 순서
  1) select
  2) from
  3) where
  4) group by
  5) having
  6) oder by
  7) limit

# 국가별 20세 이하 유저수가
# 500명 이상인 유저수 국가 TOP5를 조회

select country, count(id) as user_count
from `thelook_ecommerce.users`
where age <= 20
group by country
having user_count >= 500
order by user_count desc
limit 5;
#프로그래머스 문제 5-1
#어디에서: ANIMAL_INS
#조건 : INTAKE_CONDITION이 Aged가 아님
#조회 항목 : ANIMAL_ID, NAME 
SELECT ANIMAL_ID, NAME
from ANIMAL_INS
where INTAKE_CONDITION != "Aged"
order by ANIMAL_ID;

#프로그래머스 문제 5-2
#조건 :INTAKE_CONDITION이 Sick임
SELECT ANIMAL_ID, NAME
from ANIMAL_INS
where INTAKE_CONDITION = "Sick"
order by ANIMAL_ID; 

#프로그래머스 문제 5-3
#조건 : 이름이 있는 동물
SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
ORDER BY ANIMAL_ID;

#프로그래머스 문제 5-4
#조건 : 이름이 없는 동물
SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NULL
ORDER BY ANIMAL_ID;

#프로그래머스 문제 5-5
SELECT FLAVOR
FROM FIRST_HALF
ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID ASC;

#프로그래머스 문제 5-6
SELECT NMAE
FROM ANIMAL_INS
ORDER BY DATETIME
LIMIT 1;

#프로그래머스 문제 5-7
#정렬조건 : NAME, 2차 정렬조건 : DATETIME
SELECT ANIMAL_ID,NAME,DATETIME
FROM ANIMAL_INS
ORDER BY NAME, DATETIME DESC;

#프로그래머스 문제 5-8
#정렬조건 : ANIMAL_ID
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;

#프로그래머스 문제 5-9
#동물 보호소에 들어온 모든 동물의 이름과 보호 시작일을 조회
#정렬 : ANIMAL_ID 역순
SELECT NAME, DATETIME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID DESC;

#프로그래머스 문제 5-10
#동물 보호소에 들어온 모든 동물의 정보를 ANIMAL_ID순으로 조회
SELECT *
FROM ANIMAL_INS
ORDER BY ANIMAL_ID
#LEVEL_2
#프로그래머스 문제 5-11
# 가격이 제일 비싼 식품의 식품 ID, 식품 이름, 식품 코드, 식품분류, 식품 가격을 조회
SELECT PRODUCT_ID,
  PRODUCT_NAME, 
  PRODUCT_CD, 
  CATEGORY, 
  PRICE
FROM FOOD_PRODUCT
ORDER BY PRICE DESC
LIMIT 1;

#프로그래머스 문제 5-12
#1)어디서 
#2)필터 조건 : 이름에 EL이 들어간다. 소문자 lower()
#3)정렬 조건 : 이름 오름차순
#4)출력 항목 
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE NAME LIKE '%EL%'
AND ANIMAL_TYPE = 'Dog'
ORDER BY NAME;

#프로그래머스 문제 5-13
#고양이와 개 각각 몇마리 인지
#고양이가 먼저 정렬되도록
SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE) AS count
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE;

#프로그래머스 문제 5-14
#상품 카테고리 코드(PRODUCT_CODE 앞 2자리) 별 상품 개수를 출력
#결과는 상품 카테고리 코드를 기준으로

SELECT LEFT(PRODECT_CODE, 2) AS CATEGORY,
  COUNT(PRODUCT_ID) AS PRODUCTS
FROM PRODUCT
GROUP BY CATEGORY #상단에 카테고리 필드 네이밍을 해줬으니 
ORDER BY CATEGORY;

#프로그래머스 문제 5-15
#만원 단위의 가격대 별로 상품 개수를 출력
#각 구간의 최소금액으로 표시

SELECT LEFT(PRICE / 10000, 1) * 10000 AS PRICE_GROUP, 
#PRICE - PRICE % 10000 AS PRICE_GROUP
  COUNT(PRODUCT_ID) AS PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP;

#프로그래머스 문제 5-16
#이름 두 번 이상 쓰인 이름과 해당 이름이 쓰인 횟수를 조회
#이름이 없는 동물은 집계에서 제외, 결과는 이름 순으로 정렬

SELECT NAME, COUNT(NAME) AS COUNT
FROM ANIMAL_INS
WHERE NAME IS NOT NULL OR NAME != ''
GROUP BY NAME
HAVING COUNT >= 2
ORDER BY NAME;

#프로그래머스 문제 5-17
#동일한 회원이 동일한 상품을 재구매한 데이터를 구하여, 재구매한 회원 ID와 재구매한 상품 ID를 출력
#1차 정렬 : 회원 ID를 기준으로 오름차순 정렬
#2차 정렬 : 상품 ID를 기준으로 내림차순
SELECT USER_ID, PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(ONLINE_SALE_ID) >= 2
ORDER BY USER_ID, PRODUCT_ID DESC
#연습문제 5 (24문제)
#연습문제 5-1
#회원 테이블(users)에서 국가별 유저수를 조회
#조회 항목 : country, user_count
#조건 : 국가의 유저수가 3000명 이상인 국가
#정렬 : 국가별 유저수 많은 순서로 정렬

SELECT COUNTRY, COUNT(ID) AS USER_COUNT
FROM USERS
GROUP BY COUNTRY
HAVING USER_COUNT >= 3000
ORDER BY USER_COUNT

#연습문제 5-2
select *
from `thelook_ecommerce.products`
where category = 'Sweaters'
and department = 'Women'
order by retail_price
limit 5

#연습문제 5-3
#상품정보 테이블(products)에서 조회
select brand, avg(retail_price) as retail_price_avg
from `thelook_ecommerce.products`
where category = 'Sweaters'
and department = 'Women'
group by brand
having retail_price_avg <= 100
order by retail_price_avg;

#연습문제 5-4
select id, 
  name, 
  category, 
  brand, 
  cost, 
  retail_price, 
  retail_price-cost as profit, 
  (retail_price - cost) / retail_price * 100 as profit_rate 
from `thelook_ecommerce.products`;

#연습문제 5-4
#Swim 카테고리 제품의 각 브랜드별 최소 이익률, 최대 이익률, 평균 이익률을 조회. 
select brand, 
  min((retail_price - cost) / retail_price * 100) as min_profit_rate, 
  max((retail_price - cost) / retail_price * 100) as max_profit_rate, 
  avg((retail_price - cost) / retail_price * 100) as avg_profit_rate
from `thelook_ecommerce.products`
where category = 'Swim'
group by brand;

#연습문제 5-5
#평균 이익률이 높은 TOP5 브랜드와 평균 이익률을 조회
select brand, 
  avg((retail_price - cost) / retail_price * 100) as avg_profit_rate
from `thelook_ecommerce.products`
where category = 'Swim'
group by brand
order by avg_profit_rate desc
limit 5;

#추가 문제 1
#주문(order)에서 여자면서 order_id와 user_id 같은 사람을 찾으세요

select *
from `thelook_ecommerce.orders`
where gender = 'F'
and order_id = user_id

#추가 문제 2
#events 테이블에서 user_id가 존재하고, browser가 Chrome인 조건에 부합하는 인원의 도시와 수를 조회
#정렬 : 인원수 기준 내림차순
select city, count(id) as cnt
from `thelook_ecommerce.events`
where user_id IS NOT NULL
and browser = 'Chrome'
group by city
order by cnt desc