SQL <> 시각화 연습에는 SQLite
카스다녔을 땐 Mysql
요즘 듣는 러닝스푼즈는 GCP Bigquery
예전에 입문용 공부는 oracle
머리가 혼란스러워서 (특히 날짜함수) 정리를 하기로 마음먹었다 🥲

1. 날짜함수

---

- 현재시간반환

• Bigquery

  • CURRENT_DATE() - 년월일
  • CURRENT_DATETIME () - 년월일시분초

• MySQL

  • NOW(), CURRENT_TIMESTAMP() - 현재 날짜와 시간 반환
  • CURDATE(), CURRENT_DATE() - 현재 날짜 반환
  • CURTIME(), CURRENT_TIME() - 현재 시각 반환

• SQLite

  • date() - 년,월,일 반환
  • time() - 년,월,일,시 반환
  • datetime() - 년,월,일,시,분,초 반환

- 날짜 더하기

• Bigquery

  • DATETIME_ADD(시간, INTERVAL N {기준}) - datetime
  • DATE_ADD(시간, INTERVAL N {기준}) - date
    

• MySQL

  • ADDDATE(시간, INTERVAL N {기준}) - date
  • ADDTIEM(시간, INTERVAL N {기준}) - datetime

• SQLite

  • julianday(시간) + julianday(시간)
  • datetime(시간, '+ n {기준}', '+ n {기준}'..)

- 날짜 빼기

• Bigquery

  • DATE_SUB(시간, INTERVAL N {기준}) - date
  • DATE_DIFF(시간1, 시간2, {기준}) - 시간1에서 시간2를 빼기 date
  • DATETIME_SUB(시간, INTERVAL N {기준}) - datetime
  • DATETIME_DIFF(시간1, 시간2, {기준}) - 시간1에서 시간2를 빼기 datetime

• MySQL

  • SUBDATE(시간, INTERVAL N {기준}) - date
  • SUBTIEM(시간, INTERVAL N {기준}) - datetime
  • DATEDIFF(date1, date2) - 두 날짜 사이의 일수를 숫자로 반환(date1 - date2)
  • TIMEDIFF(time1, time2) - 두 시간의 차이를 datetime 형태로 반환(time1 - time2)
  • TIMESTAMPDIFF({기준}, 날짜1, 날짜2) - 날짜2-날짜1
    

• SQLite

  • julianday(시간) - julianday(시간)
  • datediff(시간1, 시간2)

- 특정 기준 통일

• Bigquery :DATETIME_TRUNC(시간, {기준})
  

• MySQL

  • DATE_TRUNC('날짜', '남기는 부분')
  • DATETIME_TRUNC( '날짜', '남기는 부분')
  • TIMESTAMP_TRUNC( '날짜', '남기는 부분')
    

• SQLite

- 특정 기준 추출

• Bigquery : EXTRACT({기준} FROM 시간)
  

• MySQL
  

• SQLite

2. 포맷, 형변환

---

- 포맷

• Bigquery

  • date(시간컬럼) - 년, 월, 일
  • FORMAT_DATETIME({요소}, 시간) - datetime
  • FORMAT_DATE(요소}, 시간) - date
    

• MySQL

  • DATE_FORMAT(시간,{요소})
    

• SQLite

3. 문자열 함수

---

CHAR_LENGTH(COL1) : 문자열길이반환
CONCAT(COL1,’텍스트’) : 문자열 합치기
REPLACE(COL1,”대상 텍스트”, “바뀌는 내용”) : 문자열 바꾸기
REFT(COL1,3) : 문자열 슬라이스 - 왼쪽에서 3번째
RIGHT(COL1,3) : 문자열 슬라이스 - 오른쪽에서 3번째
SUBSTR(COL1,2,2) : 문자열 슬라이스 - 2번째 문자에서 시작해서 2개까지
UPPER(COL1) – 대문자 변환
LOWER(COL1) – 소문자 변환
TRIM(문자열) – 양쪽 공백삭제
RTRIM(문자열) – 오른쪽 공백 삭제
LTRIM(문자열) – 왼쪽 공백 삭제

+ Window Fuction

---

- 순위함수

row_number : 동일 값 상관없이 차례대로 순위 ex)1,2,3,4,5
rank : 동일값은 같은 순위로 하되 아래 순위 건너뜀 ex)1,2,3,3,5
dense_rank : 동일값 같은 순위, 아래순위 건너뛰지 않음 ex)1,2,3,3,4

- 일반 집계함수

SUM : 합 / order by 사용 시 누적합
MAX : 최대값
MIN : 최소값
AVG : 평균값
COUNT : 갯수 / order by 사용 시 누적평균 / ROWS BETWEEN N PRECEDING AND CURRENT ROW 시 이동평균

- 행 순서 함수

FIRST_VALUE : 첫번째값
LAST_VALUE : 마지막값
LAG : N번째 앞 값 ex) lag(order_date,1)
LEAD : N번째 뒤 값 ex) LEAD(order_date,1)

- 비율 함수

PERCENT_RANK : 특정 범위 내에서 맨 윗 행을 0, 맨 아래 행을 1로 하여 각 행의 위치를 백분율로 나타낸 값
CUME_DIST : 특정 범위 안에서 해당 ROW의 누적 백분위를 구하는 것(맨 윗 행도 집계에 포함)

---

study note