데이터베이스 질문과 답변
WITH A AS (
SELECT 1 PK_1, 1 PK_2, 'A' A_ID, '가' B_ID, 'T_1' T_ID, 'N' T_YN FROM DUAL
UNION ALL
SELECT 1 PK_1, 2 PK_2, 'A' A_ID, '가' B_ID, 'T_2' T_ID, 'N' T_YN FROM DUAL
UNION ALL
SELECT 1 PK_1, 3 PK_2, 'A' A_ID, '나' B_ID, 'T_3' T_ID, 'N' T_YN FROM DUAL
UNION ALL
SELECT 1 PK_1, 4 PK_2, 'A' A_ID, '나' B_ID, 'T_3' T_ID, 'N' T_YN FROM DUAL
UNION ALL
SELECT 2 PK_1, 5 PK_2, 'B' A_ID, '나' B_ID, 'T_4' T_ID, 'N' T_YN FROM DUAL
UNION ALL
SELECT 2 PK_1, 6 PK_2, 'B' A_ID, '나' B_ID, 'T_4' T_ID, 'Y' T_YN FROM DUAL
UNION ALL
SELECT 3 PK_1, 7 PK_2, 'B' A_ID, '나' B_ID, 'T_5' T_ID, 'Y' T_YN FROM DUAL
)
select * from a;
위와 같은 예시 데이터가 있다고 가정하고 질문 드립니다.
아래와 같은 결과를 도출하고자 합니다. T_RATE 값을 구하고자 합니다.
공식은 해당 row의 A_ID, B_ID 값으로 자기자신 테이블 전체를 뒤져서 그 갯수 와 T_YN = 'Y' 인 값을 확률(?)을 구하고자 합니다.
고수님들의 도움 부탁드립니다.
PK_1 PK_2 A_ID B_ID T_ID T_YN T_RATE
1 1 A 가 T_1 N ROUND(0/2, 0) = 0
1 2 A 가 T_2 N ROUND(0/2, 0) = 0
1 3 A 나 T_3 N ROUND(0/1, 0) = 0
1 4 A 다 T_3 N ROUND(0/1, 0) = 0
2 5 B 나 T_4 N ROUND(2/3, 0) = 1
2 6 B 나 T_4 Y ROUND(2/3, 0) = 1
3 7 B 나 T_5 Y ROUND(2/3, 0) = 1
WITH A AS (
SELECT 1 PK_1, 1 PK_2, 'A' A_ID, '가' B_ID, 'T_1' T_ID, 'N' T_YN FROM DUAL
UNION ALL
SELECT 1 PK_1, 2 PK_2, 'A' A_ID, '가' B_ID, 'T_2' T_ID, 'N' T_YN FROM DUAL
UNION ALL
SELECT 1 PK_1, 3 PK_2, 'A' A_ID, '나' B_ID, 'T_3' T_ID, 'N' T_YN FROM DUAL
UNION ALL
SELECT 1 PK_1, 4 PK_2, 'A' A_ID, '다' B_ID, 'T_3' T_ID, 'N' T_YN FROM DUAL
UNION ALL
SELECT 2 PK_1, 5 PK_2, 'B' A_ID, '나' B_ID, 'T_4' T_ID, 'N' T_YN FROM DUAL
UNION ALL
SELECT 2 PK_1, 6 PK_2, 'B' A_ID, '나' B_ID, 'T_4' T_ID, 'Y' T_YN FROM DUAL
UNION ALL
SELECT 3 PK_1, 7 PK_2, 'B' A_ID, '나' B_ID, 'T_5' T_ID, 'Y' T_YN FROM DUAL
)
SELECT PK_1
, PK_2
, A_ID
, B_ID
, T_ID
, T_YN
, 'ROUND('
|| COUNT(CASE WHEN T_YN = 'Y' THEN 1 END ) OVER(PARTITION BY A_ID , B_ID , T_YN ) || '/'
|| COUNT(*) OVER(PARTITION BY A_ID , B_ID ) || ',0) = 0' AS T_RATE -- <= 0과 1의 구분을 모르겠어요.
FROM A
제가 글을 좀 이해하기 난해하게 작성한거 같네요. 죄송.
T_RATE 값은 저렇게 문자를 찍어달라는게 아니고.. 비율값(?)을 소수점 첫째자리에서 반올림한다는 의미였어요. ^^;;
아래 데이터 결과를 보면 다른 행들은 맞는데...
5번째 행에 YN_CNT 값이 2가 되어야.. 제가 구하고자 하는 값입니다. 그리고 마지막 최종값은 YN_CNT / TOT_CNT * 100 의 몫을 구하고자 하는거에요.
그런데 제가 예시한 데이터는 예시라 데이터가 7개 밖에 안 되는데.. 실제로는 데이터 사이즈가 한 ROW에 연결되는 TOT_CNT 값이 700개 이상 나옵니다.
그래서 속도도 고려를 해야 할 것 같아요.
SUB QUERY로 구현을 해 보았는데.. 속도가 너무 느리더라구요. ㅠㅠ
2 5 B 나 T_4 N 3 0
WITH A AS (
SELECT 1 PK_1, 1 PK_2, 'A' A_ID, '가' B_ID, 'T_1' T_ID, 'N' T_YN FROM DUAL
UNION ALL
SELECT 1 PK_1, 2 PK_2, 'A' A_ID, '가' B_ID, 'T_2' T_ID, 'N' T_YN FROM DUAL
UNION ALL
SELECT 1 PK_1, 3 PK_2, 'A' A_ID, '나' B_ID, 'T_3' T_ID, 'N' T_YN FROM DUAL
UNION ALL
SELECT 1 PK_1, 4 PK_2, 'A' A_ID, '다' B_ID, 'T_3' T_ID, 'N' T_YN FROM DUAL
UNION ALL
SELECT 2 PK_1, 5 PK_2, 'B' A_ID, '나' B_ID, 'T_4' T_ID, 'N' T_YN FROM DUAL
UNION ALL
SELECT 2 PK_1, 6 PK_2, 'B' A_ID, '나' B_ID, 'T_4' T_ID, 'Y' T_YN FROM DUAL
UNION ALL
SELECT 3 PK_1, 7 PK_2, 'B' A_ID, '나' B_ID, 'T_5' T_ID, 'Y' T_YN FROM DUAL
)
SELECT PK_1
, PK_2
, A_ID
, B_ID
, T_ID
, T_YN
, COUNT(*) OVER(PARTITION BY A_ID , B_ID ) as TOT_CNT
, COUNT(CASE WHEN T_YN = 'Y' THEN 1 END) OVER(PARTITION BY A_ID , B_ID , T_YN) as YN_CNT
FROM A;
-- 5째행이 2 이라시면
WITH A AS (
SELECT 1 PK_1, 1 PK_2, 'A' A_ID, '가' B_ID, 'T_1' T_ID, 'N' T_YN FROM DUAL
UNION ALL
SELECT 1 PK_1, 2 PK_2, 'A' A_ID, '가' B_ID, 'T_2' T_ID, 'N' T_YN FROM DUAL
UNION ALL
SELECT 1 PK_1, 3 PK_2, 'A' A_ID, '나' B_ID, 'T_3' T_ID, 'N' T_YN FROM DUAL
UNION ALL
SELECT 1 PK_1, 4 PK_2, 'A' A_ID, '다' B_ID, 'T_3' T_ID, 'N' T_YN FROM DUAL
UNION ALL
SELECT 2 PK_1, 5 PK_2, 'B' A_ID, '나' B_ID, 'T_4' T_ID, 'N' T_YN FROM DUAL
UNION ALL
SELECT 2 PK_1, 6 PK_2, 'B' A_ID, '나' B_ID, 'T_4' T_ID, 'Y' T_YN FROM DUAL
UNION ALL
SELECT 3 PK_1, 7 PK_2, 'B' A_ID, '나' B_ID, 'T_5' T_ID, 'Y' T_YN FROM DUAL
)
SELECT PK_1
, PK_2
, A_ID
, B_ID
, T_ID
, T_YN
, COUNT(*) OVER(PARTITION BY A_ID , B_ID ) as TOT_CNT
, COUNT(CASE WHEN T_YN = 'Y' THEN 1 END) OVER(PARTITION BY A_ID ) as YN_CNT
, ROUND(COUNT(CASE WHEN T_YN = 'Y' THEN 1 END ) OVER(PARTITION BY A_ID , B_ID , T_YN ) /
COUNT(*) OVER(PARTITION BY A_ID ) ,0) AS T_RATE
FROM A
감사합니다. 제가 설명이 좀 엉성해서...
최종 원하는 결과는 아래와 같이 구하면 될거 같네요. 속도 문제는 INDEX hint로 해결을 했어요.
실제 쿼리는 샘플 데이터 자체가 Where 조건이 있는 쿼리라서요.
WITH A AS (
SELECT 1 PK_1, 1 PK_2, 'A' A_ID, '가' B_ID, 'T_1' T_ID, 'N' T_YN FROM DUAL
UNION ALL
SELECT 1 PK_1, 2 PK_2, 'A' A_ID, '가' B_ID, 'T_2' T_ID, 'N' T_YN FROM DUAL
UNION ALL
SELECT 1 PK_1, 3 PK_2, 'A' A_ID, '나' B_ID, 'T_3' T_ID, 'N' T_YN FROM DUAL
UNION ALL
SELECT 1 PK_1, 4 PK_2, 'A' A_ID, '다' B_ID, 'T_3' T_ID, 'N' T_YN FROM DUAL
UNION ALL
SELECT 2 PK_1, 5 PK_2, 'B' A_ID, '나' B_ID, 'T_4' T_ID, 'N' T_YN FROM DUAL
UNION ALL
SELECT 2 PK_1, 6 PK_2, 'B' A_ID, '나' B_ID, 'T_4' T_ID, 'Y' T_YN FROM DUAL
UNION ALL
SELECT 3 PK_1, 7 PK_2, 'B' A_ID, '나' B_ID, 'T_5' T_ID, 'Y' T_YN FROM DUAL
)
SELECT PK_1
, PK_2
, A_ID
, B_ID
, T_ID
, T_YN
, COUNT(*) OVER(PARTITION BY A_ID , B_ID ) as TOT_CNT
, COUNT(CASE WHEN T_YN = 'Y' THEN 1 END) OVER(PARTITION BY A_ID, B_ID ) as YN_CNT
, ROUND(COUNT(CASE WHEN T_YN = 'Y' THEN 1 END) OVER(PARTITION BY A_ID , B_ID) /
COUNT(*) OVER(PARTITION BY A_ID, B_ID) ,0) AS T_RATE
FROM A
) 버튼을 클릭하여 작성 하시면 됩니다.