sqlite 3.25支持分析函数了

〇〇

sqlite> with t(a) as (select 1  union all select a+1 from t where a<6)
   ...> select t.a, avg(a)over()a1,avg(a)over(order by a)a2 from t;
1|3.5|1.0
2|3.5|1.5
3|3.5|2.0
4|3.5|2.5
5|3.5|3.0
6|3.5|3.5

〇〇

https://www.sqlite.org/releaselog/3_25_2.html

〇〇

可以用来解题
sqlite> with t(n) as (select 1  union all select n+1 from t where n<100),
   ...>      s as (select a.n
   ...>              from t a,t b
   ...>             where a.n >= 2*b.n
   ...>               and a.n%b.n = 0
   ...>             group by a.n
   ...>            having sum(b.n) > a.n)
   ...> select max(gap) from ( select lead(n) over(order by n) - n as gap from s)
   ...> ;
6

〇〇

〇〇 发表于 2018-9-28 15:54
可以用来解题
sqlite> with t(n) as (select 1  union all select n+1 from t where n      s as (select  ...

比oracle执行快
SQL> set timi on  
SQL> 1
  1* with t as (select level n from dual connect by level <= 100),
SQL> c/100/10000
  1* with t as (select level n from dual connect by level <= 10000),
SQL> /

  MAX(GAP)
----------
         6

Elapsed: 00:00:15.11


sqlite> .timer on
sqlite> with t(n) as (select 1  union all select n+1 from t where n<10000),
   ...>      s as (select a.n
   ...>              from t a,t b
   ...>             where a.n >= 2*b.n
   ...>               and a.n%b.n = 0
   ...>             group by a.n
   ...>            having sum(b.n) > a.n)
   ...> select max(gap) from ( select lead(n) over(order by n) - n as gap from s)
   ...> ;
6
Run Time: real 9.755 user 9.732521 sys 0.004999

〇〇

oracle改为浮点数，快一点
SQL> with t(n) as (select cast (1 as BINARY_FLOAT) from dual union all select cast ( n+1 as BINARY_FLOAT) from t where n<10000),
  2       s as (select a.n
  3               from t a,t b
  4              where a.n >= 2*b.n
  5                and mod(a.n,b.n) = 0
  6              group by a.n
  7             having sum(b.n) > a.n)
  8  select max(gap) from ( select lead(n) over(order by n) - n as gap from s)
  9  ;

  MAX(GAP)
----------
  6.0E+000

Elapsed: 00:00:11.20

solomon_007

才 2M ，好mini

〇〇

过去mysql也只有几M

newkid

在我这里还是没有ORACLE快：

sqlite> .timer on
sqlite> with t(n) as (select 1  union all select n+1 from t where n<10000),
   ...>      s as (select a.n
   ...>              from t a,t b
   ...>             where a.n >= 2*b.n
   ...>               and a.n%b.n = 0
   ...>             group by a.n
   ...>            having sum(b.n) > a.n)
   ...> select max(gap) from ( select lead(n) over(order by n) - n as gap from s
)
   ...> ;
6
Run Time: real 11.334 user 11.310073 sys 0.015600
sqlite>


jsu@JSU12P>  with t(n) as (select 1 from dual union all select  n+1  from t where n<10000),
  2        s as (select a.n
  3                from t a,t b
  4               where a.n >= 2*b.n
  5                 and mod(a.n,b.n) = 0
  6               group by a.n
  7              having sum(b.n) > a.n)
  8   select max(gap) from ( select lead(n) over(order by n) - n as gap from s)
  9   ;

  MAX(GAP)
----------
         6

Elapsed: 00:00:09.85

〇〇

newkid 发表于 2018-9-28 22:11
在我这里还是没有ORACLE快：

sqlite> .timer on

你怎么调优oracle的，我的配置不低了
SQL> set timi on
SQL> with t(n) as (select 1 from dual union all select  n+1  from t where n<10000),
  2       s as (select a.n
  3               from t a,t b
  4              where a.n >= 2*b.n
  5                and mod(a.n,b.n) = 0
  6              group by a.n
  7             having sum(b.n) > a.n)
  8  select max(gap) from ( select lead(n) over(order by n) - n as gap from s)
  9  ;

  MAX(GAP)
----------
         6

Elapsed: 00:00:11.52
SQL> show sga

Total System Global Area 8.1336E+10 bytes
Fixed Size                  7653480 bytes
Variable Size            1.4764E+10 bytes
Database Buffers         6.5230E+10 bytes
Redo Buffers              260780032 bytes
In-Memory Area           1073741824 bytes

〇〇

〇〇 发表于 2018-9-29 07:55
你怎么调优oracle的，我的配置不低了
SQL> set timi on
SQL> with t(n) as (select 1 from dual union  ...

sqlite是自己编译的
gcc shell.c sqlite3.c -lpthread -ldl -O3 -o sqlite3