LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@ukijumotahaneniarukenia

sqlite3 generate_seriesを使用できるようにする

Last updated at Posted at 2019-12-01

まえがき

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
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?