我在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()函数
发布评论