こんちには。
サーバーサイドがおっさんばかりなのはフロントエンドについていくだけの精神力が無くなったからだって話に戦慄しているエンジニアの長谷川です。
はじめてのエントリーなんで最近MySQLで試してみた内容をメモ程度に投稿してみます。
連番を振る
まずMySQLで連番を振りたい場合、ユーザー定義変数を使ってインクリメントさせてできます。
元データ
Ships
| id | name |
|---|---|
| 2001 | 飛竜 |
| 2002 | 蒼龍 |
| 2003 | 加賀 |
| 2004 | 大鳳 |
これに対してこうすると
SELECT id, name, @n:=@n+1 AS no FROM Ships, (SELECT @n:=0) m;
結果
| id | name | no |
|---|---|---|
| 2001 | 飛竜 | 1 |
| 2002 | 蒼龍 | 2 |
| 2003 | 加賀 | 3 |
| 2004 | 大鳳 | 4 |
元のデータから引き続いて連番を振る
元のテーブル参照して一旦仮テーブルに連番振りたいときは結合します。
Ships
| id | name | no |
|---|---|---|
| 1001 | 三笠 | 1 |
| 1002 | 長門 | 2 |
| 1003 | 陸奥 | 3 |
ShipsTemp
| id | name |
|---|---|
| 2001 | 飛竜 |
| 2002 | 蒼龍 |
| 2003 | 加賀 |
| 2004 | 大鳳 |
Shipsが元テーブルでそのnoの最大値3から連番を振ります。
SELECT id, name, @n:=@n+1 AS no FROM ShipsTemp, (SELECT @n:=max(no) FROM Ships) m;
結果
| id | name | no |
|---|---|---|
| 2001 | 飛竜 | 4 |
| 2002 | 蒼龍 | 5 |
| 2003 | 加賀 | 6 |
| 2004 | 大鳳 | 7 |
グループごとに連番を振る
ここまではよかったんですが、今回はデータ全体で連番ではなくグループごとに連番を振りたかったんですね。
Ships
| id | name | no | class |
|---|---|---|---|
| 1001 | 金剛 | 1 | 戦艦 |
| 1002 | 比叡 | 2 | 戦艦 |
| 1003 | 榛名 | 3 | 戦艦 |
| 1004 | 霧島 | 4 | 戦艦 |
| 1005 | 筑摩 | 1 | 重巡洋艦 |
| 1006 | 利根 | 2 | 重巡洋艦 |
| 1007 | 高雄 | 3 | 重巡洋艦 |
| 1008 | 長良 | 1 | 軽巡洋艦 |
| 1009 | 阿武隈 | 2 | 軽巡洋艦 |
| 1010 | 葛城 | 1 | 航空母艦 |
ShipsTemp
| id | name | class |
|---|---|---|
| 2001 | 大和 | 戦艦 |
| 2002 | 飛竜 | 航空母艦 |
| 2003 | 足柄 | 重巡洋艦 |
| 2004 | 鈴谷 | 軽巡洋艦 |
| 2005 | 武蔵 | 戦艦 |
| 2006 | 蒼龍 | 航空母艦 |
| 2007 | 妙高 | 重巡洋艦 |
| 2008 | 熊野 | 軽巡洋艦 |
| 2009 | 加賀 | 航空母艦 |
| 2010 | 那智 | 重巡洋艦 |
| 2011 | 大鳳 | 航空母艦 |
これに対して結果が以下のようになるクエリーがないものか
| id | name | no | class |
|---|---|---|---|
| 2001 | 大和 | 5 | 戦艦 |
| 2005 | 武蔵 | 6 | 戦艦 |
| 2002 | 飛竜 | 2 | 航空母艦 |
| 2006 | 蒼龍 | 3 | 航空母艦 |
| 2009 | 加賀 | 4 | 航空母艦 |
| 2011 | 大鳳 | 5 | 航空母艦 |
| 2004 | 鈴谷 | 3 | 軽巡洋艦 |
| 2008 | 熊野 | 4 | 軽巡洋艦 |
| 2003 | 足柄 | 4 | 重巡洋艦 |
| 2007 | 妙高 | 5 | 重巡洋艦 |
| 2010 | 那智 | 6 | 重巡洋艦 |
これはさすがにSQLじゃなくて一旦SELECTで取ってきてプログラムでループで回すかストアドプロシージャ使わないと無理だろうとなと思ったんですが調べてみました。
とりあえずGROUP BY
無理でしょうけどとりあえずGROUP BYして連結してみる。
SELECT id, t.name, @n:=@n+1 AS no, t.class FROM ShipsTemp t INNER JOIN ( SELECT @n:=max(no), class FROM Ships GROUP BY class ) m ON m.class = t.class ORDER BY t.class, id;
結果
| id | name | no | class |
|---|---|---|---|
| 2001 | 大和 | 4 | 戦艦 |
| 2005 | 武蔵 | 5 | 戦艦 |
| 2002 | 飛竜 | 6 | 航空母艦 |
| 2006 | 蒼龍 | 7 | 航空母艦 |
| 2009 | 加賀 | 8 | 航空母艦 |
| 2011 | 大鳳 | 9 | 航空母艦 |
| 2004 | 鈴谷 | 10 | 軽巡洋艦 |
| 2008 | 熊野 | 11 | 軽巡洋艦 |
| 2003 | 足柄 | 12 | 重巡洋艦 |
| 2007 | 妙高 | 13 | 重巡洋艦 |
| 2010 | 那智 | 14 | 重巡洋艦 |
どうやら最初に3はセットされるけどその後はただインクリメントされるようです。
グループ別最大値を取得してカウンターの値を足す
最大値とカウンターを分ければいいんじゃね？
SELECT id, t.name, max_no+@n AS no, t.class, @n:=@n+1 FROM ShipsTemp t INNER JOIN ( SELECT max(no) AS max_no, class FROM Ships GROUP BY class ) m ON m.class = t.class, (SELECT @n:=1) n ORDER BY t.class, id;
結果
| id | name | no | class | @n:=@n+1 |
|---|---|---|---|---|
| 2001 | 大和 | 5 | 戦艦 | 2 |
| 2005 | 武蔵 | 6 | 戦艦 | 3 |
| 2002 | 飛竜 | 4 | 航空母艦 | 4 |
| 2006 | 蒼龍 | 5 | 航空母艦 | 5 |
| 2009 | 加賀 | 6 | 航空母艦 | 6 |
| 2011 | 大鳳 | 7 | 航空母艦 | 7 |
| 2004 | 鈴谷 | 9 | 軽巡洋艦 | 8 |
| 2008 | 熊野 | 10 | 軽巡洋艦 | 9 |
| 2003 | 足柄 | 12 | 重巡洋艦 | 10 |
| 2007 | 妙高 | 13 | 重巡洋艦 | 11 |
| 2010 | 那智 | 14 | 重巡洋艦 | 12 |
カテゴリーごとの最大値は反映してるけど、カウンターは上昇し続けてます。
それはそうだ、変数はいつも一つ！
重複がなけりゃいいーっていう問題ならこれでも……。
グループが変わったところでカウンターをリセット
ならば変数を2つにしてカウンターが変わったらカウンターをリセットすればいいんじゃね？
SELECT id, t.name, @c AS old_class, CASE WHEN @c<>t.class THEN @n:=1 ELSE @n:=@n+1 END AS counter, max_no+@n AS new_no, @c:=t.class AS new_class FROM ShipsTemp t INNER JOIN ( SELECT max(no) AS max_no, class FROM Ships GROUP BY class ) m ON m.class = t.class, (SELECT @n:=1, @c:='') c ORDER BY t.class, id;
結果
| id | name | old_class | counter | new_no | new_class |
|---|---|---|---|---|---|
| 2001 | 大和 | 1 | 5 | 戦艦 | |
| 2005 | 武蔵 | 戦艦 | 2 | 6 | 戦艦 |
| 2002 | 飛竜 | 戦艦 | 1 | 2 | 航空母艦 |
| 2006 | 蒼龍 | 航空母艦 | 2 | 3 | 航空母艦 |
| 2009 | 加賀 | 航空母艦 | 3 | 4 | 航空母艦 |
| 2011 | 大鳳 | 航空母艦 | 4 | 5 | 航空母艦 |
| 2004 | 鈴谷 | 航空母艦 | 1 | 3 | 軽巡洋艦 |
| 2008 | 熊野 | 軽巡洋艦 | 2 | 4 | 軽巡洋艦 |
| 2003 | 足柄 | 軽巡洋艦 | 1 | 4 | 重巡洋艦 |
| 2007 | 妙高 | 重巡洋艦 | 2 | 5 | 重巡洋艦 |
| 2010 | 那智 | 重巡洋艦 | 3 | 6 | 重巡洋艦 |
いい具合にclassが変わったところでカウンターがリセットされました。
安全のために並び替え
でもこれ ORDER BY が効いてグループ順に処理してるっていう確証がないと破綻しちゃうよね。
SELECTはORDER BYより先に評価されるって言うから、今回たまたまうまく行っただけで場合によってはグループ順に並ばないかもしれません。
ならば、先に並び替えたテーブルを結合すれば確実にグループ順に処理されるでしょう。
SELECT t2.id, t2.name, @c AS old_class, CASE WHEN @c<>t2.class THEN @n:=1 ELSE @n:=@n+1 END AS counter, max_no+@n AS new_no, @c:=t2.class AS new_class FROM (SELECT * FROM ShipsTemp ORDER BY class, id) t2 INNER JOIN ( SELECT id, max_no FROM ShipsTemp t INNER JOIN ( SELECT max(no) AS max_no, class FROM Ships GROUP BY class ) m ON m.class = t.class ORDER BY t.class, id ) m2 ON m2.id=t2.id, (SELECT @n:=1, @c:='') c ORDER BY class, t2.id;
結果は同じです。
結論
試した限りではグループごとに連番振ることができました。
しかし、これで確実にグループ順に処理されるという確証は欲しいところです。
