데이터베이스 질문과 답변
안녕하세요.
다중컬럼에서 합계 구하는쿼리 질문좀드리겠습니다.
아래 표는 데이터이고 01:사과, 02:귤, 03:수박, 04:포도 입니다.
| NO | FRUIT1 | FRUIT2 | FRUIT3 |
| 1 | 01,03 | 01,02 | 02,04 |
| 2 | 01 | NULL | NULL |
| 3 | 03,02 | 04 | NULL |
| 4 | 01,02,03 | NULL | 02 |
아래표처럼 원하는 결과값을 얻으려면 쿼리를 어떻게 만들어야하는지 답변좀 부탁드리겠습니다.
| 과일 | 수 | % |
| 사과 | 4 | 28.6 |
| 귤 | 5 | 35.7 |
| 수박 | 3 | 21.4 |
| 포도 | 2 | 14.3 |
| 합계 | 14 | 100 |
by 마농
[2022.07.14 13:14:33]
-- Oracle --
WITH fruit_data AS
(
SELECT 1 no, '01,03' fruit1, '01,02' fruit2, '02,04' fruit3 FROM dual
UNION ALL SELECT 2, '01' , '' , '' FROM dual
UNION ALL SELECT 3, '03,02' , '04', '' FROM dual
UNION ALL SELECT 4, '01,02,03', '' , '02' FROM dual
)
, fruit_code AS
(
SELECT '01' cd, '사과' nm FROM dual
UNION ALL SELECT '02', '귤' FROM dual
UNION ALL SELECT '03', '수박' FROM dual
UNION ALL SELECT '04', '포도' FROM dual
)
SELECT NVL(a.nm, '합계') nm
, COUNT(b.no) cnt
, ROUND(
RATIO_TO_REPORT(COUNT(b.no)) OVER(PARTITION BY GROUPING(a.cd))
* 100, 1) rat
FROM fruit_code a
LEFT OUTER JOIN fruit_data
UNPIVOT (fruit FOR gb IN (fruit1, fruit2, fruit3)) b
ON INSTR(b.fruit, a.cd) > 0
GROUP BY ROLLUP((a.cd, a.nm))
;
by 우리집아찌
[2022.07.14 13:46:35]
MYSQL 도 ORACLE에서 쓰는 분석함수 거의 다 되나보네요.
by 마농
[2022.07.14 14:11:24]
앗.. MySQL 이네요.
MySQL 은
UNPIVOT 안되고
ROLLUP 구문에 괄호 사용 안되고, GROUPING 안되고.
ratio_to_report 안되고(오류)
SUM(COUNT()) OVER() 사용도 안되고(오류는 안나지만 원하는 결과 안나옴)
추가 : SUM(COUNT()) OVER() 가 아예 안되는건 아니고 롤업일 경우에만 안됨 일반 그룹바이에서는 정상 동작.
by 마농
[2022.07.14 15:41:46]
-- MariaDB --
WITH fruit_data AS
(
SELECT 1 no, '01,03' fruit1, '01,02' fruit2, '02,04' fruit3
UNION ALL SELECT 2, '01' , null, null
UNION ALL SELECT 3, '03,02' , '04', null
UNION ALL SELECT 4, '01,02,03', null, '02'
)
, fruit_code AS
(
SELECT '01' cd, '사과' nm
UNION ALL SELECT '02', '귤'
UNION ALL SELECT '03', '수박'
UNION ALL SELECT '04', '포도'
)
SELECT IFNULL(SUBSTR(nm, 3), '합계') nm
, cnt
, ROUND(cnt / MAX(cnt) OVER() * 100, 1) rat
FROM (SELECT CONCAT(cd, nm) nm
, SUM( IFNULL(SIGN(INSTR(fruit1, cd)), 0)
+ IFNULL(SIGN(INSTR(fruit2, cd)), 0)
+ IFNULL(SIGN(INSTR(fruit3, cd)), 0)
) cnt
FROM fruit_code a
LEFT OUTER JOIN fruit_data b
ON 1=1
GROUP BY CONCAT(cd, nm)
WITH ROLLUP
) a
;
by 밍밍밍밍
[2022.07.14 15:46:50]
감사합니다!
by 신이만든지기
[2022.07.14 16:14:42]
SELECT ifnull(code, '합계') code, sum(cnt) cnt, sum(rate) rate
FROM (SELECT code, count(code) AS cnt, round(count(code) / sum(count(*)) OVER() * 100, 1) AS rate
FROM (SELECT NO, SUBSTRING_INDEX(SUBSTRING_INDEX(str, ',', no), ',', -1) code, str
FROM (SELECT concat(ifnull(fruit1, ''), ',', IFNULL(fruit2, ''), ',', ifnull(fruit3, '')) str FROM tmp) a,
(SELECT @no := @no + 1 AS NO
FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t1,
(SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t2,
(SELECT @no := 0) t3) b
WHERE no <= LENGTH(str) - LENGTH(REPLACE(str, ',', '')) + 1) aa
WHERE code != ''
GROUP BY code) bb
GROUP BY code WITH ROLLUP;
mysql은 어렵네요. ;;
) 버튼을 클릭하여 작성 하시면 됩니다.