CTE中的SQLite RANDOM()函数 [英] SQLite RANDOM() function in CTE

问题描述

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

I found behavior of RANDOM() function in SQLite, which doesn't seems correct.

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

I want to generate random groups using random RANDOM() and CASE. However, it looks like CTE is not behaving in a correct way.

首先，让我们创建一个表

First, let's create a table

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;

行为不正确

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列不正确.我们可以看到，组不匹配，甚至有些组不见了.

Using such query we get a table, which generates correct values for RAND_1_TO_4 columns, but GROUP_IT column is incorrect. We can see, that groups don't match and some groups even missing.

正确的行为

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

I found a walkaround for such problem by creating a temporary table instead of using CTE. It helped.

-- 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列未正确生成?

What is the reasons behind such behaviour, where GROUP_IT column is not generated properly?

推荐答案

如果您使用 EXPLAIN ，您会看到，每次引用 RAND_1_TO_4 列时，都会重新计算其值，并且会使用新的随机数使用(我怀疑，但不是100％肯定这与 random()是非确定性函数有关).空值适用于那些 CASE 测试最终都不为真的情况.

If you look at the bytecode generated by the incorrect query using EXPLAIN, you'll see that every time the RAND_1_TO_4 column is referenced, its value is re-calculated and a new random number is used (I suspect but aren't 100% sure this has something to do with how random() is a non-deterministic function). The null values are for those times when none of the CASE tests end up being true.

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

When you insert into a temporary table and then use that for the rest, the values of course remain static and it works as expected.

这篇关于CTE中的SQLite RANDOM()函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！