DROP DATABASE IF EXISTS mcs98_db;
CREATE DATABASE mcs98_db;
USE mcs98_db;
CREATE TABLE t1 (t TINYINT, s SMALLINT, m MEDIUMINT, i INT, bi BIGINT, d1 DECIMAL(5,2), d2 DECIMAL(36,12), rl FLOAT, dbl DOUBLE)ENGINE=Columnstore;
INSERT INTO t1 VALUES(NULL, NULL, 1234, -1000012898, 700000012898, 34.21, 90000000000000000009.124312000091, 14.01, 3900000000000001.23), 
(12, 345, 1234, -1000012899, 70000001289, 34.21, 90000000000000000009.124312000091, 14.01, 3900000000000001.23), 
(12, 345, 1234, -1000012898, 700000012899, 34.22, 90000000000000000009.124312000092, 14.02, 3900000000000001.24), 
(13, 346, 1235, NULL, NULL, NULL, NULL, NULL, NULL), 
(28, 1345, 11234, -2000012898, 1700000012899, 134.22, 190000000000000000009.124312000092, 114.02, 13900000000000001.24);
SELECT moda(t) FROM t1;
moda(t)
12
SELECT moda(s) FROM t1;
moda(s)
345
SELECT moda(m) FROM t1;
moda(m)
1234
SELECT moda(i) FROM t1;
moda(i)
-1000012898
SELECT moda(bi) FROM t1;
moda(bi)
700000012899
SELECT moda(d1) FROM t1;
moda(d1)
34.21
SELECT moda(d2) FROM t1;
moda(d2)
90000000000000000009.124312000091
SELECT moda(rl) FROM t1;
moda(rl)
14.010000228881836
SELECT moda(dbl) FROM t1;
moda(dbl)
3.900000000000001e15
CREATE TABLE t2 (
t tinyint,
tu tinyint unsigned,
s smallint,
su smallint unsigned,
m mediumint,
mu mediumint unsigned,
i int,
iu int unsigned,
b bigint,
bu bigint unsigned,
d1 decimal(5,2),
du1 decimal(5,2) unsigned,
d2 decimal(36,12),
du2 decimal(36,12) unsigned,
rl float,
dbl double,
v char(4),
vc varchar(10)
) engine=columnstore;
INSERT INTO t2 VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(10, 10, 2010, 2010, 1237567, 1237567, 74836460, 74836460, 2223372036854775816,
2223372036854775816, 22.34, 22.34, 70000000605040302018.124312000091,
70000000605040302019.124312000091, 2344.32, 12345678.91011, 'five', 'five123456'),
(-10, 10, -2010, 2010, -1237567, 1237567, -74836460, 74836460,
-2223372036854775816, 2223372036854775816, -22.34, 22.34,
-70000000605040302019.124312000091, 70000000605040302029.124312000091,
-2344.32,  -123456789.1011, 'four', 'five654321'),
(1, 1, 201, 201, 1234867, 1234867, 7483646, 7483646, 2223372036854775807,
2223372036854775807, 13.34, 13.34, 70000000605040302019.124312000091,
70000000605040302029.124312000091, 234.432, 12345678.91011, 'five', 'five123456'),
(-1, 1, -201, 201, -1234867, 1234867, -7483646, 7483646, -2223372036854775807,
2223372036854775807, -13.34, 13.34, -70000000605040302019.124312000091,
70000000605040302019.124312000091, -234.432, -1234567.891011, 'four', 'four123456'),
(10, 10, 2010, 2010, 1237567, 1237567, 74836460, 74836460, 2223372036854775816,
2223372036854775816, 22.34, 22.34, 70000000605040302019.124312000091,
70000000605040302019.124312000091, 2344.32, 1234567.891011, 'five', 'five123456'),
(-10, 10, -2010, 2010, -1237567, 1237567, -74836460, 74836460,
-2223372036854775816, 2223372036854775816, -22.34, 22.34,
-70000000605040302019.124312000091, 70000000605040302029.124312000091,
-2344.32, -12345678.91011, 'four', 'five654321'),
(1, 1, 201, 201, 1234867, 1234867, 7483646, 7483646, 2223372036854775807,
2223372036854775807, 13.34, 13.34, 70000000605040302018.124312000091,
70000000605040302029.124312000091, 234.432, 12345678.91011, 'six', 'six1234567');
SELECT moda(t) FROM t2;
moda(t)
1
SELECT moda(tu) FROM t2;
moda(tu)
10
SELECT moda(s) FROM t2;
moda(s)
201
SELECT moda(su) FROM t2;
moda(su)
2010
SELECT moda(m) FROM t2;
moda(m)
1234867
SELECT moda(mu) FROM t2;
moda(mu)
1237567
SELECT moda(i) FROM t2;
moda(i)
7483646
SELECT moda(iu) FROM t2;
moda(iu)
74836460
SELECT moda(b) FROM t2;
moda(b)
2223372036854775807
SELECT moda(bu) FROM t2;
moda(bu)
2223372036854775816
SELECT moda(d1) FROM t2;
moda(d1)
13.34
SELECT moda(du1) FROM t2;
moda(du1)
22.34
SELECT moda(d2) FROM t2;
moda(d2)
-70000000605040302019.124312000091
SELECT moda(du2) FROM t2;
moda(du2)
70000000605040302029.124312000091
SELECT moda(rl) FROM t2;
moda(rl)
234.4320068359375
SELECT moda(dbl) FROM t2;
moda(dbl)
12345678.91011
SELECT moda(v) FROM t2;
moda(v)
five
SELECT moda(vc) FROM t2;
moda(vc)
five123456
SELECT i FROM t2 WHERE i >= (SELECT moda(i) FROM t2) order by i;
i
7483646
7483646
74836460
74836460
SELECT d2 FROM t2 WHERE d2 < (SELECT moda(d2) FROM t2);
d2
SELECT tu, moda(i) FROM t2 GROUP BY tu;
tu	moda(i)
1	7483646
10	-74836460
NULL	0
SELECT floor(moda(rl)) FROM t2;
floor(moda(rl))
234
SELECT ceiling(moda(dbl)) FROM t2;
ceiling(moda(dbl))
12345679
SELECT moda(floor(rl)) FROM t2;
moda(floor(rl))
234
SELECT t, moda(tu) 'q1' FROM t2 GROUP BY t HAVING moda(tu) > 5;
t	q1
-10	10
10	10
DROP DATABASE mcs98_db;