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

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

# 参考文献

[愛おしすぎるMandelbrot集合をProcessingで描く](https://qiita.com/ryunryunryun/items/85a7e95186af4490198b)

[Outlandish Recursive Query Examples](https://www.sqlite.org/lang_with.html)

[マンデルブロ集合](https://ja.wikipedia.org/wiki/%E3%83%9E%E3%83%B3%E3%83%87%E3%83%AB%E3%83%96%E3%83%AD%E9%9B%86%E5%90%88)

[データベース](https://oku.edu.mie-u.ac.jp/~okumura/php/db.html)

# 環境

```bash
[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> 
```



# クエリ

参考文献[マンデルブロ集合](https://ja.wikipedia.org/wiki/%E3%83%9E%E3%83%B3%E3%83%87%E3%83%AB%E3%83%96%E3%83%AD%E9%9B%86%E5%90%88)に貼ってあるクエリを実行すると、以下の結果が得られてびっくりした。

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

```math
\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,**ごとに最大再帰回数をもとめ、取得した最大値を元に飾り付けの部分を整形して出力している。

```sql
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;
```

```sql
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;
                                    ....#
                                   ..#*..
                                 ..+####+.
                            .......+####....   +
                           ..##+*##########+.++++
                          .+.##################+.
              .............+###################+.+
              ..++..#.....*#####################+.
             ...+#######++#######################.
          ....+*################################.
 #############################################...
          ....+*################################.
             ...+#######++#######################.
              ..++..#.....*#####################+.
              .............+###################+.+
                          .+.##################+.
                           ..##+*##########+.++++
                            .......+####....   +
                                 ..+####+.
                                   ..#*..
                                    ....#
                                    +.
```

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

```sql
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軸から直積して組み合わせをもとめる。

```sql
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**の部分。

```sql
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軸の組み合わせでできる行数と同じ。

```sql
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である。

```sql
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関数は複数の引数のうち最も大きい方を返却する。単一の引数の場合は受け取った引数をそのまま返却する。

```sql
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と考えて明細行単位でみてみる。

```sql
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        
```

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

```sql
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**与えるとグループごとにサマリした結果と同じになった。

```sql
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**にするとおもしろい。ちょっとかわる。

```sql
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の場合

```sql
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;
```

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

```sql
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の場合


```sql
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;
```

ちょっとへった

```sql
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の場合

```sql
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;
```

いなくなった

```sql
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

```sql
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;
```

```sql
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

```sql
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;
```

```sql
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](https://www.sqlite.org/json1.html#jmini)

日本語版はこれかな
[SQLite 3.25](https://code-examples.net/ja/docs/sqlite/index)

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

面白いものも見つけた。

[UNIX データベース入門](http://www.wakhok.ac.jp/biblion/1994/DB/DB.html)

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

sql面白い!