You've already forked mariadb-columnstore-engine
mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-07-30 19:23:07 +03:00
173 lines
6.3 KiB
Plaintext
173 lines
6.3 KiB
Plaintext
USE tpch1;
|
|
DROP TABLE IF EXISTS MCOL3434CS;
|
|
DROP TABLE IF EXISTS MCOL3434INNO;
|
|
CREATE TABLE `MCOL3434INNO` (`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`cid` int(11) DEFAULT NULL,
|
|
`d` datetime DEFAULT NULL,
|
|
`c` decimal(12,4) DEFAULT NULL,
|
|
PRIMARY KEY (`id`) ) ENGINE=InnoDB;
|
|
CREATE TABLE `MCOL3434CS` ( `id` int(11) NOT NULL,
|
|
`cid` int(11) DEFAULT NULL,
|
|
`d` datetime DEFAULT NULL,
|
|
`c` decimal(12,4) DEFAULT NULL)engine=columnstore;
|
|
insert into MCOL3434INNO (cid,d,c) values
|
|
(1 , '2018-01-01 00:00:00' ,20000.0500),
|
|
(59, '2018-12-10 15:58:02' ,9411.9900),
|
|
(35, '2018-09-17 15:58:02' ,36870.7000),
|
|
(39, '2018-01-29 15:58:02' ,95532.2400),
|
|
(31, '2018-05-07 15:58:02' ,47070.5300),
|
|
(31, '2019-05-06 15:58:02' ,86272.1300),
|
|
(83, '2018-07-30 15:58:02' ,30866.2900),
|
|
(86, '2018-11-19 15:58:02' ,39956.1100),
|
|
(80, '2018-02-05 15:58:02' ,63099.1800),
|
|
(73, '2018-02-26 15:58:02' ,69119.2500),
|
|
(12, '2018-08-27 15:58:02' ,25862.1200),
|
|
(72, '2018-01-15 15:58:02' ,2750.4100),
|
|
(62, '2019-08-12 15:58:02' ,21755.6300),
|
|
(4 , '2018-07-30 15:58:02' ,66189.2800),
|
|
(64, '2019-04-08 15:58:02' ,7740.9000),
|
|
(79, '2018-03-26 15:58:02' ,32161.7800),
|
|
(38, '2017-10-23 15:58:02' ,63938.2300),
|
|
(32, '2018-04-30 15:58:02' ,46690.8100),
|
|
(28, '2017-10-09 15:58:02' ,8932.5700),
|
|
(49, '2019-04-08 15:58:02' ,53954.2800),
|
|
(8 , '2018-02-12 15:58:02' ,75162.7700),
|
|
(36, '2018-08-20 15:58:02' ,56255.9700),
|
|
(23, '2018-09-24 15:58:02' ,71082.9200),
|
|
(11, '2018-11-19 15:58:02' ,67723.0100),
|
|
(19, '2017-11-27 15:58:02' ,98601.9300),
|
|
(20, '2019-07-29 15:58:02' ,59633.1800),
|
|
(86, '2018-08-13 15:58:02' ,8402.3700),
|
|
(81, '2018-02-05 15:58:02' ,62094.3200),
|
|
(67, '2018-09-24 15:58:02' ,40681.7600),
|
|
(59, '2018-04-16 15:58:02' ,77994.5700),
|
|
(78, '2018-08-06 15:58:02' ,41158.3900),
|
|
(41, '2018-01-29 15:58:02' ,86817.7300),
|
|
(88, '2018-02-12 15:58:02' ,34142.0000),
|
|
(32, '2018-07-23 15:58:02' ,87281.6800),
|
|
(67, '2018-04-09 15:58:02' ,60700.2200),
|
|
(87, '2018-08-27 15:58:02' ,99412.2500),
|
|
(41, '2019-07-01 15:58:02' ,14074.1800),
|
|
(48, '2017-10-16 15:58:02' ,41803.8400),
|
|
(18, '2018-06-04 15:58:02' ,64304.2600),
|
|
(31, '2018-06-18 15:58:02' ,15637.4400),
|
|
(93, '2019-04-15 15:58:02' ,14780.2800),
|
|
(17, '2018-11-05 15:58:02' ,60211.8000),
|
|
(74, '2017-12-18 15:58:02' ,17876.8100),
|
|
(26, '2018-03-05 15:58:02' ,4426.1100),
|
|
(93, '2018-09-10 15:58:02' ,71415.0300),
|
|
(7 , '2019-03-18 15:58:02' ,93697.3000),
|
|
(99, '2019-05-27 15:58:02' ,67578.6700),
|
|
(100,'2017-10-30 15:58:02' ,76466.4500),
|
|
(97, '2018-07-23 15:58:02' ,92691.8400),
|
|
(93, '2018-01-01 15:58:02' ,50753.3700),
|
|
(96, '2019-02-04 15:58:02' ,57590.3400),
|
|
(0 , '2019-02-18 15:58:02' ,36886.0500),
|
|
(2 , '2019-08-19 15:58:02' ,95945.4100),
|
|
(78, '2019-08-05 15:58:02' ,79709.8100),
|
|
(90, '2019-06-10 15:58:02' ,86259.0300),
|
|
(97, '2019-02-11 15:58:02' ,48557.2900),
|
|
(58, '2018-10-08 15:58:02' ,55589.8400),
|
|
(40, '2019-01-21 15:58:02' ,36140.1500),
|
|
(88, '2019-01-21 15:58:02' ,90286.2400),
|
|
(59, '2019-03-04 15:58:02' ,47115.0700),
|
|
(61, '2018-06-11 15:58:02' ,31477.3500),
|
|
(69, '2018-09-03 15:58:02' ,46080.9000),
|
|
(79, '2018-08-13 15:58:02' ,36584.1100),
|
|
(20, '2017-12-18 15:58:02' ,82544.5900);
|
|
insert into MCOL3434CS select * from MCOL3434INNO;
|
|
select distinct cid, week(d) as week,
|
|
max(c) over (partition by cid, week(d)) as c_mx,
|
|
sum(c) over (partition by cid, week(d)) as c_sum,
|
|
count(c) over (partition by cid, week(d)) as c_cnt,
|
|
max(week(d)) over (partition by year(d)) as w_max
|
|
from MCOL3434INNO where year(d) = '2018' order by id;
|
|
cid week c_mx c_sum c_cnt w_max
|
|
1 0 20000.0500 20000.0500 1 49
|
|
59 49 9411.9900 9411.9900 1 49
|
|
35 37 36870.7000 36870.7000 1 49
|
|
39 4 95532.2400 95532.2400 1 49
|
|
31 18 47070.5300 47070.5300 1 49
|
|
83 30 30866.2900 30866.2900 1 49
|
|
86 46 39956.1100 39956.1100 1 49
|
|
80 5 63099.1800 63099.1800 1 49
|
|
73 8 69119.2500 69119.2500 1 49
|
|
12 34 25862.1200 25862.1200 1 49
|
|
72 2 2750.4100 2750.4100 1 49
|
|
4 30 66189.2800 66189.2800 1 49
|
|
79 12 32161.7800 32161.7800 1 49
|
|
32 17 46690.8100 46690.8100 1 49
|
|
8 6 75162.7700 75162.7700 1 49
|
|
36 33 56255.9700 56255.9700 1 49
|
|
23 38 71082.9200 71082.9200 1 49
|
|
11 46 67723.0100 67723.0100 1 49
|
|
86 32 8402.3700 8402.3700 1 49
|
|
81 5 62094.3200 62094.3200 1 49
|
|
67 38 40681.7600 40681.7600 1 49
|
|
59 15 77994.5700 77994.5700 1 49
|
|
78 31 41158.3900 41158.3900 1 49
|
|
41 4 86817.7300 86817.7300 1 49
|
|
88 6 34142.0000 34142.0000 1 49
|
|
32 29 87281.6800 87281.6800 1 49
|
|
67 14 60700.2200 60700.2200 1 49
|
|
87 34 99412.2500 99412.2500 1 49
|
|
18 22 64304.2600 64304.2600 1 49
|
|
31 24 15637.4400 15637.4400 1 49
|
|
17 44 60211.8000 60211.8000 1 49
|
|
26 9 4426.1100 4426.1100 1 49
|
|
93 36 71415.0300 71415.0300 1 49
|
|
97 29 92691.8400 92691.8400 1 49
|
|
93 0 50753.3700 50753.3700 1 49
|
|
58 40 55589.8400 55589.8400 1 49
|
|
61 23 31477.3500 31477.3500 1 49
|
|
69 35 46080.9000 46080.9000 1 49
|
|
79 32 36584.1100 36584.1100 1 49
|
|
select distinct cid, week(d) as week,
|
|
max(c) over (partition by cid, week(d)) as c_mx,
|
|
sum(c) over (partition by cid, week(d)) as c_sum,
|
|
count(c) over (partition by cid, week(d)) as c_cnt,
|
|
max(week(d)) over (partition by year(d)) as w_max
|
|
from MCOL3434CS where year(d) = '2018' order by id;
|
|
cid week c_mx c_sum c_cnt w_max
|
|
1 0 20000.0500 20000.0500 1 49
|
|
59 49 9411.9900 9411.9900 1 49
|
|
35 37 36870.7000 36870.7000 1 49
|
|
39 4 95532.2400 95532.2400 1 49
|
|
31 18 47070.5300 47070.5300 1 49
|
|
83 30 30866.2900 30866.2900 1 49
|
|
86 46 39956.1100 39956.1100 1 49
|
|
80 5 63099.1800 63099.1800 1 49
|
|
73 8 69119.2500 69119.2500 1 49
|
|
12 34 25862.1200 25862.1200 1 49
|
|
72 2 2750.4100 2750.4100 1 49
|
|
4 30 66189.2800 66189.2800 1 49
|
|
79 12 32161.7800 32161.7800 1 49
|
|
32 17 46690.8100 46690.8100 1 49
|
|
8 6 75162.7700 75162.7700 1 49
|
|
36 33 56255.9700 56255.9700 1 49
|
|
23 38 71082.9200 71082.9200 1 49
|
|
11 46 67723.0100 67723.0100 1 49
|
|
86 32 8402.3700 8402.3700 1 49
|
|
81 5 62094.3200 62094.3200 1 49
|
|
67 38 40681.7600 40681.7600 1 49
|
|
59 15 77994.5700 77994.5700 1 49
|
|
78 31 41158.3900 41158.3900 1 49
|
|
41 4 86817.7300 86817.7300 1 49
|
|
88 6 34142.0000 34142.0000 1 49
|
|
32 29 87281.6800 87281.6800 1 49
|
|
67 14 60700.2200 60700.2200 1 49
|
|
87 34 99412.2500 99412.2500 1 49
|
|
18 22 64304.2600 64304.2600 1 49
|
|
31 24 15637.4400 15637.4400 1 49
|
|
17 44 60211.8000 60211.8000 1 49
|
|
26 9 4426.1100 4426.1100 1 49
|
|
93 36 71415.0300 71415.0300 1 49
|
|
97 29 92691.8400 92691.8400 1 49
|
|
93 0 50753.3700 50753.3700 1 49
|
|
58 40 55589.8400 55589.8400 1 49
|
|
61 23 31477.3500 31477.3500 1 49
|
|
69 35 46080.9000 46080.9000 1 49
|
|
79 32 36584.1100 36584.1100 1 49
|
|
DROP TABLE IF EXISTS MCOL3434CS;
|
|
DROP TABLE IF EXISTS MCOL3434INNO;
|