github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/sample/sample.sql (about)

     1  -- The sample column is equal or larger than the original column
     2  drop table if exists sample01;
     3  create table sample01 (col1 int, col2 decimal(6), col3 varchar(30));
     4  insert into sample01 values (1, null, 'database');
     5  insert into sample01 values (2, 38291.32132, 'database');
     6  insert into sample01 values (3, null, 'database management system');
     7  insert into sample01 values (4, 10, null);
     8  insert into sample01 values (1, -321.321, null);
     9  insert into sample01 values (2, -1, null);
    10  select * from sample01;
    11  select sample (col3, 3 rows) from sample01;
    12  select sample (col3, 4 rows) as newCol3 from sample01;
    13  select sample (col3, 3 rows) as newCol3 from sample01 where col3 is not null;
    14  select sample (col3, 3 rows) from sample01 where col3 is null;
    15  select sample (col2, 2 rows) from sample01 where col3 is null group by col1;
    16  select col1, sample(col2, 20 rows) from sample01 group by col1 order by col1;
    17  
    18  select sample (col1 * 3, 10 rows) as newCol1 from sample01 where col2 is not null;
    19  select col1,sample (col2 * 3, 10 rows) as newCol from sample01 group by col1;
    20  
    21  -- nested with functions
    22  select sample (reverse(col3), 4 rows) as newcol3 from sample01;
    23  select col1, sample (startswith(col3, 'database'), 3 rows) from sample01;
    24  select col1, sample (endswith(col3, 'system'), 3 rows) from sample01;
    25  drop table sample01;
    26  
    27  -- sample column is time type
    28  drop table if exists sample02;
    29  create table sample02 (col1 int, col2 datetime);
    30  insert into sample02 values (1, '2020-10-13 10:10:10');
    31  insert into sample02 values (2, null);
    32  insert into sample02 values (1, '2021-10-10 00:00:00');
    33  insert into sample02 values (2, '2023-01-01 12:12:12');
    34  insert into sample02 values (2, null);
    35  insert into sample02 values (3, null);
    36  insert into sample02 values (4, '2023-11-27 01:02:03');
    37  select * from sample02;
    38  select sample (col2, 4 rows) from sample02 order by col2 desc;
    39  select sample (col2, 5 rows) from sample02 group by col1 order by col2;
    40  select sample (col2, 100 percent) from sample02 group by col1 order by col2;
    41  select sample (col2, 0 percent) from sample02 group by col1 order by col2 desc;
    42  select col1, sample (col2, 5 rows) as newColumn from sample02 group by col1 order by col2;
    43  drop table sample02;
    44  
    45  -- sample multiple columns
    46  drop table if exists sample03;
    47  create table sample03 (col1 int, col2 float, col3 decimal, col4 enum('1','2','3','4'));
    48  insert into sample03 values (1, 12.21, 32324.32131, 1);
    49  insert into sample03 values (2, null, null, 2);
    50  insert into sample03 values (2, -12.1, 34738, null);
    51  insert into sample03 values (1, 90.2314, null, 4);
    52  insert into sample03 values (1, 43425.4325, -7483.432, 2);
    53  select * from sample03;
    54  select sample(col1, col2, col3, col4, 2 rows) from sample03 where col2 != null and col4 is not null;
    55  -- sample multi columns cannot have alias
    56  select sample(col1, col2, col3, col4, 2 rows) from sample03 where col4 is not null;
    57  select sample(col1, col2, col3, 4 rows) from sample03 where col4 is not null;
    58  select sample(col1 + 100, col2 + 100, col3 + 100, 4 rows) from sample03 where col4 is not null;
    59  select sample(col1 * 2, col2 * 3, col3, 100 rows) from sample03 where col1 = 2;
    60  select sample(col1 * 2, col2 * 3, col3, 100 rows) from sample03 where col1 = 2 limit 1;
    61  drop table sample03;
    62  
    63  -- with prepare
    64  drop table is exists sample04;
    65  create table sample04 (col1 int, col2 binary);
    66  insert into sample04 values (1, 'a');
    67  insert into sample04 values (2, 'b');
    68  insert into sample04 values (3, 'c');
    69  insert into sample04 values (1, null);
    70  insert into sample04 values (2, null);
    71  insert into sample04 values (2, 'c');
    72  prepare s1 from 'select col1, sample(col2, 4 rows) from sample04 group by col1';
    73  execute s1;
    74  prepare s2 from 'select col1, sample(col2, 100 percent) from sample04 group by col1 order by col1 desc';
    75  execute s2;
    76  drop table sample04;
    77  
    78  -- sample from temporary table
    79  drop table if exists sample05;
    80  create temporary table sample05 (col1 int, col2 binary);
    81  insert into sample05 values (1, 'a');
    82  insert into sample05 values (2, 'b');
    83  insert into sample05 values (3, 'c');
    84  insert into sample05 values (1, null);
    85  insert into sample05 values (2, null);
    86  insert into sample05 values (2, 'c');
    87  insert into sample05 (col1, col2) values (2, true);
    88  select sample (col1, col2, 5 rows) from sample05;
    89  select sample (col1, col2, 100 percent) from sample05;
    90  select sample (col1, col2, 0 percent) from sample05;
    91  drop table sample05;