[TIL] 2024-10-02 (ML/SQL)

Today I Learned (2024-10-02)

---

목차

• Today I Learned (2024-10-02)
• 오늘 공부한 내용
  • 1. 머신러닝
    • Clustering - KMeans
  • 2. KDT (SQL)
    • 자료형
    • 함수(문자열)
    • 함수(수치형)
    • 날짜형
    • 윈도우 함수
    • 윈도우 함수 - 순위
• 어려웠던 내용
• 궁금한 내용과 부족한 내용
• 느낀 점

---

오늘 공부한 내용

1. 머신러닝

Clustering - KMeans

• 클러스터링은 특징이 비슷한 데이터들끼리 그룹핑해주는 머신러닝 기법을 총칭함

• 레이블(정답)이 없는 데이터를 분류해주는 비지도학습(Unsupervised Learning)에서 주로 사용됨

• 주요 클러스터링 알고리즘

  • KMeans, DBSCAN, Hierachical Clustering 등이 있으며, 이외에도 다양한 Clustering 알고리즘이 존재함
  • 그 중에서 가장 기본적이면서도, 최근까지도 사용되는 알고리즘이 KMeans 알고리즘임

• 동작방식

  • k 값 결정(얼마나 많은 클러스터가 필요한지를 결정)
  • 초기 Centroid 결정
  • 모든 데이터를 순회하며, 각 데이터마다 가장 가까운 Centroid 를 찾아서 해당 클러스터에 할당
  • 모든 데이터를 순회한 후, Centroid 를 각 클러스터의 중심으로 이동
  • 각 클러스터에 재할당되는 데이터가 없을 때까지 3과 4를 반복

• 클러스터 갯수 설정 방법

  • inertia value

    • 군집화된 후, 각 Centroid 에서, 군집된 데이터 간의 거리를 합산한 값으로, 응집도를 수치화한 것임
    • 해당 값이 작을 수록, 군집화가 잘되었다고 할 수 있음

    ks = range(1,10)
    inertias = []
    
    for k in ks:
        model = KMeans(n_clusters=k)
        model.fit(X)
        inertias.append(model.inertia_)
    
    fig = go.Figure()
    fig.add_trace(
        go.Scatter(
            x=list(ks),
            y=inertias,
            mode='lines+markers'
        )
    )
    fig.show()

    

• K-Means 단점

  • K 를 미리 정해줘야 함
  • 초기 Centroid 를 랜덤하게 결정하므로, 초기 Centroid 위치에 따라, 잘못 분류할 수 있음

2. KDT (SQL)

자료형

• 문자열 데이터
  • string 으로 표시되는 컬럼
• 수치형 데이터
  • bigint, double 으로 표시되는 컬럼
• 날짜형 데이터
  • timestamp, date 등으로 표시되는 컬럼
• 논리형 데이터
  • boolean 으로 표시되는 컬럼

함수(문자열)

• POSITION

  • 찾고자 하는 글자가 몇 번째에 위치해 있는지 검색하여 위치를 숫자로 반환

  • 1부터 시작한다. (0부터 아님)

  • 공백도 인식

    SELECT POSITION('T' IN 'XTZ')
    -- SELECT POSITION('T' IN 'XYZ')
    -- SELECT POSITION('T' IN 'XTZT')

• SUBSTRING

  • 문자열의 일부를 잘라내어 반환

  • SUBSTRING(문자열 또는 컬럼, 자르기 시작할 위치, 잘라낼 문자열 길이)

    • 잘라낼 문자열 길이를 지정하지 않으면 문자열 끝까지 전부 반환함

    -- SELECT SUBSTR('ABC',2)
    -- SELECT SUBSTRING('ABC',2)
    -- SELECT SUBSTRING('CHUNJAE EDUCATION',9,3)
    SELECT SUBSTRING('CHUNJAE EDUCATION',-3,3)

• UPPER

  • 문자열을 대문자로 바꿔 반환

    SELECT UPPER('abc')

• LOWER

  • 문자열을 소문자로 바꿔 반환

    SELECT LOWER('ABC')

• LENGTH

  • 문자열의 글자 수를 반환

  • 단, 공백도 글자 하나로 친다.

    SELECT LENGTH('CHUNJAE EDUCATION');

• CONCAT

  • 여러 문자열을 하나로 합쳐 반환

  • 숫자도 문자열이기만 하면 합칠 수 있음

    SELECT CONCAT('abcde','12345','/*&&');
    
    SELECT CONCAT("userid",' - ',"proc_ymd")
    FROM "learning_analytics"."e_assessment"
    LIMIT 5
    
    SELECT 'abc' || 'xyc'

• REPLACE

  • 지정한 문자열을 바꿔 반환

    -- SELECT REPLACE('TEXTBOOK','X','S')
    SELECT REPLACE('TXTFILES','S','')
    

• L(R)PAD

  • 지정한 문자열의 길이만큼 특정 문자열을 채워 반환

    -- SELECT LPAD('ABC',5,'X'), RPAD('ABC',5,'X')
    SELECT LPAD('ABC',6,'XYD'), RPAD('ABC',6,'XY')

함수(수치형)

• ABS

  • 숫자의 절댓값을 반환

    SELECT ABS(5.24), ABS(-5.43);

• CEILING

  • 숫자를 정수로 올림해 반환

    SELECT CEILING(44.552); -- 45.0

• FLOOR

  • 숫자를 정수로 내림해 반환

    SELECT FLOOR(44.552) -- 44.0
    SELECT FLOOR(-2.54) -- -3.0
    SELECT FLOOR(-3.14) -- -4.0
    SELECT FLOOR(3.14) -- 3.0

• ROUND

  • 숫자를 해당 소수점 자리수까지 반올림해서 반환

  • 가장 마지막에 사용.( 여러번 사용하면 오차가 날 수 있다)

    SELECT ROUND(44.552,2) -- 44.55
    SELECT ROUND(3.141592, 2); -- 3.14

• TRUNCATE

  • 숫자에서 소수점 이하를 전부 버림하여 반환

    SELECT TRUNCATE(448.5525); -- 448.0
    SELECT TRUNCATE(-448.5525); -- -448.0

• POWER

  • 앞의 숫자를 뒤의 숫자만큼 제곱하여 반환

    SELECT POWER(5,2) -- 25.0

• MOD

  • 앞의 숫자를 뒤의 숫자로 나눈 나머지를 반환

    SELECT MOD(5,2) -- 1
    SELECT 5%2 -- 1

날짜형

SELECT 
    NOW() AS "현재",
    YEAR(NOW()) AS "년도",
    QUARTER(NOW()) AS "분기",
    MONTH(NOW()) AS "월",
    WEEK(NOW()) AS "주",
    DAY(NOW()) AS "일",
    HOUR(NOW()) AS "시",
    MINUTE(NOW()) AS "분",
    SECOND(NOW()) AS "초",
    MILLISECOND(NOW()) AS "밀리초"
;

SELECT 
    NOW(), 
    DAY_OF_YEAR(NOW()) AS "연 중 몇번째 일",
    DAY_OF_WEEK(NOW()) AS "주 중 몇번째 일"
;

-- 버림
SELECT
    NOW() AS "현재",
    DATE_TRUNC('year',NOW()),
    DATE_TRUNC('quarter',NOW()),
    DATE_TRUNC('month',NOW()),
    DATE_TRUNC('week',NOW()),
    DATE_TRUNC('day',NOW()),
    DATE_TRUNC('hour',NOW()),
    DATE_TRUNC('minute',NOW()),
    DATE_TRUNC('second',NOW()),
    DATE_TRUNC('millisecond',NOW())

-- 연산(더하기)
SELECT 
    NOW() AS "현재",
    DATE_ADD('year',1,NOW()),
    DATE_ADD('year',-1,NOW()),
    DATE_ADD('year',1,NOW()),
    DATE_ADD('quarter',1,NOW()),
    DATE_ADD('month',1,NOW()),
    DATE_ADD('week',1,NOW()),
    DATE_ADD('day',1,NOW()),
    DATE_ADD('hour',1,NOW()),
    DATE_ADD('minute',1,NOW()),
    DATE_ADD('second',1,NOW()),
    DATE_ADD('millisecond',1,NOW())


-- 간격, 비교하기
SELECT 
    NOW() AS "현재",
    DATE_DIFF('year',DATE_ADD('year',1,NOW()),NOW()),
    DATE_DIFF('year',DATE_ADD('month',1,NOW()),NOW()),
    DATE_DIFF('quarter',DATE_ADD('quarter',1,NOW()),NOW()),
    DATE_DIFF('month',DATE_ADD('month',1,NOW()),NOW()),
    DATE_DIFF('week',DATE_ADD('week',1,NOW()),NOW()),
    DATE_DIFF('day',DATE_ADD('day',1,NOW()),NOW()),
    DATE_DIFF('hour',DATE_ADD('hour',1,NOW()),NOW()),
    DATE_DIFF('minute',DATE_ADD('minute',1,NOW()),NOW()),
    DATE_DIFF('second',DATE_ADD('second',1,NOW()),NOW()),
    DATE_DIFF('millisecond',DATE_ADD('year',1,NOW()),NOW())

-- 데이터 타입 변경(date -> string)
SELECT 
    NOW(),
    DATE_FORMAT(NOW(), '%Y-%m-%d'),
    DATE_FORMAT(NOW(), '%Y-%m%d'),
    DATE_FORMAT(NOW(), '%Y%m'),
    DATE_FORMAT(NOW(), '%H:%i:%s'),
    DATE_FORMAT(NOW(), '%r'),
    DATE_FORMAT(NOW(), '%T')

SELECT
    NOW(),
    CONCAT(DATE_FORMAT(NOW(),'%y-%m-%d '), DATE_FORMAT(NOW(),'%H:%i:%s'))

-- 데이터 타입 변경(string -> date)
SELECT DATE_PARSE('2024-10-02','%Y-%m-%d'),
SELECT DATE_PARSE('2024-10-02   23:58:59','%Y-%m-%d %H:%i:%s')
SELECT DATE_PARSE('23:58:59','%H:%i:%s')

-- 데이터 타입 변경(cast)
SELECT 
    NOW(),
    CAST(DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s') AS VARCHAR),
    CAST(NOW() AS VARCHAR),
    CAST(CAST(NOW() AS VARCHAR) AS TIMESTAMP)

윈도우 함수

• GROUP BY(그룹화)와 유사하게 특정한 그룹에 대해 집계를 하는 함수
• GROUP BY가 테이블의 형태를 변화시키는 것과 달리, 윈도우 함수는 원본 그대로의 테이블에서 집계를 수행한다.

윈도우 함수 - 순위

• RANK

  • 행의 값이 같으면 같은 순위 부여-
  • 그 다음 값은 동일 값이 나온 숫자만큼 건너 뜀
  • 예 ) 학교 등의 석차

• DENSE_RANK

  • 행의 값이 같으면 같은 순위 부여
  • 같은 값은 하나의 건수로 취급
  • 예 ) 올림픽 등의 순위

• ROW_NUMBER

  • 같은 값이 나오더라도 고유한 값을 따로 부여함
  • 행 번호 붙이는거랑 동일함. 줄세우기

SELECT 
    "proc_ymd","userid","mcode","lecture_type","eventtime_kst",
    ROW_NUMBER() OVER(PARTITION BY "proc_ymd","userid","mcode","lecture_type" ORDER BY "eventtime_kst") AS "_ROW_NUMBER",
    RANK() OVER(PARTITION BY "proc_ymd","userid","mcode","lecture_type" ORDER BY "eventtime_kst") AS "_RANK",
    DENSE_RANK() OVER(PARTITION BY "proc_ymd","userid","mcode","lecture_type" ORDER BY "eventtime_kst") AS "_DENSE_RANK"
FROM "learning_analytics"."e_learning_action"
WHERE 1=1
    AND "userid" = '6494ffb5-e2bd-4825-9990-8bedd869185e'
LIMIT 100

---

어려웠던 내용

• JOIN...
• Machine 러닝

---

궁금한 내용과 부족한 내용

• 머신러닝이 맞나?

---

느낀 점

• 데이터 엔지니어에 대해 좀 알아봐야지