SQL 3일차. 숫자/문자열 함수, 날짜함수

 

*오늘의 SQL 명령어 (숫자)

명령어	설명	예시	비고
round	반올림 명령어	select round(123.56789,3)     >>>    123.568 select round(123.56789,-2)    >>>    100.0	-round(반올림할 숫자, 자릿수) 소수점은 정수, 정수는 마이너스)
trunc	내림 명령어	select trunc(123.56789,3)       >>>   123.567 select trunc(123.56789,-2)      >>>   100.0	-trunc(숫자, 자리수)
mod	나머지 명령어	select mod(10, 3)      >>>    1	-mod(숫자, 나눌값) -나머지를 출력해줌
power	제곱 명령어	select power(10, 3)    >>>    1000.0	-power(숫자, 승수)
sqrt	제급근 명령어	select sqrt(10)    >>>    3.16227766...	-sqrt(숫자)

 

 

 

 

*오늘의 SQL 명령어 (문자열)

명령어	설명	예시	비고
substr	문자열 일부 출력 명령어	select substr('hello world', 7, 5)    >>>    world	-substr(문자열, 시작위치, 길이)
left	문자열 좌측부터 일부 출력	select left('064-000-1234', 3)      >>>    '064'	-left(문자열, 길이)
right	문자열 우측부터 일부출력	select right('064-000-1234', 8)    >>>    '000-1234'	-right(문자열, 길이)
concat	여러 문자열 연결	select concat('paul','-','lab')    >>>    paul-lab select 'paul'||'-'||'lab'     >>>    paul-lab	-단위 붙이기도 가능 -연결 연산자(||)로도 가능
lower	소문자 변경  명령어	select lower('AbcAbc')    >>>    abcabc
upper	대문자 변경 명령어	select upper('AbcAbc')    >>>    ABCABC
initcap	첫 문자만  대문자로 변경	select initcap('abcabc')    >>>    Abcabc
replace	문자열 교체 명령어	select replace(gender,'M', 'Man') from `thelook_ecommerce.users` >>>    'gender' 항목의 'M'을 'Man'으로 변경.
length	문자열 길이	select length('hello world')    >>>    11	-count와 구분하여 기억하기.
instr	특정 문자열의 위치  구하는 명령어	select instr('abcdef', 'b')    >>>    2	-instr(문자열, 인덱스번호) -index는 1부터 시작함
IFNULL	NULL값에 새로운 값 입력	select id,   IFNULL(name, '이름없음') from `weniv.weniv_event` limit 1000 >>>   id의  null값에 '이름없음' 값이 들어가게 됨.	-IFNULL(열이름, 넣을 값)
cast	형변환 문자열	select cast('123' as int64)    >>>   123	-cast(문자열 as 표현식) -표현식의 종류 참고 (int64, float64, numeric, bignumeric, string 등) -NULL은 그냥 null값으로 출력.

 

 

 

 

*오늘의 SQL 명령어 (시간, 날짜 관련)

명령어	설명	예시	비고
DATE()	날짜 명령어 (bigquery에서 error)	select date(created_at) from `thelook_ecommerce.users`	-timezone 설정 가능
DATETIME()	시간 명령어
CURRENT_ DATE()	현재 시간 명령어
FORMAT_ DATE	날짜 형식 지정	select format_date('%y-%m-%d', date '2023-01-25') >>>    23-01-25	-지정된 포멧스트링에 따라 날짜 형태를 변경. -모든 날짜 형식 참고
FORMAT_ DATETIME	시간 형식 지정		-format_datetime(형식, 시간)
EXTRACT	데이터 추출 명령어	select extract(year from date '2023-1-1');    >>>    2023	-extract (날짜 from date '입력값') -extract (날짜 from date (컬럼값)) -extract (시간 from datetime(컬럼값)) -날짜의 연, 월, 일, 시, 분, 초를 추출할때 사용.
DATE_DIFF	두 개의 시간/날짜  간격 출력 명령어	select date_diff(current_date(), date '2023-01-01', day) >>>    24	-date_diff(날짜, 날짜, 단위)
DATE_ADD	지정된 날짜간격 추가 함수	select date_add(current_date(), interval 100 day) >>>    2023-05-05	-day, month, year  가능
DATE_SUB	지정된 날짜간격 빼는 함수
DATETIME_ ADD	지정된 시간간격 추가 함수		-microsecond, millisecond, second, minute, hour, day, week, month, quarter, year 가능
DATETIEM_ SUB	지정된 시간간격 빼는 함수

 

 

 

 

 

*BIGQUERY는 안되지만 MYSQL에서 되는 명령어

-날짜와 시간 추출하기

BIGQUERY	MYSQL	비고
EXTRACT(YEAR FROM JOINED)	YEAR(JOINED)	DAY, HOUR, MINUTE, SECOND
DATE_FORMAT(JOINED, '%M')	MONTH(JOINED)

 

 

 

 

 

* 문제풀이

▼ 클릭!

---

 

SQL 연습문제 <6-5>

회원(users) 테이블에서 2020년 7월 1일 부터 2020년 7월 10일까지 가입한 회원정보를 조회하세요.
가입일시는 created_at 입니다.
가입일(DATE 타입)을 만든후 비교해보세요.

<내가 작성한 방법>  BETWEEN 사용

select *
from `thelook_ecommerce.users`
where date(created_at) 
  between date(2020, 7, 1) 
  and date(2020, 7, 10)

<그외 방법 1>  연산자 사용

select *
FROM thelook_ecommerce.users
where date(created_at) >= '2020-07-01' 
  and date(created_at) <= '2020-07-10';

<그외 방법 2>  연산자 + DATE 사용

select *
from thelook_ecommerce.users
where date(created_at) >= date(2020,7,1)
  and date(created_at) <= date(2020,07,10)

<그외 방법 3>  EXTRACT + BETWEEN 사용

select *
from `thelook_ecommerce.users`
where EXTRACT(YEAR FROM created_at) = 2020 
  and Extract(month from created_at) = 7
  and Extract(day from created_at) between 1 and 10

 

 

 

 

SQL 연습문제 <6-6>

주문정보(orders) 테이블에서 현재로부터 1년전에서 오늘까지 주문한 데이터를 조회하세요.
- 현재일시 : current_datetime()
- 주문일시 : created_at

<내가 쓴 답>  DATE_ADD, 연산자 사용

select *
from `thelook_ecommerce.orders`
where date_add(date(created_at), interval 1 year) 
  >= current_date()

<그외 방법 1>  DATE, DATE_SUB, 연산자 사용 (연단위)

select *
from `thelook_ecommerce.orders`
where date(created_at) >= 
  date_sub(current_date(), interval 1 year)

<그외 방법 2>  DATE, DATE_SUB, 연산자 사용 (일단위)

select *
from `thelook_ecommerce.orders`
where date(created_at) >= 
  date_sub(current_date(), interval 365 day)

 

 

 

 

SQL 연습문제 <6-9>

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

<내가 쓴 답>  : 조금 헷갈려서 다시 공부해보기.

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

 

 

 

 

SQL 연습문제 <6-10>

회원(users) 테이블에서 다음 내역을 조회하세요.

- id
- 이름(first_name)
- 이름의 길이(name_length)
- 이름(first_name)의 앞 3글자(part_name)
- 이름의 앞 3글자를 별표 처리한 이름(name_with_asterisk)

<내가 쓴 답>  SUBSTR + REPLACE 사용

select id, first_name, 
  length(first_name) as name_length,
  substr(first_name, 1, 3) as part_name,
  replace(first_name, substr(first_name,1,3), '***') as name_with_asterisk
from `thelook_ecommerce.users`

<더 좋은 답>  LEFT + REPLACE 사용

select id, first_name,
  length(first_name) as name_length,
  left(first_name,3) as part_name,
  replace(first_name, left(first_name,3),'***') as name_with_asterisk
from thelook_ecommerce.users

<그외 방법>  SUBSTR + CONCAT 사용

select id, first_name,
  length(first_name) as name_length,
  left(first_name,3) as part_name,
  '***' || substr(first_name, 4) as name_with_asterisk
 # = concat('***', substr(first_name, 4)as name_with_asterisk

 

 

 

 

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

 

 

 

 

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), '*****')
 from `thelook_ecommerce.users`

<그외 방법>  : 한 번씩 직접 작성해보기

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

select concat(left(email, 3),'*****', substr(email, instr(email, '@')))
from `thelook_ecommerce.users`

select left(email,3)|| '*****' || substr(email, INSTR(email,'@'))
from `thelook_ecommerce.users`

 

---