지피티와 함께하는 통계쿼리 공부일기

sqld 공부할땐 대충 뭐가 뭔지 기억이라도 하고 있었는데

이제 기억도 안나긔

ROLLUP, CUBE, GROUPING SETS 같은 거 지피티한테 물어봐서 대답이 돌아와도 눈에 안들어옴

 

그래서 심층리서치를 사용해 차근차근 보크숩을 해보게씀

 

 

도메인은 인사로 잡았고

지피티가 직원-부서-직무의 기본 관계와 직원-근태 기록 으로 4개 테이블 잡아줬긔

 

-- 기존 테이블이 있다면 삭제하고 새로 생성
DROP TABLE IF EXISTS attendance;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS jobs;
DROP TABLE IF EXISTS departments;

-- 1) 부서 테이블
CREATE TABLE departments (
    dept_id     INT PRIMARY KEY AUTO_INCREMENT,
    dept_name   VARCHAR(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2) 직무 테이블
CREATE TABLE jobs (
    job_id      INT PRIMARY KEY AUTO_INCREMENT,
    job_title   VARCHAR(50) NOT NULL,
    min_salary  INT NOT NULL,
    max_salary  INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3) 직원 테이블 (부서, 직무 FK 참조)
CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name  VARCHAR(50),
    last_name   VARCHAR(50),
    gender      CHAR(1),
    hire_date   DATE,
    dept_id     INT,
    job_id      INT,
    salary      INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id),
    FOREIGN KEY (job_id)  REFERENCES jobs(job_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4) 근태 테이블 (직원 FK 참조)
CREATE TABLE attendance (
    att_id      INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT,
    att_date    DATE,
    status      CHAR(1),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

테이블을 만들고

 

-- 부서 데이터 삽입 (예: 5개 부서)
INSERT INTO departments(dept_id, dept_name) VALUES
(1, 'HR'), (2, 'IT'), (3, 'Sales'), (4, 'Finance'), (5, 'Marketing');

-- 직무 데이터 삽입 (예: 5개 직무 및 급여 범위)
INSERT INTO jobs(job_id, job_title, min_salary, max_salary) VALUES
(1, 'Manager',     70000, 120000),
(2, 'Engineer',    60000, 100000),
(3, 'Analyst',     50000,  90000),
(4, 'Clerk',       30000,  50000),
(5, 'Salesperson', 40000,  80000);

기본 데이터를 넣어줬긔

 

 

그리고 

샘플 데이터는 지피티가 그냥 줄까? 하는거 

프로시저로 달라고 했긔 

 

프로시저 gen_sample_data의 로직:

• 입력 파라미터로 생성할 직원 수를 받습니다 (p_count).
• 1부터 p_count까지 루프를 돌면서 각 회차마다 새로운 직원 레코드를 생성합니다.
  • 무작위 부서(dept_id)와 직무(job_id)를 선택합니다.
  • 직무에 대응하는 급여 범위(min~max) 내에서 랜덤 급여 금액을 책정합니다.
  • 성별은 50% 확률로 'M' 또는 'F'로 지정합니다.
  • 입사일(hire_date)은 2015-01-01부터 오늘 날짜 사이에서 무작위 날짜를 생성합니다.
  • 직원의 이름은 예시용으로 몇 가지 남녀 이름 목록에서 무작위로 선택합니다 (남성 이름: John, Mike, Robert / 여성 이름: Jane, Emily, Anna 등). 성(last_name)은 Kim, Lee, Park, Choi, Smith 등에서 임의 선택합니다.
• 직원 레코드를 INSERT한 후 LAST_INSERT_ID()를 이용해 방금 생성된 직원의 ID를 가져옵니다.
• 이어서 해당 직원의 근태 기록을 5~15건 사이의 랜덤 개수로 생성합니다.
  • 각 근태 레코드에 대해, 직원의 입사일 이후 오늘 사이의 임의 날짜를 선택합니다. (※ 입사일 이전 날짜는 생성하지 않음)
  • 근태 상태는 80% 확률로 'P'(출석), 20% 확률로 'A'(결석)로 지정합니다.
• 루프를 완료하면 지정한 수의 직원과 다수의 근태 레코드가 데이터베이스에 삽입됩니다.

아래는 해당 프로시저 생성 및 실행 스크립트입니다. 프로시저를 생성한 후, 예시로 100명의 직원을 생성하도록 호출합니다 (원하는 데이터 양에 따라 파라미터를 조정 가능):

 

 

DELIMITER $$
CREATE PROCEDURE gen_sample_data(IN p_count INT)   #프로시저 선언부, p_count : 몇 명의 직원을 생성할지 받는 파라미터, $: 구분자로 내부의 ;를 무시하고 문장 끝이 END $ 라는 것을 알려줌
BEGIN
    DECLARE i INT DEFAULT 1;        # 직원 생성 루프용 변수
    DECLARE dept_count INT;         # 전체 부서 수 저장 변수
    DECLARE job_count INT;          # 전체 직무 수 저장 변수
    DECLARE rand_dept INT;          # 무직위로 선택할 부서 ID
    DECLARE rand_job INT;           # 무직위로 선택할 직무 ID
    DECLARE min_sal INT;            # 직무의 최소 급여
    DECLARE max_sal INT;            # 직무의 최대 급여
    DECLARE rand_gender CHAR(1);    # 성별 (M/F)
    DECLARE fn VARCHAR(50);         # 이름
    DECLARE ln VARCHAR(50);         # 성
    DECLARE hire_dt DATE;           # 입사일
    DECLARE empId INT;              # 생성된 직원의 ID
    DECLARE att_count INT;          # 생성할 근태 데이터 수
    DECLARE days_range INT;         # 입사일~오늘까지 일수 차이
    DECLARE days_offset INT;        # 랜덤 근태 날짜를 위한 오프셋
    DECLARE att_dt DATE;            # 생성할 근태 날짜
    DECLARE att_status CHAR(1);     # 출결 상태 (P/A)

    # 총 부서 수와 직무 수 조회
    SELECT COUNT(*) INTO dept_count FROM departments;
    SELECT COUNT(*) INTO job_count FROM jobs;

    WHILE i <= p_count DO   # 직무 수만큼 반복
        # 무작위 부서 및 직무 선택
        SET rand_dept = FLOOR(1 + RAND() * dept_count);
        SET rand_job = FLOOR(1 + RAND() * job_count);

        -- 선택된 직무의 급여 범위 조회
        SELECT min_salary, max_salary INTO min_sal, max_sal
        FROM jobs WHERE job_id = rand_job;

        # 성별 무작위 선택
        IF RAND() < 0.5 THEN
            SET rand_gender = 'M';
        ELSE
            SET rand_gender = 'F';
        END IF;

        # 성별에 따라 이름 무작위 선택
        IF rand_gender = 'M' THEN
            CASE MOD(i, 3)
                WHEN 0 THEN SET fn = 'John';
                WHEN 1 THEN SET fn = 'Mike';
                ELSE SET fn = 'Robert';
            END CASE;
        ELSE
            CASE MOD(i, 3)
                WHEN 0 THEN SET fn = 'Jane';
                WHEN 1 THEN SET fn = 'Emily';
                ELSE SET fn = 'Anna';
            END CASE;
        END IF;

        # 성 무작위 선택
        CASE MOD(i, 5)
            WHEN 0 THEN SET ln = 'Kim';
            WHEN 1 THEN SET ln = 'LEE';
            WHEN 2 THEN SET ln = 'Park';
            WHEN 3 THEN SET ln = 'Choi';
            WHEN 4 THEN SET ln = 'Smith';
        END CASE;

        # 입사일: 2015년 1월 1일부터 오늘까지 무작위
        SET hire_dt = DATE_ADD('2015-01-01', INTERVAL FLOOR(RAND() * 4018) DAY);

        # 직원 데이터 삽입
        INSERT INTO employees(dept_id, job_id, first_name, last_name, gender, hire_date, salary)
            VALUES (rand_dept, rand_job, fn, ln, rand_gender, hire_dt, FLOOR(min_sal + RAND() * (max_sal - min_sal + 1)));

        # 방금 삽입한 직원 ID 저장
        SET empId = LAST_INSERT_ID();

        # 5~15개 사이의 무작위 근태 데이터 개수 설정
        SET att_count = FLOOR(RAND() * 11) + 5;

        # 입사일 기준 오늘까지 일수 계산
        SET days_range = DATEDIFF(CURDATE(), hire_dt);
        IF days_range < 0 THEN
            SET days_range = 0;
        END IF;

        WHILE att_count > 0 DO  # 근태 데이터 수만큼 반복
            IF days_range > 0 THEN
                SET days_offset = FLOOR(RAND() * (days_range + 1));
            ELSE
                SET days_offset = 0;
            END IF;
            SET att_dt = DATE_ADD(hire_dt, INTERVAL days_offset DAY);    # 근태 일자 생성

            # 출결 상태 무작위 (80% 출석, 20% 결석)
            IF RAND() < 0.2 THEN
                SET att_status = 'A';
            ELSE
                SET att_status = 'P';
            END IF;

            # 근태 데이터 삽입
            INSERT INTO attendance(employee_id, att_date, status) VALUES (empId, att_dt, att_status);

            # 근태 카운트 감소
            SET att_count = att_count - 1;
        END WHILE;

        # 다음 직원
        SET i = i + 1;
        END WHILE;
    END $$
    DELIMITER ;



    # 직원 100명 생성
    CALL gen_sample_data(100);

짜자잔~ (짜잔형 아님)

TIP: 대량의 데이터를 생성할 때
RAND() 함수를 사용하면 편리합니다. 위에서는 부서/직무 선택, 성별 결정, 날짜 오프셋 결정 등에
RAND()를 활용했습니다. MySQL 8.0 기준으로 이러한 프로시저와 함수 사용이 가능하며, 데이터 양을 조절하여 다양한 시나리오를 실습할 수 있습니다 datacamp.com.

 

 

이렇게 준비했긔.

 

다음 포스팅에서는 본격저긍로 통계쿼리를 연습해보겠긔

그럼 다음 포스팅에서 건강하게 만납시다~