まえがき

generate_seriesを使用できるようにするためにいろいろ調べた。使えるようになるといろいろ表現の幅が広がって便利。

参考文献

拡張ロードのやり方を参考にさせてもらいました。
https://wave.hatenablog.com/entry/2018/05/28/185000

環境

$sqlite -version
-- Loading resources from /home/sqlite/.sqliterc
3.30.0 2019-10-04 15:03:17 c20a35336432025445f9f7e289d0cc3e4003fb17f45a4ce74c6269c407c6e09f

$gcc --version
gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39)
Copyright (C) 2015 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

$bash --version
GNU bash, バージョン 5.0.0(1)-release (x86_64-pc-linux-gnu)
Copyright (C) 2019 Free Software Foundation, Inc.
ライセンス GPLv3+: GNU GPL バージョン 3 またはそれ以降 <http://gnu.org/licenses/gpl.html>

This is free software; you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.

$cat /etc/redhat-release 
CentOS Linux release 7.6.1810 (Core)

手順

#1. cソースをダウンロード

ここらへん、うろうろして見つけた。

$curl -LO https://www.sqlite.org/src/raw/ext/misc/series.c?name=0c97f63378fddc9f425e82ba139b9aaf902211f24ced115c2b6ae12b425f7334

#2. ファイルリネーム

短くする。

$mv series.c?name=0c97f63378fddc9f425e82ba139b9aaf902211f24ced115c2b6ae12b425f7334 series.c

#3. ライブラリ生成

soファイル作る。

$sudo gcc -fPIC -shared -I /usr/local/src/sqlite-autoconf-3300000/ -o series.so series.c

使用

以下で読み込む。

.load ./series.so

$sqlite
-- Loading resources from /home/sqlite/.sqliterc
SQLite version 3.30.0 2019-10-04 15:03:17
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>>>.load ./series.so

詳しい使用感は以下に記載されている。

sqlite>>>SELECT value FROM generate_series(1,10);
value     
----------
1         
2         
3         
4         
5         
6         
7         
8         
9         
10

インクリメント指定。

sqlite>>>SELECT value FROM generate_series(1,10,2);
value     
----------
1         
3         
5         
7         
9         
sqlite>>>SELECT value FROM generate_series(-10,10,2);
value     
----------
-10       
-8        
-6        
-4        
-2        
0         
2         
4         
6         
8         
10

ラテラル感。

sqlite>>>SELECT s1.value,s2.value FROM generate_series(1,3) s1,generate_series(s1.value+1,s1.value+3) s2;
value       value     
----------  ----------
1           2         
1           3         
1           4         
2           3         
2           4         
2           5         
3           4         
3           5         
3           6

こういうのもできる。

sqlite>>>SELECT value,abs(value) FROM generate_series(-10,1,2);
value       abs(value)
----------  ----------
-10         10        
-8          8         
-6          6         
-4          4         
-2          2         
0           0

普段はホームディレクトリ配下にある**.sqliterc**に追加しておくのがハンディかも。

$cat ~/.sqliterc
.mode column
.headers on
.width 0
.nullvalue "NULL"
.prompt "sqlite>>>"
.load ./series.so

あとがき

ちょっと便利になった。

以上、ありがとうございました。

20191201追記

日付周りとかとコラボさせてみた。

上記の手順を踏まえてあらかじめceil関数とか使えるようにしておく。

$curl -LO https://www.sqlite.org/contrib/download/extension-functions.c?get=25
$mv extension-functions.c?get=25 extension-functions.c
$sudo gcc -fPIC -shared -I /usr/local/src/sqlite-autoconf-3300000/ -o extension-functions.so extension-functions.c

$cat ~/.sqliterc
.mode column
.headers on
.width 0
.nullvalue "NULL"
.prompt "sqlite>>>"
.load ./series.so
.load ./extension-functions.so

$sqlite3 < <(cat <<EOS
  select
    s1.value as seq
    ,date(date('now','+'||(s1.value-1)||' day'),'-4 day') as day_bef_4
    ,date(date('now','+'||(s1.value-1)||' day'),'-3 day') as day_bef_3
    ,date(date('now','+'||(s1.value-1)||' day'),'-2 day') as day_bef_2
    ,date(date('now','+'||(s1.value-1)||' day'),'-1 day') as day_bef_1
    ,date('now','+'||(s1.value-1)||' day') as day_base_0
    ,date(date('now','+'||(s1.value-1)||' day'),'+1 day') as day_aft_1
    ,date(date('now','+'||(s1.value-1)||' day'),'+2 day') as day_aft_2
    ,date(date('now','+'||(s1.value-1)||' day'),'+3 day') as day_aft_3
    ,date(date('now','+'||(s1.value-1)||' day'),'+4 day') as day_aft_4
  from generate_series(1,10) s1;
EOS
)
seq         day_bef_4   day_bef_3   day_bef_2   day_bef_1   day_base_0  day_aft_1   day_aft_2   day_aft_3   day_aft_4 
----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------
1           2019-11-27  2019-11-28  2019-11-29  2019-11-30  2019-12-01  2019-12-02  2019-12-03  2019-12-04  2019-12-05
2           2019-11-28  2019-11-29  2019-11-30  2019-12-01  2019-12-02  2019-12-03  2019-12-04  2019-12-05  2019-12-06
3           2019-11-29  2019-11-30  2019-12-01  2019-12-02  2019-12-03  2019-12-04  2019-12-05  2019-12-06  2019-12-07
4           2019-11-30  2019-12-01  2019-12-02  2019-12-03  2019-12-04  2019-12-05  2019-12-06  2019-12-07  2019-12-08
5           2019-12-01  2019-12-02  2019-12-03  2019-12-04  2019-12-05  2019-12-06  2019-12-07  2019-12-08  2019-12-09
6           2019-12-02  2019-12-03  2019-12-04  2019-12-05  2019-12-06  2019-12-07  2019-12-08  2019-12-09  2019-12-10
7           2019-12-03  2019-12-04  2019-12-05  2019-12-06  2019-12-07  2019-12-08  2019-12-09  2019-12-10  2019-12-11
8           2019-12-04  2019-12-05  2019-12-06  2019-12-07  2019-12-08  2019-12-09  2019-12-10  2019-12-11  2019-12-12
9           2019-12-05  2019-12-06  2019-12-07  2019-12-08  2019-12-09  2019-12-10  2019-12-11  2019-12-12  2019-12-13
10          2019-12-06  2019-12-07  2019-12-08  2019-12-09  2019-12-10  2019-12-11  2019-12-12  2019-12-13  2019-12-14

$sqlite3 < <(cat <<EOS
  select
    s1.value as seq
    ,date('now',(s1.value-5)||' day') as day_bef_4
    ,date('now',(s1.value-4)||' day') as day_bef_3
    ,date('now',(s1.value-3)||' day') as day_bef_2
    ,date('now',(s1.value-2)||' day') as day_bef_1
    ,date('now',(s1.value-1)||' day') as day_base_0
    ,date('now','+'||(s1.value)  ||' day') as day_aft_1 
    ,date('now','+'||(s1.value+1)||' day') as day_aft_2
    ,date('now','+'||(s1.value+2)||' day') as day_aft_3
    ,date('now','+'||(s1.value+3)||' day') as day_aft_4
  from generate_series(1,10) s1;
EOS
)
seq         day_bef_4   day_bef_3   day_bef_2   day_bef_1   day_base_0  day_aft_1   day_aft_2   day_aft_3   day_aft_4 
----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------
1           2019-11-27  2019-11-28  2019-11-29  2019-11-30  2019-12-01  2019-12-02  2019-12-03  2019-12-04  2019-12-05
2           2019-11-28  2019-11-29  2019-11-30  2019-12-01  2019-12-02  2019-12-03  2019-12-04  2019-12-05  2019-12-06
3           2019-11-29  2019-11-30  2019-12-01  2019-12-02  2019-12-03  2019-12-04  2019-12-05  2019-12-06  2019-12-07
4           2019-11-30  2019-12-01  2019-12-02  2019-12-03  2019-12-04  2019-12-05  2019-12-06  2019-12-07  2019-12-08
5           2019-12-01  2019-12-02  2019-12-03  2019-12-04  2019-12-05  2019-12-06  2019-12-07  2019-12-08  2019-12-09
6           2019-12-02  2019-12-03  2019-12-04  2019-12-05  2019-12-06  2019-12-07  2019-12-08  2019-12-09  2019-12-10
7           2019-12-03  2019-12-04  2019-12-05  2019-12-06  2019-12-07  2019-12-08  2019-12-09  2019-12-10  2019-12-11
8           2019-12-04  2019-12-05  2019-12-06  2019-12-07  2019-12-08  2019-12-09  2019-12-10  2019-12-11  2019-12-12
9           2019-12-05  2019-12-06  2019-12-07  2019-12-08  2019-12-09  2019-12-10  2019-12-11  2019-12-12  2019-12-13
10          2019-12-06  2019-12-07  2019-12-08  2019-12-09  2019-12-10  2019-12-11  2019-12-12  2019-12-13  2019-12-14

差日数
https://blog.sgnet.co.jp/2017/08/sqlite-sqlite-2.html
https://www.sqlite.org/lang_datefunc.html

$cat ./日付生成.sh
# !/bin/bash
sqlite3 < <(cat <<EOS
  select
    date('now',(-s1.value)||' day') as start_pos
    ,date('now') as base_pos
    ,date('now',(+s1.value)||' day') as end_pos
    ,julianday(date('now',(-s1.value)||' day')) as start_pos_jly
    ,julianday('now') as base_pos_jly
    ,julianday(date('now',(+s1.value)||' day')) as end_pos_jly
    ,ceil(round(julianday(date('now',(+s1.value)||' day'))-julianday('now'))) as diff_to_end
    ,ceil(round(julianday('now')-julianday(date('now',(+s1.value)||' day')))) as diff_to_start
  from
    generate_series(1,"$@") s1
      inner join generate_series(s1.value-1,s1.value+"$@") s2 on s1.value-1=s2.value
      inner join generate_series(s1.value+1,s1.value+"$@") s3 on s1.value+1=s3.value
  ;
EOS
)

$./日付生成.sh 10
start_pos   base_pos    end_pos     start_pos_jly  base_pos_jly      end_pos_jly  diff_to_end  diff_to_start
----------  ----------  ----------  -------------  ----------------  -----------  -----------  -------------
2019-11-30  2019-12-01  2019-12-02  2458817.5      2458818.82898762  2458819.5    1            -1           
2019-11-29  2019-12-01  2019-12-03  2458816.5      2458818.82898762  2458820.5    2            -2           
2019-11-28  2019-12-01  2019-12-04  2458815.5      2458818.82898762  2458821.5    3            -3           
2019-11-27  2019-12-01  2019-12-05  2458814.5      2458818.82898762  2458822.5    4            -4           
2019-11-26  2019-12-01  2019-12-06  2458813.5      2458818.82898762  2458823.5    5            -5           
2019-11-25  2019-12-01  2019-12-07  2458812.5      2458818.82898762  2458824.5    6            -6           
2019-11-24  2019-12-01  2019-12-08  2458811.5      2458818.82898762  2458825.5    7            -7           
2019-11-23  2019-12-01  2019-12-09  2458810.5      2458818.82898762  2458826.5    8            -8           
2019-11-22  2019-12-01  2019-12-10  2458809.5      2458818.82898762  2458827.5    9            -9           
2019-11-21  2019-12-01  2019-12-11  2458808.5      2458818.82898762  2458828.5    10           -10

これで3の倍数出るんだなー。

$echo "with sub as(select value as n from generate_series(0,10)),build_sql as(select 'select value,1+value from generate_series('||n||','||n+n+n+n||'); as rez from sub)select rez from build_sql;" | sqlite3
rez       
----------
0         
3         
6         
9         
12        
15        
18        
21        
24        
27        
30

これだと2の倍数。

$echo "with sub as(select value as n from generate_series(0,10)),build_sql as(select 'select value,1+value from generate_series('||n||','||n+n+||');' as rez from sub)select rez from build_sql;"|sqlite3
rez       
----------
0         
2         
4         
6         
8         
10        
12        
14        
16        
18        
20

そのままでる。

$echo "with sub as(select value as n from generate_series(1,10)),build_sql as(select 'select value,1+value from generate_series('||n||','||n+n|');' as rez from sub)select rez from build_sql;"|sqlite3
rez       
----------
1         
2         
3         
4         
5         
6         
7         
8         
9         
10

不思議な規則。

$echo "with sub as(select value as n from generate_series(-10,0)),build_sql as(select 'select value,1+value from generate_series('||n||','||n+n+n||');'as rez from sub)select rez from build_sql;"|sqlite3
rez       
----------
-20       
-18       
-16       
-14       
-12       
-10       
-8        
-6        
-4        
-2        
0         
$echo "with sub as(select value as n from generate_series(-10,10)),build_sql as(select 'select value,1+value from generate_series('||n||','||n+n+n|');' as rez from sub)select rez from build_sql;"|sqlite3
rez       
----------
-20       
-18       
-16       
-14       
-12       
-10       
-8        
-6        
-4        
-2        
0         
2         
4         
6         
8         
10        
12        
14        
16        
18        
20