[AI School-TIL] SQL(BigQuery) 3일차_23.01.25

[오늘 진도]

소수점 처리

• round : 반올림
• trunc : 버림
• mod : 나머지
• power : 제곱
• sqrt : 제곱근

select round(100.56789,3)

select trunc(178.56789,-2)

select mod(10,3)

select power(10,3)

select sqrt(10)

 

문자열 함수

• substr(문자열, 시작 위치, 길이) : 문자열 일부만 가져오기

-길이를 빼면 시작 위치부터 끝까지 다 출력됨

• left(문자열, 길이)
• right(문자열, 길이)
• concat : 문자열 합치기 ( '||'를 사용해서도 결합 가능)
• lower : 소문자로 변환
• upper : 대문자 변환
• initcap : 첫번째 문자만 대문자로 변환
• replace : 원하는 문자 찾아서 바꾸기
• length : 문자 길이
• instr : 특정 문자가 있는 위치 찾기

select substr('hello world', 2, 3)
select substr('hello world', 3)

select left('064-000-0000', 3)
select right('064-000-1234', 8)

select concat('abc', '123', 'ooo')
select 'abc' || '123' || '000'

select lower('AbcAbc')
upper('AbcAbc')
initcap
replace
length
instr

 

NULL값 관련

• IFNULL(null, '이름없음') : null값에 일괄적으로 입력해줌

 

형변환

• cast ('data' as 타입명)

(1) 문자 -> 숫자

(2) 문자 -> 자연수(INTEGER)

(3) 문자 -> FLOAT

(4) INTEGER -> 문자

(5) FLOAT -> 문자

(6) True, False -> 문자

(7) 날짜*

* DATE, DATETIME, TIMESTAMP

select CAST('123' as INT64) + CAST('123' as INT64)
select CAST('123.123' AS FLOAT64)
select CAST('123.123' AS NUMERIC) 

select CAST(123 AS STRING)
select CAST(123.123 AS STRING)
select CAST(true AS STRING)
select CAST(false AS STRING)
select CAST(NULL AS STRING) #null은 string으로 변환 안 됨

select DATE('2011-12-01 11:12:34')
select DATETIME('2011-12-01 11:12:34')

SELECT DATE(2023,1,1);
SELECT DATE('2023-1-1');
SELECT DATE('2023-1-1', 'Asia/Seoul');

 

• current_date() : 현재 시간 (DB마다 다름...)

집계

ex) 연도별, 월별, 일별, 시간별, 요일별, 국가별 ...

• EXTRACT : 추출하기 (group by 할 때 많이 쓰임)

select
  id,
  EXTRACT(YEAR FROM DATE(created_at)) as year,
  EXTRACT(MONTH FROM DATE(created_at)) as month,
  EXTRACT(DAY FROM DATE(created_at)) as day
from `thelook_ecommerce.users`
limit 10

select
  id,
  EXTRACT(YEAR FROM DATE(created_at)) as year,
  EXTRACT(MONTH FROM DATE(created_at)) as month,
  EXTRACT(DAY FROM DATE(created_at)) as day,
  EXTRACT(HOUR FROM DATETIME(created_at)) as hour,
  EXTRACT(MINUTE FROM DATETIME(created_at)) as minute,
  EXTRACT(SECOND FROM DATETIME(created_at)) as second,
from `thelook_ecommerce.users`
limit 10

 

포맷에 맞게 문자열 출력

• format_date(포맷, 데이터값)
• format_datetime(포맷, 데이터값)

내용을 입력하세요.

 

날짜간 차이 계산

• date_diff

SELECT DATE_DIFF(CURRENT_DATE(), DATE '2023-1-1', DAY)

 

날짜에 더하기, 빼기

• date_add(date, interval)
• date_sub(date, interval)

내용을 입력하세요.

 

 

시간 차이 계산

시간 더하기, 빼기

• datetime_add
• datetime_sub

### SQL 연습문제 6
### SQL 연습문제 6-1
# 상품정보(products) 테이블에서 상품의 id, 상품명(name), 판매가격(retail_price)를 조회
# 판매가격은 소수점 2자리에서 반올림
select
  id,
  name,
  round(retail_price, 2) as retail_price
from `thelook_ecommerce.products`

### SQL 연습문제 6-2
# 회원(users) 테이블에서 나이가 홀수인 유저만 조회하세요.
# 조회 항목은 id, first_name, last_name, age 
select 
  id,
  first_name,
  last_name,
  age
from `thelook_ecommerce.users`
where mod(age, 2) = 1

### SQL 연습문제 6-3
# 회원(users) 테이블에서 전체이름(full_name)을 조회하세요.
# 성(first_name)과 이름(last_name)을 합쳐서 조회합니다.
# 이름(last_name)은 모두 대문자로 표시
select first_name,
  last_name,
  upper(last_name),
  concat(first_name, ', ', upper(last_name)) as full_name,
from `thelook_ecommerce.users`

select first_name,
  last_name,
  upper(last_name),
  first_name || ', ' || upper(last_name) as full_name,
from `thelook_ecommerce.users`

### SQL 연습문제 6-4
# 회원(users) 테이블에서 회원아이디(id), 이메일(email), 가입연도(signup_year)을 조회하세요.
select 
  id,
  email,
  extract(year from date(created_at)) as signup_year
from `thelook_ecommerce.users`

### SQL 연습문제 6-5
#회원(users) 테이블에서 2020년 7월 1일 부터 2020년 7월 10일까지 가입한 회원정보를 조회하세요.
#가입일시는 created_at 입니다.
#가입일(DATE 타입)을 만든후 비교해보세요.
select id, created_at, DATE(created_at)
from `thelook_ecommerce.users`
where DATE(created_at) >= '2020-07-01' and DATE(created_at) <= '2020-07-10'
order by created_at DESC

### SQL 연습문제 6-6
#주문정보(orders) 테이블에서 현재로부터 1년전에서 오늘까지 주문한 데이터를 조회하세요.
#- 현재일시 : current_datetime()
#- 주문일시 : created_at
select *
from `thelook_ecommerce.orders`
where DATE(created_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
order by created_at;

### SQL 연습문제 6-7
#회원(users) 테이블에서 가입연도(signup_year), 연도별 가입자(user_count)를 조회하세요.
select
  extract(YEAR from date(created_at)) as signup_year,
  count(id) as user_count
from `thelook_ecommerce.users`
group by signup_year
order by signup_year

### SQL 연습문제 6-9
#특정 연도의 월별 주문건수
#주문정보(orders) 테이블에서 2020년도의 월별 주문건수를 조회하세요.
#조회항목은 연도, 월, 주문건수 입니다.select 
select 
  extract(year from created_at) as year,
  extract(month from created_at) as month,
  count(order_id) as order_count
from `thelook_ecommerce.orders`
where extract(year from created_at) = 2022
group by year, month
order by month

### SQL 연습문제 6-10
#회원(users) 테이블에서 다음 내역을 조회하세요.
#- id
#- 이름(first_name)
#- 이름의 길이(name_length)
#- 이름(first_name)의 앞 3글자(part_name)
#- 이름의 앞 3글자를 별표 처리한 이름(name_with_asterisk)
select
  id,
  first_name,
  length(first_name),
  left(first_name, 3) as part_name,
  replace(first_name, left(first_name, 3), '***') as name_with_asterisk,
  '***' || substr(first_name, 4) as name_with_asterisk2,
  concat('***', substr(first_name, 4)) as name_with_asterisk3,
from `thelook_ecommerce.users`

### SQL 연습문제 6-11
#회원(users) 테이블에서 전체 유저의 가입연도별 데이터를 조회하려고 합니다.
#가입연도(signup_year), 연도별 가입자수(user_count), 최고나이(max_age), 최저나이(min_age), 평균나이(avg_age)를 조회하세요.
#평균나이는 소수점 둘째자리까지 표시해주세요. 이하 소수점은 반올림처리하여 표시해주세요.
#정렬 순서는 가입연도 순(signup_year)입니다.
select
  extract(year from created_at) as signup_year,
  count(id) as user_count,
  max(age) as max_age,
  min(age) as min_age,
  round(avg(age), 2) as avg_age
from `thelook_ecommerce.users`
group by signup_year
order by signup_year

### SQL 연습문제 6-12
#회원(users) 테이블에서 브라질 여성유저의 시간대별 유저 가입자수를 조회하세요.
#조회 항목
#- 시간대(hour)
#- 가입자수(user_count)
select 
  extract(hour from created_at) as hour,
  count(id) as user_count
from `thelook_ecommerce.users`
where country = 'Brasil'
and gender = 'F'
group by hour
order by hour;

### SQL 연습문제 6-13
#회원(users) 테이블에서 남성유저의 가입연도별 국가별 데이터를 조회하려고 합니다.
#조회 항목은 다음과 같습니다.
#- 가입 연도(signup_year)
#- 국가명(country)
#- 가입자수(user_count)
#- 최고나이(max_age)
#- 최저나이(min_age)
#- 평균나이(avg_age) - - 소수점 2자리 반올림
#정렬순서는 가입연도 내림차순, 가입자수 내림차순 입니다.
#그룹핑 결과에서 가입자수가 100명 이상인 데이터만 표시해주세요. 
select 
  extract(year from created_at) as signup_year,
  country,
  count(id) as user_count,
  max(age) as max_age,
  min(age) as min_age,
  round(avg(age),2) as avg_age
from `thelook_ecommerce.users`
where gender = 'M'
group by signup_year, country 
having user_count >= 100
order by signup_year desc, user_count desc

 

#프로그래머스 문제
### 프로그래머스 문제 6-1 (level 1)
#12세 이하인 여자 환자 목록 출력하기
SELECT 
    PT_NAME,
    PT_NO,
    GEND_CD,
    AGE,
    IFNULL(TLNO, 'NONE') AS TLNO
FROM PATIENT
WHERE AGE <= 12
AND GEND_CD = 'W'
ORDER BY AGE DESC, PT_NAME ASC

### 프로그래머스 문제 6-2 (level 1)
#경기도에 위치한 식품창고 목록 출력하기
SELECT 
    WAREHOUSE_ID, 
    WAREHOUSE_NAME,
    ADDRESS,
    IFNULL(FREEZER_YN, 'N') AS FREEZER_YN
FROM FOOD_WAREHOUSE
WHERE ADDRESS LIKE '경기도%'
ORDER BY WAREHOUSE_ID

### 프로그래머스 문제 6-3 (level 1)
#강원도에 위치한 생산공장 목록 출력하기
SELECT 
    FACTORY_ID,
    FACTORY_NAME,
    ADDRESS
FROM FOOD_FACTORY
WHERE LEFT(ADDRESS, 3) = '강원도'
ORDER BY FACTORY_ID

### 프로그래머스 문제 6-4 (level 1)
#조건에 맞는 도서 리스트 출력하기
SELECT 
    BOOK_ID,
    DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
FROM BOOK
WHERE EXTRACT(YEAR FROM PUBLISHED_DATE) = 2021
AND CATEGORY = '인문'
ORDER BY PUBLISHED_DATE

### 프로그래머스 문제 6-5 (level 1)
#흉부외과 또는 일반외과 의사 목록 출력하기
SELECT 
    DR_NAME,
    DR_ID,
    MCDP_CD,
    DATE_FORMAT(HIRE_YMD, '%Y-%m-%d') AS HIRE_YMD
FROM DOCTOR
WHERE MCDP_CD IN ('CS', 'GS')
ORDER BY HIRE_YMD DESC, DR_NAME

### 프로그래머스 문제 6-6 (level 1)
#조건에 맞는 회원수 구하기
SELECT COUNT(USER_ID) AS USERS
FROM USER_INFO
WHERE EXTRACT(YEAR FROM JOINED) = 2021
AND AGE BETWEEN 20 and 29;

#MySQL 한정
SELECT COUNT(USER_ID) AS USERS
FROM USER_INFO
WHERE YEAR(JOINED) = 2021
AND AGE BETWEEN 20 and 29;

### 프로그래머스 문제 6-7 (level 1)
#최댓값 구하기
SELECT MAX(DATETIME) AS "시간"
FROM ANIMAL_INS;

#다른 방법
SELECT DATETIME
FROM ANIMAL_INS
ORDER BY DATETIME DESC
LIMIT 1;

 

• MySQL 한정 날짜, 시간 추출 함수

- YEAR()

- MONTH()

- DAY()

- HOUR()

- MINUTE()

- SECOND()

### 프로그래머스 문제 6-8 (level 2)
#NULL 처리하기
SELECT ANIMAL_TYPE,
    IFNULL(NAME, 'No name') AS NAME,
    SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

### 프로그래머스 문제 6-9 (level 2)
#입양 시각 구하기(1)
SELECT 
    EXTRACT(HOUR FROM DATETIME) AS HOUR,
    COUNT(ANIMAL_ID) AS COUNT
FROM ANIMAL_OUTS
WHERE EXTRACT(HOUR FROM DATETIME) BETWEEN 9 AND 19
GROUP BY HOUR
ORDER BY HOUR

### 프로그래머스 문제 6-10 (level 2)
#DATETIME에서 DATE로 형 변환
SELECT 
   ANIMAL_ID,
   NAME,
   DATE_FORMAT(DATETIME, '%Y-%m-%d') AS 날짜
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

 

 

https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions

Date functions  |  BigQuery  |  Google Cloud

https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_elements_date_time

Format elements  |  BigQuery  |  Google Cloud

 

select
created_at,
EXTRACT(DAYOFWEEK FROM created_at) as DAYOFWEEK,
EXTRACT(DAY FROM created_at) as DAY,
EXTRACT(DAYOFYEAR FROM created_at) as DAYOFYEAR,
EXTRACT(WEEK FROM created_at) as WEEK,
EXTRACT(ISOWEEK FROM created_at) as ISOWEEK,
EXTRACT(MONTH FROM created_at) as MONTH,
EXTRACT(QUARTER FROM created_at) as QUARTER,
EXTRACT(YEAR FROM created_at) as YEAR,
EXTRACT(ISOYEAR FROM created_at) as ISOYEAR
from `thelook_ecommerce.users`
order by id