MySQL8 _ row_number関数を利用して当番表作成

side menu

  • めも一覧

  • GD 関連

    • GDによる画像読み書き簡易化関数の作成
    • GD 利用、bitmap 形式画像の読み書き
    • カウントダウン日数画像出力ソース配布と設置解説
  • SQL関連一覧

    • MySQLでの日付期間比較
    • SQLiteでの日付期間比較
    • SQLiteでの連続日付view作成=カレンダーテーブル
    • SQLiteでの曜日計算
    • MySQLでヒストグラム作成
    • 入れ子集合モデルでサイトマップ by SQLite
    • 入れ子集合モデルでサイトマップ by MySQL
    • ランク付け:by MySQL
    • 抜けデータ補完:by MySQL
    • カンマ区切りデータ:by MySQL
    • row_number関数を利用して当番表作成:by MySQL8,mariaDB10
    • window関数解説:by MySQL8,mariaDB10
  • その他

    • phpによる月齢計算クラス
  • miztools のサイトマップ
     このサイトのrss
    miztools の更新履歴

MySQL8,mariaDB10 : row_number()を利用して当番表作成

PostreSQL や Oracle に加え mysql8.1 , mariaDB 10.2 で使えるようになったランク関数 row_number()を利用して当番表の作成を試みた。
以下は、mariaDB 10.4 で検証しています

  • まずは元データ作成

    1. 当番を組みたい日の日付のみ入ってる calendarテーブル:開始日以降の日付で順序づけするので、日付にprimary key 設定。
      CREATE TABLE calendar(
       cdate date  primary key not null
      );
      

    2. 当番を組むユーザーテーブル:対象者のみとしてもよいが、対象とするか除外するかのフラグカラムを作ると、過去のユーザーもデータとして残る。さらには、グループ別当番にも対応可能になる。
      よって行を特定するための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');
    

  • 割り当て表

    1. 本日以降の当番割り当て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での当日日付関数
      

    2. 特定日以降の当番巡回割り当て

      上記では、1巡しか表示できていなかったので、改良してみた。
      with句も利用すれば、select文1回発行で済むので、viewにするまでもないと思われる。 そうすると開始日付は適宜設定できて、userテーブル側のsorterをその日からの昇順としておいて、calendarテーブル側がuser人数より多い場合は、2巡目3巡目も表示させる。
      変更点、求めた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 ;
      

    3. user側の複数グループでの当番表:flagカラム利用

      同一日の担当が一人ではない場合、sorter カラムのデータを同じにすれば同一日複数人の表示は可能になります。
      しかし、単純に同一ループではなく、職務の違う当番表を複数作りたい場合は、userテーブルのflagカラムで職務切り分けをしておけば、flag条件切り替えで対応可能です。 そこで、一つの職務毎ではなく、2つ同時に表示するものにしてみましょう。
      変更点、user選択のサブテーブルを複数にし、calendar側もカラムを増やし、対象人数が異なっても剰余データがそれぞれに対応するようにした。 同様にすれば、3つめ4つめのグループも同時表示可能。
      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              |
      +------+-------------+------------+-------+---------------+---------+---------+------------+------+-------------------------------------------+
      

[設置日 2020-09-20] : 初回設置

| ページtopへ | miztools top | site map | cake.org