데이터베이스 질문과 답변

특정 행을열로_상위부서 예산배분 0 5 4,357

by 구사일생 [SQL Query] 행을열로 예산배분 [2025.03.06 20:06:32]

팀별 예산 테이블

조직코드 사업비예산
A0001 100000
A0002 120000
A0003 130000
A9999 10000
B0001 110000
B0002 130000
B9999 20000

A9999, B9999는 각팀의 상위 부서의 예산 임.

그러나 실질적으로 가용 가능한 예산을 팀별로 다시 확인 하면

조직코드 사업비예산 상위부서 실제사용가능예산
A0001 100000 10000 103333.3333
A0002 120000 10000 123333.3333
A0003 130000 10000 133333.3333
B0001 110000 20000 120000
B0002 130000 20000 140000

A9999, B9999 부서에 배정된 예산을 하위 팀의 갯수로(실제로는 갯수로 나누는 팀과 정해진 비율로 나누는 경우 2가지 임)

나누어 팀별 예산에 합하여 가용예산을 다시 계산 하여야 합니다.

물론 조직 테이블에 

A0001  A9999

A0002 A9999  이런 식으로 되어 있기는 합니다.

이걸 한번에 계산을 하려 하니 팀코드와 부서코드가 다른 행에 있어 쉽지가 않네요 그렇다고 SUM, UNION ALL로 하면 조직이 변경 되었을때 엄청난 수작업이 발생 할 듯 하여

고수님들의 지도 부탁드립니다.

 

by 마농 [2025.03.07 00:10:03]

조직은 2단계 뿐인가요?
2단계 이상 가능하다면?
좀 더 현실적인 예시를 들어 주세요.
단계별로 예산이 어떻게 배분되어야 하는지?

by 구사일생 [2025.03.07 07:59:57]

조직 데이터는 2단계만 존재 합니다 연2회 이상 변경이 발생 하여 상위구조 관계는 간단히 설계 하고 데이터를 행으로  만들어 사용 중 입니다(전사 조직은 복잡 하지만 예산 관련 조직을 별도 관리 중)

by 마농 [2025.03.07 08:50:33]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
WITH t_budget AS
(
SELECT 'A0001' cd, 100000 bg FROM dual
UNION ALL SELECT 'A0002', 120000 FROM dual
UNION ALL SELECT 'A0003', 130000 FROM dual
UNION ALL SELECT 'A9999',  10000 FROM dual
UNION ALL SELECT 'B0001', 110000 FROM dual
UNION ALL SELECT 'B0002', 130000 FROM dual
UNION ALL SELECT 'B9999',  20000 FROM dual
)
, t_org AS
(
SELECT 'A0001' cd, 'A9999' p_cd FROM dual
UNION ALL SELECT 'A0002', 'A9999' FROM dual
UNION ALL SELECT 'A0003', 'A9999' FROM dual
UNION ALL SELECT 'A9999', ''      FROM dual
UNION ALL SELECT 'B0001', 'B9999' FROM dual
UNION ALL SELECT 'B0002', 'B9999' FROM dual
UNION ALL SELECT 'B9999', ''      FROM dual
)
SELECT p_cd
     , cd
     , p_bg
     , bg
     , bg + ROUND(p_bg / cnt, 4) t_bg
  FROM (SELECT a.p_cd, a.cd, b.bg
             , SUM(NVL2(a.p_cd, 0, b.bg)) OVER(PARTITION BY NVL(a.p_cd, a.cd)) p_bg
             , COUNT(*) OVER(PARTITION BY NVL(a.p_cd, a.cd)) - 1 cnt
          FROM t_org a
          LEFT OUTER JOIN t_budget b
            ON a.cd = b.cd
        )
 WHERE p_cd IS NOT NULL
 ORDER BY p_cd, cd
;

 

by 우주민 [2025.03.07 08:58:57]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
with t_amt as
(
 select 'A0001' as code,    100000 as amt union all
 select 'A0002' as code,    120000 as amt union all
 select 'A0003' as code,    130000 as amt union all
 select 'A9999' as code,    10000 as amt union all
 select 'B0001' as code,    110000 as amt union all
 select 'B0002' as code,    130000 as amt union all
 select 'B9999' as code,    20000 as amt
)
,t_code as (
 select 'A0001' as code,    'A9999' as upcode union all
 select 'A0002' as code,    'A9999' as upcode union all
 select 'A0003' as code,    'A9999' as upcode union all
 select 'A9999' as code,    null as upcode union all
 select 'B0001' as code,    'B9999' as upcode union all
 select 'B0002' as code,    'B9999' as upcode union all
 select 'B9999' as code,    null as upcode
)
select t1.code, t1.upcode
     , t2.amt, t3.amt, t1.ct
     , t2.amt + t3.amt / t1.ct
  from (select t1.code, t1.upcode
             , count(1) over(partition by upcode) as ct
          from t_code t1
         where upcode is not null
       ) t1
  left outer join t_amt t2
    on t1.code = t2.code
  left outer join t_amt t3
    on t1.upcode = t3.code

상위 코드값 테이블이 있다는 가정으로 만들어봤습니다.

by 구사일생 [2025.03.07 21:14:42]

역시 깔끔한 답을 주셨네요

저는 같은 테이블을 여러번 읽어서 구하긴 하였는데 답변을 이용해서 수정을 해야 겠네요

추가 질문이 있는데 새글 등록을 하였습니다.

모두 감사합니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입