10-3-4. 배치와 데드 튜플

-- [SQL-10-3-4-a]

DROP TABLE IF EXISTS startdbpg.su_ord_big_ymd;

CREATE TABLE startdbpg.su_ord_big_ymd
(     
    ord_dt         DATE          NOT NULL
    ,mbr_id        VARCHAR(40)   NOT NULL 
    ,shop_id       VARCHAR(40)   NOT NULL 
    ,ord_amt       DECIMAL(18,3) NULL 
);

ALTER TABLE startdbpg.su_ord_big_ymd
    ADD CONSTRAINT pk_su_ord_big_ymd PRIMARY KEY(ord_dt, mbr_id, shop_id);
SQL
복사

-- [SQL-10-3-4-b]

BEGIN;
    -- 기존 한 달치 데이터 삭제
    DELETE  FROM startdbpg.su_ord_big_ymd t1
    WHERE   t1.ord_dt >= '2025-01-01'::date
    AND     t1.ord_dt <  '2025-01-01'::date + INTERVAL '1 mon';

    -- 새로운 집계 데이터 입력
    INSERT INTO startdbpg.su_ord_big_ymd
            (ord_dt, mbr_id, shop_id, ord_amt)
    SELECT  t1.ord_dtm::date ord_dt ,t1.mbr_id ,t1.shop_id
            ,SUM(t1.ord_amt) ord_amt
    FROM    startdbpg.tr_ord_vac t1
    WHERE   t1.ord_dtm >= '2025-01-01'::date
    AND     t1.ord_dtm <  '2025-01-01'::date + INTERVAL '1 mon'
    GROUP BY t1.ord_dtm::date ,t1.mbr_id ,t1.shop_id;
COMMIT;
SQL
복사

-- [SQL-10-3-4-c]

DROP TABLE IF EXISTS startdbpg.su_ord_big_ymd_for_bat;

CREATE TABLE startdbpg.su_ord_big_ymd_for_bat AS
SELECT * FROM startdbpg.su_ord_big_ymd WHERE 1 != 1;
SQL
복사

-- [SQL-10-3-4-d]

BEGIN;
    TRUNCATE TABLE startdbpg.su_ord_big_ymd_for_bat;

    -- 1. 임시 테이블에 집계
    INSERT INTO startdbpg.su_ord_big_ymd_for_bat (ord_dt, mbr_id, shop_id, ord_amt)
    SELECT  t1.ord_dtm::date ord_dt ,t1.mbr_id ,t1.shop_id
            ,SUM(t1.ord_amt) ord_amt
    FROM    startdbpg.tr_ord_vac t1
    WHERE   t1.ord_dtm >= '2025-01-01'::date
    AND     t1.ord_dtm <  '2025-01-01'::date + INTERVAL '1 mon'
    GROUP BY t1.ord_dtm::date ,t1.mbr_id ,t1.shop_id;
    
    -- 2. 임시 테이블과 비교해 더 이상 필요 없는 데이터만 삭제
    DELETE  FROM startdbpg.su_ord_big_ymd t1
    WHERE   t1.ord_dt >= '2025-01-01'::date
    AND     t1.ord_dt <  '2025-01-01'::date + INTERVAL '1 mon'
    AND     NOT EXISTS(
                SELECT  *
                FROM    startdbpg.su_ord_big_ymd_for_bat x
                WHERE   x.ord_dt = t1.ord_dt
                AND     x.mbr_id = t1.mbr_id
                AND     x.shop_id = t1.shop_id);
    
    -- 3. 임시 테이블과 비교해 변경된 데이터만 UPDATE (중요: 실제 변경이 있을 때만)
    UPDATE  startdbpg.su_ord_big_ymd t1
    SET     ord_amt = t2.ord_amt
    FROM    startdbpg.su_ord_big_ymd_for_bat t2
    WHERE   t1.ord_dt = t2.ord_dt
    AND     t1.mbr_id = t2.mbr_id
    AND     t1.shop_id = t2.shop_id
    -- 중요 조건, 변경된 값이 존재할 때만 UPDATE 처리
    AND     t1.ord_amt IS DISTINCT FROM t2.ord_amt;
    
    -- 4. 임시 테이블과 비교해 신규 데이터만 입력
    INSERT INTO startdbpg.su_ord_big_ymd(ord_dt, mbr_id, shop_id, ord_amt)
    SELECT  t2.ord_dt ,t2.mbr_id ,t2.shop_id ,t2.ord_amt
    FROM    startdbpg.su_ord_big_ymd_for_bat t2
    WHERE   t2.ord_dt >= '2025-01-01'::date
    AND     t2.ord_dt <  '2025-01-01'::date + INTERVAL '1 mon'
    AND     NOT EXISTS(
                SELECT  *
                FROM    startdbpg.su_ord_big_ymd t1
                WHERE   t1.ord_dt = t2.ord_dt
                AND     t1.mbr_id = t2.mbr_id
                AND     t1.shop_id = t2.shop_id);
COMMIT;
SQL
복사