github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/sample/sample.result (about) 1 drop table if exists sample01; 2 create table sample01 (col1 int, col2 decimal(6), col3 varchar(30)); 3 insert into sample01 values (1, null, 'database'); 4 insert into sample01 values (2, 38291.32132, 'database'); 5 insert into sample01 values (3, null, 'database management system'); 6 insert into sample01 values (4, 10, null); 7 insert into sample01 values (1, -321.321, null); 8 insert into sample01 values (2, -1, null); 9 select * from sample01; 10 col1 col2 col3 11 1 null database 12 2 38291 database 13 3 null database management system 14 4 10 null 15 1 -321 null 16 2 -1 null 17 select sample (col3, 3 rows) from sample01; 18 col3 19 database 20 database 21 database management system 22 select sample (col3, 4 rows) as newCol3 from sample01; 23 newCol3 24 database 25 database 26 database management system 27 select sample (col3, 3 rows) as newCol3 from sample01 where col3 is not null; 28 newCol3 29 database 30 database 31 database management system 32 select sample (col3, 3 rows) from sample01 where col3 is null; 33 col3 34 null 35 select sample (col2, 2 rows) from sample01 where col3 is null group by col1; 36 col2 37 10 38 -321 39 -1 40 select col1, sample(col2, 20 rows) from sample01 group by col1 order by col1; 41 col1 col2 42 1 -321 43 2 38291 44 2 -1 45 3 null 46 4 10 47 select sample (col1 * 3, 10 rows) as newCol1 from sample01 where col2 is not null; 48 newCol1 49 6 50 12 51 3 52 6 53 select col1,sample (col2 * 3, 10 rows) as newCol from sample01 group by col1; 54 col1 newCol 55 1 -963 56 2 114873 57 2 -3 58 3 null 59 4 30 60 select sample (reverse(col3), 4 rows) as newcol3 from sample01; 61 newcol3 62 esabatad 63 esabatad 64 metsys tnemeganam esabatad 65 select col1, sample (startswith(col3, 'database'), 3 rows) from sample01; 66 col1 startswith(col3, database) 67 1 true 68 2 true 69 3 true 70 select col1, sample (endswith(col3, 'system'), 3 rows) from sample01; 71 col1 endswith(col3, system) 72 1 false 73 2 false 74 3 true 75 drop table sample01; 76 drop table if exists sample02; 77 create table sample02 (col1 int, col2 datetime); 78 insert into sample02 values (1, '2020-10-13 10:10:10'); 79 insert into sample02 values (2, null); 80 insert into sample02 values (1, '2021-10-10 00:00:00'); 81 insert into sample02 values (2, '2023-01-01 12:12:12'); 82 insert into sample02 values (2, null); 83 insert into sample02 values (3, null); 84 insert into sample02 values (4, '2023-11-27 01:02:03'); 85 select * from sample02; 86 col1 col2 87 1 2020-10-13 10:10:10 88 2 null 89 1 2021-10-10 00:00:00 90 2 2023-01-01 12:12:12 91 2 null 92 3 null 93 4 2023-11-27 01:02:03 94 select sample (col2, 4 rows) from sample02 order by col2 desc; 95 col2 96 2023-11-27 01:02:03 97 2023-01-01 12:12:12 98 2021-10-10 00:00:00 99 2020-10-13 10:10:10 100 select sample (col2, 5 rows) from sample02 group by col1 order by col2; 101 col2 102 null 103 2020-10-13 10:10:10 104 2021-10-10 00:00:00 105 2023-01-01 12:12:12 106 2023-11-27 01:02:03 107 select sample (col2, 100 percent) from sample02 group by col1 order by col2; 108 col2 109 2020-10-13 10:10:10 110 2021-10-10 00:00:00 111 2023-01-01 12:12:12 112 2023-11-27 01:02:03 113 select sample (col2, 0 percent) from sample02 group by col1 order by col2 desc; 114 col2 115 select col1, sample (col2, 5 rows) as newColumn from sample02 group by col1 order by col2; 116 col1 newColumn 117 3 null 118 1 2020-10-13 10:10:10 119 1 2021-10-10 00:00:00 120 2 2023-01-01 12:12:12 121 4 2023-11-27 01:02:03 122 drop table sample02; 123 drop table if exists sample03; 124 create table sample03 (col1 int, col2 float, col3 decimal, col4 enum('1','2','3','4')); 125 insert into sample03 values (1, 12.21, 32324.32131, 1); 126 insert into sample03 values (2, null, null, 2); 127 insert into sample03 values (2, -12.1, 34738, null); 128 insert into sample03 values (1, 90.2314, null, 4); 129 insert into sample03 values (1, 43425.4325, -7483.432, 2); 130 select * from sample03; 131 col1 col2 col3 col4 132 1 12.21 32324 1 133 2 null null 2 134 2 -12.1 34738 null 135 1 90.2314 null 4 136 1 43425.434 -7483 2 137 select sample(col1, col2, col3, col4, 2 rows) from sample03 where col2 != null and col4 is not null; 138 col1 col2 col3 col4 139 select sample(col1, col2, col3, col4, 2 rows) from sample03 where col4 is not null; 140 col1 col2 col3 col4 141 1 12.21 32324 1 142 2 null null 2 143 1 90.2314 null 4 144 1 43425.434 -7483 2 145 select sample(col1, col2, col3, 4 rows) from sample03 where col4 is not null; 146 col1 col2 col3 147 1 12.21 32324 148 2 null null 149 1 90.2314 null 150 1 43425.434 -7483 151 select sample(col1 + 100, col2 + 100, col3 + 100, 4 rows) from sample03 where col4 is not null; 152 col1 + 100 col2 + 100 col3 + 100 153 101 112.21000003814697 32424 154 102 null null 155 101 190.2313995361328 null 156 101 43525.43359375 -7383 157 select sample(col1 * 2, col2 * 3, col3, 100 rows) from sample03 where col1 = 2; 158 col1 * 2 col2 * 3 col3 159 4 null null 160 4 -36.30000114440918 34738 161 select sample(col1 * 2, col2 * 3, col3, 100 rows) from sample03 where col1 = 2 limit 1; 162 col1 * 2 col2 * 3 col3 163 4 null null 164 drop table sample03; 165 drop table is exists sample04; 166 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 13 near " is exists sample04;"; 167 create table sample04 (col1 int, col2 binary); 168 insert into sample04 values (1, 'a'); 169 insert into sample04 values (2, 'b'); 170 insert into sample04 values (3, 'c'); 171 insert into sample04 values (1, null); 172 insert into sample04 values (2, null); 173 insert into sample04 values (2, 'c'); 174 prepare s1 from 'select col1, sample(col2, 4 rows) from sample04 group by col1'; 175 execute s1; 176 col1 col2 177 1 a 178 2 b 179 2 c 180 3 c 181 prepare s2 from 'select col1, sample(col2, 100 percent) from sample04 group by col1 order by col1 desc'; 182 execute s2; 183 col1 col2 184 3 c 185 2 b 186 2 c 187 1 a 188 drop table sample04; 189 drop table if exists sample05; 190 create temporary table sample05 (col1 int, col2 binary); 191 insert into sample05 values (1, 'a'); 192 insert into sample05 values (2, 'b'); 193 insert into sample05 values (3, 'c'); 194 insert into sample05 values (1, null); 195 insert into sample05 values (2, null); 196 insert into sample05 values (2, 'c'); 197 insert into sample05 (col1, col2) values (2, true); 198 select sample (col1, col2, 5 rows) from sample05; 199 col1 col2 200 1 a 201 2 b 202 3 c 203 1 null 204 2 null 205 2 c 206 2 1 207 select sample (col1, col2, 100 percent) from sample05; 208 col1 col2 209 1 a 210 2 b 211 3 c 212 1 null 213 2 null 214 2 c 215 2 1 216 select sample (col1, col2, 0 percent) from sample05; 217 col1 col2 218 drop table sample05;