时间戳

首页 > 解决方案 > CTE 中的 SQLite RANDOM() 函数

sqlite - CTE 中的 SQLite RANDOM() 函数

问题描述

我在 SQLite 中发现了 RANDOM() 函数的行为，这似乎不正确。

我想使用随机 RANDOM() 和 CASE 生成随机组。但是，看起来 CTE 的行为方式不正确。

期望与现实

首先，让我们创建一个表

DROP TABLE IF EXISTS tt10ROWS;
CREATE TEMP TABLE tt10ROWS (
    some_int INTEGER);

INSERT INTO tt10ROWS VALUES
    (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
SELECT * FROM tt10ROWS;

表格1

不正确的行为

WITH 
    -- 2.a add columns with random number and save in CTE
    STEP_01 AS (
        SELECT
            *,
            ABS(RANDOM()) % 4 + 1 AS RAND_1_TO_4
        FROM tt10ROWS)
        
    -- 2.b - get random group
select
    *,
    CASE 
        WHEN RAND_1_TO_4 = 1 THEN 'GROUP_01'
        WHEN RAND_1_TO_4 = 2 THEN 'GROUP_02'
        WHEN RAND_1_TO_4 = 3 THEN 'GROUP_03'
        WHEN RAND_1_TO_4 = 4 THEN 'GROUP_04' 
        END AS GROUP_IT
from STEP_01;

使用这样的查询，我们得到一个表，它为 RAND_1_TO_4 列生成正确的值，但 GROUP_IT 列不正确。我们可以看到，组不匹配，有些组甚至丢失。

正确的行为

我通过创建临时表而不是使用 CTE 找到了解决此类问题的方法。它有帮助。

-- 1.a - add column with random number 1-4 and save as TEMP TABLE
drop table if exists ttSTEP01;
CREATE TEMP TABLE ttSTEP01 AS
        SELECT
            *,
            ABS(RANDOM()) % 4 + 1 AS RAND_1_TO_4
        FROM tt10ROWS;

-- 1.b - get random group
select
    *,
    CASE 
        WHEN RAND_1_TO_4 = 1 THEN 'GROUP_01'
        WHEN RAND_1_TO_4 = 2 THEN 'GROUP_02'
        WHEN RAND_1_TO_4 = 3 THEN 'GROUP_03'
        WHEN RAND_1_TO_4 = 4 THEN 'GROUP_04' 
        END AS GROUP_IT
from ttSTEP01;

问题

这种行为背后的原因是什么，其中 GROUP_IT 列未正确生成？

标签： sqliterandomcommon-table-expression

解决方案

如果您使用 来查看错误查询生成的字节码EXPLAIN，您会看到每次RAND_1_TO_4引用该列时，都会重新计算其值并使用新的随机数（我怀疑但不是 100% 确定这有与如何random()是非确定性函数有关）。CASE空值适用于没有任何测试最终为真的那些时间。

当您插入临时表然后将其用于其余部分时，这些值当然保持静态并且按预期工作。

推荐阅读