MySQL8,mariaDB10 : row_number()を利用して当番表作成
PostreSQL や Oracle に加え mysql8.1 , mariaDB 10.2 で使えるようになったランク関数 row_number()を利用して当番表の作成を試みた。
以下は、mariaDB 10.4 で検証しています
まずは元データ作成
- 当番を組みたい日の日付のみ入ってる calendarテーブル：開始日以降の日付で順序づけするので、日付にprimary key 設定。
CREATE TABLE calendar( cdate date primary key not null );
- 当番を組むユーザーテーブル：対象者のみとしてもよいが、対象とするか除外するかのフラグカラムを作ると、過去のユーザーもデータとして残る。さらには、グループ別当番にも対応可能になる。
よって行を特定するためのid と、当番順序保持のカラムsorterは別にする。sorterは連続している必要はないが、設定したい日付以降で順番に並べるためその日の当番者が最小値であるようにする。CREATE TABLE user( id integer primary key not null auto_increment , name varchar(256) , sorter integer not null , flag char(1) default '' );
-- データ例 INSERT INTO calendar values ('2020-09-05'), ('2020-09-06'), ('2020-09-12'), ('2020-09-13'), ('2020-09-19'), ('2020-09-20'), ('2020-09-21'), ('2020-09-22') , ('2020-09-26'), ('2020-09-27'), ('2020-10-03'), ('2020-10-04'), ('2020-10-10'), ('2020-10-11'), ('2020-10-17'), ('2020-10-18') ; INSERT INTO user (name, sorter) values ('aaa',1), ('bcde',2), ('ff', 11), ('f2',12) ,('xyz', 26); INSERT INTO user (name, sorter, flag) values ('abc',1,'a'), ('aabbc',2, 'a'), ('aaff',3,'a'), ('xx',11,'b') ,('xy',12,'b');
- 当番を組みたい日の日付のみ入ってる calendarテーブル：開始日以降の日付で順序づけするので、日付にprimary key 設定。
割り当て表
本日以降の当番割り当てview
viewにする場合は開始日付を固定値にすると、日付変更の度に毎回作り直しになるので、当日開始にする。userテーブルのsortnum カラムは、当日の人が最小値になるよう再調整。CREATE VIEW shift as select c.cdate, u.sortnum, u.name , u.id from ( select row_number() over(order by sorter) as sortnum, id,name from user where flag = '' ) as u join (select row_number() over(order by cdate) as rownum, cdate from calendar where cdate >= CURRENT_DATE() ) as c on u.sortnum = c.rownum ; -- CURRENT_DATE() はmysql,mariaDBでの当日日付関数
特定日以降の当番巡回割り当て
上記では、１巡しか表示できていなかったので、改良してみた。
with句も利用すれば、select文１回発行で済むので、viewにするまでもないと思われる。 そうすると開始日付は適宜設定できて、userテーブル側のsorterをその日からの昇順としておいて、calendarテーブル側がuser人数より多い場合は、２巡目３巡目も表示させる。
変更点、求めたrow_number()にさらにuser人数で剰余をとる計算を行うには、()で括れば、求めた数値にさらに演算可能。-1してるのは、0開始じゃないと、剰余計算がずれるから。
蛇足、日付けのところをプレースホルダーにすると、アプリケーション側での変更がさらに楽になるだろう。with u AS ( select ( row_number() over(order by sorter) ) -1 as sortnum, id,name from user where flag = '' ) , c AS ( select ( (row_number() over(order by cdate))-1 ) % (select count(*) from u) as rownum , cdate from calendar where cdate >= '2020-09-20' ) select c.cdate, u.sortnum, u.name , u.id from c join u on u.sortnum = c.rownum order by c.cdate ;
user側の複数グループでの当番表：flagカラム利用
同一日の担当が一人ではない場合、sorter カラムのデータを同じにすれば同一日複数人の表示は可能になります。
しかし、単純に同一ループではなく、職務の違う当番表を複数作りたい場合は、userテーブルのflagカラムで職務切り分けをしておけば、flag条件切り替えで対応可能です。 そこで、一つの職務毎ではなく、２つ同時に表示するものにしてみましょう。
変更点、user選択のサブテーブルを複数にし、calendar側もカラムを増やし、対象人数が異なっても剰余データがそれぞれに対応するようにした。 同様にすれば、３つめ４つめのグループも同時表示可能。with u1 AS ( select ( row_number() over(order by sorter) ) -1 as sortnum, id,name from user where flag = 'a' ) , u2 AS ( select ( row_number() over(order by sorter) ) -1 as sortnum, id,name from user where flag = 'b' ) , c AS ( select ( (row_number() over(order by cdate))-1 ) % (select count(*) from u1) as num1 ,( (row_number() over(order by cdate))-1 ) % (select count(*) from u2) as num2 , cdate from calendar where cdate >= '2020-09-01' ) select c.cdate, u1.id as a_id, u1.name as a_name , u2.id as b_id, u2.name as b_name from c join u1 on u1.sortnum = c.num1 join u2 on u2.sortnum = c.num2 order by c.cdate ;結果+------------+------+--------+------+--------+ | cdate | a_id | a_name | b_id | b_name | +------------+------+--------+------+--------+ | 2020-09-20 | 6 | abc | 9 | xx | | 2020-09-21 | 7 | aabbc | 10 | xy | | 2020-09-22 | 8 | aaff | 9 | xx | | 2020-09-26 | 6 | abc | 10 | xy | | 2020-09-27 | 7 | aabbc | 9 | xx | | 2020-10-03 | 8 | aaff | 10 | xy | | 2020-10-04 | 6 | abc | 9 | xx | | 2020-10-10 | 7 | aabbc | 10 | xy | | 2020-10-11 | 8 | aaff | 9 | xx | | 2020-10-17 | 6 | abc | 10 | xy | | 2020-10-18 | 7 | aabbc | 9 | xx | +------------+------+--------+------+--------+ -- explain 結果 with句のサブクエリは呼び出しごとに展開されるらしい。 +------+-------------+------------+-------+---------------+---------+---------+------------+------+-------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+-------+---------------+---------+---------+------------+------+-------------------------------------------+ | 1 | PRIMARY | <derived7> | ALL | NULL | NULL | NULL | NULL | 10 | Using temporary; Using filesort | | 1 | PRIMARY | <derived4> | ref | key0 | key0 | 9 | u1.sortnum | 2 | Using where | | 1 | PRIMARY | <derived8> | ref | key0 | key0 | 8 | c.num2 | 2 | | | 8 | DERIVED | user | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Using temporary | | 7 | DERIVED | user | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Using temporary | | 4 | DERIVED | calendar | index | PRIMARY | PRIMARY | 3 | NULL | 16 | Using where; Using index; Using temporary | | 6 | SUBQUERY | <derived3> | ALL | NULL | NULL | NULL | NULL | 10 | | | 3 | DERIVED | user | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Using temporary | | 5 | SUBQUERY | <derived2> | ALL | NULL | NULL | NULL | NULL | 10 | | | 2 | DERIVED | user | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Using temporary | +------+-------------+------------+-------+---------------+---------+---------+------------+------+-------------------------------------------+
