Sql 随机排序的子查询不适用于具有 500.000.000 行的数据库

发布于08月03日

我想使用DB Browser for SQLite在脱机数据库上执行更新查询.

我在几行上测试了我的查询，它在那里工作得很好，但不是在我的数据库上，它有500.000.000行+.看起来这里根本没有执行随机子查询，而是获取GROUP BY的第一行.

查询:

UPDATE  
table
SET typ = 3 WHERE id IN (
  SELECT id FROM (
    SELECT * FROM table ORDER BY RANDOM()
  )  
  WHERE typ = 1 GROUP BY idg HAVING COUNT(idg) > 5
)

样本数据:

id |idg| typ
1  | 1 | 1
2  | 1 | 1
3  | 1 | 1
4  | 1 | 1
5  | 1 | 1
6  | 1 | 1
7  | 1 | 1
8  | 2 | 1
9  | 2 | 1
10 | 2 | 1
11 | 2 | 1
12 | 2 | 1
13 | 2 | 1
14 | 2 | 1
15 | 2 | 1

是否有任何修复或解决方法可以成功执行我的查询？

推荐答案

如果您的SQLite版本是3.33.0+，则可以使用UPDATE ... FROM...语法，这样您就可以向表添加一个查询，该查询使用窗口函数ROW_NUMBER()来判断特定的idg是否有超过5行，并返回随机的id:

WITH cte AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY idg ORDER BY RANDOM()) rn
  FROM tablename
  WHERE typ = 1
)
UPDATE tablename AS t
SET typ = 3 
FROM cte AS c
WHERE t.id = c.id AND c.rn = 6; -- rn = 6 makes sure that there are at least 6 rows

See the demo.

对于SQLite 3.25.0+，使用具有ROW_NUMBER()窗口功能的运算符IN:

UPDATE tablename
SET typ = 3 
WHERE id IN (
  SELECT id 
  FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY idg ORDER BY RANDOM()) rn
    FROM tablename
    WHERE typ = 1
  )
  WHERE rn = 6 -- rn = 6 makes sure that there are at least 6 rows 
);

See the demo.

Sql 随机排序的子查询不适用于具有 500.000.000 行的数据库

推荐答案

Sql相关问答推荐

无效和不匹配的计数

PostgreSQL：按小时查看调整日期

Select 非重复值并按条件排除行

根据开始日期和结束日期的差异计算每天的计费

在Postgres中合并相似的表

从类似JSON的字符串列创建新列

如何在连接中使用三个不同的列，从而在PostgreSQL中只获得两个列？

PostgreSQL：查找继承表中的特定记录属于哪个表

从另一个没有公共键的表中获取值来加入

除了风格之外，还有什么理由更喜欢简单的CASE WHEN而不是搜索呢？

使用多个数据库调用重载 CQRS 模式

计算 ID 满足条件的次数

在自引用表中使用分组和计数的SQL查询语句

如何创建一个递归计数器来查找一个元素有多少父级和子级？

正则表达式忽略特定数据部分的分隔符

joins 组合多个重复数据删除策略

编写查询以根据级别 (p2) 返回父位置

使用日期和间隔作为键加入 Athena 上的表？

Postgres 窗口函数未按预期工作

snowflake插入覆盖行为