CAST
숫자 자료형과 문자열 사이 자료형 변환
CAST([컬럼명] AS [변경할 자료형])
[컬럼명]::[변경할 자료형]
SELECT dt, CAST(dt AS VARCHAR) as yyyymmdd- 자료형의 종류
| 문자형 | 숫자형 | 날짜형 | 논리형 |
|---|---|---|---|
| CHAR, VARCHAR, LONG, CLOB | INT, BIGINT, NUMERIC, FLOAT, DOUBLE | DATE, TIMESTAMP | BOOLEAN |
CONCAT / ||
문자형 데이터 연결
CONCAT([문자열1], [문자열2], ..., [문자열N])
문자열1 || 문자열2 || ... || 문자열N
COALESCE (=NVL)
특정 값 치환
COALESCE([컬럼명], [변경 후 값])
변경 전/후의 자료형이 동일해야 한다.
CASE WHEN ~ ELSE ~ END
조건별 GROUPING
CASE WHEN [조건] THEN [변경할 값] END
CASE WHEN gender = 'M' THEN '남성'
WHEN gender = 'F' THEN '여성'
ELSE 'NA'
END- 위의 예에서
ELSE를 제외하면M과F이외의 값은NULL처리 됨
SELECT CASE WHEN i.item_name LIKE '%바지%' THEN '바지류' -- 치마바지라면!?
WHEN i.item_name LIKE '%치마%' THEN '치마류'
WHEN i.item_name LIKE '%원피스%' THEN '원피스류'
ELSE '미분류'
END AS item_type, COUNT(i.item_name), SUM(gmv) AS gmv- 주의) 먼저 걸린 조건으로 처리되므로 원하는 결과에 따라 조건문 작성 순서를 고려해야 함
문자형 데이터 다루기
LEFT / RIGHT / SUBSTRING
문자형 데이터 자르기
LEFT([컬럼명], [왼쪽에서부터 포함할 문자 수])
RIGHT([컬럼명], [오른쪽에서부터 포함할 문자 수])
SUBSTRING([컬럼명], [시작위치],[포함할 문자 수])
SELECT dt, LEFT(CAST(dt AS VARCHAR),4) AS yyyy,
SUBSTRING(CAST(dt AS VARCHAR),5,2) AS mm,
RIGHT(CAST(dt AS VARCHAR),2) AS dd
FROM online_order ooTRIM, LTRIM, RTRIM
특정 문자열 자르기
REPLACE
특정 문자열을 임의값으로 변경
UPPER / LOWER
대문자 / 소문자로 변경
LENGTH, LEN
문자열의 길이 반환
날짜 데이터 다루기
날짜 관련 함수는 SQL에 따라 문법이 다르므로 사용하고 있는 SQL 문법에 맞는지 서치 해야함
now() in oracle과 같이 서치해서 사용
TO_CHAR()
날짜 자료형을 문자열로 변환
TO_CHAR([컬럼명], 'yyyymmdd')
-- 표현 형식 변경
SELECT TO_CHAR(NOW(), 'yyyy / mm / dd')
SELECT TO_CHAR(NOW(), 'yyyy - mm - dd')TO_DATE()
문자열을 날짜 자료형으로 변환
TO_DATE([컬럼명], 'yyyymmdd')
INTERVAL (=DATEADD)
-- 날짜 더하기
SELECT NOW() + INTERVAL '1 MONTH'
-- 날짜 빼기
SELECT NOW() + INTERVAL '-1 YEAR'
SELECT NOW() - INTERVAL '1 DAY'DATEDIFF()
날짜 데이터 간 차이를 반환
DATEDIFF('day', [날짜], [날짜])
DATE_PART
날짜로부터 년도, 월, 주 등을 확인
SELECT DATE_PART('YEAR', NOW()) -- 주 SELECT DATE_PART('WEEK', NOW())
DATE_TRUNC()
현재 날짜를 기준으로 시작일 반환
DATE_TRUNC('quarter', NOW())분기의 시작일
숫자 데이터 다루기
나눗셈의 경우 numeric 자료형이 하나라도 있어야 제대로 적용
연산 값을 집계할 때 연산 전 컬럼에 집계함수를 각각 적용해 주어야 함
SELECT c.cate1,
SUM(CAST(discount AS numeric)) / SUM(gmv) AS discount_rate,
SUM(gmv) - SUM(discount) AS paid_amount,
SUM(CAST(product_profit AS numeric)) / SUM(gmv) AS product_magin,
SUM(CAST(total_profit AS numeric)) / SUM(gmv) AS total_magin
FROM online_order oo
JOIN item i ON oo.itemid = i.id
JOIN category c ON i.category_id = c.id
GROUP BY 1
ORDER BY 3- 할인율, 판매가, 이익률 계산 예시
ABS
절대값 반환
ROUND / CEILING / TRUNC
반올림 / 올림 / 내림
ROUND([적용할 컬럼], [표시할 소수점자리])
SELECT c.cate1,
ROUND(SUM(CAST(discount AS numeric)) / SUM(gmv),2) AS discount_rate,
SUM(gmv) - SUM(discount) AS paid_amount,
ROUND(SUM(CAST(product_profit AS numeric)) / SUM(gmv),2) AS product_magin,
ROUND(SUM(CAST(total_profit AS numeric)) / SUM(gmv) *100) || '%' AS total_magin
FROM online_order oo
JOIN item i ON oo.itemid = i.id
JOIN category c ON i.category_id = c.id
GROUP BY 1
ORDER BY 3- 다른 SQL에서는 numeric 자료형과 varchar 자료형을 그냥 concat 할 경우 에러날 수 있으므로 형변환 후 연결해주는 것이 안전하다.
COUNT
COUNT([컬럼명]),COUNT(DISTINCT [컬럼명])
SELECT i.item_name
, SUM(unitsold) AS unitsold
, COUNT(DISTINCT userid) AS user_count
, ROUND(SUM(CAST(gmv AS numeric)) / COUNT(DISTINCT userid)) AS avg_gmv_per_customer
FROM online_order oo
JOIN item i ON oo.itemid = i.id
GROUP BY 1
ORDER BY 4 DESC- 유저 아이디 당 평균 구매수량, 아이디 당 평균 구매금액 계산 예시
