데이터 이관·검증 쿼리

SELECT
       'old' AS src
     , COUNT(*) AS cnt
     , SUM(CASE WHEN REGEXP_LIKE(amt, '^[0-9]+
  

) THEN CAST(amt AS INT) END) AS sum_amt
     , MIN(join_ymd) AS min_ymd
     , MAX(join_ymd) AS max_ymd
  FROM old_cust
 UNION ALL
SELECT
       'new'
     , COUNT(*)
     , SUM(amt)
     , MIN(TO_CHAR(join_dt, 'YYYYMMDD'))
     , MAX(TO_CHAR(join_dt, 'YYYYMMDD'))
  FROM new_cust;

SRC | CNT | SUM_AMT | MIN_YMD  | MAX_YMD
----+-----+---------+----------+---------
old | 12  | 28000   | 20240105 | 20241003
new | 9   | 20700   | 20240105 | 20241010
(2행)

SELECT
       g.code
     , COALESCE(o.cnt, 0) AS old_cnt
     , COALESCE(n.cnt, 0) AS new_cnt
     , COALESCE(n.cnt, 0) - COALESCE(o.cnt, 0) AS diff
  FROM grade_code g
  LEFT JOIN (SELECT grade, COUNT(*) AS cnt FROM old_cust GROUP BY grade) o ON o.grade = g.code
  LEFT JOIN (SELECT grade, COUNT(*) AS cnt FROM new_cust GROUP BY grade) n ON n.grade = g.code
 ORDER BY g.code;

CODE | OLD_CNT | NEW_CNT | DIFF
-----+---------+---------+-----
G1   | 4       | 5       | 1
G2   | 4       | 2       | -2
G3   | 3       | 2       | -1
(3행)

SELECT id FROM old_cust
EXCEPT
SELECT id FROM new_cust
 ORDER BY id;
SELECT id FROM new_cust
EXCEPT
SELECT id FROM old_cust
 ORDER BY id;

ID
--
5
6
7
12
(4행)

ID
--
99
(1행)

SELECT
       id
     , name_diff
     , ymd_diff
     , amt_diff
     , grade_diff
  FROM (
        SELECT
               o.id
             , CASE WHEN o.name = n.name OR (o.name IS NULL AND n.name IS NULL) THEN 0 ELSE 1 END AS name_diff
             , CASE WHEN o.join_ymd = TO_CHAR(n.join_dt, 'YYYYMMDD') THEN 0 ELSE 1 END AS ymd_diff
             , CASE WHEN o.amt = CAST(n.amt AS VARCHAR) THEN 0 ELSE 1 END AS amt_diff
             , CASE WHEN o.grade = n.grade THEN 0 ELSE 1 END AS grade_diff
          FROM old_cust o
          JOIN new_cust n ON n.id = o.id
       ) t
 WHERE name_diff + ymd_diff + amt_diff + grade_diff > 0
 ORDER BY id;

ID | NAME_DIFF | YMD_DIFF | AMT_DIFF | GRADE_DIFF
---+-----------+----------+----------+-----------
3  | 0         | 0        | 1        | 0
4  | 1         | 0        | 0        | 0
9  | 1         | 0        | 0        | 0
10 | 0         | 1        | 0        | 0
11 | 0         | 0        | 0        | 1
(5행)