如何在Android Room中查询存储yyyy-MM-dd格式日期的表的当前与最长连续天数

前置说明 #

你在表中存的yyyy-MM-dd格式文本日期，本身完全适配SQLite的内置日期函数，不需要额外转成时间戳就能完成连续序列计算。这套实现不用把全表数据拉到应用层循环遍历，直接靠SQLite的窗口函数就能跑完所有统计：核心是连号序列的经典解法——同一段连续日期里，「日期值减去排序后的行号」的差值是固定值，断档后差值会直接变化，靠这个差值就能把所有连续段切分开。 #

一、历史最长连续有记录天数（最长streak） #

计算逻辑 #

1. 先对全表日期做去重：同一天可能有多条行为记录（比如示例中2021-03-09存了2条），重复日期不影响连续计数，必须先去重避免计数偏误
2. 把去重后的日期按升序排序，给每个日期分配一个从1开始的连续行号
3. 计算每个日期对应的差值：日期往前推对应行号的天数，同一段连续日期的这个计算结果完全一致
4. 按这个固定差值分组，统计每组的日期数量，取最大值就是历史最长连续天数

可直接写入Room DAO的SQL代码 #

SELECT MAX(streak_length) AS max_streak
FROM (
    SELECT COUNT(*) AS streak_length
    FROM (
        SELECT 
            date,
            ROW_NUMBER() OVER (ORDER BY date) AS row_num,
            DATE(date, '-' || ROW_NUMBER() OVER (ORDER BY date) || ' days') AS group_key
        FROM (
            SELECT DISTINCT date FROM behavior_record
        )
    )
    GROUP BY group_key
)

示例数据验证 #

跑这段SQL返回的结果为6，和你给出的2021-03-01至2021-03-06共6天的预期结果完全一致。 #

二、截至最新记录的当前连续有记录天数（当前streak） #

计算逻辑 #

1. 同样先做日期去重，把日期按降序从新到旧排序
2. 用同样的差值逻辑切分连续段，直接定位最新日期所属的连续段，统计这段的日期数量
3. 加边界判断：如果最新记录和当前本地日期的间隔超过1天，说明连续记录已经断了，当前streak直接返回0

可直接写入Room DAO的SQL代码 #

SELECT 
    CASE
        WHEN julianday('now', 'localtime') - julianday(max_date) > 1 THEN 0
        ELSE current_streak
    END AS current_streak
FROM (
    SELECT COUNT(*) AS current_streak, MAX(date) AS max_date
    FROM (
        SELECT 
            date,
            ROW_NUMBER() OVER (ORDER BY date DESC) AS row_num,
            DATE(date, '+' || ROW_NUMBER() OVER (ORDER BY date DESC) || ' days') AS group_key
        FROM (SELECT DISTINCT date FROM behavior_record)
    )
    WHERE group_key = (
        SELECT DATE(MAX(date), '+1 days') 
        FROM (SELECT DISTINCT date FROM behavior_record)
    )
)

示例数据验证 #

跑这段SQL返回的结果为4，和你给出的2021-03-09至2021-03-12共4天的预期结果完全一致。 #

实操注意事项 #

• 所有计算前必须对日期做DISTINCT去重，否则同一天的多条重复记录会打乱行号计数逻辑，导致连续长度统计偏大
• SQLite的日期函数默认用UTC时区，计算本地自然日连续天数时，一定要加localtime参数，避免跨时区导致的日期错位
• 上述SQL用到了窗口函数（ROW_NUMBER()），如果你的minSdk < 30，只需要在模块的build.gradle中引入最新版androidx.sqlite:sqlite依赖，Room会自动内置支持高版本SQLite，不需要额外适配低版本系统

内容的提问来源于stack exchange，提问作者Martin