时间戳

首页 > 解决方案 > 选择查询以在表中多次选择连续行

java - 选择查询以在表中多次选择连续行

问题描述

我有多个表，在 Android Room 数据库的每个表中都有超过 10 万条记录，并使用 MVVM 模式。

每个表都有两列，一个 id（主键）和一个字符串列。

要求：在已知间隔长度的多个间隔选择连续行。

说明：假设，由于一些背景计算，我有一个 200 的间隔，其中间隔从 300 开始，到 500 结束。我需要在表格中每 2K 条记录之后选择这 200 行（500-300=200）行。所以在这种情况下，查询应该选择下面写的模式中的 200 行

前 2K 记录的行从 300 到 500，

第二个 2K 记录中从 2300 到 2500 的行，

第三个 2K 记录中从 4300 到 4500 的行

同样直到餐桌结束。

目前我正在安排这样的查询，因为我知道间隔所以

s1 和 e1 是 300 和 500，

s2 和 e2 是 2300 和 2500，

s3 和 e3 是 4300 和 4500 等等

 @Query("SELECT * FROM CATEGORY_TABLE WHERE (id BETWEEN :s1 AND :e1 ) OR (id BETWEEN :s2 AND :e2 ) OR (id BETWEEN :s3 AND :e3 ) OR (id BETWEEN :s4 AND :e4 ) OR (id BETWEEN :s5 AND :e5 ) " +
            "OR (id BETWEEN :s6 AND :e6 ) OR (id BETWEEN :s7 AND :e7 ) OR (id BETWEEN :s8 AND :e8 ) OR (id BETWEEN :s9 AND :e9 ) OR (id BETWEEN :s10 AND :e10 ) OR" +
            " (id BETWEEN :s11 AND :e11 ) OR (id BETWEEN :s12 AND :e12 ) OR (id BETWEEN :s13 AND :e13 ) OR (id BETWEEN :s14 AND :e14 ) OR (id BETWEEN :s15 AND :e15 ) OR " +
            "(id BETWEEN :s16 AND :e16 ) OR (id BETWEEN :s17 AND :e17 ) OR (id BETWEEN :s18 AND :e18 ) OR (id BETWEEN :s19 AND :e19 ) OR (id BETWEEN :s20 AND :e20 ) OR " +
            "(id BETWEEN :s21 AND :e21 ) OR (id BETWEEN :s22 AND :e22 ) OR (id BETWEEN :s23 AND :e23 ) OR (id BETWEEN :s24 AND :e24 ) OR (id BETWEEN :s25 AND :e25 ) OR " +
            "(id BETWEEN :s26 AND :e26 ) OR (id BETWEEN :s27 AND :e27 ) OR (id BETWEEN :s28 AND :e28 ) OR (id BETWEEN :s29 AND :e29 ) OR (id BETWEEN :s30 AND :e30 ) OR " +
            "(id BETWEEN :s31 AND :e31 ) OR (id BETWEEN :s32 AND :e32 ) OR (id BETWEEN :s33 AND :e33 ) OR (id BETWEEN :s34 AND :e34 ) OR (id BETWEEN :s35 AND :e35 ) OR " +
            "(id BETWEEN :s36 AND :e36 ) OR (id BETWEEN :s37 AND :e37 ) OR (id BETWEEN :s38 AND :e38 ) OR (id BETWEEN :s39 AND :e39 ) OR (id BETWEEN :s40 AND :e40 )"+ 
"(id BETWEEN :s41 AND :e41 ) OR (id BETWEEN :s42 AND :e42 ) OR (id BETWEEN :s43 AND :e43 ) OR (id BETWEEN :s44 AND :e44 ) OR (id BETWEEN :s45 AND :e45 ) OR " +
 "(id BETWEEN :s46 AND :e46 ) OR (id BETWEEN :s47 AND :e47 ) OR (id BETWEEN :s48 AND :e48 ) OR (id BETWEEN :s49 AND :e49 ) OR (id BETWEEN :s50 AND :e50 )");
    List<Category> getCategorySpecificRows(int s1, int e1, int s2, int e2, int s3, int e3, int s4, int e4, int s5, int e5,
                                                         int s6, int e6, int s7, int e7, int s8, int e8, int s9, int e9, int s10, int e10,
                                                         int s11, int e11, int s12, int e12, int s13, int e13, int s14, int e14, int s15, int e15,
                                                         int s16, int e16, int s17, int e17, int s18, int e18, int s19, int e19, int s20, int e20,
                                                         int s21, int e21, int s22, int e22, int s23, int e23, int s24, int e24, int s25, int e25,
                                                         int s26, int e26, int s27, int e27, int s28, int e28, int s29, int e29, int s30, int e30,
                                                         int s31, int e31, int s32, int e32, int s33, int e33, int s34, int e34, int s35, int e35,
                                                         int s36, int e36, int s37, int e37, int s38, int e38, int s39, int e39, int s40, int e40,
    int s41, int e41, int s42, int e42, int s43, int e43, int s43, int e44, int s45, int e45, int s46, int e46, int s47, int e47, int s48, int e48, int s49, int e49, int s50, int e50);

并使用此 BETWEEN 50 次迭代超过 100K 记录表。它工作正常，但性能很差。将这些数据发送回存储库大约需要 500 毫秒 - 1500 毫秒。什么应该是编写此查询的更好方法以使此数据获取时间有效。

标签： javasqliteselectandroid-sqliteandroid-room

解决方案

第一个 2K 记录从 300 到 500 行开始，第二个 2K 记录从 2300 到 2500 行开始，第三个 2K 记录从 4300 到 4500 行开始，同样直到表的末尾。

你可以使用artihemtics！模块在这里派上用场：

select *
from category
where id % 2000 between 300 and 500

这使得查询写起来更短，但效率可能仍然是一个问题。无论如何，每 2000 行选取 300 行可能需要进行全面扫描。

推荐阅读