【MySQL】DBをSQLite3→MySQLとしたときの修正方法

Question

railsでアプリを作成しております。
DBをSQLite3からMySQLへ変更し、SQL文を修正していたところ、SQL文の書き方で詰まってしまったため、ご教授いただければ幸いです。

実現したいこと（MySQLで実現したい）

SQLite用に記述したもの（下記）をMySQL用へ修正したい。

テーブル構成

• chosenitemsテーブル

id	session_id	item_id
1	1	2
2	1	3
2	1	2
2	1	2
2	1	5
3	1	7
4	1	4
5	1	5

• itemsテーブル

id	name	image	competition_id
1	a	image1	1
2	b	image2	1
2	c	image2	1
2	d	image2	1
2	e	image5	2
3	f	image9	2
4	g	image4	2
5	h	image5	2

取得したいデータ

competition_idでグルーピングしてから、
item_id内で同じ数字をカウントしていき、最も多かったitem_idとその個数を抽出。

例えば、
competition_idが1の場合は、item_idは2が最多で、その個数は3
competition_idが2の場合は、item_idは5が最多で、その個数は2
competition_idが3の場合は、・・・
competition_idが4の場合は、・・・

SQLiteで記述した場合（DBにSQLiteを使っていたとき）

app/controllers/competitions_controller.rb

winners_in_index =
    "SELECT *
        FROM (
            SELECT
                *,
                RANK() OVER (PARTITION BY competition_id ORDER BY COUNT DESC) AS rank
                FROM (
                    SELECT
                        items.competition_id,
                        items.id,
                        items.image,
                        items.name,
                        count(*) AS count
                    FROM chosenitems INNER JOIN items ON chosenitems.item_id = items.id
                    GROUP BY items.competition_id, items.id
            ) AS t
        ) AS tt
    WHERE rank = 1
    GROUP BY competition_id"
@winners_in_index= ActiveRecord::Base.connection.select_all(winners_in_index).to_ary

試したこと

このRANK関数はないけど、MySQLのみでランキング表示させたい
記事を参考にすすめております。

winners_in_index =
    "SELECT *
        FROM (
               SET @rank=0,@before_line_count=0;
               SELECT
               CASE WHEN @before_line_count=count THEN @rank
               ELSE @rank:=@rank+1
               END AS rank,
               * ,
               @before_line_count:=count AS count
                FROM (
                    SELECT
                        items.competition_id,
                        items.id,
                        items.image,
                        items.name,
                        count(*) AS count
                    FROM chosenitems INNER JOIN items ON chosenitems.item_id = items.id
                    GROUP BY items.competition_id, items.id
            ) AS t
        ) AS tt
    WHERE rank = 1
    GROUP BY competition_id"
@winners_in_index= ActiveRecord::Base.connection.select_all(winners_in_index).to_ary

このように記述した場合、下記のエラーが発生してしまいます。

Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET @rank=0,@before_line_count=0; SELECT CAS' at line 2

2021/01/24 06:57追記

別テーブル（itemsテーブル）に対して実験的に下記SQLを試したところ

• itemsテーブル

id	name	points
1	a	image1
2	b	image2
2	c	image2
2	d	image2
2	e	image5
3	f	image9
4	g	image4
5	h	image5
（続きがあります）
```
SET @rnk=0,@before_line_points=0; SELECT CASE WHEN @before_line_points = points THEN @rnk ELSE @rnk:=@rnk+1 END AS rnk,name,@before_line_points:=points AS points FROM items ORDER BY points desc;
```
以下の結果になりました。
```
Query OK, 0 rows affected (0.00 sec)

+------+-----------------+--------+
| rnk | name | points |
+------+-----------------+--------+
| 1 | book9 | 99 |
| 2 | book10 | 98 |
| 3 | color10 | 97 |
| 4 | color5 | 96 |
| 4 | movie2 | 96 |
| 5 | book4 | 94 |
| 5 | book3 | 94 |

ここまでは、合っていると推測しました。
そこで、このSQLを組みました。

SET @rnk=0,@before_line_count=0; SELECT * FROM (SELECT CASE WHEN @before_line_count = count THEN @rnk ELSE @rnk:=@rnk+1 END AS rnk,,@before_line_count:=count AS count FROM (SELECT items.competition_id,items.id,items.image,items.name,count() AS count FROM chosenitems INNER JOIN items ON chosenitems.item_id = items.id GROUP BY items.competition_id,items.id) AS t ORDER BY count desc) AS tt;

すると、以下のエラーがでました。

Query OK, 0 rows affected (0.00 sec)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*,@before_line_count:=count AS count FROM (SELECT items.competition_id,items.id,' at line 1

どなたか、わかる方がいらっしゃいましたら、ご教授いただければ幸いです。

## 2021/01/24 09:53追記
MySQLバージョン８からRANK（）関数が使えるようになっていましたが、エイリアスにrankとすると、キーワード関係でエラーが発生していました。
そこで、下記のように修正することで、正しく動作しました。

winners_in_index =
"SELECT
competition_id,
MIN(id) AS id,
MIN(image) AS image,
MIN(name) AS name,
MIN(count) AS count,
MIN(rnk) AS rnk
FROM
(SELECT
,
RANK() OVER (PARTITION BY competition_id ORDER BY COUNT DESC) rnk
FROM (SELECT
items.competition_id,
items.id,
items.image,
items.name,
count() AS count
FROM
chosenitems INNER JOIN items ON chosenitems.item_id = items.id
GROUP BY items.competition_id,items.id) AS t) AS tt
WHERE rnk = 1
GROUP BY competition_id;"

# 環境
macOS BigSur
ruby 2.7.0
Rails 6.1.1
mysql  Ver 8.0.23

Answer 1

SQL文中に別なセンテンスが含まれているからです。

              SET @rank=0,@before_line_count=0;

select文中で変数初期化するのではなく、実行前に処理するのです。

追記

元々のSQLでもそうですが、求めたい項目は確定していません。
明確なのは、最終的にGroup byしている項目(competition_id)だけで、それ以外は最初に抽出されている項目にすぎません。
具体的に取り出したい結果も明示してみて下さい。

因みにMySQLのバージョンが8以降なら、RANK()は使用できるはずですが、なぜ変更するのですか？
単にエラーになったからという事であれば、Group byに関するものかもしれません。

Comments

[katahik]

sazi 様
質問文に追記しましたが、MySQLバージョン8からRANK（）関数が使えるようになっていましたが、エイリアスをrankにすることでエラーが出ていました。
一応解決には至ったのですが、
```
SET @rnk=0,@before_line_count=0; SELECT * FROM (SELECT CASE WHEN @before_line_count = count THEN @rnk ELSE @rnk:=@rnk+1 END AS rnk,*,@before_line_count:=count AS count FROM (SELECT items.competition_id,items.id,items.image,items.name,count(*) AS count FROM chosenitems INNER JOIN items ON chosenitems.item_id = items.id GROUP BY items.competition_id,items.id) AS t ORDER BY count desc) AS tt;
```
このSQL文の正しい書き方がわかっておりません。
ご指摘いただいたとおり、最終的に求めたい項目を＊でなくて、個別に指定するということでしょうか？
お忙しいところ申し訳ありませんが、ご教授いただければ嬉しいです。

[sazi]

そもそもGroup by の指定がある場合、select　項目に指定できるのは、group byした項目か集計関数のみです。
SQLiteはエラーに関してそれが緩いのです。MySQLもONLY_FULL_GROUP_BYを無効にすると同じように緩くはできます。
だけどその場合、group byしていない項目はDBMSがレスポンスに一番影響しないものを拾っただけの値です。
なので、*ではなく明確に指定しましょうという事をコメントしています。
明確にされれば、変数を使用しないSQLもアドバイスはできます。

[katahik]

２点質問させてください。

【１点目】
>> そもそもGroup by の指定がある場合、select　項目に指定できるのは、group byした項目か集計関数のみです。
このことについて、
自分が書いたコードに関しての質問で恥ずかしいのですが、コード文内で、
```
SELECT
items.competition_id,
items.id,
items.image,
items.name,
count(*) AS count
FROM
chosenitems INNER JOIN items ON chosenitems.item_id = items.id
GROUP BY items.competition_id,items.id
```
という箇所があります。
ここではなぜ、GROUP BY句で指定しているitems.competition_idとitems.id以外のものをSELECT句に記述しているのに、正常に作動するのでしょうか？
以下、抽出内容
+----------------+----+---------------------------+----------------+-------+
| competition_id | id | image | name | count |
+----------------+----+---------------------------+----------------+-------+
| 1 | 1 | monster1.jpeg | monster1 | 4 |
| 1 | 2 | monster2.jpeg | monster2 | 1 |
| 1 | 4 | monster4.jpeg | monster4 | 1 |
| 3 | 11 | monster3.jpeg | monster3 | 1 |

[katahik]

【２点目】
>> なので、*ではなく明確に指定しましょうという事をコメントしています。明確にされれば、変数を使用しないSQLもアドバイスはできます。

このことについて、
最終的に抽出したい項目は、
competition_id,id,image,name,count,rnkです。

抽出例としては、以下の通りです。
+----------------+------+---------------------------+----------------+-------+------+
| competition_id | id | image | name | count | rnk |
+----------------+------+---------------------------+----------------+-------+------+
| 1 | 1 | monster1.jpeg | monster1 | 4 | 1 |
| 3 | 11 | monster3.jpeg | monster3 | 1 | 1 |
| 4 | 14 | monster2.jpeg | monster2 | 2 | 1 |

この場合、既存の以下のコードをどのように修正したら、上記の抽出例のような抽出ができるのでしょうか？
変数をしようしないSQLとはどういったことでしょうか？
```
SELECT items.competition_id,items.id,items.image,items.name,count(*) AS count FROM chosenitems INNER JOIN items ON chosenitems.item_id = items.id GROUP BY items.competition_id,items.id ORDER BY count desc;
```

[sazi]

> GROUP BY句で指定しているitems.competition_idとitems.id以外のものをSELECT句に記述しているのに、正常に作動するのでしょうか？

ONLY_FULL_GROUP_BYが無効に設定されているのでしょう。

> competition_idでグルーピングしてから、item_id内で同じ数字をカウントしていき、最も多かったitem_idとその個数を抽出。
ここまででitemsテーブルを特定できる情報は、competition_idとidです。
そこから、
> 最終的に抽出したい項目は、competition_id,id,image,name,count,rnkです。

に関して、itemsテーブルのどのレコードなのかを限定はされていません。
なので、selectできているimage,nameは適当でDBMS任せになっています。

[katahik]

GROUP BY句で指定したもの以外（image,name）をSELECTに指定してもエラーにならなかったのは、ONLY_FULL_GROUP_BYが無効に設定されているから。

また、
最終的に抽出したい項目である、competition_id,id,image,name,count,rnkのうち、imageとnameにおいては、指定していないため、DB側でよしなに取得している。
ただし、DB任せになっているため、取得したいデータをとってこない可能性もある。
ということでしょうか？

[sazi]

そうですね。

Answer 2

プログラムにSQLゴリゴリと書いているならやることは1つ。

プログラムのSQLを書き換えるのではなく、
MySQLで動くSQLをきちんと作り、想定の結果を得られてからプログラムで使うこと。

あとは結局起きているのは構文エラーなので、
ドキュメントで機能が正しく使って組めているのか確認することです。

一気に組んでしまうのではなく、
小ブロックごとに組んで確認します。
これにより問題がどこで起きるのかが見えやすくなります。

あと、大抵のプログラミング言語で、1回にexecuteできるSQLは1本です。
SET @rank=0,@before_line_count=0;
;が入ってるので切れてますね。

Comments

[katahik]

m.ts10806 様
親身になって考えていただきありがとうございました。
MySQLバージョン８からRANK（）関数が使えるようになっていましたが、エイリアスにrankとすると、キーワード関係でエラーが発生していました。そこを修正することで、解決することができました。

・ターミナルで試してからプログラムに使うこと
・小ブロックごとに組むこと

この２つを胸に刻みました。

[m.ts10806]

SQL自体を覚えるのは必須ですが、フレームワーク使ってるなら、なるべくフレームワークの機能だけで組めないか検討してください。

[katahik]

>> フレームワーク使ってるなら、なるべくフレームワークの機能だけで組めないか検討してください。
今回でいうとrailsなので、ActiveRecordの機能だけで組めないか検討するということでしょうか？
簡単なもの（findとかwhereだけなど）なら、ActiveRecordでやってきましたが、今回のように複雑（自分の中では）なものは、生SQLをActiveRecordに埋め込むことを知ってからは、このように記述していってしまっています、、、

[m.ts10806]

DBのバージョンアップや今回みたいな切り替えで使えなくなる可能性も高いですし、あまり依存した書き方は良くないかなと。
機能使ったほうがデータが扱いやすくはなりますし。

[katahik]

なるほど、覚えておきます。
ありがとうございます。

[m.ts10806]

まぁあくまで「優先事項として検討してください」というレベルではあります。