MySQL _ ヒストグラム作成

MySQL : ヒストグラム作成

他のSQL言語(Oracle とか postgreSQLとか)には、統計用の関数でヒストグラムを集計するものがあるようだが、MySQLには実装されていないので、SQLパズルとして解いてみた。
以下は、MySQL 5.5 で検証しています

• まずは元データtableの定義

  ヒストグラムの対象となるデータとして、ここでは、ある学年の身長や体重の分布、成績表を取り上げてみる。

  • まずは、基礎データとしては、生徒ごとの出席番号(id)と、名前(name)。
  • 身長や体重は、成長期で有れば、学年最初と最後で変化していくので、専用tableに時期名カラムと生徒idでprimaryとしてデータを保持とする。
  • 成績表も、学期ごとの中間、期末の考査名と生徒idでprimaryとしてデータをもつ。
    成績データ格納方法は、利用法によって考えどころである。
    • 科目ごとのカラムに点数を格納するか
    • 科目idカラムも連動primary keyにいれて、点数カラムを一つにするか
    ２つの方法それぞれの成績表でヒストグラム作成するにはどのようなSQLになるのかやってみることにする。

  ※　実用としては、もっといろんな付帯情報を格納するカラムを作ったりすると思うが、ここでは、単純化しておく。
  「default charset=utf8」もvarcharやtext型カラムのあるtable には付けておく。 これにより、mysql server の charset 指定には依存せず、client 側設定が正しければ、きちんと多バイト文字が格納される。

  --  生徒出席番号表
  create table `student_base` (
    `id` int primary key
  , `name` varchar(50) not null
  ) default charset=utf8;
  --  身長、体重データ
  create table `student_physics` (
    `student_id` int  not null
  , `season_id` int not null
  , `weight` double
  , `height` double
  , primary key (student_id, season_id)
  ) default charset=utf8;
  create table `season_base` (
    `id` int primary key auto_increment
  , `title` varchar(100) not null
  , `find`  date
  ) default charset=utf8;
  
  --  成績表 A : 科目ごとのカラムに点数格納
  create table `subject_each` (
    `id` int primary key auto_increment
  , `student_id` int not null
  , `term_id` int not null
  , `kokugo`  int
  , `suugaku` int
  , `rika`    int
  , `syakai`  int
  , `eigo`    int
  , unique `unikey` (term_id, student_id)
  );
  create table `term_base` (
    `id` int primary key auto_increment
  , `title` varchar(100) not null
  , `find`  date
  ) default charset=utf8;
  
  --  成績表 B : 科目名はsubject_baseテーブルをforein key とし、点数用カラムをひとつに
  create table `subject_record` (
    `id` int primary key auto_increment
  , `student_id` int not null
  , `term_id` int not null
  , `subject_id` int not null
  , `result` int
  ,  unique `unirec`  (term_id, subject_id, student_id)
  ) ;
  create table `subject_base` (
    `id` int primary key auto_increment
  , `title` varchar(100) not null
  ) default charset=utf8;
  

• data 作成

  とりあえず、集計SQL文の動作確認用に、適宜データを入れておく。

  insert into `student_base` values
    (1,'aikawa'),(2,'inada'),(3,'kino'),(4,'komaba'),(5,'satou'),(6,'suzuki')
   ,(7,'tanaka'),(8,'tokiwa'),(9,'nakajima'),(10,'nishikawa'),(11,'hachiken'),(12,'mikage');
  insert into `season_base` (title,find) values ('春','2014-04-15'),('秋','2014-09-30'),('冬','2015-02-10');
  insert into `term_base` (title,find)  values ('前期中間','2014-06-06'),('前期末','2014-09-09')
   ,('後期中間','2014-12-05'),('学年末','2015-03-03');
  insert into `subject_base` (title)  values ('国語'),('数学'),('理科'),('社会'),('英語');
  
  insert into `student_physics` values
   (1,1,168.2, 56.3),(2,1, 149.5, 66.6),(3,1, 149.0, 42.2),(4,1, 167.5, 63.3),(5,1, 167, 67),(6,1, 160,56)
  , (7,1,178.2, 66.3),(8,1, 150.5, 56.6),(9,1, 169.0, 58.2),(10,1, 160.5, 53.3),(11,1, 157, 67),(12,1, 156,47)
  , (1,2,168.7, 56.0),(2,2, 149.5, 58.6),(3,2, 153.0, 45.8),(4,2, 172.5, 66.3),(5,2, 167, 69),(6,2, 163,57)
  , (7,2,180.9, 66.0),(8,2, 150.0, 58.6),(9,2, 169.0, 58.2),(10,2, 160.5, 52.3),(11,2, 157, 71),(12,2, 158, 50);
  
  insert into `subject_each` values
   (null, 1, 1, 80, 90, 82, 98, 78) , (null, 2,1, 80,100,80,70,95 ), (null,3,1, 70,70,60,80,90 ), (null,4,1, 60,50,70,60,50 )
  , (null,5,1, 90,60,90,70,70 ), (null,6,1, 90,100,100,80,95 ), (null,7,1, 65,50,70,60,90 ), (null,8,1, 20,2,70,30,20)
  , (null,9,1, 70,90,70,85,65 ), (null,10,1, 50,60,50,80,80 ), (null,11,1, 60,40,55,70,60 ), (null,12,1, 50,40,50,65,60 )
  , (null, 1, 2, 90, 95, 80, 100, 80) , (null, 2,2, 80,95,80,75,100 ), (null,3,2, 80,70,60,60,90 ), (null,4,2, 66,50,70,60,60 )
  , (null,5,2, 95,70,85,70,80 ), (null,6,2, 100,100,100,90,90 ), (null,7,2, 80,50,70,60,85 ), (null,8,2, 30,12,70,40,40)
  , (null,9,2, 95,85,70,80,75 ), (null,10,2, 85,50,60,75,80 ), (null,11,2, 75,30,60,70,65 ), (null,12,2, 70,40,60,65,80 );
  
   --  test 用にて、subject_record へも、 subject_each からデータ作成
  insert into `subject_record` (student_id,term_id,subject_id , result)
    select student_id , term_id , 1 , kokugo from `subject_each`
    union all
    select student_id , term_id , 2 , suugaku from `subject_each`
    union all
    select student_id , term_id , 3 , rika from `subject_each`
    union all
    select student_id , term_id , 4 , syakai from `subject_each`
    union all
    select student_id , term_id , 5 , eigo from `subject_each` ;
  

• ヒストグラム

  • 各階級ごとに集計して、union allする方法 : 身長分布の場合で

    • まずは、時期指定での集計： student_physics.season_id = 1 の場合

      select  '150cm 未満' as kubun, count(height) as kensu
        from  student_physics 
        where height < 150  and  season_id = 1
       union all
      select  '150cm 以上-160cm 未満' , count(height) 
        from  student_physics 
        where height >= 150 and  height < 160  and  season_id = 1
       union all
      select  '160cm 以上-170cm 未満' , count(height) 
        from  student_physics 
        where height >= 160 and  height < 170  and  season_id = 1
       union all
      select  '170cm 以上' , count(height) 
        from  student_physics 
        where height >= 170  and season_id = 1
       union all
      select  '全件数' , count(height) 
        from  student_physics  where season_id = 1
      ;
      

      ※ between は両端を含むので使えない。上下両方の区分にカウントされると件数狂う
      ※ 先頭 select 文でカラムエイリアスを与えれば、以降の select 文では、記述順にカラムを当てはめるので、エイリアス名は不要。

    • 複数 season_id ごとの集計データを縦表示、 seasonごとの 並べ替え

      select ss.title , h.kubun , h.kensu
      from  ( select season_id, '150cm 未満' as kubun,1 as k_order, count(height) as kensu
        from  student_physics
         where height < 150
         group by season_id
        union all
        select season_id,  '150cm 以上-160cm 未満' as kubun,2, count(height) 
        from  student_physics  
         where height >= 150 and  height < 160
         group by season_id
        union all
        select season_id, '160cm 以上-170cm 未満' as kubun,3, count(height) 
        from  student_physics 
         where height >= 160 and  height < 170
         group by season_id
        union all
        select season_id,  '170cm 以上' as kubun,4, count(height) 
        from  student_physics 
         where height >= 170 
         group by season_id
        union all
        select season_id,  '全件数' as kubun,5, count(height) 
        from  student_physics 
         group by season_id
       order by  season_id , k_order 
       ) h left join  season_base ss on h.season_id = ss.id 
      ;
      

      ※ season_id に対応する season_base.title は、union後のsubquery tableに対してjoinする
      ※ kubun の文字列での並べ替えでは、うまく順序づけできないことがあるので、k_order カラムエイリアスをつくり階級順序番号を付けている

  • 階級テーブルを作成して、left joinする方法：体重分布の場合で

    範囲指定を、where でいちいち個別指定するのは、毎回の作業となると面倒です。 また、season 別を横に並べたいときは、各season ごとの集計をサブクエリにして、join する必要がありますが、さらに同じ事を多重に記述の必要が出てきます。
    そこで、階級テーブルを作成し、これに、階級ごとの範囲データを登録して、連結する事にしてみましょう。

    • まずは、身長、体重用 に double データ型での階級テーブル作成

      create table `range_dbl` (
        yoto_code varchar(10)
      , title varchar(100)
      , d_start double  --  greater or equals
      , d_end   double  --  less than
      , primary key (yoto_code, d_start,d_end)
      ) default charset=utf8;
       --  ※ yoto_code は 身長用、体重用の区別や、分割幅を変えた設定も一つのテーブルに登録して区別するため。
      
      insert into  `range_dbl` values
         ('w-10','体重 < 40kg', 0, 40),('w-10','体重 40kg<= w < 50kg', 40, 50),('w-10','体重 50kg<= w < 60kg', 50, 60)
       , ('w-10','体重 60kg<= w < 70kg', 60, 70),('w-10','体重 >= 70kg', 70, 200)
       , ('w-20','体重 < 40kg', 0, 40),('w-20','体重 40kg<= w 60kg', 40, 60)
       , ('w-20','体重 60kg<= w <80kg', 60, 80),('w-20','体重 >= 80kg', 80, 200) 
       , ('h-10','身長 < 140cm', 0, 140),('h-10','身長 140cm<= h 150cm', 140, 150),('h-10','身長 150cm<= h <160cm', 150, 160)
       , ('h-10','身長 160cm<= h 170cm', 160, 170),('h-10','身長 >= 170cm', 170, 300) 
       ;
      

    • 集計 season指定

      select  r.title , count(p.weight) as w_kensu , r.d_start 
       from  range_dbl r left join  student_physics p
         on r.d_start <= p.weight and p.weight < r.d_end  and p.season_id = 1
       where  r.yoto_code = 'w-10'
       group by  r.d_start   with rollup
       ;
      

      ※ データ値 0 の階級行も表示させるには、階級テーブルが左で、集計 count() にはカラム指定必ず入れる　：　mysql は outer join や right join には対応していないため
      ※ mysql 最適化の法則では、left側tableのカラムのみの制限は、where句に書いた方が速いらしい
      ※ mysql では group by に出現しないカラムでも一意になる場合は、select句にそのまま記述してよいし、逆にgroup by に記述すると無駄なソート作業が発生する。 他のRDBSではgroup by句にも r.title 必要
      ※ with rollup で全階級の合計　を表示。
      この場合 order by句をいれると、rollup で作成したnull 階級が除外されてしまうので、並べ替え順も考えて、 group by カラムは r.d_start にする

    • 複数 season を横並べ

      select  rbase.title , s1.w_kensu as season1_kensu , s2.w_kensu as season2_kensu 
        , rbase.d_start
      from
       ( select d_start , title from range_dbl where yoto_code = 'w-10'
          union select null , '合計'
       ) rbase
       left join ( select r.d_start , count(p.weight) as w_kensu
         from range_dbl r left join  student_physics p
           on  r.d_start <= p.weight and p.weight < r.d_end  and p.season_id = 1
         where  r.yoto_code = 'w-10'
         group by  r.d_start   with rollup
       ) s1  on  rbase.d_start = s1.d_start or (rbase.d_start is null and s1.d_start is null)
       left join ( select r.d_start , count(p.weight) as w_kensu
         from range_dbl r left join  student_physics p
           on  r.d_start <= p.weight and p.weight < r.d_end  and p.season_id =2
         where  r.yoto_code = 'w-10'
         group by  r.d_start   with rollup
       ) s2  on  rbase.d_start = s2.d_start or (rbase.d_start is null and s2.d_start is null)
      order by  case when rbase.d_start is null then (select max(d_start)+1 from range_dbl)
                else rbase.d_start end  asc
      ;
      

      ※ rbase のサブクエリは、rollup で出てくる null の行に対してtitle を与えるため union している
      ※ 最後の order by 句は null 行を最後尾に持っていくための case 条件切り分け
      ※ r.yoto_code = 'w-10'　が何回も同じ物出てくるのが不満なら、変数でいれるとか、 procedure にしてしまうとか。
      ※ p.season_id について、２つしか登録してないので、とりあえず２カラムで済んでいるが、継続的に、複数id の集計を横並び(クロス集計型)に出すには、その数だけサブクエリのjoin が必要。
      p.season_id がいくつあるか不明の時は、通常は、アプリ側で、「 select distinct season_id from student_physics 」で存在するidを取得して、SQL文字列作成する。 SQLでの実行にこだわるなら、MySQL5.1以降なら procedure でも、同様にidを得ながら SQL文を文字列として作成し、 prepare and execute で実行させることが出来る。 ということで、次の章へ。

  • クロス集計型　時期別ヒストグラムを procedure で作る

    delimiter #
    create  procedure `weight_cross` (in code varchar(10))
     comment ' inputの code は  range_dbl.yoto_code にマッチする文字列 , @sql変数に最終実行sql文を格納する '
    begin
    declare tmpSql,sect varchar(4096) default '';
    declare  idmax,dmax int default 0;
    set @idx = 0;
    set @sql = "select distinct season_id  into @sid  from student_physics  order by season_id  limit 1 offset ? ";
    select count( d_start ) into idmax from  range_dbl where yoto_code = code;
    --  code の正当性チェック
    if idmax >0 then
    select count(  distinct season_id ) into idmax from  student_physics;
    --  season_id の件数＝ループ数
     prepare tmp_stmt from @sql;
     WHILE idmax>@idx DO
      execute tmp_stmt using @idx;
      set sect   = concat( sect , " , `s",@sid, "`.kensu as `s",@sid,"_kensu` " );
      set tmpSql = concat( tmpSql, " left join (select rr.d_start , count(ss.weight) as kensu "
        ," from range_dbl rr left join student_physics ss  on rr.d_start <= ss.weight and ss.weight < rr.d_end  and ss.season_id = "
        ,@sid ,"  where rr.yoto_code = '",code,"'  group by rr.d_start  with rollup ) as `s",@sid
        ,"`  on `r0`.d_start = `s",@sid,"`.d_start  or (`r0`.d_start is null  and  `s",@sid,"`.d_start is null) ");
      set @idx=@idx+1;
     END WHILE;
     deallocate prepare tmp_stmt;
     select max(d_start) + 1 into dmax from  range_dbl;
     set @sql = concat( "select  rbase.title ",sect
       ," , rbase.jun from ( select d_start as jun, d_start , title  from range_dbl  where yoto_code = '",code
       ,"'  union  select ",dmax," , null , '合計' ) rbase ", tmpSql 
       , " order by rbase.jun " );
     prepare tmp_stmt from @sql;
     execute tmp_stmt;
     deallocate prepare tmp_stmt;
    end if;
    end #
    
    delimiter ;
    set @c = 'w-10';
    call weight_cross(@c);
    --  最終実行sql文を表示してみる
    select @sql;
    

    いくつか、はまりどころメモ

    • codeデータを外から入れて、injection になったりしないか？
      最初に現れる select 文内では、変数 code は in で型指定されているので、そのデータ型にあわせて、escape して展開される模様。この select文では、injection は起きない
      以降は、テーブルに実在するデータとマッチする行が存在するときのみ実行することになるので、@sql文作成では 文字列括り内にはめ込みでok . ただし、yoto_codeのデータ自体に " ' などエスケープ必要な文字が存在しないことが前提ではある。
    • 全部declare変数に出来ないのか？
      ※ prepared 文で select into 変数　を実行する時は、declare変数は解釈されない。 @付きユーザー変数なら代入が行われる。
      ※ prepared 文の ?プレースホルダーに代入する値の指定も @付きユーザー変数しか使えない
      　例： execute tmp_stmt using @idx;
    • じゃあ、全部 @付きユーザー変数 にしちゃえば？
      @付きユーザー変数は、 procedure からみて、外部のglobal変数であるので、多用して、他の用途に使っていた同名変数が書き変わるのも問題である。 procedure 実行終了後も、メモリに居座る問題もある。declare変数は、実行終了後は解放される。
      ということで、@付きユーザー変数は必要最小限として、他は、 procedure 内local変数としてdeclare宣言している。

  • 科目別カラムの成績表でヒストグラム

    • int型階級テーブル作成

      create table range_int (
        yoto_code varchar(10)
      , title varchar(100)
      , i_start int  --  greater or equals
      , i_end   int  --  less than
      , primary key (yoto_code, i_start,i_end)
      ) default charset=utf8;
      
      insert into  `range_int` values
        ('res-20','赤点＜30',0,30),('res-20','30≦x＜50',30,50),('res-20','50≦x＜70',50,70)
       ,('res-20','70≦x＜90',70,90),('res-20','90≦x',90,110);
      

      ※ 最上位の最大値は 100 を含ませたいので、 100 より大きい数値で指定。101でも問題ない。 共通一次用の 200点満点体系では、最大階級の最大値は 201以上にする

    • 単一時期 単一科目を指定 : kokugo カラムを集計 , term_id = 1

      select  r.title , count(s.kokugo) as koku_kensu , r.d_start 
       from  range_int r left join  subject_each s 
         on r.i_start <= s.kokugo and s.kokugo < r.i_end  and s.term_id = 1
       where  r.yoto_code = 'res-20'
       group by  r.d_start   with rollup
      ;
      

    • 複数科目の集計を横並べ

      select  rbase.title , kok.kensu as kokugo_kensu , suu.kensu as suugaku_kensu , ei.kensu as eigo_kensu
        ,  rbase.i_start
       from  ( select i_start , title from range_int where yoto_code = 'res-20'
          union select  null , '合計'
       ) rbase
      
       left join ( select r.i_start , count(s.kokugo) as kensu
         from  range_int r left join  subject_each s 
           on r.i_start <= s.kokugo and s.kokugo < r.i_end  and s.term_id = 1
         where  r.yoto_code = 'res-20'
         group by  r.i_start  with rollup
       ) kok  on  rbase.i_start = kok.i_start or (rbase.i_start is null and kok.i_start is null)
      
       left join ( select r.i_start , count(s.suugaku) as kensu
         from  range_int r left join  subject_each s 
           on r.i_start <= s.suugaku and s.suugaku < r.i_end  and s.term_id = 1
         where  r.yoto_code = 'res-20'
         group by  r.i_start  with rollup
       ) suu  on  rbase.i_start = suu.i_start or (rbase.i_start is null and suu.i_start is null)
      
       left join ( select r.i_start , count(s.eigo) as kensu
         from  range_int r left join  subject_each s 
           on r.i_start <= s.eigo and s.eigo < r.i_end  and s.term_id = 1
         where  r.yoto_code = 'res-20'
         group by  r.i_start  with rollup
       ) ei  on  rbase.i_start = ei.i_start or (rbase.i_start is null and ei.i_start is null)
      order by  case when  rbase.i_start is null then (select max(i_start)+1 from range_int)
                else rbase.i_start end  asc
      ;
      

  • 成績表 B タイプ(点数カラムは一つ)でヒストグラム

    階級テーブルは同じ物が使える

    • 単一時期 単一科目を指定 : subject_id = 1 and term_id = 1

      select  r.title , count(s.result) as koku_kensu , r.i_start
       from  range_int r left join  subject_record s 
         on r.i_start <= s.result and s.result < r.i_end  and s.term_id = 1 and s.subject_id = 1
       where  r.yoto_code = 'res-20'
       group by  r.i_start  with rollup
      ;
      

    • 単一科目 で　考査別(term_id別)集計を横並べ

      select  rbase.title , t1.kensu as `前期中間_kensu` , t2.kensu as `前期末_kensu`
         , r.i_start
       from  ( select i_start , title from  range_int  where  yoto_code = 'res-20'
          union select  null , '合計'
       ) rbase
      
       left join ( select r.i_start , count(s.result) as kensu
         from  range_int r left join  subject_record s 
           on r.i_start <= s.result and s.result < r.i_end  and s.term_id = 1 and s.subject_id = 1
         where  r.yoto_code = 'res-20'
         group by  r.i_start  with rollup
       ) t1  on rbase.i_start = t1.i_start  or (rbase.i_start is null  and  t1.i_start is null)
      
       left join ( select r.i_start , count(s.result) as kensu
         from  range_int r left join  subject_record s 
           on r.i_start <= s.result and s.result < r.i_end  and s.term_id = 2 and s.subject_id = 1
         where  r.yoto_code = 'res-20'
         group by  r.i_start  with rollup
       ) t2  on rbase.i_start = t2.i_start  or (rbase.i_start is null  and  t2.i_start is null)
      
      order by  case when  rbase.i_start is null  then  (select max(i_start)+1 from range_int)
                else  rbase.i_start  end  asc
      ;
      

    • 点数カラムが一つなら、縦表示集計で、大項目中項目小項目　ごとの group by はよくみかけるsql文と思う。

      select  s.term_id , s.subject_id , r.i_start , r.title , count(s.result)
        from  subject_record s left join range_int r 
          on  r.i_start <= s.result and s.result < r.i_end and  r.yoto_code = 'res-20'
        group by s.term_id , s.subject_id , r.i_start  with rollup
      ;
      

      ※ アプリ側で並べ替えるなら、これで十分と思うが、 explain してみると、index が使えていない。 group by に 別tableのカラムが入ってくるせいらしい

    • 各idに対応するbase tableのtitleを表示

      select  t0.title, s0.title, rbase.title , h.kensu
       from  ( select  s.term_id , s.subject_id , r0.i_start  , count(s.result) as kensu
         from  subject_record s left join range_int r0
           on  r0.i_start <= s.result and s.result < r0.i_end and  r0.yoto_code = 'res-20'
         group by  s.term_id , s.subject_id , r0.i_start  with rollup
       ) h
       left join  ( select id,title from term_base union select null,'全合計'
       ) t0  on t0.id = h.term_id or (t0.id is null and h.term_id is null)
       left join  ( select id,title from subject_base union select null,'全科合計'
       ) s0  on s0.id = h.subject_id or (s0.id is null an  h.subject_id is null)
       left join  ( select i_start,title from range_int where yoto_code = 'res-20'
          union select  null , '合計'
       ) rbase  on rbase.i_start = h.i_start or (rbase.i_start is null and h.i_start is null)
       ;
      

      ※ with rollupによりnull値のあるgroup化カラムと、null値のない base系tableのカラムと を結合すると、合計値のところのnull行が選択されなくなるので、 前出のrbase サブクエリのようにid が nullの行を加えて join 必要。 range_intのgroup化カラムの null行にもタイトルを与えるには、結局 rbaseサブクエリもjoin必要。
      ※　0件の階級行が抜けてしまうのは、縦集計では、しょうがない。

  • クロス集計型　科別時期別ヒストグラムを procedure で作る

    in 引数に 時期別term_id、や科目別subject_id　を指定すると、指定時期の指定科目についての集計を行う。
    いずれか、または、どちらもが、null や、0 の時は、指定時期の科目別クロス、指定科目の時期別クロス、または、全時期、全科目でクロス集計を行う

    delimiter #
    create  procedure `record_cross` (in code varchar(10),in tm_id int, in sj_id int)
     comment ' inputの第１引数 code は  range_int.yoto_code にマッチする文字列.
     第２引数 tm_id は term_base.id にマッチする番号、０の時は全時期を横に列挙.
     第３引数 sj_id は subject_base.id にマッチする番号、０の時は全科目を横に列挙. 
     @sql変数に最終実行sql文を格納する '
    begin
    declare tmpSql,sect varchar(4096) default '';
    declare tname,sname,tblname varchar(100) default '';
    declare  idmax,dmax int default 0;
    set @idx = 0;
    select count( i_start ) into idmax from  range_int where yoto_code = code;
    --  code の正当性チェック
    if idmax >0 then
    --  ループ数 取得
    set idmax = 0;
    set @sql = "select distinct term_id,subject_id  into @tid,@sid  from subject_record where 1  ";
    CASE when  tm_id is null or tm_id=0 then 
      begin 
      case when sj_id is null or sj_id =0 then 
          select count( distinct term_id,subject_id ) into idmax from  subject_record;
      else
          select count( distinct term_id) into idmax from  subject_record where subject_id = sj_id ;
          set @sql = concat(@sql , " and  subject_id = ",sj_id );
      end case;
      end;
     else
      begin
      case when sj_id is null or sj_id =0 then
          select count( distinct  subject_id ) into idmax from  subject_record where term_id = tm_id ;
          set @sql = concat(@sql , " and  term_id = ",tm_id );
      else 
          select count(*) into idmax from  subject_record where subject_id = sj_id and  term_id = tm_id ;
          if idmax >0 then
          set idmax = 1;
          set @sql = concat(@sql , " and  subject_id = ",sj_id ," and term_id = ", tm_id);
          end if;
     end case;
     end;
    END CASE;
    
     IF idmax >0 THEN
      set @sql = concat(@sql , " order by term_id,subject_id  limit 1 offset ? ");
      prepare tmp_stmt from @sql;
      WHILE idmax>@idx DO
       execute tmp_stmt using @idx;
       select title into tname from term_base where id=@tid;
       select title into sname from subject_base where id=@sid;
       set tblname = concat( "s",@tid,"_",@sid );
       set sect   = concat( sect , " , `", tblname, "`.kensu as `_",tname,"_",sname,"_` " );
       set tmpSql = concat( tmpSql, " left join (select rr.i_start , count(ss.result) as kensu "
        ," from range_int rr left join subject_record ss  on rr.i_start <= ss.result and ss.result < rr.i_end  and ss.term_id="
        ,@tid, " and ss.subject_id=",@sid ,"  where rr.yoto_code = '",code,"'  group by rr.i_start  with rollup ) as `",tblname
        ,"`  on `r0`.i_start = `",tblname,"`.i_start  or (`r0`.i_start is null and `",tblname,"`.i_start is null) ");
       set @idx=@idx+1;
      END WHILE;
      deallocate prepare tmp_stmt;
      select max(i_start) + 1 into dmax from  range_int;
      set @sql = concat( "select  r0.title ",sect
       ," , r0.jun from ( select i_start as jun, i_start , title  from range_int  where yoto_code = '",code
       ,"'  union  select ",dmax," , null , '合計' ) r0 ", tmpSql 
       , " order by r0.jun " );
      prepare tmp_stmt from @sql;
      execute tmp_stmt;
      deallocate prepare tmp_stmt;
     ELSE
       set @sql = "";	--  実行されてないので、無駄なデータは削除
     END IF;
    end if;
    end #
    
    delimiter ;
    call record_cross('rec-20',0,0);
    --  最終実行sql文を表示してみる
    select @sql;