Snowflake查询返回0至N条记录而非预期单条的问题排查

1. 为什么查询返回0至N条记录？ #

因为WHERE子句里的子查询会逐行执行：每处理applications表中的一条记录，就会重新计算一次包含uniform和random()的表达式，生成一个新的随机ID。

• 如果当前行的ID刚好等于这次计算出的随机值，就会被选中；
• 要是多行各自对应的随机计算结果刚好匹配自身ID，就会返回多条；
• 要是所有行对应的随机结果都不匹配自身ID，就返回0条。

比如处理ID=4的行时，子查询算出4，这条被选中；处理ID=7的行时，子查询又算出7，这条也被选中，最终就会返回两条记录。

2. 修改方案：确保每次返回恰好1条记录 #

核心思路是只计算一次随机目标ID，再用这个固定值去匹配表中的记录，以下是两种可行方案：

方式一：预计算目标ID后关联查询 #

with applications as (
    select 1 + seq4() as ID, randstr(5, random()) as NAME
    from table(generator(rowcount => 22)) v
),
target_id as (
    select LEAST(1 + round(25 * uniform(0::float, 1::float, random()) * uniform(0::float, 1::float, random())), 22) as selected_id
)
SELECT
    a.NAME
    , a.ID
FROM
    applications a
JOIN target_id t ON a.ID = t.selected_id

方式二：用QUALIFY结合ROW_NUMBER实现 #

直接用你需要的分布作为排序依据，取排名第一的记录，既符合概率分布要求，又保证只返回1条：

with applications as (
    select 1 + seq4() as ID, randstr(5, random()) as NAME
    from table(generator(rowcount => 22)) v
)
SELECT
    NAME
    , ID
FROM
    applications a
QUALIFY ROW_NUMBER() OVER (ORDER BY uniform(0::float, 1::float, random()) * uniform(0::float, 1::float, random()) DESC) = 1

内容的提问来源于stack exchange，提问作者mdahlman