随机排序的子查询在具有500.000000行的数据库上不起作用

Question

我想用DB Browser for SQLite对离线数据库执行更新查询。

我在几行上测试了我的查询，它在那里运行得很好，但在我的数据库中却没有，因为我的数据库有500.000000行以上。看起来随机子查询根本没有在那里执行，分组依据的第一行被占用。

查询：

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

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

Answer 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

请参阅演示

对于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 
);

请参阅演示