Help us understand the problem. What is going on with this article?

sqlite3でマンデルブロ集合を見つけた。

まえがき

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面白い!

ukijumotahaneniarukenia
sqlが大好き。(oracle) WordPressはじめました。sql oracleやそれ以外について調べたこととか記していきます。 https://github.com/ukijumotahaneniarukenia
https://ukijumotahaneniarukenia.site/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした