TIL_20250527 (화) 날짜/시간 함수, 유클리드 계산식
# 5-4
-- 5-4 1번 예제: 현재 날짜/시간 출력 함수
SELECT
CURRENT_DATE() AS dt, -- 오늘 날짜 (SQL 표준)
CURRENT_TIMESTAMP() AS stamp, -- 오늘 날짜+시간 (SQL 표준)
-- 여기부턴 MySQL에서 많이 쓰이는 방법
DATE(NOW()) AS dt_now, -- 오늘 날짜 (MySQL 방식)
NOW() AS dtime -- 오늘 날짜+시간 (MySQL 방식, 실무에서 가장 자주 사용)
-- 5-4 2번 예제
SELECT
CAST('2025-05-27' AS DATE) AS DT, -- SQL 표준 / PostgreSQL / MySQL 공통 사용 가능
CAST('2025-05-27 13:00:00' AS DATETIME) AS DATE_TIME, -- MySQL 전용 (PostgreSQL은 TIMESTAMP 사용)
TIMESTAMP('2025-05-26 13:00:00') AS TS, -- MySQL은 DATETIME 반환 / PostgreSQL은 TIMESTAMP 반환
DATE('2025-05-26') AS My_DT -- MySQL은 문자열에서 날짜 추출 / PostgreSQL은 date_trunc 등 사용
-- 5-4 2번 예제 PostgreSQL 원래 쿼리
SELECT
CAST('2025-05-27' AS DATE) AS DT,
CAST('2025-05-27 13:00:00' AS TIMESTAMP) AS STAMP,;
# 오류 원인 = timestamp는 mysql에서 데이터 타입이 아니라 함수
-- 5-4 3번 예제 년, 월, 일, 시간 추출
SELECT
STAMP,
-- PostgreSQL 방식: EXTRACT 함수 사용
-- EXTRACT(YEAR FROM STAMP) AS Year,
-- EXTRACT(MONTH FROM STAMP) AS Month,
-- EXTRACT(DAY FROM STAMP) AS Day,
-- EXTRACT(HOUR FROM STAMP) AS Hour
--
-- MySQL 방식: 함수가 더 직관적이고 실무에서 더 많이 씁니다
YEAR(STAMP) AS Year, -- MySQL 전용 함수 (EXTRACT도 되긴 하지만 덜 씁니다)
MONTH(STAMP) AS Month,
DAY(STAMP) AS Day,
HOUR(STAMP) AS Hour
FROM
(SELECT TIMESTAMP('2016-01-30 12:00:00') AS STAMP) AS t;
-- (SELECT CAST('2016-01-30 12:00:00' AS DATETIME) AS STAMP) AS t; 이것도 됌
--
-- (SELECT '2016-01-30 12:00:00' AS STAMP) AS t; ->> MySQL에서만 문자열만 형식에 맞추면 알아서 DATETIME 형식으로 인식
--
-- (SELECT CAST('2016-01-30 12:00:00' AS TIMESTAMP) AS STAMP) AS t; 이게 책 내용 postgreSQL 원래 지문이지만 예외 !
-- 5-4 4번예제 substring으로 날짜 추출 (SQL 공통, 시간 데이터가 문자열일 때 사용)
select --
STAMP,
substring(STAMP,1,4) as `year`, -- 실수 2가지 STAMP컬럼명 대소문자 구분 필
substring(STAMP,6,2) as `month`, -- 예약어로 컬럼명 지정시 백틱
substring(STAMP,9,2) as `day`,
substring(STAMP,12,2) as `hour`
FROM (SELECT CAST('2016-01-30 12:00:00' AS CHAR ) AS STAMP) AS t; -- 이 부분만 MySQL에서 가능하게 바꿈
-- CHAR 부분 VARCHAR 도 가능
-- postgreSQL은 TEXT로 사용 MySQL에서는 TEXT라는 데이터 타입은 존재하지만 cast( -- as TEXT)는 작동 안함
-- SQL 레시피 6-4
# 6-4 1번 예제 숫자 데이터의 절댓값, 제곱 평균 제곱근(RMS) 계산하기
-- 여기서 등장하는 거리라는 개념은 물리적인 거리가 아님 ex) 작년 매출과 올해 매출의 차이 => 거리
select *
from location_1d
-- 이 테이블안에 데이터 값은 가상의 값 ==>> 단지 거리를 구하는 예시를 보여주기 위한 용도
-- 거리 계산시 공식 ==>> 절대값 차이 -> 제곱 -> 제곱근
select
ABS(x1 - x2), -- 절대값인 이유 우리 세계는 음수가 없기때문에
SQRT(power(x1 - x2,2))
from location_1d
-- 결국 거리 계산 공식을 사용해서 구한 값을 어떻게 사용하냐 ?
-- 1. 추천시스템
-- 2. 이상 탐지 -> 튀는 값 찾기
-- 3. 군집화 고객분류(고객 행동이 비슷하면 묶음)
-- 4. 머신러닝 모델 정확도 측정
-- 6-4 예제 2번 유클리드 거리 (직선으로 잰 거리 그림으로 표현 쫙 (피타고라스 정리 기반 공식))
select
ABS(x1 - x2),
SQRT(power(x1 - x2,2) + power(y1 - y2, 2)) as dist -- 제곱근을 이용하는건 단지 피타고라스 정리를 이용하기 위함 직각삼각형
from location_2d
-- 예제 1번에 이어 거리 계산 공식을 통해 값을 구해 적용 시켜서 추천 시스템으로 예를 들면
-- 예제: 영화 추천 시스템 🎬
-- 가정:
--
-- x축 = 액션 영화 선호도 (0~5점)
--
-- y축 = 멜로 영화 선호도 (0~5점)
--
-- 사용자 A → (x1, y1) = (4, 1)
-- 액션 영화는 좋아함 (4)
--
-- 멜로는 별로 안 좋아함 (1)
--
-- 사용자 B → (x2, y2) = (2, 4)
-- 액션 영화는 좀 보지만
--
-- 멜로는 좋아함
--
-- 이렇게 유클리드 거리 값이 작으면 두 사용자 간 취향이 유사하다고 판단할 수 있으며,
-- 이 경우 A 사용자가 좋아한 콘텐츠를 B 사용자에게 추천할 수 있음
--
-- 유클리드 거리의 범위는 데이터 값의 범위에 따라 달라지므로,
-- "거리가 멀다 / 가깝다"는 기준은 해당 데이터의 스케일 안에서 해석해야 함
-- 0.0 - 1.0 정확도를 위해 범위를 줄이는게 맞다.
-- 영화 4개 만점 5점 = 총 합 20점인데 최저점은 4점이고 최고점은 20점
-- 여기서 4점은 0, 20점은 1로 변환해서 계산하는게 좋고 이것이 전처리 과정이다.