Select random percentage from a table in Snowflake (while using the WHERE clause)

Question

Using this page as a guide: https://docs.snowflake.com/en/sql-reference/constructs/sample.html

For this exercise, I need to split a portion of the records in a table 50/50:

These work. I get almost exactly 50% of the table row count:

SELECT * FROM MyTable SAMPLE (50);
SELECT * FROM MyTable TABLESAMPLE (50);

As soon as I apply a WHERE clause, SAMPLE no longer works:

SELECT * FROM MyTable
WHERE country = ‘USA’ 
AND load_date = CURRENT_DATE
SAMPLE (50);

This led me to this from the above snowflake page:

Method 1; applies sample to one of the joined tables

select i, j 
    from table1 as t1 inner join table2 as t2 sample (50)
    where t2.j = t1.i 
    ;

Method 2; applies sample to the result of the joined tables

select * 
   from ( 
         select * 
            from t1 join t2
               on t1.a = t2.c
        ) sample (50);

Both methods work but the number of returned records is 57%, not 50% in both cases.

Is QUALIFY ROW_NUMBER() OVER (ORDER BY RANDOM()) a better option? While this does work with a WHERE clause, I can’t figure out how to set a percentage instead of a row count max. Example:

SELECT * FROM MyTable
WHERE country = ‘USA’
AND load_date = CURRENT_DATE
QUALIFY ROW_NUMBER() OVER (ORDER BY RANDOM()) = (50)

--this gives me 50 rows, not 50% of rows or 4,457 rows (total rows after where clause in this example is 8,914)

user_3287250 · Answer

You need to sample your table first before you do your where clause. I believe in your example the where clause is running first and then a sample is taken of that. Try this instead (un-tested):

with ct as (
   SELECT * FROM MyTable SAMPLE (50)
)
select 
   *
from ct 
WHERE country = ‘USA’ 
AND load_date = CURRENT_DATE

or this I suppose:

select 
   *
from (SELECT * FROM MyTable SAMPLE (50))
WHERE country = ‘USA’ 
AND load_date = CURRENT_DATE

user_10676716 · Answer

You could use percent_rank() instead of row_number():

SELECT * FROM MyTable
WHERE country = 'USA'
AND load_date = CURRENT_DATE
QUALIFY PERCENT_RANK() OVER (ORDER BY RANDOM()) <= 0.5

user_17020911 · Answer

SAMPLE(50) is not a feature returning exactly 50% rows of a table. This is more like "Generate a random number of each row and evaluate the number is lower or higher than the percentage". So, it doesn't produce deterministic results, and there will be some deviation because of the randomness.

SAMPLE / TABLESAMPLE — Snowflake Documentation: https://docs.snowflake.com/en/sql-reference/constructs/sample.html

BERNOULLI (or ROW): Includes each row with a probability of p/100. Similar to flipping a weighted coin for each row.

If you want to split a table into 2 data sets with exactly 50/50 ratio, NTILE() would be helpful.

NTILE(n) is a function to divide an ordered data set equally into the number of "buckets" specified in the argument by generating 1 to n numbers for each row sequentially and cyclically. For example, NTILE(2) OVER (ORDER BY C1) generates 1, 2, 1, 2, ... sequentially for each row ordered by C1 column, so you can split the data set by using the value in "BUCKET" column.

NTILE — Snowflake Documentation: https://docs.snowflake.com/en/sql-reference/functions/ntile.html

Divides an ordered data set equally into the number of buckets specified by constant_value. Buckets are sequentially numbered 1 through constant_value.

Therefore, if you want to extract exactly 50% of rows from a table at random, you can use ORDER BY RANDOM() with the NTILE() function as below:

with ntiled as (
    select *, ntile(2) over (order by random()) bucket
    from snowflake_sample_data.tpch_sf1.customer
)
select count_if(bucket = 1), count_if(bucket = 2)
from ntiled
;
/*
COUNT_IF(BUCKET = 1)    COUNT_IF(BUCKET = 2)
75000   75000
*/

Select random percentage from a table in Snowflake (while using the WHERE clause)

3 Answers