まえがき

sqlite3のサイトをぼーっとブラブラしていたら、かっこいいもの見つけたので、いろいろ掘り下げた結果をメモする。参考文献の数学の公式とかはもう結構厳しい😂。ただ、頑張ってみる。

参考文献

愛おしすぎるMandelbrot集合をProcessingで描く

Outlandish Recursive Query Examples

マンデルブロ集合

データベース

環境

[sqlite❣77131ea43b7d (水 10月 16 20:21:16) ~]$sqlite --version
3.30.0 2019-10-04 15:03:17 c20a35336432025445f9f7e289d0cc3e4003fb17f45a4ce74c6269c407c6e09f
[sqlite❣77131ea43b7d (水 10月 16 20:21:27) ~]$sqlite
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>

クエリ

参考文献マンデルブロ集合に貼ってあるクエリを実行すると、以下の結果が得られてびっくりした。

(x*x + y*y) < 4.0は$\sqrt[]{x_n^2 + y_n^2}$が2以上になるまで繰り返しを表現していると思われ、x*x-y*y + cx,2.0*x*y + cy列は

\begin{eqnarray}
x_0 = 0, & \quad &  x_{n + 1} = x_n^2 - y_n^2 + a \\
y_0 = 0, & \quad & y_{n + 1} = 2x_ny_n + b
\end{eqnarray}

を表現していると思われる。前回の再帰蓄積結果であるx,yから今回の再帰処理結果x*x-y*y + cx,2.0*x*y + cyを求め、再帰繰返条件の上限(x*x + y*y) < 4.0 AND iter<28に達するまで、各再帰処理ごとに結果を蓄積していく。蓄積した結果からX,Y座標の組み合わせcx, cy,ごとに最大再帰回数をもとめ、取得した最大値を元に飾り付けの部分を整形して出力している。

WITH RECURSIVE
  xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+0.05 FROM xaxis WHERE x<1.2),
  yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+0.1 FROM yaxis WHERE y<1.0),
  m(iter, cx, cy, x, y) AS (
    SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis
    UNION ALL
    SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m 
     WHERE (x*x + y*y) < 4.0 AND iter<28
  ),
  m2(iter, cx, cy) AS (
    SELECT max(iter), cx, cy FROM m GROUP BY cx, cy
  ),
  a(t) AS (
    SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '') 
    FROM m2 GROUP BY cy
  )
SELECT group_concat(rtrim(t),x'0a') FROM a;

sqlite> WITH RECURSIVE
   ...>   xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+0.05 FROM xaxis WHERE x<1.2),
   ...>   yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+0.1 FROM yaxis WHERE y<1.0),
   ...>   m(iter, cx, cy, x, y) AS (
   ...>     SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis
   ...>     UNION ALL
   ...>     SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m 
   ...>      WHERE (x*x + y*y) < 4.0 AND iter<28
   ...>   ),
   ...>   m2(iter, cx, cy) AS (
   ...>     SELECT max(iter), cx, cy FROM m GROUP BY cx, cy
   ...>   ),
   ...>   a(t) AS (
   ...>     SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '') 
   ...>     FROM m2 GROUP BY cy
   ...>   )
   ...> SELECT group_concat(rtrim(t),x'0a') FROM a;
                                    ....#
                                   ..#*..
                                 ..+####+.
                            .......+####....   +
                           ..##+*##########+.++++
                          .+.##################+.
              .............+###################+.+
              ..++..#.....*#####################+.
             ...+#######++#######################.
          ....+*################################.
 #############################################...
          ....+*################################.
             ...+#######++#######################.
              ..++..#.....*#####################+.
              .............+###################+.+
                          .+.##################+.
                           ..##+*##########+.++++
                            .......+####....   +
                                 ..+####+.
                                   ..#*..
                                    ....#
                                    +.

もうちょいねばる。ひとまず、量を少なめにして確認していく。

sqlite> with recursive
   ...>   xaxis(pre,x) as (select -1,-1 union all select x,x+0.5 from xaxis where x<1.0)select * from xaxis;
pre         x         
----------  ----------
-1          -1        
-1          -0.5      
-0.5        0.0       
0.0         0.5       
0.5         1.0       
sqlite> with recursive
   ...>   xaxis(pre,x) as (select -1,-1 union all select x,x+0.5 from xaxis where x<1.0),
   ...>   yaxis(pre,y) as (select -1,-1 union all select y,y+0.5 from yaxis where y<1.0) select * from yaxis;
pre         y         
----------  ----------
-1          -1        
-1          -0.5      
-0.5        0.0       
0.0         0.5       
0.5         1.0

XとY軸から直積して組み合わせをもとめる。

sqlite> with recursive
   ...>   xaxis(pre,x) as (select -1,-1 union all select x,x+0.5 from xaxis where x<1.0),
   ...>   yaxis(pre,y) as (select -1,-1 union all select y,y+0.5 from yaxis where y<1.0) select * from xaxis,yaxis;
pre         x           pre         y         
----------  ----------  ----------  ----------
-1          -1          -1          -1        
-1          -1          -1          -0.5      
-1          -1          -0.5        0.0       
-1          -1          0.0         0.5       
-1          -1          0.5         1.0       
-1          -0.5        -1          -1        
-1          -0.5        -1          -0.5      
-1          -0.5        -0.5        0.0       
-1          -0.5        0.0         0.5       
-1          -0.5        0.5         1.0       
-0.5        0.0         -1          -1        
-0.5        0.0         -1          -0.5      
-0.5        0.0         -0.5        0.0       
-0.5        0.0         0.0         0.5       
-0.5        0.0         0.5         1.0       
0.0         0.5         -1          -1        
0.0         0.5         -1          -0.5      
0.0         0.5         -0.5        0.0       
0.0         0.5         0.0         0.5       
0.0         0.5         0.5         1.0       
0.5         1.0         -1          -1        
0.5         1.0         -1          -0.5      
0.5         1.0         -0.5        0.0       
0.5         1.0         0.0         0.5       
0.5         1.0         0.5         1.0

再帰処理はデバッグしにくいので、再帰繰返条件を短くして、見ていくことにする。
union allより上の部分SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxisは非再帰項で一度しか実行されない。
union allより下の部分SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM mは再帰項で再帰繰返条件がtureになるかぎり、実行され続ける。この例だと、where iter = 0の部分。

sqlite> with recursive
   ...>   xaxis(pre,x) as (select -1,-1 union all select x,x+0.5 from xaxis where x<1.0),
   ...>   yaxis(pre,y) as (select -1,-1 union all select y,y+0.5 from yaxis where y<1.0),
   ...>   m(iter, cx, cy, x, y) AS (
   ...>     SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis
   ...>     union all
   ...>     SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m
   ...>     where iter = 0
   ...> )select * from m;
iter        cx          cy          x           y         
----------  ----------  ----------  ----------  ----------
0           -1          -1          0.0         0.0       
0           -1          -0.5        0.0         0.0       
0           -1          0.0         0.0         0.0       
0           -1          0.5         0.0         0.0       
0           -1          1.0         0.0         0.0       
0           -0.5        -1          0.0         0.0       
0           -0.5        -0.5        0.0         0.0       
0           -0.5        0.0         0.0         0.0       
0           -0.5        0.5         0.0         0.0       
0           -0.5        1.0         0.0         0.0       
0           0.0         -1          0.0         0.0       
0           0.0         -0.5        0.0         0.0       
0           0.0         0.0         0.0         0.0       
0           0.0         0.5         0.0         0.0       
0           0.0         1.0         0.0         0.0       
0           0.5         -1          0.0         0.0       
0           0.5         -0.5        0.0         0.0       
0           0.5         0.0         0.0         0.0       
0           0.5         0.5         0.0         0.0       
0           0.5         1.0         0.0         0.0       
0           1.0         -1          0.0         0.0       
0           1.0         -0.5        0.0         0.0       
0           1.0         0.0         0.0         0.0       
0           1.0         0.5         0.0         0.0       
0           1.0         1.0         0.0         0.0       
1           -1          -1          -1.0        -1.0      
1           -1          -0.5        -1.0        -0.5      
1           -1          0.0         -1.0        0.0       
1           -1          0.5         -1.0        0.5       
1           -1          1.0         -1.0        1.0       
1           -0.5        -1          -0.5        -1.0      
1           -0.5        -0.5        -0.5        -0.5      
1           -0.5        0.0         -0.5        0.0       
1           -0.5        0.5         -0.5        0.5       
1           -0.5        1.0         -0.5        1.0       
1           0.0         -1          0.0         -1.0      
1           0.0         -0.5        0.0         -0.5      
1           0.0         0.0         0.0         0.0       
1           0.0         0.5         0.0         0.5       
1           0.0         1.0         0.0         1.0       
1           0.5         -1          0.5         -1.0      
1           0.5         -0.5        0.5         -0.5      
1           0.5         0.0         0.5         0.0       
1           0.5         0.5         0.5         0.5       
1           0.5         1.0         0.5         1.0       
1           1.0         -1          1.0         -1.0      
1           1.0         -0.5        1.0         -0.5      
1           1.0         0.0         1.0         0.0       
1           1.0         0.5         1.0         0.5       
1           1.0         1.0         1.0         1.0

iterごとに件数等見てみる。1回の再帰処理で25行ずつ。この25行はX軸とY軸の組み合わせでできる行数と同じ。

sqlite> with recursive
   ...>   xaxis(pre,x) as (select -1,-1 union all select x,x+0.5 from xaxis where x<1.0),
   ...>   yaxis(pre,y) as (select -1,-1 union all select y,y+0.5 from yaxis where y<1.0),
   ...>   m(iter, cx, cy, x, y) AS (
   ...>     SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis
   ...>     union all
   ...>     SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m
   ...>     where iter < 2
   ...> )select iter,count(*),group_concat(cx,','),group_concat(cy,','),group_concat(x,','),group_concat(y,',') from m group by iter;
iter        count(*)    group_concat(cx,',')                                                                                 group_concat(cy,',')                                                                                 group_concat(x,',')                                                                                  group_concat(y,',')                                                                                
----------  ----------  ---------------------------------------------------------------------------------------------------  ---------------------------------------------------------------------------------------------------  ---------------------------------------------------------------------------------------------------  ---------------------------------------------------------------------------------------------------
0           25          -1,-1,-1,-1,-1,-0.5,-0.5,-0.5,-0.5,-0.5,0.0,0.0,0.0,0.0,0.0,0.5,0.5,0.5,0.5,0.5,1.0,1.0,1.0,1.0,1.0  -1,-0.5,0.0,0.5,1.0,-1,-0.5,0.0,0.5,1.0,-1,-0.5,0.0,0.5,1.0,-1,-0.5,0.0,0.5,1.0,-1,-0.5,0.0,0.5,1.0  0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0  0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0
1           25          -1,-1,-1,-1,-1,-0.5,-0.5,-0.5,-0.5,-0.5,0.0,0.0,0.0,0.0,0.0,0.5,0.5,0.5,0.5,0.5,1.0,1.0,1.0,1.0,1.0  -1,-0.5,0.0,0.5,1.0,-1,-0.5,0.0,0.5,1.0,-1,-0.5,0.0,0.5,1.0,-1,-0.5,0.0,0.5,1.0,-1,-0.5,0.0,0.5,1.0  -1.0,-1.0,-1.0,-1.0,-1.0,-0.5,-0.5,-0.5,-0.5,-0.5,0.0,0.0,0.0,0.0,0.0,0.5,0.5,0.5,0.5,0.5,1.0,1.0,1  -1.0,-0.5,0.0,0.5,1.0,-1.0,-0.5,0.0,0.5,1.0,-1.0,-0.5,0.0,0.5,1.0,-1.0,-0.5,0.0,0.5,1.0,-1.0,-0.5,0
2           25          -1,-1,-1,-1,-1,-0.5,-0.5,-0.5,-0.5,-0.5,0.0,0.0,0.0,0.0,0.0,0.5,0.5,0.5,0.5,0.5,1.0,1.0,1.0,1.0,1.0  -1,-0.5,0.0,0.5,1.0,-1,-0.5,0.0,0.5,1.0,-1,-0.5,0.0,0.5,1.0,-1,-0.5,0.0,0.5,1.0,-1,-0.5,0.0,0.5,1.0  -1.0,-0.25,0.0,-0.25,-1.0,-1.25,-0.5,-0.25,-0.5,-1.25,-1.0,-0.25,0.0,-0.25,-1.0,-0.25,0.5,0.75,0.5,  1.0,0.5,0.0,-0.5,-1.0,0.0,0.0,0.0,0.0,0.0,-1.0,-0.5,0.0,0.5,1.0,-2.0,-1.0,0.0,1.0,2.0,-3.0,-1.5,0.0

cx,cyごとにみるとこうなる。各cx,cyの組み合わせ25件ごとに3件ずつ。完成形でのクエリは最大再帰回数を取得しているが今回はイメージをつかむための簡略版なので、どの行も2である。

sqlite> with recursive
   ...>   xaxis(pre,x) as (select -1,-1 union all select x,x+0.5 from xaxis where x<1.0),
   ...>   yaxis(pre,y) as (select -1,-1 union all select y,y+0.5 from yaxis where y<1.0),
   ...>   m(iter, cx, cy, x, y) AS (
   ...>     SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis
   ...>     union all
   ...>     SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m
   ...>     where iter < 2
   ...> )select cx,cy,count(*)over(),max(iter),count(*),group_concat(iter,','),group_concat(x,','),group_concat(y,',') from m group by cx,cy;
cx          cy          count(*)over()  max(iter)   count(*)    group_concat(iter,',')  group_concat(x,',')  group_concat(y,',')
----------  ----------  --------------  ----------  ----------  ----------------------  -------------------  -------------------
-1          -1          25              2           3           0,1,2                   0.0,-1.0,-1.0        0.0,-1.0,1.0       
-1          -0.5        25              2           3           0,1,2                   0.0,-1.0,-0.25       0.0,-0.5,0.5       
-1          0.0         25              2           3           0,1,2                   0.0,-1.0,0.0         0.0,0.0,0.0        
-1          0.5         25              2           3           0,1,2                   0.0,-1.0,-0.25       0.0,0.5,-0.5       
-1          1.0         25              2           3           0,1,2                   0.0,-1.0,-1.0        0.0,1.0,-1.0       
-0.5        -1          25              2           3           0,1,2                   0.0,-0.5,-1.25       0.0,-1.0,0.0       
-0.5        -0.5        25              2           3           0,1,2                   0.0,-0.5,-0.5        0.0,-0.5,0.0       
-0.5        0.0         25              2           3           0,1,2                   0.0,-0.5,-0.25       0.0,0.0,0.0        
-0.5        0.5         25              2           3           0,1,2                   0.0,-0.5,-0.5        0.0,0.5,0.0        
-0.5        1.0         25              2           3           0,1,2                   0.0,-0.5,-1.25       0.0,1.0,0.0        
0.0         -1          25              2           3           0,1,2                   0.0,0.0,-1.0         0.0,-1.0,-1.0      
0.0         -0.5        25              2           3           0,1,2                   0.0,0.0,-0.25        0.0,-0.5,-0.5      
0.0         0.0         25              2           3           0,1,2                   0.0,0.0,0.0          0.0,0.0,0.0        
0.0         0.5         25              2           3           0,1,2                   0.0,0.0,-0.25        0.0,0.5,0.5        
0.0         1.0         25              2           3           0,1,2                   0.0,0.0,-1.0         0.0,1.0,1.0        
0.5         -1          25              2           3           0,1,2                   0.0,0.5,-0.25        0.0,-1.0,-2.0      
0.5         -0.5        25              2           3           0,1,2                   0.0,0.5,0.5          0.0,-0.5,-1.0      
0.5         0.0         25              2           3           0,1,2                   0.0,0.5,0.75         0.0,0.0,0.0        
0.5         0.5         25              2           3           0,1,2                   0.0,0.5,0.5          0.0,0.5,1.0        
0.5         1.0         25              2           3           0,1,2                   0.0,0.5,-0.25        0.0,1.0,2.0        
1.0         -1          25              2           3           0,1,2                   0.0,1.0,1.0          0.0,-1.0,-3.0      
1.0         -0.5        25              2           3           0,1,2                   0.0,1.0,1.75         0.0,-0.5,-1.5      
1.0         0.0         25              2           3           0,1,2                   0.0,1.0,2.0          0.0,0.0,0.0        
1.0         0.5         25              2           3           0,1,2                   0.0,1.0,1.75         0.0,0.5,1.5        
1.0         1.0         25              2           3           0,1,2                   0.0,1.0,1.0          0.0,1.0,3.0

次に、飾り付けの部分をみてみる。

group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '')

これは「空白( ),ドット(.),プラス(+),アスタリスク(*),ナンバー(#)」の5つの文字からナンバー(#)にしない割合を繰返回数/7では調節していると思われる。分母が大きくなれば、ナンバー(#)になりにくくなるし、小さくなれば、ナンバー(#)になりやすくなる。

使われているmin関数は複数の引数のうち最も小さい方を返却する。一方でmax関数は複数の引数のうち最も大きい方を返却する。単一の引数の場合は受け取った引数をそのまま返却する。

sqlite> select min(1,1,2);
min(1,1,2)
----------
1         
sqlite> select min(1,-1,2);
min(1,-1,2)
-----------
-1         
sqlite> select min(-1,2);
min(-1,2) 
----------
-1        
sqlite> select max(-1,2);
max(-1,2) 
----------
2         
sqlite> select max(-1,-2);
max(-1,-2)
----------
-1        
sqlite> select max(-1,-2,3);
max(-1,-2,3)
------------
3           
sqlite> select max(-1);
max(-1)   
----------
-1        
sqlite> select min(-1);
min(-1)   
----------
-1

ダミデータ用意。ここでのgrpがiterと考えて明細行単位でみてみる。

sqlite> with recursive cnt(x) as (values(1) union all select x+1 from cnt where x<30)
   ...> ,sub as(select x%3 as grp,x from cnt order by grp)
   ...> select grp,x/7,min(x/7,4),substr(' .+*#',1+min(x/7,4),1),group_concat(substr(' .+*#',min(x/7,4),1),'')over(partition by grp),count(*)over() as all_cnt,count(grp)over(partition by grp) as grp_cnt,row_number()over(partition by grp) as grp_seq from sub;
grp         x/7         min(x/7,4)  substr(' .+*#',1+min(x/7,4),1)  group_concat(substr(' .+*#',min(x/7,4),1),'')over(partition by grp)  all_cnt     grp_cnt     grp_seq   
----------  ----------  ----------  ------------------------------  -------------------------------------------------------------------  ----------  ----------  ----------
0           0           0                                             ..+++*                                                             30          10          1         
0           0           0                                             ..+++*                                                             30          10          2         
0           1           1           .                                 ..+++*                                                             30          10          3         
0           1           1           .                                 ..+++*                                                             30          10          4         
0           2           2           +                                 ..+++*                                                             30          10          5         
0           2           2           +                                 ..+++*                                                             30          10          6         
0           3           3           *                                 ..+++*                                                             30          10          7         
0           3           3           *                                 ..+++*                                                             30          10          8         
0           3           3           *                                 ..+++*                                                             30          10          9         
0           4           4           #                                 ..+++*                                                             30          10          10        
1           0           0                                              ..++*                                                             30          10          1         
1           0           0                                              ..++*                                                             30          10          2         
1           1           1           .                                  ..++*                                                             30          10          3         
1           1           1           .                                  ..++*                                                             30          10          4         
1           1           1           .                                  ..++*                                                             30          10          5         
1           2           2           +                                  ..++*                                                             30          10          6         
1           2           2           +                                  ..++*                                                             30          10          7         
1           3           3           *                                  ..++*                                                             30          10          8         
1           3           3           *                                  ..++*                                                             30          10          9         
1           4           4           #                                  ..++*                                                             30          10          10        
2           0           0                                             ...++*                                                             30          10          1         
2           0           0                                             ...++*                                                             30          10          2         
2           1           1           .                                 ...++*                                                             30          10          3         
2           1           1           .                                 ...++*                                                             30          10          4         
2           2           2           +                                 ...++*                                                             30          10          5         
2           2           2           +                                 ...++*                                                             30          10          6         
2           2           2           +                                 ...++*                                                             30          10          7         
2           3           3           *                                 ...++*                                                             30          10          8         
2           3           3           *                                 ...++*                                                             30          10          9         
2           4           4           #                                 ...++*                                                             30          10          10

これをサマリ行単位で見てみる。

sqlite> with recursive cnt(x) as (values(1) union all select x+1 from cnt where x<30)
   ...> ,sub as(select x%3 as grp,x from cnt order by grp)
   ...> select grp,group_concat(substr(' .+*#',min(x/7,4),1),'')from sub group by grp;
grp         group_concat(substr(' .+*#',min(x/7,4),1),'')
----------  ---------------------------------------------
0             ..+++*                                     
1              ..++*                                     
2             ...++*

んでこれを集合全体でさまり。とおもったけど、group_concatの第2引数に0a与えるとグループごとにサマリした結果と同じになった。

sqlite> with recursive cnt(x) as (values(1) union all select x+1 from cnt where x<30)
   ...> ,sub as(select x%3 as grp,x from cnt order by grp)
   ...> ,subb as (select grp,group_concat(substr(' .+*#',min(x/7,4),1),'') as item from sub group by grp)
   ...> select group_concat(rtrim(item),'')from subb;
group_concat(rtrim(item),'')
----------------------------
  ..+++*   ..++*  ...++*    
sqlite> with recursive cnt(x) as (values(1) union all select x+1 from cnt where x<30)
   ...> ,sub as(select x%3 as grp,x from cnt order by grp)
   ...> ,subb as (select grp,group_concat(substr(' .+*#',min(x/7,4),1),'') as item from sub group by grp)
   ...> select group_concat(rtrim(item),x'')from subb;
group_concat(rtrim(item),x'')
-----------------------------
  ..+++*   ..++*  ...++*     
sqlite> with recursive cnt(x) as (values(1) union all select x+1 from cnt where x<30)
   ...> ,sub as(select x%3 as grp,x from cnt order by grp)
   ...> ,subb as (select grp,group_concat(substr(' .+*#',min(x/7,4),1),'') as item from sub group by grp)
   ...> select group_concat(rtrim(item),x'0a')from subb;
group_concat(rtrim(item),x'0a')
-------------------------------
  ..+++*
   ..++*
  ...++*

ちなみに0bにするとおもしろい。ちょっとかわる。

sqlite> with recursive cnt(x) as (values(1) union all select x+1 from cnt where x<30)
   ...> ,sub as(select x%3 as grp,x from cnt order by grp)
   ...> ,subb as (select grp,group_concat(substr(' .+*#',min(x/7,4),1),'') as item from sub group by grp)
   ...> select group_concat(rtrim(item),x'0b')from subb;
group_concat(rtrim(item),x'0b')
-------------------------------
  ..+++*
           ..++*
                  ...++*

最後にパラメータいじってあそぶ

分母1の場合

WITH RECURSIVE
  xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+0.05 FROM xaxis WHERE x<1.2),
  yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+0.1 FROM yaxis WHERE y<1.0),
  m(iter, cx, cy, x, y) AS (
    SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis
    UNION ALL
    SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m 
     WHERE (x*x + y*y) < 4.0 AND iter<28
  ),
  m2(iter, cx, cy) AS (
    SELECT max(iter), cx, cy FROM m GROUP BY cx, cy
  ),
  a(t) AS (
    SELECT group_concat( substr(' .+*#', 1+min(iter/1,4), 1), '') 
    FROM m2 GROUP BY cy
  )
SELECT group_concat(rtrim(t),x'0a') FROM a;

ナンバー(#)の割合もりもり。

sqlite> WITH RECURSIVE
   ...>   xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+0.05 FROM xaxis WHERE x<1.2),
   ...>   yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+0.1 FROM yaxis WHERE y<1.0),
   ...>   m(iter, cx, cy, x, y) AS (
   ...>     SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis
   ...>     UNION ALL
   ...>     SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m 
   ...>      WHERE (x*x + y*y) < 4.0 AND iter<28
   ...>   ),
   ...>   m2(iter, cx, cy) AS (
   ...>     SELECT max(iter), cx, cy FROM m GROUP BY cx, cy
   ...>   ),
   ...>   a(t) AS (
   ...>     SELECT group_concat( substr(' .+*#', 1+min(iter/1,4), 1), '') 
   ...>     FROM m2 GROUP BY cy
   ...>   )
   ...> SELECT group_concat(rtrim(t),x'0a') FROM a;
......+++++****************###################****+++++++++++++++
.....+++****************########################*****++++++++++++
....++****************###########################******++++++++++
...++**************################################*****+++++++++
..++*************##################################******++++++++
..+***********######################################******+++++++
.+*******############################################******++++++
.***#################################################*******+++++
.*####################################################******+++++
.#####################################################******+++++
.#####################################################******+++++
.#####################################################******+++++
.*####################################################******+++++
.***#################################################*******+++++
.+*******############################################******++++++
..+***********######################################******+++++++
..++*************##################################******++++++++
...++**************################################*****+++++++++
....++****************###########################******++++++++++
.....+++****************########################*****++++++++++++
......+++++****************###################****+++++++++++++++
.......+++++++****************#############****++++++++++++++++++

分母3の場合

WITH RECURSIVE
  xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+0.05 FROM xaxis WHERE x<1.2),
  yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+0.1 FROM yaxis WHERE y<1.0),
  m(iter, cx, cy, x, y) AS (
    SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis
    UNION ALL
    SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m 
     WHERE (x*x + y*y) < 4.0 AND iter<28
  ),
  m2(iter, cx, cy) AS (
    SELECT max(iter), cx, cy FROM m GROUP BY cx, cy
  ),
  a(t) AS (
    SELECT group_concat( substr(' .+*#', 1+min(iter/3,4), 1), '') 
    FROM m2 GROUP BY cy
  )
SELECT group_concat(rtrim(t),x'0a') FROM a;

ちょっとへった

sqlite> WITH RECURSIVE
   ...>   xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+0.05 FROM xaxis WHERE x<1.2),
   ...>   yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+0.1 FROM yaxis WHERE y<1.0),
   ...>   m(iter, cx, cy, x, y) AS (
   ...>     SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis
   ...>     UNION ALL
   ...>     SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m 
   ...>      WHERE (x*x + y*y) < 4.0 AND iter<28
   ...>   ),
   ...>   m2(iter, cx, cy) AS (
   ...>     SELECT max(iter), cx, cy FROM m GROUP BY cx, cy
   ...>   ),
   ...>   a(t) AS (
   ...>     SELECT group_concat( substr(' .+*#', 1+min(iter/3,4), 1), '') 
   ...>     FROM m2 GROUP BY cy
   ...>   )
   ...> SELECT group_concat(rtrim(t),x'0a') FROM a;
           ........................++***#.........
        ..........................++*##*++...........
      .........................++++######++............
     ......................++++++**######**++++#........
    .....................+++#####################........
   ....................++++#####################++........
  ............+#++++++++++*#####################*#.........
 ............++*######****#######################++.........
 ...........+++*#################################+..........
 .....++++++####################################++..........
 ##############################################+++..........
 .....++++++####################################++..........
 ...........+++*#################################+..........
 ............++*######****#######################++.........
  ............+#++++++++++*#####################*#.........
   ....................++++#####################++........
    .....................+++#####################........
     ......................++++++**######**++++#........
      .........................++++######++............
        ..........................++*##*++...........
           ........................++***#.........
              ......................#+.........

分母13の場合

WITH RECURSIVE
  xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+0.05 FROM xaxis WHERE x<1.2),
  yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+0.1 FROM yaxis WHERE y<1.0),
  m(iter, cx, cy, x, y) AS (
    SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis
    UNION ALL
    SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m 
     WHERE (x*x + y*y) < 4.0 AND iter<28
  ),
  m2(iter, cx, cy) AS (
    SELECT max(iter), cx, cy FROM m GROUP BY cx, cy
  ),
  a(t) AS (
    SELECT group_concat( substr(' .+*#', 1+min(iter/13,4), 1), '') 
    FROM m2 GROUP BY cy
  )
SELECT group_concat(rtrim(t),x'0a') FROM a;

いなくなった

sqlite> WITH RECURSIVE
   ...>   xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+0.05 FROM xaxis WHERE x<1.2),
   ...>   yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+0.1 FROM yaxis WHERE y<1.0),
   ...>   m(iter, cx, cy, x, y) AS (
   ...>     SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis
   ...>     UNION ALL
   ...>     SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m 
   ...>      WHERE (x*x + y*y) < 4.0 AND iter<28
   ...>   ),
   ...>   m2(iter, cx, cy) AS (
   ...>     SELECT max(iter), cx, cy FROM m GROUP BY cx, cy
   ...>   ),
   ...>   a(t) AS (
   ...>     SELECT group_concat( substr(' .+*#', 1+min(iter/13,4), 1), '') 
   ...>     FROM m2 GROUP BY cy
   ...>   )
   ...> SELECT group_concat(rtrim(t),x'0a') FROM a;
                                        +
                                     +.
                                   .++++.
                                   .++++.      .
                             ++.+++++++++++. ....
                           . ++++++++++++++++++.
                           .+++++++++++++++++++. .
                .. .+.    .+++++++++++++++++++++.
                .+++++++..+++++++++++++++++++++++
             ...++++++++++++++++++++++++++++++++
 +++++++++++++++++++++++++++++++++++++++++++++
             ...++++++++++++++++++++++++++++++++
                .+++++++..+++++++++++++++++++++++
                .. .+.    .+++++++++++++++++++++.
                           .+++++++++++++++++++. .
                           . ++++++++++++++++++.
                             ++.+++++++++++. ....
                                   .++++.      .
                                   .++++.
                                     +.
                                        +
                                    .

繰り返しの数36

WITH RECURSIVE
  xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+0.05 FROM xaxis WHERE x<1.2),
  yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+0.1 FROM yaxis WHERE y<1.0),
  m(iter, cx, cy, x, y) AS (
    SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis
    UNION ALL
    SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m 
     WHERE (x*x + y*y) < 4.0 AND iter<36
  ),
  m2(iter, cx, cy) AS (
    SELECT max(iter), cx, cy FROM m GROUP BY cx, cy
  ),
  a(t) AS (
    SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '') 
    FROM m2 GROUP BY cy
  )
SELECT group_concat(rtrim(t),x'0a') FROM a;

sqlite> WITH RECURSIVE
   ...>   xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+0.05 FROM xaxis WHERE x<1.2),
   ...>   yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+0.1 FROM yaxis WHERE y<1.0),
   ...>   m(iter, cx, cy, x, y) AS (
   ...>     SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis
   ...>     UNION ALL
   ...>     SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m 
   ...>      WHERE (x*x + y*y) < 4.0 AND iter<36
   ...>   ),
   ...>   m2(iter, cx, cy) AS (
   ...>     SELECT max(iter), cx, cy FROM m GROUP BY cx, cy
   ...>   ),
   ...>   a(t) AS (
   ...>     SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '') 
   ...>     FROM m2 GROUP BY cy
   ...>   )
   ...> SELECT group_concat(rtrim(t),x'0a') FROM a;
                                    ....#
                                   ..#*..
                                 ..+####+.
                            .......+####....   +
                           ..##+*##########+.++++
                          .+.##################+.
              .............+###################+.+
              ..++..#.....*#####################+.
             ...+#######++#######################.
          ....+*################################.
 #############################################...
          ....+*################################.
             ...+#######++#######################.
              ..++..#.....*#####################+.
              .............+###################+.+
                          .+.##################+.
                           ..##+*##########+.++++
                            .......+####....   +
                                 ..+####+.
                                   ..#*..
                                    ....#
                                    +.

繰り返しの数48

WITH RECURSIVE
  xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+0.05 FROM xaxis WHERE x<1.2),
  yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+0.1 FROM yaxis WHERE y<1.0),
  m(iter, cx, cy, x, y) AS (
    SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis
    UNION ALL
    SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m 
     WHERE (x*x + y*y) < 4.0 AND iter<48
  ),
  m2(iter, cx, cy) AS (
    SELECT max(iter), cx, cy FROM m GROUP BY cx, cy
  ),
  a(t) AS (
    SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '') 
    FROM m2 GROUP BY cy
  )
SELECT group_concat(rtrim(t),x'0a') FROM a;

sqlite> WITH RECURSIVE
   ...>   xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+0.05 FROM xaxis WHERE x<1.2),
   ...>   yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+0.1 FROM yaxis WHERE y<1.0),
   ...>   m(iter, cx, cy, x, y) AS (
   ...>     SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis
   ...>     UNION ALL
   ...>     SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m 
   ...>      WHERE (x*x + y*y) < 4.0 AND iter<48
   ...>   ),
   ...>   m2(iter, cx, cy) AS (
   ...>     SELECT max(iter), cx, cy FROM m GROUP BY cx, cy
   ...>   ),
   ...>   a(t) AS (
   ...>     SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '') 
   ...>     FROM m2 GROUP BY cy
   ...>   )
   ...> SELECT group_concat(rtrim(t),x'0a') FROM a;
                                    ....#
                                   ..#*..
                                 ..+####+.
                            .......+####....   +
                           ..##+*##########+.++++
                          .+.##################+.
              .............+###################+.+
              ..++..#.....*#####################+.
             ...+#######++#######################.
          ....+*################################.
 #############################################...
          ....+*################################.
             ...+#######++#######################.
              ..++..#.....*#####################+.
              .............+###################+.+
                          .+.##################+.
                           ..##+*##########+.++++
                            .......+####....   +
                                 ..+####+.
                                   ..#*..
                                    ....#
                                    +.

あとがき

こういうのみるとやめられなくなるよなー😂。マンデルブロ集合が好きになった。
wikiの画像ずっと見ていられる。。
sqlite3も最近いろいろ機能追加あったもようなので、これからもいろいろいじくり倒して試していきたい。😂
分析関数の練習になった。json関数とかもあるらしい。

The JSON1 Extension

日本語版はこれかな
SQLite 3.25

sqlite> select json(' { "this" : "is", "a": [ "test" ] } ') ;
{"this":"is","a":["test"]}

面白いものも見つけた。

UNIX データベース入門

まだまだ知らないこともおおいので、C言語とかも頑張ってソースコードは読めるようになっていきたい。どんな実装になっているか興味すごくある。

sql面白い!