SQLで商品のアンケート集計をしたい

Question

商品のアンケートで以下の中から商品の印象を
3つまで答えてもらいデータがカンマ区切りで登録されています。

1. 安い
2. 高い
3. 見た目が良い
4. また買いたい
5. うまい

id	name	answer
1	商品A	1,5
2	商品B	2,3,4
3	商品A	5
4	商品A	1,2,5
5	商品B	3,4,5
5	商品B	3
5	商品B	3

商品ごとにベスト3を集計したい

|id|name|best1|best2|best3|
|:--|:--:|--:|
|1|商品A|5|1|2|
|2|商品B|3|4|2|

いろいろとみなさまに質問させていただき勉強しています。
SQLは奥が深いなと思います。

Comments

[m.ts10806]

テーブル構造変えないと複雑化するばかりでパフォーマンス期待できない駄文になると思いますが、そもそも自身で何かやってみたことはあるのでしょうか。

[m.ts10806]

テーブル定義等提示されないと何とも言えませんし、
ただ、現状だとSQLでなくアプリケーション側で集計したほうがいいとしか。
SQLだけでやりたいなら構造変えるよう強くすすめます。

[gentaro]

> 3つまで答えてもらいデータがカンマ区切りで登録

そもそもこれがSQLにおけるバッドノウハウだという事を知っているのだろうか。

[takumil]

素人でどのようなテーブル構造が良いのか、、、すみません。

[m.ts10806]

「データベース 最適化」で検索

Answer 1

SUBSTRING_INDEX()と添え字用のインラインビューを用いで,区切りを縦方向に展開します。
次に、ランキングを求めます。
最後に、group_concat()でスコア順に並べます。

select name, group_concat(answer order by score desc) ranking
from (
    select name, answer, count(*) score
    from (
        select  name
              , substring_index(substring_index(answer, ',', n.i), ',', -1) as answer
        from  tbl
              cross join (
                select 1 as i 
                union all select 2 union all select 3 union all select 4 union all select 5
              ) n
    ) step1
    group by name, answer
) step2
group by name

Comments

[takumil]

回答いただきありがとうございました。

Answer 2

まず、商品ごとに、それぞれの選択肢に何票投票されたかを集計するViewを作成します。これでデータの持ち方が正規刑になるので応用が利きやすくなります。

CREATE OR REPLACE VIEW Poll(name, answer, total) AS
  SELECT name, answer, SUM(cnt)
  FROM (
              SELECT name, '1' AS answer, SIGN(INSTR(answer, '1')) AS cnt FROM Survey
    UNION ALL SELECT name, '2' AS answer, SIGN(INSTR(answer, '2')) AS cnt FROM Survey
    UNION ALL SELECT name, '3' AS answer, SIGN(INSTR(answer, '3')) AS cnt FROM Survey
    UNION ALL SELECT name, '4' AS answer, SIGN(INSTR(answer, '4')) AS cnt FROM Survey
    UNION ALL SELECT name, '5' AS answer, SIGN(INSTR(answer, '5')) AS cnt FROM Survey
  ) T
  GROUP BY name, answer;

あとは以下のようなSQLで実現できるかと思います。

SELECT
  name,
  MAX(CASE WHEN rnk = 0 THEN answer END) best1,
  MAX(CASE WHEN rnk = 1 THEN answer END) best2,
  MAX(CASE WHEN rnk = 2 THEN answer END) best3
FROM (
  SELECT 
    name, 
    answer, 
    (SELECT COUNT(*) FROM Poll P2 WHERE P1.name = P2.name AND P1.total < P2.total) AS RNK
  FROM Poll P1
) T
GROUP BY name

Comments

[takumil]

回答いただきありがとうございます。