You've already forked mariadb-columnstore-engine
mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-07-29 08:21:15 +03:00
206 lines
7.4 KiB
Plaintext
206 lines
7.4 KiB
Plaintext
DROP DATABASE IF EXISTS MCOL5776;
|
|
CREATE DATABASE MCOL5776;
|
|
USE MCOL5776;
|
|
CREATE TABLE t(x INTEGER, y INTEGER) ENGINE=Columnstore;
|
|
INSERT INTO t(x,y) VALUES (1,2), (2,3), (3,3);
|
|
SELECT COUNT(y) OVER (PARTITION BY y) FROM t GROUP BY x;
|
|
COUNT(y) OVER (PARTITION BY y)
|
|
1
|
|
2
|
|
2
|
|
SELECT COUNT(y) OVER (PARTITION BY LEFT(y, 10)) FROM t GROUP BY x;
|
|
COUNT(y) OVER (PARTITION BY LEFT(y, 10))
|
|
1
|
|
2
|
|
2
|
|
DROP TABLE t;
|
|
CREATE TABLE t(ci1 integer, ci2 integer) engine=Columnstore;
|
|
INSERT INTO t(ci1, ci2) VALUES (NULL, 1), (NULL, 2), (1,3), (1,4), (2,5), (2,6), (3,7), (3,8);
|
|
SELECT CONCAT(ci1,ci2), CONCAT(ci1,ci2), SUM(ci2), AVG(ci2) FROM t GROUP BY ci1;
|
|
CONCAT(ci1,ci2) CONCAT(ci1,ci2) SUM(ci2) AVG(ci2)
|
|
14 14 7 3.5000
|
|
26 26 11 5.5000
|
|
38 38 15 7.5000
|
|
NULL NULL 3 1.5000
|
|
SELECT sum(ci1), abs(ci1) FROM t GROUP BY abs(ci1), abs(ci1);
|
|
sum(ci1) abs(ci1)
|
|
2 1
|
|
4 2
|
|
6 3
|
|
NULL NULL
|
|
SELECT sum(ci1), abs(ci1) FROM t GROUP BY abs(ci1);
|
|
sum(ci1) abs(ci1)
|
|
2 1
|
|
4 2
|
|
6 3
|
|
NULL NULL
|
|
DROP TABLE t;
|
|
CREATE TABLE t1(col1 INT, col2 TEXT)ENGINE=Columnstore;
|
|
INSERT INTO t1 VALUES(1, repeat('s',20)),(2, repeat('o',20)),(3, 'sss'),(4, 'ooo');
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
SELECT * FROM t1;
|
|
col1 col2
|
|
1 ssssssssssssssssssss
|
|
2 oooooooooooooooooooo
|
|
3 sss
|
|
4 ooo
|
|
1 ssssssssssssssssssss
|
|
2 oooooooooooooooooooo
|
|
3 sss
|
|
4 ooo
|
|
SELECT col2 FROM t1 GROUP BY col2 HAVING col2 LIKE '%o%' ORDER BY col2;
|
|
col2
|
|
ooo
|
|
oooooooooooooooooooo
|
|
SELECT col1 FROM t1 GROUP BY col1 HAVING col1 > 1 ORDER BY col1;
|
|
col1
|
|
2
|
|
3
|
|
4
|
|
SELECT col1, col2 FROM t1 GROUP BY col2 HAVING col1 > 1 AND col2 LIKE '%o%' ORDER BY col1;
|
|
col1 col2
|
|
2 oooooooooooooooooooo
|
|
4 ooo
|
|
SELECT col1, col2 FROM t1 GROUP BY col2 HAVING col2 LIKE '%o%' AND col1 > 1 ORDER BY col1;
|
|
col1 col2
|
|
2 oooooooooooooooooooo
|
|
4 ooo
|
|
SELECT col2, col1 FROM t1 GROUP BY col2 HAVING col1 > 1 AND col2 LIKE '%o%' ORDER BY col1;
|
|
col2 col1
|
|
oooooooooooooooooooo 2
|
|
ooo 4
|
|
SELECT col2, col1 FROM t1 GROUP BY col2 HAVING col2 LIKE '%o%' AND col1 > 1 ORDER BY col1;
|
|
col2 col1
|
|
oooooooooooooooooooo 2
|
|
ooo 4
|
|
SELECT col1, col2 FROM t1 GROUP BY col2 HAVING col2 LIKE '%o%' AND col1 > 1 ORDER BY col1;
|
|
col1 col2
|
|
2 oooooooooooooooooooo
|
|
4 ooo
|
|
SELECT col1, col2 FROM t1 GROUP BY col1 HAVING col1 > 1 AND col2 LIKE '%o%' ORDER BY col1;
|
|
col1 col2
|
|
2 oooooooooooooooooooo
|
|
4 ooo
|
|
SELECT col1, col2 FROM t1 GROUP BY col1 HAVING col2 LIKE '%o%' AND col1 > 1 ORDER BY col1;
|
|
col1 col2
|
|
2 oooooooooooooooooooo
|
|
4 ooo
|
|
SELECT col2, col1 FROM t1 GROUP BY col1 HAVING col1 > 1 AND col2 LIKE '%o%' ORDER BY col1;
|
|
col2 col1
|
|
oooooooooooooooooooo 2
|
|
ooo 4
|
|
SELECT col2, col1 FROM t1 GROUP BY col1 HAVING col2 LIKE '%o%' AND col1 > 1 ORDER BY col1;
|
|
col2 col1
|
|
oooooooooooooooooooo 2
|
|
ooo 4
|
|
SELECT col1, col2 FROM t1 GROUP BY col1 HAVING col2 LIKE '%o%' AND col1 > 1 ORDER BY col1;
|
|
col1 col2
|
|
2 oooooooooooooooooooo
|
|
4 ooo
|
|
SELECT col2, col1 FROM t1 GROUP BY col1 HAVING col1 > 1 AND col2 = 'ooo' ORDER BY col1;
|
|
col2 col1
|
|
ooo 4
|
|
SELECT col2, col1 FROM t1 GROUP BY col1 HAVING col2 = 'ooo' AND col1 > 1 ORDER BY col1;
|
|
col2 col1
|
|
ooo 4
|
|
SELECT col2, col1 FROM t1 GROUP BY col1 HAVING col1 > 1 AND col2 >= 'ooo' ORDER BY col1;
|
|
col2 col1
|
|
oooooooooooooooooooo 2
|
|
sss 3
|
|
ooo 4
|
|
SELECT col2, col1 FROM t1 GROUP BY col1 HAVING col2 >= 'ooo' AND col1 > 1 ORDER BY col1;
|
|
col2 col1
|
|
oooooooooooooooooooo 2
|
|
sss 3
|
|
ooo 4
|
|
SELECT col2, col1 FROM t1 GROUP BY col1 HAVING col1 > 1 AND col2 < 'ooo' ORDER BY col1;
|
|
col2 col1
|
|
SELECT col2, col1 FROM t1 GROUP BY col1 HAVING col2 < 'ooo' AND col1 > 1 ORDER BY col1;
|
|
col2 col1
|
|
SELECT col2, col1 FROM t1 GROUP BY col1 HAVING col1 > 1 AND 'ooo' < col2 ORDER BY col1;
|
|
col2 col1
|
|
oooooooooooooooooooo 2
|
|
sss 3
|
|
SELECT col2, col1 FROM t1 GROUP BY col1 HAVING 'ooo' < col2 AND col1 > 1 ORDER BY col1;
|
|
col2 col1
|
|
oooooooooooooooooooo 2
|
|
sss 3
|
|
SELECT col1, col2, SUM(LENGTH(col2)) FROM t1 GROUP BY col1 HAVING SUM(LENGTH(col2)) > 10 ORDER BY col1;
|
|
col1 col2 SUM(LENGTH(col2))
|
|
1 ssssssssssssssssssss 40
|
|
2 oooooooooooooooooooo 40
|
|
SELECT col1, col2, SUM(LENGTH(col2)) a FROM t1 GROUP BY col1 HAVING a > 1 AND col2 LIKE '%o%' ORDER BY col1;
|
|
col1 col2 a
|
|
2 oooooooooooooooooooo 40
|
|
4 ooo 6
|
|
DROP TABLE t1;
|
|
CREATE TABLE empsalary (
|
|
depname VARCHAR(100),
|
|
empno BIGINT,
|
|
salary INT,
|
|
enroll_date DATE
|
|
) ENGINE=Columnstore;
|
|
INSERT INTO empsalary VALUES ('develop' , 10, 5200, '2007-08-01');
|
|
INSERT INTO empsalary VALUES ('sales' , 1, 5000, '2006-10-01');
|
|
INSERT INTO empsalary VALUES ('personnel', 5, 3500, '2007-12-10');
|
|
INSERT INTO empsalary VALUES ('sales' , 4, 4800, '2007-08-08');
|
|
INSERT INTO empsalary VALUES ('personnel', 2, 3900, '2006-12-23');
|
|
INSERT INTO empsalary VALUES ('develop' , 7, 4200, '2008-01-01');
|
|
INSERT INTO empsalary VALUES ('develop' , 9, 4500, '2008-01-01');
|
|
INSERT INTO empsalary VALUES ('sales' , 3, 4800, '2007-08-01');
|
|
INSERT INTO empsalary VALUES ('develop' , 8, 6000, '2006-10-01');
|
|
INSERT INTO empsalary VALUES ('develop' , 11, 5200, '2007-08-15');
|
|
INSERT INTO empsalary VALUES ('develop' , 12, null, '2008-08-09');
|
|
SELECT depname, empno, MODA(salary) OVER(PARTITION BY depname ORDER BY enroll_date) FROM empsalary ORDER BY depname, empno, enroll_date;
|
|
depname empno MODA(salary) OVER(PARTITION BY depname ORDER BY enroll_date)
|
|
develop 7 5200
|
|
develop 8 6000
|
|
develop 9 5200
|
|
develop 10 5200
|
|
develop 11 5200
|
|
develop 12 5200
|
|
personnel 2 3900
|
|
personnel 5 3500
|
|
sales 1 5000
|
|
sales 3 4800
|
|
sales 4 4800
|
|
SELECT AVG(salary),depname, MODA(salary) OVER(PARTITION BY depname ORDER BY enroll_date) FROM empsalary GROUP BY depname ORDER BY depname, AVG(salary);
|
|
AVG(salary) depname MODA(salary) OVER(PARTITION BY depname ORDER BY enroll_date)
|
|
5020.0000 develop 0
|
|
3700.0000 personnel 3900
|
|
4866.6667 sales 4800
|
|
DROP TABLE empsalary;
|
|
CREATE TABLE orders(o_custkey INT) ENGINE=Columnstore;
|
|
SELECT o_custkey, COUNT(*) FROM orders WHERE o_custkey < 100 GROUP BY o_custkey HAVING COUNT(*) < 20 ORDER BY 1;
|
|
o_custkey COUNT(*)
|
|
SELECT o_custkey, COUNT(*) FROM orders WHERE o_custkey < 100 GROUP BY o_custkey HAVING COUNT(*) <= 20 ORDER BY 1;
|
|
o_custkey COUNT(*)
|
|
SELECT o_custkey, COUNT(*) FROM orders WHERE o_custkey < 100 GROUP BY o_custkey HAVING COUNT(*) > 20 ORDER BY 1;
|
|
o_custkey COUNT(*)
|
|
SELECT o_custkey, COUNT(*) FROM orders WHERE o_custkey < 100 GROUP BY o_custkey HAVING COUNT(*) >= 20 ORDER BY 1;
|
|
o_custkey COUNT(*)
|
|
SELECT o_custkey, COUNT(*) FROM orders WHERE o_custkey < 100 GROUP BY o_custkey HAVING COUNT(*) = 20 ORDER BY 1;
|
|
o_custkey COUNT(*)
|
|
SELECT o_custkey, COUNT(*) FROM orders WHERE o_custkey < 100 GROUP BY o_custkey HAVING COUNT(*) <> 20 ORDER BY 1;
|
|
o_custkey COUNT(*)
|
|
SELECT o_custkey, COUNT(*) FROM orders WHERE o_custkey < 100 GROUP BY o_custkey HAVING COUNT(*) IN (15, 20) ORDER BY 1;
|
|
o_custkey COUNT(*)
|
|
SELECT o_custkey, COUNT(*) FROM orders WHERE o_custkey < 100 GROUP BY o_custkey HAVING COUNT(*) NOT IN (15, 20) ORDER BY 1;
|
|
o_custkey COUNT(*)
|
|
SELECT o_custkey, COUNT(*) FROM orders WHERE o_custkey < 100 GROUP BY o_custkey HAVING COUNT(*) BETWEEN 15 AND 20 ORDER BY 1;
|
|
o_custkey COUNT(*)
|
|
SELECT o_custkey, COUNT(*) FROM orders WHERE o_custkey < 100 GROUP BY o_custkey HAVING COUNT(*) NOT BETWEEN 15 and 20 ORDER BY 1;
|
|
o_custkey COUNT(*)
|
|
SELECT o_custkey, COUNT(*) FROM orders WHERE o_custkey < 100 GROUP BY o_custkey HAVING COUNT(*) IS NULL ORDER BY 1;
|
|
o_custkey COUNT(*)
|
|
SELECT o_custkey, COUNT(*) FROM orders WHERE o_custkey < 100 GROUP BY o_custkey HAVING COUNT(*) IS NOT NULL ORDER BY 1;
|
|
o_custkey COUNT(*)
|
|
DROP TABLE orders;
|
|
CREATE TABLE t(k INT) ENGINE = Columnstore;
|
|
INSERT INTO t(k) VALUES (1), (2), (2), (3), (3), (4), (4),(4),(4),(4),(995), (NULL);
|
|
SELECT k + k a FROM t GROUP BY a HAVING a >= 8;
|
|
a
|
|
1990
|
|
8
|
|
DROP DATABASE MCOL5776;
|