快速设置排序顺序列

x33g5p2x  于 2021-08-09  发布在  Java
关注(0)|答案(1)|浏览(231)

假设我有一个带有列的sqlite表 recordId TEXT, name TEXT, job TEXT, sortOrder NUM .
我想设定 sortOrder 基于按名称和作业排序表中所有行的列。
我目前的做法是:
(1) SELECT recordId from People order by name, job (1b)在 sqlite3_step() 循环,保存 recordId 将值转换为 vector<string> orderedRecordIds . 当我们完成这个循环时, orderedRecordIdsrecordId 按所需顺序排列的值。
(2) 在一个循环中,做一个 sqlite3_exec() 对于每个 recordId 窗体的

UPDATE People SET sortOrder = <i> WHERE recordId = '<orderedRecordIds[i]>'

这些都可以,但是太慢了。
对于具有200k条记录的数据库,执行步骤(1)大约需要1秒,执行步骤(2)大约需要12秒。
我不担心做第(1)步的时间。
但我正在想办法让第(2)步更快。
我在Map上有索引 recordId 列,我认为这将有助于找到每一行来设置 sortOrder 步骤(2)中的值。
如果我使用 rowid 而不是 recordId ,它将步骤(2)降低到10秒。
我在想这都是单独打给 sqlite3_exec() 让事情变慢了。所以我试着在一个exec语句中构建一个巨大的 CASE 声明:

UPDATE People SET sortOrder = CASE
    WHEN recordId='abc' THEN 0
    WHEN recordId='def' THEN 1
    /* <and so on for 200k rows> */
END

但这是非常缓慢的。
我觉得应该有一个非常快速的方法来完成第(2)步。相比之下,当我使用create index为这个大表的一列创建索引时,它需要大约20毫秒的时间
也许使用 recordId 以及 sortOrder 列,然后基于与该表的联接进行更新?
或者有一种方法可以在一个单步/循环中完成这一切,而不是2步?
(顺便说一句,我意识到当问题出现时,我可以避免 sortOrder 只需在name和job字段上创建一个索引。但在我的实际应用程序中,我所排序的一些字段是计算值,在某些情况下是基于相关表中的值。这就是为什么我想有一个 sortOrder 列在第一位。也许有一种方法可以根据其他表中的相关值编制索引。但现在请考虑我的问题。)

mefy6pfw

mefy6pfw1#

为什么不使用sqlite 3.25版的窗口函数呢?
如果我正确地跟踪了您,下面的查询将为您提供所需的结果:

select p.*, row_number() over(order by name, job) rn from people p;

您可以使用此查询创建一个视图,而不是存储值。。。当涉及到更新表时,它要复杂一些,因为sqlite不支持update语句中的联接。您可以首先具体化包含排序顺序的临时表,然后使用它来更新表,如:

create temp table people_tmp as
select recordId, row_number() over(order by name, job) rn from people;

update people
set sortOrder = (
    select rn from people_tmp pt where pt.recordId = people.recordId
);

相关问题