mirror of
https://github.com/tursodatabase/libsql.git
synced 2024-12-16 12:38:46 +00:00
ad79ef48a2
* add tests * add bench
255 lines
5.8 KiB
Plaintext
255 lines
5.8 KiB
Plaintext
|
|
CREATE TABLE t1(a, b);
|
|
INSERT INTO t1 VALUES(NULL, 1);
|
|
INSERT INTO t1 VALUES(NULL, 2);
|
|
INSERT INTO t1 VALUES(NULL, 3);
|
|
|
|
|
|
SELECT sum(b) OVER win FROM t1
|
|
WINDOW win AS (
|
|
ORDER BY a DESC NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
|
|
)
|
|
|
|
|
|
CREATE TABLE t1(a, b);
|
|
INSERT INTO t1 VALUES(1, NULL);
|
|
INSERT INTO t1 VALUES(2, 45);
|
|
INSERT INTO t1 VALUES(3, 66.2);
|
|
INSERT INTO t1 VALUES(4, 'hello world');
|
|
INSERT INTO t1 VALUES(5, 'hello world');
|
|
INSERT INTO t1 VALUES(6, X'1234');
|
|
INSERT INTO t1 VALUES(7, X'1234');
|
|
INSERT INTO t1 VALUES(8, NULL);
|
|
|
|
|
|
CREATE TABLE testjson(id INTEGER PRIMARY KEY, j TEXT, x TEXT);
|
|
INSERT INTO testjson VALUES(1, '{"a":1
|
|
|
|
SELECT json_group_array(json(j)) FROM testjson;
|
|
|
|
|
|
SELECT json_group_array(json(j)) OVER (ORDER BY id) FROM testjson;
|
|
|
|
|
|
SELECT json_group_array(json(j)) OVER (
|
|
ORDER BY id RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
|
|
EXCLUDE TIES
|
|
) FROM testjson;
|
|
|
|
|
|
SELECT json_group_array(json(j)) OVER (
|
|
ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
|
|
) FROM testjson;
|
|
|
|
|
|
SELECT json_group_array(json(j)) OVER (
|
|
ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
|
|
) FROM testjson;
|
|
|
|
|
|
UPDATE testjson SET j = replace(j,char(125),',"e":9'||char(125));
|
|
SELECT j FROM testjson;
|
|
|
|
|
|
SELECT group_concat(x,'') OVER (
|
|
ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING
|
|
) FROM testjson ORDER BY id;
|
|
|
|
|
|
SELECT json_group_array(json(j)) OVER (
|
|
ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING
|
|
) FROM testjson;
|
|
|
|
|
|
SELECT json_group_object(x,json(j)) OVER (
|
|
ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING
|
|
) FROM testjson;
|
|
|
|
|
|
SELECT group_concat(x,'') FILTER (WHERE id!=2) OVER (
|
|
ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
|
|
) FROM testjson;
|
|
|
|
|
|
SELECT json_group_array(json(j)) FILTER (WHERE id!=2) OVER (
|
|
ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
|
|
) FROM testjson
|
|
|
|
|
|
SELECT json_group_object(x,json(j)) FILTER (WHERE id!=2) OVER (
|
|
ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
|
|
) FROM testjson
|
|
|
|
|
|
CREATE TABLE x(a);
|
|
INSERT INTO x VALUES(1);
|
|
INSERT INTO x VALUES(2);
|
|
|
|
|
|
WITH y AS (
|
|
SELECT Row_Number() OVER (win) FROM x WINDOW win AS (PARTITION BY a)
|
|
)
|
|
SELECT * FROM y;
|
|
|
|
|
|
CREATE TABLE t1(a, c);
|
|
CREATE INDEX i1 ON t1(a);
|
|
|
|
INSERT INTO t1 VALUES(0, 421);
|
|
INSERT INTO t1 VALUES(1, 844);
|
|
INSERT INTO t1 VALUES(2, 1001);
|
|
|
|
|
|
SELECT a, sum(c) OVER (
|
|
ORDER BY a RANGE BETWEEN 0 PRECEDING AND 3 PRECEDING
|
|
) FROM t1;
|
|
|
|
|
|
INSERT INTO t1 VALUES(NULL, 123);
|
|
INSERT INTO t1 VALUES(NULL, 111);
|
|
INSERT INTO t1 VALUES('xyz', 222);
|
|
INSERT INTO t1 VALUES('xyz', 333);
|
|
|
|
SELECT a, sum(c) OVER (
|
|
ORDER BY a RANGE BETWEEN 0 PRECEDING AND 3 PRECEDING
|
|
) FROM t1;
|
|
|
|
|
|
SELECT a, sum(c) OVER (
|
|
ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING
|
|
) FROM t1;
|
|
|
|
|
|
SELECT a, sum(c) OVER (
|
|
ORDER BY a RANGE BETWEEN 0 PRECEDING AND 3 PRECEDING EXCLUDE NO OTHERS
|
|
) FROM t1;
|
|
|
|
|
|
SELECT a, sum(c) OVER (
|
|
ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING EXCLUDE NO OTHERS
|
|
) FROM t1;
|
|
|
|
|
|
CREATE TABLE t1(a, c);
|
|
CREATE INDEX i1 ON t1(a);
|
|
|
|
INSERT INTO t1 VALUES(7, 997);
|
|
INSERT INTO t1 VALUES(8, 997);
|
|
INSERT INTO t1 VALUES('abc', 1001);
|
|
|
|
|
|
SELECT a, sum(c) OVER (
|
|
ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING
|
|
) FROM t1;
|
|
|
|
|
|
SELECT a, sum(c) OVER (
|
|
ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING EXCLUDE NO OTHERS
|
|
) FROM t1;
|
|
|
|
|
|
CREATE TABLE t1(a, c);
|
|
CREATE INDEX i1 ON t1(a);
|
|
|
|
INSERT INTO t1 VALUES(NULL, 46);
|
|
INSERT INTO t1 VALUES(NULL, 45);
|
|
INSERT INTO t1 VALUES(7, 997);
|
|
INSERT INTO t1 VALUES(7, 1000);
|
|
INSERT INTO t1 VALUES(8, 997);
|
|
INSERT INTO t1 VALUES(8, 1000);
|
|
INSERT INTO t1 VALUES('abc', 1001);
|
|
INSERT INTO t1 VALUES('abc', 1004);
|
|
INSERT INTO t1 VALUES('xyz', 3333);
|
|
|
|
|
|
SELECT a, max(c) OVER (
|
|
ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING
|
|
) FROM t1;
|
|
|
|
|
|
SELECT a, min(c) OVER (
|
|
ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING
|
|
) FROM t1;
|
|
|
|
|
|
SELECT a, max(c) OVER (
|
|
ORDER BY a RANGE BETWEEN 0 PRECEDING AND 2 PRECEDING
|
|
) FROM t1;
|
|
|
|
|
|
SELECT a, min(c) OVER (
|
|
ORDER BY a RANGE BETWEEN 0 PRECEDING AND 2 PRECEDING
|
|
) FROM t1;
|
|
|
|
|
|
BEGIN TRANSACTION;
|
|
CREATE TABLE t1(a, c);
|
|
INSERT INTO t1 VALUES('aa', 111);
|
|
INSERT INTO t1 VALUES('BB', 660);
|
|
INSERT INTO t1 VALUES('CC', 938);
|
|
INSERT INTO t1 VALUES('dd', 979);
|
|
COMMIT;
|
|
|
|
CREATE INDEX i1 ON t1(a COLLATE nocase);
|
|
|
|
|
|
SELECT sum(c) OVER
|
|
(ORDER BY a COLLATE nocase RANGE BETWEEN 10.0 PRECEDING AND 5.0 PRECEDING)
|
|
FROM t1;
|
|
|
|
|
|
CREATE TABLE seps(x);
|
|
INSERT INTO seps(x) VALUES ('1'), ('22'), ('333'), ('4444');
|
|
SELECT group_concat('-', x)
|
|
OVER ( ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING )
|
|
FROM seps;
|
|
|
|
|
|
CREATE TABLE t1(i INTEGER PRIMARY KEY, v);
|
|
INSERT INTO t1 VALUES( 1, 'one' );
|
|
INSERT INTO t1 VALUES( 2, 'two' );
|
|
|
|
|
|
SELECT
|
|
json_group_array( v ) OVER w,
|
|
json_group_array( v ) OVER w
|
|
FROM t1
|
|
window w as (
|
|
range between unbounded preceding and unbounded following
|
|
)
|
|
|
|
|
|
SELECT
|
|
group_concat( v ) OVER w,
|
|
json_group_array( v ) OVER w,
|
|
json_group_array( v ) OVER w,
|
|
group_concat( v ) OVER w
|
|
FROM t1
|
|
window w as (
|
|
range between unbounded preceding and unbounded following
|
|
)
|
|
|
|
|
|
SELECT value FROM json_each('[1,2,3,4,5]');
|
|
|
|
|
|
SELECT key, value FROM json_each('[1,2,3,4,5]');
|
|
|
|
|
|
SELECT rowid, value FROM json_each('[1,2,3,4,5]');
|
|
|
|
|
|
SELECT sum(value) OVER (ORDER BY rowid) FROM json_each('[1,2,3,4,5]')
|
|
|
|
|
|
SELECT sum(value) OVER (
|
|
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
|
|
) FROM json_each('[1,2,3,4,5]')
|
|
|
|
|
|
SELECT sum(value) OVER (ORDER BY rowid DESC) FROM json_each('[1,2,3,4,5]')
|
|
|
|
|
|
SELECT sum(value) OVER (ORDER BY value ASC) FROM json_each('[2,1,4,3,5]')
|
|
|