CTE中的SQLite RANDOM()函数

本文介绍了CTE中的SQLite RANDOM()函数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在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.