MySQL（MariaDB）でPostgreSQLのgenerate_seriesっぽいことをする

PostgreSQLにはgenerate_seriesという集合を返す関数があるのですが、これを使うと連続した値を生成することができます。
例えば各レコードに保存されている1%単位の度数（frequency）に対して、それぞれの度数の合計数を歯抜けにせず抽出したいとします。
以下のようなテーブルがあったとします。

create table book_stores( 
  book_id int not null
  , stored_at timestamp not null
  , value int not null
);

valueには度数（0から100の値）が入っていると思ってください。

PostgreSQLの場合

PostgreSQLの場合は以下のようなSQL文を実行するとレコードに存在しない度数だったしてもその度数を歯抜けにすることなく抽出できます。

select
  frequences.frequency
  , ( 
    select
      count(*) 
    from
      book_stores 
    where
      book_id = 1 
      and value = frequency
  ) AS amount_value 
from
  (select generate_series(0, 100) as frequency) AS frequences
;

実行すると以下のような結果が帰ってきます。

 frequency | values
-----------+--------
         0 |      0
         1 |      1
         2 |      1
         3 |      2
         4 |      1
         5 |      1
         6 |      0
         7 |      2
         8 |      0
         9 |      1
======（略）========
        99 |      1
       100 |      0
(101 rows)

こんな感じでSQL文で連続した値をとれるので場合によっては、アプリケーション側で歯抜けを許容しない場合はSQL文で完結できます。

MariaDBの場合

色々調べたところPostgreSQLのgenerate_seriesと同様な動きをする関数はなさそうでした。
ただ条件はついてしまうのですが、ユーザー変数を使うことで対応できそうです。
以下のようなSQL文になります。

select
  @num := @num + 1 as frequency
  , ( 
    select
      count(*) 
    from
      book_stores 
    where
      book_id = 1
      and value = @num
  ) as value
from
  book_stores
  , (select @num := - 1) num 
limit
  101
;

実行すると以下のような結果が帰ってきます。

frequency       value
0       0
1       1
2       1
3       2
4       1
5       1
6       0
7       2
8       0
9       1
======（略）========
99      1
100     0

注意点としては対象となるテーブルのレコード数が生成しようとしている範囲数よりも多い必要があります。
例えば、0から100までの101個のそれぞれの合計を取得しているので対象となるテーブルのレコード数は101レコード以上必要になります。
実際どうなるのか試してみました。
対象となるテーブルのレコード数を10レコードにしてみて同じSQL文を実行してみました。

frequency       value
0       0
1       0
2       1
3       0
4       0
5       0
6       0
7       0
8       0
9       0

最初の10個までしか値が生成されませんでした。

まとめ

そんなに使用用途は多くないのですがgenerate_seriesは日付に対しても使えるので期間を作ることもできます。
アプリ側でも対応できるので必須な機能ではないのですが、SQLで完結できるところは完結させてもよいのではないかと思います。

今回のサンプル

postgres-mysql-generate_series