데이터베이스 질문과 답변
안녕하세요.
업무 진행하다가 막히는게 있어서 질문드립니다.ㅠㅠ
예를 들어 gubun1, gubun2, val라는 컬럼이 있습니다.
gubun1에는 x1, x2, y1, y3
gubun2에는 1,2,3
라는 코드성 데이터가 있구요.
| gubun1 | gubun2 | val |
| x1 | 1 | 50 |
| x2 | 1 | 60 |
| y1 | 1 | 100 |
| y3 | 1 | 100 |
| x1 | 2 | 10 |
| x2 | 2 | 20 |
| y1 | 2 | 100 |
| y3 | 2 | 100 |
| x1 | 3 | 30 |
| x2 | 3 | 40 |
| y1 | 3 | 100 |
| y3 | 3 | 100 |
위와 같이 데이터가 구성된다고 하면
x1은 y1하고 나눈 데이터가 있어야 하고
x2는 y3하고 나눈 데이터가 있어야 합니다.
즉 결과는 아래와 같이 나와야 합니다.
고수님들 부탁드리겠습니다.
| gubun1 | gubun2 | val |
| x1/y1 | 1 | 0.5 |
| x1/y1 | 2 | 0.1 |
| x1/y1 | 3 | 0.3 |
| x2/y3 | 1 | 0.6 |
| x2/y3 | 2 | 0.2 |
| x2/y3 | 3 | 0.4 |
by 마농
[2017.05.02 13:07:24]
WITH t AS
(
SELECT 'x1' gubun1, 1 gubun2, 50 val FROM dual
UNION ALL SELECT 'x2', 1, 60 FROM dual
UNION ALL SELECT 'y1', 1, 100 FROM dual
UNION ALL SELECT 'y3', 1, 100 FROM dual
UNION ALL SELECT 'x1', 2, 10 FROM dual
UNION ALL SELECT 'x2', 2, 20 FROM dual
UNION ALL SELECT 'y1', 2, 100 FROM dual
UNION ALL SELECT 'y3', 2, 100 FROM dual
UNION ALL SELECT 'x1', 3, 30 FROM dual
UNION ALL SELECT 'x2', 3, 40 FROM dual
UNION ALL SELECT 'y1', 3, 100 FROM dual
UNION ALL SELECT 'y3', 3, 100 FROM dual
)
-- 1. Case 로 Group By 하기
SELECT CASE WHEN gubun1 IN ('x1', 'y1') THEN 'x1/y1' ELSE 'x2/y3' END gubun1
, gubun2
, MIN(CASE WHEN gubun1 LIKE 'x%' THEN val END)
/ MIN(CASE WHEN gubun1 LIKE 'y%' THEN val END) val
FROM t
GROUP BY CASE WHEN gubun1 IN ('x1', 'y1') THEN 'x1/y1' ELSE 'x2/y3' END
, gubun2
ORDER BY gubun1, gubun2
;
-- 2. Model 절 활용
SELECT gubun1, gubun2, val
FROM t
MODEL
RETURN UPDATED ROWS
PARTITION BY (gubun2)
DIMENSION BY (CAST(gubun1 AS VARCHAR2(5)) gubun1)
MEASURES (val)
RULES
( val['x1/y1'] = val['x1'] / val['y1']
, val['x2/y3'] = val['x2'] / val['y3']
)
ORDER BY gubun1, gubun2
;
by 전문가되자
[2017.05.02 15:43:31]
답변 감사합니다.
by 동동동
[2017.05.02 13:25:12]
WITH T1 AS (
SELECT 'x1' GUBUN1, '1' GUBUN2, 50 VAL FROM DUAL UNION ALL
SELECT 'x2' GUBUN1, '1' GUBUN2, 60 FROM DUAL UNION ALL
SELECT 'y1' GUBUN1, '1' GUBUN2, 100 FROM DUAL UNION ALL
SELECT 'y3' GUBUN1, '1' GUBUN2, 100 FROM DUAL UNION ALL
SELECT 'x1' GUBUN1, '2' GUBUN2, 10 FROM DUAL UNION ALL
SELECT 'x2' GUBUN1, '2' GUBUN2, 20 FROM DUAL UNION ALL
SELECT 'y1' GUBUN1, '2' GUBUN2, 100 FROM DUAL UNION ALL
SELECT 'y3' GUBUN1, '2' GUBUN2, 100 FROM DUAL UNION ALL
SELECT 'x1' GUBUN1, '3' GUBUN2, 30 FROM DUAL UNION ALL
SELECT 'x2' GUBUN1, '3' GUBUN2, 40 FROM DUAL UNION ALL
SELECT 'y1' GUBUN1, '3' GUBUN2, 100 FROM DUAL UNION ALL
SELECT 'y3' GUBUN1, '3' GUBUN2, 100 FROM DUAL
)
SELECT MAX(GUBUN1) GUBUN1,
MAX(VAL1) VAL1,
MAX(GUBUN2) GUBUN2,
MAX(VAL2) VAL2,
MAX(VAL1) / MAX(VAL2) VAL
FROM (
SELECT ROWNUM + MOD(ROWNUM, 2) NUM, -- 2개씩 반복 된다고 했을시...
DECODE(MOD(ROWNUM, 2), 0, '', GUBUN1) GUBUN1,
DECODE(MOD(ROWNUM, 2), 0, '', VAL) VAL1,
DECODE(MOD(ROWNUM, 2), 0, GUBUN1, '') GUBUN2,
DECODE(MOD(ROWNUM, 2), 0, VAL, '') VAL2
FROM (
SELECT T1.* FROM T1
Order BY GUBUN2, SUBSTR(GUBUN1, -1), GUBUN1
)
)
GROUP BY NUM;
by 전문가되자
[2017.05.02 15:43:40]
답변 감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.