-- 클러스터별 평균 유사도와 최소 유사도
SELECT cluster_id,
       ROUND(AVG(similarity), 3) AS avg_sim,
       MIN(similarity) AS min_sim
FROM embedding_store
GROUP BY cluster_id;

-- 카테고리별 정확도 집계
SELECT true_label,
       ROUND(SUM(CASE WHEN true_label = pred_label THEN 1 ELSE 0 END)::decimal
             / COUNT(*), 3) AS accuracy
FROM prediction_results
GROUP BY true_label;

-- 사용자별 아이템 선택 패턴
SELECT user_id, item_category, COUNT(*) AS choice_count
FROM user_choice_log
GROUP BY user_id, item_category
ORDER BY user_id, choice_count DESC;

-- 모델별, 카테고리별 정확도 비교
SELECT model_name, true_label,
       ROUND(SUM(CASE WHEN true_label = pred_label THEN 1 ELSE 0 END)::decimal
             / COUNT(*), 3) AS accuracy
FROM prediction_results
GROUP BY model_name, true_label;

-- 원본데이터
region | product | amount
-------+---------+-------
East   | A       | 100
East   | B       | 150
West   | A       | 200
West   | B       |  50

-- 기본 GROUP BY
SELECT region, product, SUM(amount) AS total
FROM sales_summary
GROUP BY region, product;

region | product | total
-------+---------+------
East   | A       | 100
East   | B       | 150
West   | A       | 200
West   | B       |  50

-- GROUP BY ROLLUP
SELECT region, product, SUM(amount) AS total
FROM sales_summary
GROUP BY ROLLUP(region, product);

region | product | total
-------+---------+------
East   | A       | 100
East   | B       | 150
East   | NULL    | 250   <- East 소계
West   | A       | 200
West   | B       |  50
West   | NULL    | 250   <- West 소계
NULL   | NULL    | 500   <- 전체 합계

-- GROUP BY CUBE
SELECT region, product, SUM(amount) AS total
FROM sales_summary
GROUP BY CUBE(region, product);

region | product | total
-------+---------+------
East   | A       | 100
East   | B       | 150
East   | NULL    | 250   <- East 소계
West   | A       | 200
West   | B       |  50
West   | NULL    | 250   <- West 소계
NULL   | A       | 300   <- 제품 A 전체 합계
NULL   | B       | 200   <- 제품 B 전체 합계
NULL   | NULL    | 500   <- 전체 합계

-- 시퀀스 생성
CREATE SEQUENCE my_seq START 1;

-- 임베딩을 저장하는 테이블 embedding_store
CREATE TABLE embedding_store (
    id BIGINT PRIMARY KEY DEFAULT nextval('my_seq'),
    user_text TEXT,
    embedding VECTOR(4)
);

-- nextval('my_seq')로 자동 증가하는 ID를 달아주기
INSERT INTO embedding_store (user_text, embedding)
VALUES ('hello world', '[0.1, 0.2, 0.3, 0.4]');

-- 또 다른 데이터를 넣으면 ID가 자동으로 2가 됨
INSERT INTO embedding_store (user_text, embedding)
VALUES ('how are you', '[0.5, 0.6, 0.7, 0.8]');

-- 확인해보면?
SELECT * FROM embedding_store;

id |   user_text   |      embedding
---+---------------+------------------------
 1 | hello world   | [0.1, 0.2, 0.3, 0.4]
 2 | how are you   | [0.5, 0.6, 0.7, 0.8]

-- 예측 결과 테이블 prediction_results
CREATE TABLE prediction_results (
    id BIGSERIAL PRIMARY KEY,
    true_label TEXT,
    pred_label TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 저장 프로시저 insert_prediction_batch
CREATE OR REPLACE PROCEDURE insert_prediction_batch(
    p_true_label TEXT,
    p_pred_label TEXT,
    p_count INT
)
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 1..p_count LOOP
        INSERT INTO prediction_results (true_label, pred_label)
        VALUES (p_true_label, p_pred_label);
    END LOOP;
END;
$$;

-- AI 모델의 예측 결과 저장하기
-- 저장 프로시저 안쓴경우
INSERT INTO prediction_results (true_label, pred_label) VALUES ('spam', 'spam');
INSERT INTO prediction_results (true_label, pred_label) VALUES ('spam', 'spam');
INSERT INTO prediction_results (true_label, pred_label) VALUES ('spam', 'spam');
INSERT INTO prediction_results (true_label, pred_label) VALUES ('spam', 'spam');
INSERT INTO prediction_results (true_label, pred_label) VALUES ('spam', 'spam');

-- 저장 프로시저를 쓴 경우
CALL insert_prediction_batch('spam', 'spam', 5);

-- 안 썼을 때
-- 예측 테이블 prediction_logs_plain 생성
CREATE TABLE prediction_logs_plain (
    id SERIAL PRIMARY KEY,
    model_name TEXT,
    label TEXT,
    score FLOAT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 데이터 넣기
INSERT INTO prediction_logs_plain (model_name, label, score)
VALUES ('spam_detector', 'spam', 0.92);

-- 조회
SELECT id, model_name, label, score
FROM prediction_logs_plain;

 id | model_name    | label | score
----+---------------+-------+-------
  1 | spam_detector | spam  | 0.92

-- 썼을 때
-- UDT로 (label, score)를 하나의 덩어리로 정의
CREATE TYPE prediction_result_type AS (
    label TEXT,
    score FLOAT
);

-- 예측 테이블 prediction_logs_udt 생성
CREATE TABLE prediction_logs_udt (
    id SERIAL PRIMARY KEY,
    model_name TEXT,
    result prediction_result_type,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 데이터 넣기
INSERT INTO prediction_logs_udt (model_name, result)
VALUES ('spam_detector', ROW('spam', 0.92)::prediction_result_type);

-- 조회
SELECT id, model_name, (result).label, (result).score
FROM prediction_logs_udt;

 id | model_name    | label | score
----+---------------+-------+-------
  1 | spam_detector | spam  | 0.92

-- 트리거 안 썼을 때
-- 1. 메인 테이블
CREATE TABLE embedding_store_plain (
    id SERIAL PRIMARY KEY,
    text_input TEXT,
    similarity FLOAT
);

CREATE TABLE similarity_warnings_plain (
    warning_id SERIAL PRIMARY KEY,
    embedding_id INT,
    similarity FLOAT,
    warning_msg TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 2. 데이터 넣을 때
-- 새 데이터 삽입
INSERT INTO embedding_store_plain (text_input, similarity)
VALUES ('bad case', 0.3);

-- 임계치 체크 후 경고 삽입
INSERT INTO similarity_warnings_plain (embedding_id, similarity, warning_msg)
VALUES (currval('embedding_store_plain_id_seq'), 0.3, 'Similarity below threshold');

-- 트리거 썼을 때
-- 1. 메인 테이블 + 경고 테이블
CREATE TABLE embedding_store (
    id SERIAL PRIMARY KEY,
    text_input TEXT,
    similarity FLOAT
);

CREATE TABLE similarity_warnings (
    warning_id SERIAL PRIMARY KEY,
    embedding_id INT,
    similarity FLOAT,
    warning_msg TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 2. 트리거 함수 + 트리거 생성
CREATE OR REPLACE FUNCTION check_similarity_threshold()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.similarity < 0.5 THEN
        INSERT INTO similarity_warnings (embedding_id, similarity, warning_msg)
        VALUES (NEW.id, NEW.similarity, 'Similarity below threshold');
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_check_similarity
AFTER INSERT ON embedding_store
FOR EACH ROW
EXECUTE FUNCTION check_similarity_threshold();

-- 3. 데이터 넣기
INSERT INTO embedding_store (text_input, similarity)
VALUES ('bad case', 0.3);

-- scores 데이터
CREATE TABLE scores (
    student TEXT,
    subject TEXT,
    score INT
);

INSERT INTO scores VALUES
('철수', '수학', 80),
('철수', '영어', 90),
('영희', '수학', 95),
('영희', '영어', 85),
('민수', '수학', 70),
('민수', '영어', 75);

-- 집계 함수 GROUP BY로 학생별 평균 점수 구하기
SELECT student, AVG(score) AS avg_score
FROM scores
GROUP BY student;

student | avg_score
--------+----------
철수     |   85
영희     |   90
민수     |   72.5

-- 윈도우 함수 OVER로 학생별 평균 점수 구하기
SELECT student, subject, score,
       AVG(score) OVER (PARTITION BY student) AS avg_score
FROM scores;

student | subject | score | avg_score
--------+---------+-------+----------
철수     | 수학    |  80   |   85
철수     | 영어    |  90   |   85
영희     | 수학    |  95   |   90
영희     | 영어    |  85   |   90
민수     | 수학    |  70   |  72.5
민수     | 영어    |  75   |  72.5

-- ROW_NUMBER()
student | subject | score | rownum
--------+---------+-------+-------
철수     | 수학    |  95   |   1
철수     | 영어    |  95   |   2
철수     | 과학    |  90   |   3
영희     | 수학    | 100   |   1
영희     | 영어    |  80   |   2
민수     | 수학    |  70   |   1
민수     | 과학    |  70   |   2
민수     | 영어    |  60   |   3

-- RANK()
student | subject | score | rnk
--------+---------+-------+----
철수     | 수학    |  95   | 1
철수     | 영어    |  95   | 1
철수     | 과학    |  90   | 3

-- NTILE(n)
student | subject | score | bucket
--------+---------+-------+-------
철수     | 수학    | 95    | 1
철수     | 영어    | 95    | 1
철수     | 과학    | 90    | 2
-- 상위 절반 = 그룹 1, 하위 절반 = 그룹 2

-- LAG() & LEAD()
student | subject | score | prev_score | next_score
--------+---------+-------+------------+------------
민수     | 과학    |  70   |   (null)   |  60
민수     | 수학    |  70   |    70      |  60
민수     | 영어    |  60   |    70      | (null)
-- 직전 점수와 다음 점수 참조: 민수의 행마다 앞뒤 점수가 달라 붙음.

-- SUM() OVER, AVG() OVER
student | subject | score | running_total | avg_score
--------+---------+-------+----------------+----------
철수     | 수학    |  95   |       95       |   93.3
철수     | 영어    |  95   |      190       |   93.3
철수     | 과학    |  90   |      280       |   93.3

- running_total은 점수 누적합, avg_score는 평균.

{"category": "esg", "type": "finance"} -> 1행
{"category": "ai", "type": "tech"} -> 2행
{"category": "esg", "type": "policy"} -> 3행

"category"="esg" -> 1행, 3행
"category"="ai" -> 2행
"type"="finance" -> 1행
"type"="tech" -> 2행
"type"="policy" -> 3행