-- 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)) ;
-- 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 ;
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은 어렵네요. ;;