단아산보

쿼리문을 작성하다보면 특이한 경우의 수가 많이 생긴다. 보통은 제품명을 하나로 입력하지만 고객사에서 자동으로 입력되길 원해서 필터값을 모두 조합해서 등록해야 하는 경우도 생긴다. 그런데 이때 필터값은 보통 코드 테이블에 공통으로 관리 하는데 그러면 테이블 join 을 해서 subquery 로 가져 와야 하는데 이런 단어들은 어떻게 합쳐야 할찌 난감할때가 있다. 

등록이 안된 필터는 null 이나 공백을 내 놓을테니 그 부분도 대응을 해야 한다. 아래 예시는 필터 정보를 | 기호로 하나로 합쳐서 select 해주는 쿼리문이다. CONCAT 는 문자열을 합치는 용도로 쓰는데 의외로 많이 쓰이기 때문에 알아 두는것이 유용하다. 

$sql = "SELECT 
      CONCAT(
        COALESCE((SELECT name FROM material_category WHERE id = G.goods_cate), ''), '|', 
        COALESCE((SELECT name FROM material_category WHERE id = G.filter_1), ''), '|', 
        COALESCE((SELECT name FROM material_category WHERE id = G.filter_2), ''), '|', 
        COALESCE((SELECT name FROM material_category WHERE id = G.filter_3), ''), '|', 
        COALESCE((SELECT name FROM material_category WHERE id = G.filter_4), ''), '|', 
        COALESCE((SELECT name FROM material_category WHERE id = G.filter_5), ''), '|', 
        COALESCE((SELECT name FROM material_category WHERE id = G.filter_6), ''), '|', 
        COALESCE((SELECT name FROM material_category WHERE id = G.filter_7), ''), '|', 
        COALESCE((SELECT name FROM material_category WHERE id = G.filter_8), ''), '|', 
        COALESCE((SELECT name FROM material_category WHERE id = G.filter_9), ''), '|', 
        COALESCE((SELECT name FROM material_category WHERE id = G.filter_10), ''), '|', 
        COALESCE((SELECT name FROM material_category WHERE id = G.filter_11), ''), '|', 
        COALESCE((SELECT name FROM material_category WHERE id = G.filter_12), ''), '|', 
        COALESCE((SELECT name FROM material_category WHERE id = G.filter_13), ''), '|', 
        COALESCE((SELECT name FROM material_category WHERE id = G.filter_14), ''), '|', 
        COALESCE((SELECT name FROM material_category WHERE id = G.filter_15), ''), '|', 
        COALESCE((SELECT name FROM material_category WHERE id = G.filter_16), '')
      ) AS full_name
    FROM goods G
    WHERE goods_idx = :goods_idx";

$stmt = $this->pdo->prepare($sql);
$stmt->bindValue(':goods_idx', $goods_idx, \PDO::PARAM_INT);
$stmt->execute();

// fetch()로 결과를 가져오고, full_name만 반환
$result = $stmt->fetch(\PDO::FETCH_ASSOC);
return $result['full_name'] ?? null; // full_name이 없을 경우 null 반환

이렇게 합쳐진 full_name 만 return 해주는 함수 예시로 pdo 로 DB 연동을 하고 있다면 참조 하면 좋을거 같다.  

테이블을 만들다 보면 종종 autoICrement 로 자동증가로 키값을 잡는 경우가 있다. 주로 키값을 관리하는 테이블로 관리를하지만 경우에 따라서는 자동증가 기능도 사용하곤 하는데 작업을하다 보면 insert 후에 그 키값을 받아 와서 다른테이블에  insert 해야 하는 경우가 있다. 이럴때 유용하게 사용할 수 있는 함수는 lastInsertId 다. 

트랙잭션 내에서는 커밋하기 전에 넣어 줘야 값을 담아 온다. 커밋 뒤에 넣어서 안되는줄 알았는데 순서를 바꿨더니 값이 잘 나온다. 자동 증가값 키가 필요 하다면 참고 하면 좋겠다.  

$this->pdo->beginTransaction();

$stmt->execute(); // 쿼리 실행

$lastId = $this->pdo->lastInsertId(); // 자동 증가값 가져오기

$this->pdo->commit(); // 트랜잭션 커밋

1. 테이블 생성

CREATE TABLE SubjectScores (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student VARCHAR(100) NOT NULL,
    subject VARCHAR(50) NOT NULL,
    score INT NOT NULL,
    semester VARCHAR(20),
    year INT
);

2. 샘플 데이터 입력구문

INSERT INTO SubjectScores (student, subject, score, semester, year) VALUES
('Kim Min-soo', 'English', 85, '1st Semester', 2024),
('Kim Min-soo', 'Math', 90, '1st Semester', 2024),
('Kim Min-soo', 'Science', 80, '1st Semester', 2024),
('Lee Young-hee', 'English', 78, '1st Semester', 2024),
('Lee Young-hee', 'Math', 88, '1st Semester', 2024),
('Lee Young-hee', 'Science', 92, '1st Semester', 2024),
('Park Cheol-soo', 'English', 95, '1st Semester', 2024),
('Park Cheol-soo', 'Math', 85, '1st Semester', 2024),
('Park Cheol-soo', 'Science', 88, '1st Semester', 2024),
('Kim Ji-young', 'English', 82, '1st Semester', 2024),
('Kim Ji-young', 'Math', 79, '1st Semester', 2024),
('Kim Ji-young', 'Science', 91, '1st Semester', 2024);

3. 학생별 과목별 평균과 랭킹 구하는 쿼리문

SELECT student, subject, AVG(score) AS average_score,
       RANK() OVER (PARTITION BY subject ORDER BY AVG(score) DESC) AS rank
FROM SubjectScores
GROUP BY student, subject;