데이터베이스 질문과 답변

다중컬럼 통계 0 6 1,179

by 밍밍밍밍 [MySQL] [2022.07.14 11:25:44]

안녕하세요.

다중컬럼에서 합계 구하는쿼리 질문좀드리겠습니다.

아래 표는 데이터이고 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]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 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]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- 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]
?
1
2
3
4
5
6
7
8
9
10
11
12
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은 어렵네요. ;;

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입