CLUSTER_SAMPLE

Limits

Window functions are supported only in SELECT statements.
A window function cannot contain nested window functions or nested aggregate functions.
Window functions cannot be used together with aggregate functions at the same level.

Syntax

boolean cluster_sample(bigint <N>) OVER ([partition_clause])
boolean cluster_sample(bigint <N>, bigint <M>) OVER ([partition_clause])

Description

cluster_sample has two forms:

cluster_sample(N) — samples N random rows from the dataset.
cluster_sample(N, M) — samples rows at a ratio of M/N. The number of sampled rows is calculated as partition_row_count × M/N, where partition_row_count is the total row count of the partition.

Parameters

Parameter	Required	Type	Description
`N`	Yes	`BIGINT` constant	Number of rows to sample (single-argument form), or the denominator of the ratio M/N (two-argument form). If `NULL`, returns `NULL`.
`M`	Yes	`BIGINT` constant	Parts to sample. If `NULL`, returns `NULL`.
`partition_clause`	No	—	Partitions the dataset before sampling. See frame_clause.

Return value

BOOLEAN. Returns true if the row is sampled, false otherwise.

Sample data

The examples in this topic use a table named emp. Run the following statement to create the table and load the sample data:

create table if not exists emp
   (empno bigint,
    ename string,
    job string,
    mgr bigint,
    hiredate datetime,
    sal bigint,
    comm bigint,
    deptno bigint);
tunnel upload emp.txt emp; -- Replace emp.txt with the actual path (path and name) to which you upload the data file.

The emp.txt file contains the following sample data:

7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10

Examples

All examples use cluster_sample as a window function inside a subquery, then filter on flag = true in the outer query.

Sample a fixed number of rows

Sample 5 rows from the entire dataset:

select deptno, sal
	from (
      select deptno, sal, cluster_sample(5) over () as flag
      from emp
      ) sub
     where flag = true;

Result:

+------------+------------+
| deptno     | sal        |
+------------+------------+
| 30         | 2850       |
| 30         | 1500       |
| 20         | 3000       |
| 10         | 2450       |
| 10         | 1300       |
+------------+------------+

Sample by ratio

Sample approximately 20% of all rows (1 out of every 5):

select deptno, sal
    from (
        select deptno, sal, cluster_sample(5, 1) over () as flag
        from emp
        ) sub
    where flag = true;

Result:

+------------+------------+
| deptno     | sal        |
+------------+------------+
| 10         | 2450       |
| 20         | 3000       |
| 20         | 3000       |
+------------+------------+

Sample by ratio within partitions

Sample approximately 20% of rows from each department (partition by deptno):

select deptno, sal
    from (
        select deptno, sal, cluster_sample(5, 1) over (partition by deptno) as flag
        from emp
        ) sub
    where flag = true;

Result:

+------------+------------+
| deptno     | sal        |
+------------+------------+
| 10         | 1300       |
| 20         | 2975       |
| 30         | 950        |
+------------+------------+

Related functions

CLUSTER_SAMPLE is a window function. For more information about window functions for aggregation and sorting over a specified window, see Window functions.