github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/function/func_math_floor.test (about) 1 #SELECT, 科学计算 2 select floor(5.5),floor(-5.5); 3 select floor(1.1e5); 4 5 6 #SELECT, 嵌套，EXTREME VALUE 7 select floor(cast(-2 as unsigned)), floor(18446744073709551614), floor(-2); 8 SELECT floor(ceil(12345678901234567890)); 9 10 SELECT floor(18446744073709551616); 11 SELECT floor(floor(18446744073709551616)); 12 SELECT floor(floor(floor(18446744073709551616))); 13 14 #数据类型 15 CREATE TABLE t1(a BIGINT UNSIGNED); 16 INSERT INTO t1 VALUES(18446744073709551615); 17 SELECT CEILING(a) AS c, FLOOR(a) AS f FROM t1; 18 DROP TABLE t1; 19 20 #EXTREME VALUE 21 22 select floor(999999999999999999); 23 select floor(9999999999999999999999); 24 select floor(9.999999999999999999999); 25 26 27 select floor(-9.999999999999999999999); 28 select floor(-999999999999999999999.999); 29 30 #INSERT, EXTREME VALUE, DATA TYPE 31 #CREATE TABLE t1(a BIGINT UNSIGNED); 32 #INSERT INTO t1 VALUES(18446744073709551615); 33 #CREATE TABLE t2 AS 34 #SELECT CEILING(a) AS c, FLOOR(a) AS f FROM t1; 35 #SELECT * FROM t2; 36 #drop table t1; 37 #drop table t2; 38 39 # CREATE TABLE t AS SELECT CEILING(18446744073709551615) AS c, FLOOR(18446744073709551615) AS f; 40 # SELECT * FROM t; 41 # DROP TABLE t; 42 # CREATE TABLE t AS 43 # SELECT CEILING(CAST(1844674407370955161 AS DECIMAL(19, 0))) AS c, 44 # FLOOR(CAST(1844674407370955161 AS DECIMAL(19, 0))) AS f; 45 # SELECT * FROM t; 46 # DROP TABLE t; 47 # CREATE TABLE t AS 48 # SELECT CEILING(CAST(-9223372036854775808 AS DECIMAL(19, 0))) AS c, 49 # FLOOR(CAST(-9223372036854775808 AS DECIMAL(19, 0))) AS f; 50 # SELECT * FROM t; 51 # DROP TABLE t; 52 # CREATE TABLE t AS 53 # SELECT CEILING(CAST(184467440737095516 AS DECIMAL(18, 0))) AS c, 54 # FLOOR(CAST(184467440737095516 AS DECIMAL(18, 0))) AS f; 55 # SELECT * FROM t; 56 # DROP TABLE t; 57 # CREATE TABLE t AS 58 # SELECT CEILING(CAST(-922337203685477580 AS DECIMAL(18, 0))) AS c, 59 # FLOOR(CAST(-922337203685477580 AS DECIMAL(18, 0))) AS f; 60 # SELECT * FROM t; 61 # DROP TABLE t; 62 # #0.5 MO不支持这种写法 63 # CREATE TABLE t AS 64 # SELECT CEILING(CAST(99999999999999999.9 AS DECIMAL(18, 1))) AS c, 65 # FLOOR(CAST(-99999999999999999.9 AS DECIMAL(18, 1))) AS f; 66 # SELECT * FROM t; 67 # DROP TABLE t; 68 # #0.5 MO不支持这种写法 69 # CREATE TABLE t AS 70 # SELECT CEILING(CAST(999999999999999999.9 AS DECIMAL(19, 1))) AS c, 71 # FLOOR(CAST(-999999999999999999.9 AS DECIMAL(19, 1))) AS f; 72 # SELECT * FROM t; 73 # DROP TABLE t; 74 75 #WHERE 76 CREATE TABLE t1 (col1 INTEGER); 77 CREATE TABLE t2 (col1 INTEGER); 78 INSERT INTO t1 VALUES (1), (2); 79 INSERT INTO t2 VALUES (1), (2); 80 SELECT 81 t1.col1, 82 t2.col1 83 FROM 84 t1, 85 t2 86 WHERE 87 t1.col1 = t2.col1 88 AND t1.col1 = FLOOR(2); 89 drop table t1; 90 drop table t2; 91 92 #INSERT INTO 93 94 CREATE TABLE t1 (a VARCHAR(250), b INT, PRIMARY KEY (b)); 95 insert into t1 (a) select (FLOOR(0.13 * 100)); 96 insert into t1 (a) select FLOOR(1.32 * 100) from t1; 97 insert into t1 (a) select FLOOR(0.0044 * 100) from t1; 98 insert into t1 (a) select FLOOR(124.314 * 100) from t1; 99 insert into t1 (a) select FLOOR(0.3414 * 100) from t1; 100 insert into t1 (a) select FLOOR(12.4412 * 100) from t1; 101 insert into t1 (a) select FLOOR(124131.4123 * 100) from t1; 102 insert into t1 (a) select FLOOR(0.2314114 * 100) from t1; 103 insert into t1 (a) select FLOOR(13.45123 * 100) from t1; 104 insert into t1 (a) select FLOOR(0.44521 * 100) from t1; 105 insert into t1 (a) select FLOOR(0.577134 * 100) from t1; 106 insert into t1 (a) select FLOOR(1244.53 * 100) from t1; 107 insert into t1 (a) select FLOOR(6604.642222 * 100) from t1; 108 insert into t1 (a) select FLOOR(1415.313 * 100) from t1; 109 SELECT a, 110 (SELECT space(250) FROM t1 i1 111 WHERE i1.b=t1.a ORDER BY 1 LIMIT 1) AS a 112 FROM t1 ORDER BY a LIMIT 5; 113 DROP TABLE t1; 114 115 116 #0.5 MO不支持CREATE SELECT, 也不支持 WITH RECURSIVE AS 117 #CREATE TABLE t1(a INT, b INT); 118 #INSERT INTO t1 (a) VALUES (1), (2); 119 #CREATE TABLE t2 SELECT * FROM t1; 120 #SELECT (WITH RECURSIVE dt AS (SELECT t1.a AS a UNION 121 #SELECT a+1 FROM dt WHERE a<10) 122 #SELECT t1.a * CONCAT(COUNT(*), '.', FLOOR(AVG(dt.a))) 123 #FROM dt) AS subq 124 #FROM t1; 125 #DROP TABLE t1; 126 127 #嵌套 128 SELECT DATE(FLOOR(20110512154559.616)); 129 130 #INSERT INTO, distinct 131 CREATE table t1(a int, b float); 132 insert into t1 select floor(12124.413), floor(-4213.413); 133 insert into t1 select floor(12124.123), floor(-42413.409); 134 select distinct floor(a) from t1; 135 drop table t1; 136 137 #null 138 select floor(null); 139 140 #算术操作 141 select floor(123.34)-floor(123.03); 142 143 #WHERE 144 drop table if exists t1; 145 create table t1(a INT, b float); 146 insert into t1 select floor(12124.413), floor(-4213.413); 147 insert into t1 select floor(12124.123), floor(-42413.409); 148 select * from t1 where floor(a)=12124; 149 drop table t1; 150 151 152 #ON CONDITION 153 create table t1(a INT, b float); 154 create table t2(a INT, b float); 155 insert into t1 select floor(12124.413), floor(-4213.413); 156 insert into t1 select floor(12124.123), floor(-42413.409); 157 insert into t2 select floor(14124.413), floor(-4213.413); 158 insert into t2 select floor(984798.123), floor(-980.409); 159 SELECT t1.a, t2.a FROM t1 JOIN t2 ON (floor(t1.b) = floor(t2.b)); 160 drop table t1; 161 drop table t2; 162 163 #HAVING，比较操作 164 drop table if exists t1; 165 create table t1(a INT, b float); 166 insert into t1 select floor(14124.413), floor(-4213.413); 167 insert into t1 select floor(984798.123), floor(-980.409); 168 select b from t1 group by b having floor(b)<-1000; 169 drop table t1; 170 171 172 #MO支持的floor是clickhouse语义的，所以可以有两个参数, 以下在MO中运行测试,mysql跑不过 173 select floor(123.342, 1); 174 select floor(123.45, -1); 175 select floor(123.342124124214, 100); 176 select floor(-123.342124124214, -100); 177 178 select floor(123.342, null); 179 180 select floor(null, 1); 181 182 select floor(null, null);