从Snowflake中的表中随机选择百分比(使用WHERE子句时)

Question

使用此页面作为指南：https://docs.snowflake.com/en/sql-reference/constructs/sample.html

对于这个练习，我需要将表50/50:中的一部分记录拆分

这些工作。我几乎正好得到了表行计数的50%：

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

只要我应用WHERE子句，SAMPLE就不再有效：

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

这让我从上面的雪花页面了解到了这一点：

方法1；将样本应用于其中一个联接表

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

方法2；将样本应用于联接表的结果

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

这两种方法都有效，但返回的记录数为57%，而不是这两种情况下的50%。

QUALIFY ROW_NUMBER() OVER (ORDER BY RANDOM())是更好的选择吗虽然这确实适用于WHERE子句，但我不知道如何设置百分比而不是行计数最大值。示例：

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

--这给了我50行，而不是50%的行或4457行(本例中where子句之后的总行数为8914(

Answer 1

在执行where子句之前，需要先对表进行采样。我相信在你的例子中，where子句首先运行，然后对其进行采样。试试这个(未测试(：

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

或者这个我想：

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

Answer 2

您可以使用percent_rank()而不是row_number():

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

Answer 3

SAMPLE(50)不是一个返回表中50%行的特性。这更像是"；生成每一行的随机数并评估该数是否低于或高于百分比"；。所以，它不会产生确定性的结果，而且会因为随机性而产生一些偏差。

SAMPLE/TABLESAMPLE——雪花文档：https://docs.snowflake.com/en/sql-reference/constructs/sample.html

BERNOULLI(或ROW(：包括每一行，概率为p/100。类似于为每一行翻转一枚加权硬币。

如果要将一个表拆分为两个比率正好为50/50的数据集，NTILE()会很有用。

CCD_ 6是将有序数据集等分为"0"个数的函数；桶"；通过按顺序和循环地为每行生成1到n的数字，在参数中指定。例如，NTILE(2) OVER (ORDER BY C1)生成1、2、1、2。。。按C1列顺序排列的每一行，因此您可以使用"中的值来拆分数据集；BUCKET"；柱

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

将有序数据集等分为constant_value指定的存储桶数。Buckets按顺序编号为1到constant_value。

因此，如果您想从表中随机提取50%的行，可以使用ORDER BY RANDOM()和NTILE()函数，如下所示：

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
*/