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;