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バージョン8から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
![guest](/img/icon/icnUserSample.jpg)
回答2件
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2021/01/24 00:59
2021/01/24 01:31
2021/01/24 02:32
2021/01/24 02:38
2021/01/24 03:01
2021/01/24 06:24
2021/01/24 11:46