SQLite 计算两行数据差异并赋值数值:按用户统计累计登录使用天数
SQLite 登录日志 usage_days 计算实现
可直接运行的查询SQL
-- 请将 login_logs 替换为你业务中实际的登录日志表名 WITH base_data AS ( SELECT name, login_time, -- 按用户分组、登录时间排序,预先取出同用户上一次登录的自然日值 LAG(date(login_time)) OVER ( PARTITION BY name ORDER BY login_time ) AS prev_login_date FROM login_logs ) SELECT name, login_time, -- 逐行判断是否为新使用日,再对新使用日标记做累计求和,得到usage_days SUM( CASE WHEN prev_login_date IS NULL OR date(login_time) != prev_login_date THEN 1 ELSE 0 END ) OVER ( PARTITION BY name ORDER BY login_time ) AS usage_days FROM base_data ORDER BY name, login_time;
核心逻辑说明
- 按用户维度拆分登录记录,所有记录按登录时间先后排序,用窗口函数直接获取每条记录对应的同用户上一次登录的自然日
- 对每条记录做规则判断:如果是该用户的第一条登录记录,或者当前登录的自然日和上一次登录的自然日不一致,就记为一次新的使用日
- 对单个用户的所有新使用日标记按登录时间累计求和,得到的累计值就是对应记录的usage_days,同一自然日的多次登录会归属同一个使用日计数
注意事项
- 该查询依赖SQLite 3.25及以上版本的窗口函数能力,目前Python内置sqlite3、常用桌面客户端等主流环境的SQLite版本均已满足要求
- 如果使用的是低于3.25的老旧SQLite版本,可以通过自连接匹配同用户的登录记录、逐行判断日期间隔的方式实现等价逻辑,但代码冗余度和运行效率会差一些
内容的提问来源于stack exchange,提问作者Carlos
相关产品推荐
相关产品推荐

