DB (mysql)에서 여러 컬럼에서 가장 빈도가 많은 데이터 가져오기 조언 구합니다 ㅠㅠ

Question

namecol1col2col3col4col5tomACDjohnBCtomABCEjohnACD이런식의 테이블이 있을 때 name을 제외한 컬럼 데이터의 빈도수를 비교하여 순위를 매기고 싶습니다.tom (A 2회, B 1회, C 2회, D 1회, E 1회) &#61; Tom A C B D E john (A 1회, B 1회, C 2회, D 1회, E 0회) &#61; john C, A, B, D (E는 0회여서 제외) 이런 식으로.. name으로 그룹을 묶고, 각 col 별로 데이터의 갯수를 비교하여 나열하는 쿼리를 짜고 싶습니다. 결과는 name top1 top2 top3 top4 top5 tom A C B D E이런 방식으로 하려는데 혹시 쿼리 팀 주실 분 계실까요?? ㅠㅠ 데이터가 숫자가 아니고 문자다보니 max 도 사용이 안되고....각 컬럼을 하나의 컬럼으로 합쳐서 count가 많은 순으로 ORDER By 하는 방식은 해보았습니다만..그럴 경우 데이터가 하나의 컬럼으로 나와버리는데, 저는 저렇게 행의 방식으로 데이터를 출력하고 싶습니다ㅠㅠ그리고 위 방법으로 할 경우 name별로 각각 쿼리를 돌려하는 것 같아(아닐수도 있는데 제 머리로는 그렇게 생각됩니다ㅠ)좋지 않은 방식인듯 해서요..혹시 저런식으로 여러 컬럼에서 빈도 비교하는 쿼리 사용해보신 분 계시면 조언 간곡히 부탁드립니다!

Clickin · Answer

서브쿼리로 한땀한땀 짜는 방법밖에 안떠오르네요실행계획은 내다버리긴 했지만 결과는 나올겁니다select (select name from exam group by name order by count(name) desc limit 1) as name , (select col1 from exam group by col1 order by count(col1) desc limit 1) as col1 , (select col2 from exam group by col2 order by count(col2) desc limit 1) as col2 , (select col3 from exam group by col3 order by count(col3) desc limit 1) as col3 , (select col4 from exam group by col4 order by count(col4) desc limit 1) as col4 , (select col5 from exam group by col5 order by count(col5) desc limit 1) as col5 fiddle https://www.db-fiddle.com/f/mUL9ruTmkqcJvqkkSGAow8/0

작별왕바이킹 · Answer

말씀하신 구조로 데이터 저장 안될 것 같네요.name ~ col5 까지 어떤게 PK 인가요? Pk를 구성할 수가 없어요.id, name, grade 이렇게 3개 컬럼으로 구성하고 row로 밀어 넣어야 합니다.1, tom, A 2, tom, C 3, tom, D 4, john, B 5, john, C각 컬럼을 하나의 컬럼으로 합쳐서 count가 많은 순으로 ORDER By 하는 방식은 해보았습니다만..group by 로 몇 개인지 계산한 후 행/열 뒤집어 버리면 될 것 같네요.CREATE TABLE gsgipadm.newtable ( idx int NULL, name varchar NULL, grade varchar NULL, CONSTRAINT newtable_pk PRIMARY KEY (idx) ); INSERT INTO gsgipadm.newtable (idx, &#34;name&#34;, grade) VALUES(0, 'tom', 'A'), (1, 'tom', 'C'), (2, 'tom', 'D'), (3, 'tom', 'A'), (4, 'tom', 'B'), (5, 'tom', 'C'), (6, 'tom', 'E'), (7, 'john', 'B'), (8, 'john', 'C'), (9, 'john', 'A'), (10, 'john', 'C'), (11, 'john', 'D') ; select z.name , string_agg(z.top1, '') as top1 , string_agg(z.top2, '') as top2 , string_agg(z.top3, '') as top3 , string_agg(z.top4, '') as top4 , string_agg(z.top5, '') as top5 from ( select r.name , max(case when rnk &#61; '1' then grade end) as top1 , max(case when rnk &#61; '2' then grade end) as top2 , max(case when rnk &#61; '3' then grade end) as top3 , max(case when rnk &#61; '4' then grade end) as top4 , max(case when rnk &#61; '5' then grade end) as top5 from ( select *, ROW_NUMBER () OVER (PARTITION BY a.name ORDER BY a.cnt desc, a.grade asc) as rnk from ( select name, grade, count(1) as cnt from newtable n group by name, grade ) a order by name, grade asc ) r group by r.name, r.rnk ) z group by z.name order by z.name desc;서브 쿼리 하나씩 실행하면서 분석해보시면 됩니다.

으악 또 · Answer

저라면 그냥 구릅 바이로카운트 겟수 구한다음 자바에서 컬럼 나열 할꺼 같아요아님 스크립트에서 라던지

치즈감자찌개 · Answer

일단 데이터 구하고 스크립트로 따로 컬럼끼리 sort 하는게 낫지않을까싶네요SELECT `name`, count(case when a='a' then 1 END) AS cnt_a, count(case when b='b' then 1 END) AS cnt_b, count(case when c='c' then 1 END) AS cnt_c, count(case when d='d' then 1 END) AS cnt_d, count(case when e='e' then 1 END) AS cnt_e FROM Test GROUP BY Name

DB (mysql)에서 여러 컬럼에서 가장 빈도가 많은 데이터 가져오기 조언 구합니다 ㅠㅠ

4개의 답변

기술 목록