안녕하세요!
쿼리 작성 중 막혀서 도움을 요청하고자 글을 작성합니다 ㅠㅠ
컬럼에 ' , ' 콤마로 구성된 데이터 가 있는 테이블이 있습니다.
ex)
with TABLE1 AS
(
SELECT 'A' AS ID , '1' AS SEQ, '1,2,3,4,5,6' AS DATA FROM DUAL
UNION
SELECT 'A' AS ID , '2' AS SEQ,'2,3,4,5,6,7' AS DATA FROM DUAL
UNION
SELECT 'A' AS ID ,'3' AS SEQ,'4,3,2,3,1,2' AS DATA FROM DUAL
UNION
SELECT 'B' AS ID ,'1' AS SEQ,'-1,-2,-3' AS DATA FROM DUAL
UNION
SELECT 'B' AS ID ,'2' AS SEQ,'-4,-5,-6' AS DATA FROM DUAL
UNION
SELECT 'B' AS ID ,'3' AS SEQ,'-3,-4,-2' AS DATA FROM DUAL
)
SELECT * FROM TABLE1
ID | SEQ | DATA |
A | 1 | 1,2,3,4,5,6 |
A | 2 | 2,3,4,5,6,7 |
A | 3 | 4,3,2,3,1,2 |
B | 1 | -1,-2,-3 |
B | 2 | -4,-5,-6 |
B | 3 | -3,-4,-2 |
1. 예제는 콤바 개수가 5개 또는 3개 이지만, ID 'A' 에 콤마가 실제로는 600~700개 입니다..
2. 각 ID의 콤마개수는 같습니다. A는 629개, B는 324개
이 데이타를 가공을 해서 결과적으로 얻고자 하는 것은,
각 ID 의 평균 값을 얻고자 합니다!
콤마로 변환한 아래의 결과입니다.
ID | DATA |
A | 3,5,6,3,4,2 |
B | -4,-3,-1 |
(위의 'DATA' 컬럼의 값은 평균값이 아닌 임의의 숫자를 입력했습니다)
부탁드립니다 ㅠㅠ
WITH t AS ( SELECT 'A' id, 1 seq, '1,2,3,4,5,6' data FROM dual UNION ALL SELECT 'A', 2, '2,3,4,5,6,7' FROM dual UNION ALL SELECT 'A', 3, '4,3,2,3,1,2' FROM dual UNION ALL SELECT 'B', 1, '-1,-2,-3' FROM dual UNION ALL SELECT 'B', 2, '-4,-5,-6' FROM dual UNION ALL SELECT 'B', 3, '-3,-4,-2' FROM dual ) SELECT id , LISTAGG(v, ',') WITHIN GROUP(ORDER BY lv) data FROM (SELECT id , lv , ROUND(AVG(REGEXP_SUBSTR(data, '[^,]+', 1, lv)), 2) v FROM t , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 999) WHERE lv <= LENGTH(data) - LENGTH(REPLACE(data, ',')) + 1 GROUP BY id, lv ) GROUP BY id ;