sql - 为什么 SQLite CTE JOIN 中的 RANDOM() 与其他 RDBMS 的行为不同？

公共(public)表表达式 (CTE) 连接中的 RANDOM() 值在 SQLite 中的行为不符合预期。

SQL:

WITH
  tbl1(n) AS (SELECT 1 UNION ALL SELECT 2),
  tbl2(n, r) AS (SELECT n, RANDOM() FROM tbl1)
SELECT * FROM tbl2 t1 CROSS JOIN tbl2 t2;

SQLite 结果示例:

n   r                       n   r
1   7058971975145008000     1   8874103142384122000
1   1383551786055205600     2   8456124381892735000
2   2646187515714600000     1   7558324128446983000
2   -1529979429149869800    2   7003770339419606000

每列中的随机数都不同。但是 CROSS JOIN 重复行 - 所以我预计每列中有 2 对相同的数字 - PostgreSQL ， Oracle 11g 和 SQL Server 2014 (使用基于行的种子时)就是这种情况。

示例 PostgreSQL/Oracle 11g/SQL Server 2014 结果:

n   r                   n   r
1   0.117551110684872   1   0.117551110684872
1   0.117551110684872   2   0.221985165029764
2   0.221985165029764   1   0.117551110684872
2   0.221985165029764   2   0.221985165029764

问题

可以解释 SQLite 中的行为吗？这是一个错误吗？

是否有办法让 CTE 中的表 B(基于同一 CTE 中的表 A)具有额外的随机生成数字列，在 JOIN 中使用时将保持固定？

最佳答案

你的问题相当长而且漫无边际——不是一个问题。但是，这很有趣，我学到了一些东西。

这种说法是不正确的:

SQL Server 具有运行时常量函数的概念。这些是从编译的查询中提取的函数，并在查询开始时为每个表达式执行一次。最突出的例子是 getdate() (和相关的日期/时间函数)和 rand() 。

如果您运行，您可以很容易地看到这一点:

select rand(), rand()
from (values (1), (2), (3)) v(x);

每列具有相同的值，但列之间的值不同。

大多数数据库——包括 SQLite——对 rand()/random() 有更直观的解释。 (作为个人说明，在每一行上返回相同值的“随机”函数是非常违反直觉的。)每次调用它时都会得到不同的值。对于 SQL Server，您通常会使用使用 newid() 的表达式:

select rand(), rand(), rand(checksum(newid()))
from (values (1), (2), (3)) v(x);

至于你的第二个问题，SQLite 似乎实现了递归 CTE。所以这做你想要的:

WITH tbl1(n) AS (
      SELECT 1 UNION ALL SELECT 2
     ),
     tbl2(n, r) AS (
       SELECT n, RANDOM()
       FROM tbl1
       union all
       select *
       from tbl2
       where 1=0
      )
SELECT *
FROM tbl2 t1 CROSS JOIN tbl2 t2;

我没有看到任何文档说明这种情况，因此使用风险自负。 Here 是一个 DB-Fiddle。

而且，作为记录，这似乎也适用于 SQL Server。我刚刚学到了一些东西!

编辑:

正如评论中所建议的那样，实现可能并不总是发生。它似乎确实适用于同一级别的两个引用文献:

WITH tbl1(n) AS (
      SELECT 1 UNION ALL SELECT 2),
     tbl2(n, r) AS (
       SELECT n, RANDOM()
       FROM tbl1
       union all
       select *
       from tbl2
       where 1=0
      )
SELECT t2a.r, count(*)
FROM tbl2 t2a left JOIN
     tbl2 t2b
     on t2a.r = t2b.r
GROUP BY t2a.r;

关于sql - 为什么 SQLite CTE JOIN 中的 RANDOM() 与其他 RDBMS 的行为不同？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/50289527/