[PostgresSQL] 하위레벨 데이터를 이용해 상위레벨 데이터 업데이트

DB에서 상위 레벨의 item 시작, 종료일 = 하위레벨 item 시작일의 min(한개라도 있으면 값이 들어감), 하위레벨 item 종료일의 max(모든 값이 다 들어있어야함)

데이터

초기 데이터

계층형 데이터

최종적으로 원하는 데이터

---

해결 방법

• lvl1 = 컴퓨터
• lvl2 = 본체, 모니터, 프린터
• lvl3 = 메인보드, 렌카드, 파워
• lvl4 = RAM, CPU, 그래픽카드, 기타장치

1. lvl4를 기준으로 min, max값을 구한다.

select prnt_item_id, min(item_str_ymd) min_val, max(item_end_ymd) 
from item_table
group by prnt_item_id;

2. 이때, min은 lvl4의 item 시작일자 중에 하나만 있어도 lvl3에 업데이트 칠 수 있음

-- min함수는 null값을 무시하기 때문에, 1번 sql의 min값을 사용하면 됨

3. 이때, max는 lvl4의 item 종료일자가 모두 채워져있으면, lvl3에 업데이트 칠 수 있기 때문에 종료일자가 모두 채워져있는지 확인해야함

-- 종료일자가 채워져있는지 확인(count_val = sum_val)
-- count_val : prnt_item_id가 동일한 컬럼 갯수
-- sum_val : item종료일이 null이 아닌 경우(1)의 합
select prnt_item_id, min(item_str_ymd) min_val,
       max(item_end_ymd), count(*) as count_val,
       sum(case when item_str_ymd is not null then 1 else 0 end) as sum_val
from item_table
group by prnt_item_id;

4. lvl3의 item 시작일, 종료일에 값 update치기(lvl2, lvl1도 동일)

• 이때, 업데이트는 lvl3 -> lvl2 -> lvl1 순서대로 가야함

  4-1. 3번의 sql문을 테이블로 만들어서 값 update치기

  create table temp_table 
      as select prnt_item_id, min(item_str_ymd) min_val,
                max(item_end_ymd), count(*) as count_val,
                sum(case when item_str_ymd is not null then 1 else 0 end) as sum_val
         from item_table
         group by prnt_item_id;

5. lvl4를 기준으로 lvl3의 item 시작일, 종료일 update

• min

  update item_Table as a
  set item_str_ymd = t.min_val
  from temp_table as t
  where a.item_id = t.prnt_task_id;

• max : 종료일자가 채워져있는지 확인된 것만 update

  update item_Table as a
  set item_end_ymd = t.max_val
  from temp_table as t
  where a.item_id = t.prnt_task_id
  and t.count_val = t.sum_val;  -- max는 모든 값이 채워져있어야 update할 수 있으므로 체크해줘야함