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屋!