统计数据库中连续相同的数据，如连续登录天数、重复订单等，核心是通过分组标记连续区间。统计用户连续登录天数，需按用户ID分组后，用DATEDIFF(date, ROW_NUMBER())标记连续区间。本文详细为大家介绍关于数据库统计连续相同数据的详细方法与步骤。
数据库统计连续相同数据
要让数据库统计连续相同的数据，通常需要结合窗口函数或自连接来实现。以下是针对不同数据库的通用方法和示例：
核心思路
分组连续数据：通过比较当前行与前一行的值，标记连续相同数据的起始和结束位置。
计算连续次数：对每个分组内的数据计数，得到连续出现的次数。
方法一：使用窗口函数
适用于支持窗口函数的数据库。
示例场景
统计表中连续相同状态的次数。
SQL 实现
sqlWITH MarkedGroups AS (SELECT id,date,status,-- 如果当前行与前一行状态相同，则继承组ID，否则新组IDSUM(CASE WHEN status != LAG(status) OVER (ORDER BY date) OR LAG(status) OVER (ORDER BY date) IS NULL THEN 1 ELSE 0 END) OVER (ORDER BY date) AS group_idFROM your_table),GroupedStats AS (SELECT group_id,status,COUNT(*) AS consecutive_countFROM MarkedGroupsWHERE status = 1 -- 筛选目标状态GROUP BY group_id, status)SELECT status,MAX(consecutive_count) AS max_consecutive_days -- 找出最长连续次数FROM GroupedStatsGROUP BY status;
关键点
LAG(status) OVER (ORDER BY date)：获取前一行的状态。
SUM(...) OVER (ORDER BY date)：为连续相同状态分配相同的 group_id。
最终按 group_id 分组统计次数。
方法二：自连接
如果数据库不支持窗口函数，可以通过自连接实现。
示例场景
统计连续相同的 status 出现的最大次数。
SQL 实现
sqlSELECT t1.status,MAX(t1.consecutive_count) AS max_consecutive_countFROM (SELECT a.id,a.status,COUNT(*) AS consecutive_countFROM your_table aJOIN your_table b ON a.id = b.id + 1 -- 假设按id升序排列WHERE a.status = b.statusGROUP BY a.id, a.status) t1GROUP BY t1.status;
局限性
需要数据有明确的排序字段。
性能较差，大数据量时可能超时。
方法三：使用变量。
MySQL 5.7 及以下版本可通过用户变量模拟窗口函数。
SQL 实现
sqlSELECT status,MAX(consecutive_count) AS max_consecutive_countFROM (SELECT id,date,status,@group_id := IF(status = @prev_status, @group_id, @group_id + 1) AS group_id,@prev_status := status,@consecutive_count := IF(status = @prev_status, @consecutive_count + 1, 1) AS consecutive_countFROM your_table,(SELECT @group_id := 0, @prev_status := NULL, @consecutive_count := 0) AS varsORDER BY date) tWHERE status = 1 -- 筛选目标状态GROUP BY group_id, status;
实际应用案例
案例1：统计用户连续登录天数
假设表 user_logins 包含字段 user_id, login_date，需统计每个用户最长连续登录天数。
sqlWITH MarkedDays AS (SELECT user_id,login_date,DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS date_groupFROM user_logins),GroupedStats AS (SELECT user_id,date_group,COUNT(*) AS consecutive_daysFROM MarkedDaysGROUP BY user_id, date_group)SELECT user_id,MAX(consecutive_days) AS max_consecutive_loginsFROM GroupedStatsGROUP BY user_id;
案例2：统计股票连续上涨天数
假设表 stock_prices 包含字段 date, price，需统计最长连续上涨天数。
sqlWITH PriceChanges AS (SELECT date,price,CASE WHEN price > LAG(price) OVER (ORDER BY date) THEN 1 ELSE 0 END AS is_increasingFROM stock_prices),MarkedGroups AS (SELECT date,is_increasing,SUM(CASE WHEN is_increasing = 0 THEN 1 ELSE 0 END) OVER (ORDER BY date) AS group_idFROM PriceChangesWHERE is_increasing = 1)SELECT COUNT(*) AS consecutive_increasing_daysFROM MarkedGroupsGROUP BY group_idORDER BY consecutive_increasing_days DESCLIMIT 1;
以上就是数据库统计连续的详细步骤，根据实际数据库类型和版本选择合适的方法即可!确保数据有明确的排序字段，否则无法正确判断连续性。优先使用窗口函数，代码简洁且性能好。
