SQL Review (3rd. 날짜형 함수)

1.날짜함수 DATE()

1) 날짜 : DATE()

--bigquery에서는 current_date()로 현재 날짜를 얻을 수 있습니다.
SELECT CURRENT_DATE();


--일부 SQL에서는 현재 날짜를 반환합니다. bigquery에서는 error입니다.
SELECT DATE();

 숫자데이터로 가능

SELECT DATE(2023,1,1);

문자열 데이터로도 가능

SELECT DATE('2023-1-1');

timezone (원하는 지역의 시간) 설정 가능

--timezone을 설정할 수 있습니다.
SELECT DATE('2023-1-1', 'Asia/Seoul')

select date(created_at)
from `thelook_ecommerce.users`

 

2) 날짜 - 데이터 추출

SELECT EXTRACT(YEAR FROM DATE '2023-1-1');
# 연도 추출
SELECT EXTRACT(MONTH FROM DATE '2023-1-1');
# 월 추출
SELECT EXTRACT(DAY FROM DATE '2023-1-1');
# 일 추출
SELECT EXTRACT(DAY FROM CURRENT_DATE());
# 현재날짜에서 일 추출

select extract(year from date(created_at))
from `thelook_ecommerce.users`

 

2. 시간함수 DATETIME()

1) 시간 : DATETIME()

SELECT CURRENT_DATETIME()

DATETIME을 통해 시간 객체 생성 가능 . 일반적인 형식

1. DATETIME(year, month, day, hour, minute, second)
2. DATETIME(date_expression[, time_expression])
3. DATETIME(timestamp_expression [, time_zone])

#1번 형식

SELECT DATETIME(2023, 1, 25, 05, 30, 00)

#3번 형식

SELECT DATETIME(TIMESTAMP "2023-12-25 05:30:00+00", "Asia/Seoul")

 

2) 날짜 - 데이터 추출

SELECT EXTRACT(HOUR FROM CURRENT_DATETIME());
#현재 날짜에서 시간 추출

select extract(hour from created_at)
from `thelook_ecommerce.users`

 

 

3. 날짜 & 시간함수 FORMAT(형식 변경)

1) 날짜 FORMAT_DATE()

FORMAT_DATE(format_string, date_expr)

지정된 format_string에 따라 날짜(date_expr)를 변경. 모든 날짜 형식은 아래 링크에서 확인 가능

형식 요소  |  BigQuery  |  Google Cloud

형식 요소  |  BigQuery  |  Google Cloud

 

미국 방식

-- %x는 날짜를 MM/DD/YY 형식으로 표현
SELECT FORMAT_DATE('%x', DATE '2023-1-25') AS US_format;

 

우리나라 방식

SELECT FORMAT_DATE('%y-%m-%d', DATE '2023-1-25') AS KR_format;
# 이식은 그냥 외우자

SELECT FORMAT_DATE('%y-%m-%d', created_at) AS KR_format;
from `thelook_ecommerce.users`

 

 

 

 

2) 시간 FORMAT_DATETIME()

FORMAT_DATETIME(format_string, date_expr)

지정된 format_string에 따라 날짜(date_expr)를 변경. 모든 날짜 형식은 아래 링크에서 확인 가능

형식 요소  |  BigQuery  |  Google Cloud

형식 요소  |  BigQuery  |  Google Cloud

SELECT FORMAT_DATETIME('%y/%m/%d %T', DATETIME '2023-1-25 15:30:00') AS KR_format;

SELECT FORMAT_DATE('%y/%m/%d %T', created_at)
from `thelook_ecommerce.users`

 

4. 날짜 & 시간함수 두 날짜 간 계산 DATE_DIFF

DATE_DIFF(date_expression_a, date_expression_b, date_part)

• 두 날짜 간에(date_expression_a - date_expression_b) 지정된 date_part간격의 정수를 반환합니다.
• date_part는 YEAR, MONTH, DAY 등을 지정할 수 있습니다. 첫 번째 날짜가 두 번째 날짜보다 이전이면 음수로 출력

SELECT DATE_DIFF(DATE '2023-9-3', DATE '2023-1-1', DAY) AS days_diff;

• - 연산으로도 나옴 (day만 출력)

SELECT '2023-9-3'-'2020-1-1'

SELECT DATE_DIFF(delivered_at,created_at, day) # deliverea_at - created_at
from `thelook_ecommerce.orders`
where status='Complete'

 

 

 

5. 날짜 & 시간함수 ADD 와 SUB (시간 계산)

1) 날짜 DATE_ADD(SUB)() : 지정된 날짜 간격 더하기(빼기)

DATE_ADD(SUB)(date_expression, INTERVAL int64_expression date_part)

SELECT DATE_ADD(SUB)(DATE '2023-1-25', INTERVAL 5 DAY); # 일

SELECT DATE_ADD(SUB)(DATE '2023-1-25', INTERVAL 5 MONTH); # 월

SELECT DATE_ADD(SUB)(DATE '2023-1-25', INTERVAL 5 YEAR); # 년

 

SELECT DATE_ADD(SUB)(created_at, INTERVAL 5 DAY)
from `thelook_ecommerce.users`

모든 create_at 날짜에 5일씩 추가 ( 빼기)

 

 

2) 시간 DATETIME_ADD(SUB)() : 지정된 시간 간격 더하기(빼기)

 

형식은 DATE와 동일

SELECT DATETIME_ADD(SUB)(DATETIME "2023-12-25 15:30:00", INTERVAL 10 MINUTE); #10분

 

SELECT DATETIME_ADD(SUB)(created_at, INTERVAL 10 minute)
from `thelook_ecommerce.users`

모든 create_at 시간에 10분 추가 (빼기)

 

 

 

 

 

 

+ 자체 연습문제

• 6-6 번 문제

SQL 연습문제 6-6
주문정보(orders) 테이블에서 현재로부터 1년전에서 오늘까지 주문한 데이터를 조회하세요.

- 현재일시 : current_datetime()
- 주문일시 : created_at

select *
from `thelook_ecommerce.orders`
where datetime(created_at) <= current_datetime() and
datetime(created_at) >= datetime_sub(current_datetime(), INTERVAL 1 year)

 

• 6-7 번 문제

SQL 연습문제 6-7
-- 회원(users) 테이블에서 가입연도(signup_year), 연도별 가입자(user_count)를 조회하세요.

가입연도 지정하는건 성공했는데 group by를 뭘로 해줘야하는지 조금 헷갈렸다..

첫줄에 가입연도(signuo_year)지정해주었으니 , 

연도별로 그룹 화 해주고( group by signup_year) , 연도별 유저수는 집계함수로(count(id))

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

 

• 6-9번 문제

SQL 연습문제 6-9
-- 특정 연도의 월별 주문건수
-- 주문정보(orders) 테이블에서 2020년도의 월별 주문건수를 조회하세요.
-- 조회항목은 연도, 월, 주문건수 입니다.

# created_at을 문자열로 취급 후 like 함수 사용
select extract(year from date(created_at)) as year,
extract(month from date(created_at)) as month,
count(order_id) as order_count
from `thelook_ecommerce.orders`
where string(created_at) like '2020%'
group by year, month
order by month;

or (Having 사용 . 개인적으로 이게 제일 깔끔하고 직관적인듯)

select extract(year from date(created_at)) as year,
extract(month from date(created_at)) as month,
count(order_id) as order_count
from `thelook_ecommerce.orders`
group by year, month
having year = 2020
order by month;

or (이번에 배운 extract 함수 이용)

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) = 2020
group by year, month
order by month;


#my sql 한정으로 가능한 함수
year(date)
month(date)
day(date)
hour(date)
minute(date)
second(date)

 

• 6-10번 문제

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 사용 - 내가 한 방법
  replace(first_name, left(first_name, 3), '***') as name_with_asterisk,
  # ||결합연산자 사용
  '***' || substr(first_name, 4) as name_with_asterisk2,
  # concat 사용
  concat('***', substr(first_name, 4)) as name_with_asterisk3,
from `thelook_ecommerce.users`

 

• 6-14번 문제

SQL 연습문제 6-14
--- 다음은 회원(users) 테이블에서의 이메일의 아이디부분만 조회하는 쿼리입니다.

select left(email,INSTR(email,'@')-1)
from `thelook_ecommerce.users`;

위 쿼리를 수정하여 이메일의 아이디부분의 4번째글자부터 뒤의 글자를 별표 5개로 변경처리하여 조회하세요.

select replace(email, substr(left(email,INSTR(email,'@')-1), 4, 5), '*****')
from `thelook_ecommerce.users`;

 

+ 프로그래머스 문제

• 6-2번 문제

프로그래머스 6-2 문제
https://school.programmers.co.kr/learn/courses/30/lessons/131114

SELECT WAREHOUSE_ID,
WAREHOUSE_NAME,
ADDRESS,
IFNULL(FREEZER_YN, 'N') AS FREEZER_YN
from FOOD_WAREHOUSE 
WHERE ADDRESS LIKE '경기도%' # or left(address, 3) = '강원도 
ORDER BY WAREHOUSE_ID ASC

왜 안되나 했더니 주소 경기도를 지정해줘야한다.. 당연히 경기도 공장인줄 알고 안 넣었네 문제 잘 보자

 

• 6-5번 문제

프로그래머스 6-5 문제
https://school.programmers.co.kr/learn/courses/30/lessons/132203

SELECT DR_NAME,
DR_ID,
MCDP_CD,
LEFT(HIRE_YMD, 10)
FROM DOCTOR
WHERE LEFT(MCDP_CD,2) = 'CS' OR
LEFT(MCDP_CD,2) = 'GS'
ORDER BY HIRE_YMD DESC, DR_NAME ASC

이거보다

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

LEFT 함수 대신에 ->  DATE_FORMAT( , '%Y-%m-%d') 형식으로 ! 외우자 !

 

• 6-8번 문제

프로그래머스 6-8 문제
https://school.programmers.co.kr/learn/courses/30/lessons/59410

SELECT ANIMAL_TYPE,
IFNULL(NAME, 'No name') AS NAME, #oracle? 에서는 ifnull 대신 'coalesce'라는 걸 쓰는거 같다
SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

 

• 6-9번 문제

프로그래머스 6-9 문제
https://school.programmers.co.kr/learn/courses/30/lessons/59412

SELECT EXTRACT(HOUR FROM DATETIME) AS HOUR,
COUNT(ANIMAL_ID) AS COUNT
FROM ANIMAL_OUTS
GROUP BY HOUR
HAVING HOUR BETWEEN 9 AND 19 # HOUR !=7, HOUR !=8 이렇게 풀었는데 값은 같은데 틀렸다고한다
ORDER BY HOUR

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-12번 문제

SELECT MCDP_CD,
COUNT(APNT_NO) 
FROM APPOINTMENT
WHERE APNT_YMD LIKE '2022-05%'
GROUP BY MCDP_CD
ORDER BY COUNT(APNT_NO), MCDP_CD

APNT_YMD 의 타입이 TIMESTAMP던데 그냥 문자열로 취급이 되나보다..string 함수 없이도 like 사용 가능