sql统计10天内，连续签到4天及以上的人数。

Question

并且需要从今天开始往前统计，如今日是10日，某员工在45678日签到，今天未签到，不统计。
查询结果需要字段：连续签到天数，开始签到天数，人员id

Answer 1

以下回复由本人参考gpt作答:

要在MySQL中编写一个SQL查询，以统计在过去10天内连续签到4天及以上的人员数量，并且以今日为基准，考虑到今日未签到不算作连续签到的一部分，可以按照以下步骤来构建查询。

假设你有一个签到记录表 sign_in_records，表结构如下：

• employee_id (员工ID)
• sign_in_date (签到日期)

首先，确保表中的日期是以标准日期格式存储的，例如 YYYY-MM-DD。

下面是一个SQL查询示例，它实现了你所要求的功能：

SET @today := CURDATE();

-- 创建一个视图来获取过去10天的签到记录
CREATE TEMPORARY TABLE recent_sign_ins AS
SELECT employee_id, sign_in_date
FROM sign_in_records
WHERE sign_in_date BETWEEN DATE_SUB(@today, INTERVAL 9 DAY) AND @today;

-- 查找每个员工在这段时间内的签到记录
CREATE TEMPORARY TABLE streaks AS
SELECT
    employee_id,
    sign_in_date,
    @row_num := IF(@prev_employee_id = employee_id, @row_num + 1, 1) AS streak,
    @prev_employee_id := employee_id AS prev_employee_id
FROM recent_sign_ins
ORDER BY employee_id, sign_in_date;

-- 查找连续签到天数大于等于4天的记录
SELECT
    streak AS consecutive_days,
    MIN(sign_in_date) AS start_date,
    employee_id
FROM streaks
WHERE streak >= 4
GROUP BY employee_id, streak;

-- 清理临时表
DROP TEMPORARY TABLE recent_sign_ins;
DROP TEMPORARY TABLE streaks;

解释：

1. 获取过去10天的签到记录：

   • 使用 DATE_SUB 函数计算出10天的时间范围，并筛选 sign_in_records 表中在该时间范围内的签到记录。

2. 计算每个员工的签到连续天数：

   • 使用一个用户变量 @row_num 来跟踪每个员工的连续签到天数。
   • 根据 employee_id 和 sign_in_date 进行排序，并计算连续签到天数。

3. 查找连续签到天数大于等于4天的记录：

   • 对于计算出的连续签到天数，筛选出连续签到天数大于等于4天的记录。
   • 使用 MIN(sign_in_date) 来获取连续签到的开始日期。

4. 清理临时表：

   • 删除临时表以释放资源。

注意：在实际应用中，你可能需要根据具体的数据库表结构和数据量来调整查询。上述查询是基于一个假设的表结构和数据量，如果数据量很大或者表结构复杂，可能需要进一步优化。