如何在大查询中使用 RAND() 对随机 n% 记录进行采样?

Question

我想在bigquery中获取理解sql查询的帮助。

如果我按如下方式编写查询，这会从列DEP_DELAY不为空的总行中随机抽取 1% 的行。如果没有RAND()，则235208行总数。

SELECT COUNT(DEP_DELAY) FROM flights.fedtzcorr WHERE RAND() < 0.1

阅读文档时，它说它在小数点后创建了十五位精度。所以，从字面上理解，我认为 RAND(( 创建了 15 位数字 在0和100,000,000,000,000之间...

此函数生成十进制类型的值，小数点后有 15 位精度。

RAND(( 如何从总行数中抽取 1% 的行？

Answer 1

请注意，您指向的文档是与 Dataprep 相关的文档，而不是 BigQuery：

• https://cloud.google.com/dataprep/docs/html/RAND-Function_57344757

正确的文档是：

• https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#rand

生成 [0， 1( 范围内的 FLOAT64 类型的伪随机值，包括 0 和 1。

因此，RAND()生成一个介于 0 和 1 之间的数字。 然后WHERE RAND() < 0.1匹配 10% 的行(而不是 1%(。对于 1% 试试WHERE RAND() < 0.01.

有关更多示例，请参阅 https://stackoverflow.com/a/23376403/132438。

Answer 2

因此，查询可能如下所示：

DECLARE deep_delay_count INT64;
DECLARE deep_delay_pct FLOAT64;
SET deep_delay_count = (SELECT COUNT(DISTINCT DEP_DELAY)
FROM flights.fedtzcorr
WHERE DEP_DELAY IS NOT NULL);
SET deep_delay_pct = IF(deep_delay_count IS NOT NULL, 0.01 * deep_delay_count / deep_delay_count, 0);
SELECT DISTINCT DEP_DELAY
FROM flights.fedtzcorr
WHERE DEP_DELAY IS NOT NULL
AND RAND() < deep_delay_pct;