変数や JOIN を使わずグループ内の連番を取得する
準備
DROP TABLE IF EXISTS t_hoge;
CREATE TABLE t_hoge (
id INT(11) NOT NULL AUTO_INCREMENT,
group_id INT(11) NOT NULL,
create_date DATE NOT NULL,
PRIMARY KEY (id),
INDEX t_hoge_01 (group_id)
);
INSERT INTO t_hoge (group_id, create_date) VALUES (1, '2011-12-24');
INSERT INTO t_hoge (group_id, create_date) VALUES (1, '2012-12-24');
INSERT INTO t_hoge (group_id, create_date) VALUES (1, '2013-12-24');
INSERT INTO t_hoge (group_id, create_date) VALUES (1, '2014-12-24');
INSERT INTO t_hoge (group_id, create_date) VALUES (2, '2011-12-24');
INSERT INTO t_hoge (group_id, create_date) VALUES (2, '2012-12-24');
INSERT INTO t_hoge (group_id, create_date) VALUES (2, '2013-12-24');
INSERT INTO t_hoge (group_id, create_date) VALUES (2, '2014-12-24');
INSERT INTO t_hoge (group_id, create_date) VALUES (3, '2011-12-24');
INSERT INTO t_hoge (group_id, create_date) VALUES (3, '2012-12-24');
INSERT INTO t_hoge (group_id, create_date) VALUES (3, '2012-12-24');
INSERT INTO t_hoge (group_id, create_date) VALUES (3, '2013-12-24');
上記の準備でテーブルデータは下記のようになっています。
| id | group_id | create_date |
|---|---|---|
| 1 | 1 | 2011-12-24 |
| 2 | 1 | 2012-12-24 |
| 3 | 1 | 2013-12-24 |
| 4 | 1 | 2014-12-24 |
| 5 | 2 | 2011-12-24 |
| 6 | 2 | 2012-12-24 |
| 7 | 2 | 2013-12-24 |
| 8 | 2 | 2014-12-24 |
| 9 | 3 | 2011-12-24 |
| 10 | 3 | 2012-12-24 |
| 11 | 3 | 2012-12-24 |
| 12 | 3 | 2013-12-24 |
このデータをもとに最終的に下記を得るのが目的です。
| id | group_id | create_date | seq |
|---|---|---|---|
| 1 | 1 | 2011-12-24 | 1 |
| 2 | 1 | 2012-12-24 | 2 |
| 3 | 1 | 2013-12-24 | 3 |
| 4 | 1 | 2014-12-24 | 4 |
| 5 | 2 | 2011-12-24 | 1 |
| 6 | 2 | 2012-12-24 | 2 |
| 7 | 2 | 2013-12-24 | 3 |
| 8 | 2 | 2014-12-24 | 4 |
| 9 | 3 | 2011-12-24 | 1 |
| 10 | 3 | 2012-12-24 | 2 |
| 11 | 3 | 2012-12-24 | 3 |
| 12 | 3 | 2013-12-24 | 4 |
なお、上記のデータにはグループ内で重複値があります（id:10 と id:11 が重複している）。
このような重複値の場合は「id値昇順」とします。
どうやる？
結構ありがちな処理なのでググると結構解決策が見つかります。
が、今回はタイトルの通り、 mysql の変数や一時テーブルとの JOIN は行わずに取得してみます。
端的に言えば行値式を使います。
行値式ってなに？
下記のように「行としての比較」を可能にする構文です（正式には行値構成子と呼ぶらしい）。
知らない人はググってもらうとして、とりあえず下記のクエリを投げればなんとなくどういったものかは掴めると思います。
-- 等価比較
select (1, 1) = (1, 1), (1, 1) = (1, 2)\G
-- (1, 1) = (1, 1): 1
-- (1, 1) = (1, 2): 0
-- ANY=(IN)
select (1, 1) IN ((1, 1), (1, 2)), (1, 0) IN ((1, 1), (1, 2))\G
-- (1, 1) IN ((1, 1), (1, 2)): 1
-- (1, 0) IN ((1, 1), (1, 2)): 0
-- 大小比較
select
(5, 5) < (4, 4),
(5, 5) < (4, 5),
(5, 5) < (4, 6),
(5, 5) < (5, 4),
(5, 5) < (5, 5),
(5, 5) < (5, 6),
(5, 5) < (6, 4),
(5, 5) < (6, 5),
(5, 5) < (6, 6)\G
-- (5, 5) < (4, 4): 0
-- (5, 5) < (4, 5): 0
-- (5, 5) < (4, 6): 0
-- (5, 5) < (5, 4): 0
-- (5, 5) < (5, 5): 0
-- (5, 5) < (5, 6): 1
-- (5, 5) < (6, 4): 1
-- (5, 5) < (6, 5): 1
-- (5, 5) < (6, 6): 1
個人的には複合主キーで IN したいときに重宝してますが、今回は「大小比較」に着目します。
上の結果の通り、「タプルとしての比較」が行えるので、これを利用すると行番号と言うか row_number 的な「ある集合に対しての自分の位置」を取得することができます。
求めるクエリ
結論から言えば下記のクエリで取得可能です。
select
*,
(
select count(*) from t_hoge tH
where tH.group_id = H.group_id
and (tH.create_date, tH.id) <= (H.create_date, H.id)
) as seq
from t_hoge H
日本語で言えば「同じ group_id 内で『自身行以下の行』がいくつあるか？」という感じです。
そしてそれがそのまま求める結果になります。
これの面白いところはやはりタプル比較なので「重複のときはid値昇順じゃなくて降順にしたい」となった場合に下記のようにすればそのまま取得できる点です。
select
*,
(
select count(*) from t_hoge tH
where tH.group_id = H.group_id
and (tH.create_date, -tH.id) <= (H.create_date, -H.id)
) as seq
from t_hoge H
id を負数にして順序的に逆転しています。
これを実行すると下記の結果が得られます。
| id | group_id | create_date | seq |
|---|---|---|---|
| 1 | 1 | 2011-12-24 | 1 |
| 2 | 1 | 2012-12-24 | 2 |
| 3 | 1 | 2013-12-24 | 3 |
| 4 | 1 | 2014-12-24 | 4 |
| 5 | 2 | 2011-12-24 | 1 |
| 6 | 2 | 2012-12-24 | 2 |
| 7 | 2 | 2013-12-24 | 3 |
| 8 | 2 | 2014-12-24 | 4 |
| 9 | 3 | 2011-12-24 | 1 |
| 10 | 3 | 2012-12-24 | 3 |
| 11 | 3 | 2012-12-24 | 2 |
| 12 | 3 | 2013-12-24 | 4 |
id:10 と id:11 の seq が逆になっているのがわかります。
これで変数も JOIN も使わず得たい結果を得ることができました。
行値式さえ使えれば RDBMS を問わないので覚えておくと便利だと思います。
というか行値式自体あまり使われていない印象があります。
今回とは無関係に、とても便利な構文なので覚えておいて損はないと思います。