获取连续打卡次数以及开始结束时间sql编写以及分析-526互联

创建表数据

CREATE TABLE attendance_records (
  id INT PRIMARY KEY,  -- 主键id
  name VARCHAR(50),    -- 打卡人
  punch_time TIMESTAMP  -- 打开时间
);
-- 插入示例数据
INSERT INTO attendance_records (id, name, punch_time) VALUES
  (1, 'Alice', TO_TIMESTAMP('2023-10-11 08:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  (2, 'Bob', TO_TIMESTAMP('2023-10-11 09:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  (3, 'Alice', TO_TIMESTAMP('2023-10-12 08:15:00', 'YYYY-MM-DD HH24:MI:SS')),
  (4, 'Bob', TO_TIMESTAMP('2023-10-12 09:30:00', 'YYYY-MM-DD HH24:MI:SS')),
  (5, 'Alice', TO_TIMESTAMP('2023-10-13 08:05:00', 'YYYY-MM-DD HH24:MI:SS')),
  (6, 'Bob', TO_TIMESTAMP('2023-10-13 09:15:00', 'YYYY-MM-DD HH24:MI:SS')),
  (7, 'Alice', TO_TIMESTAMP('2023-10-14 08:10:00', 'YYYY-MM-DD HH24:MI:SS')),
  (8, 'Bob', TO_TIMESTAMP('2023-10-14 09:20:00', 'YYYY-MM-DD HH24:MI:SS')),
  (9, 'Alice', TO_TIMESTAMP('2023-10-16 08:10:00', 'YYYY-MM-DD HH24:MI:SS')),
  (10, 'Bob', TO_TIMESTAMP('2023-10-16 09:20:00', 'YYYY-MM-DD HH24:MI:SS')),
  (11, 'Alice', TO_TIMESTAMP('2023-10-1 08:10:00', 'YYYY-MM-DD HH24:MI:SS')),
  (12, 'Bob', TO_TIMESTAMP('2023-10-1 09:20:00', 'YYYY-MM-DD HH24:MI:SS')),
  (13, 'Alice', TO_TIMESTAMP('2023-10-2 08:10:00', 'YYYY-MM-DD HH24:MI:SS')),
  (14, 'Bob', TO_TIMESTAMP('2023-10-2 09:20:00', 'YYYY-MM-DD HH24:MI:SS')),
  (15, 'Alice', TO_TIMESTAMP('2023-10-5 08:10:00', 'YYYY-MM-DD HH24:MI:SS')),
  (16, 'Bob', TO_TIMESTAMP('2023-10-5 09:20:00', 'YYYY-MM-DD HH24:MI:SS'));

查询语句

 WITH RankedPunches AS (
  SELECT
    name,
    punch_time,
    LAG(punch_time) OVER (PARTITION BY name ORDER BY punch_time) AS prev_punch_time
  FROM
    attendance_records
)
, GroupedData AS (
  SELECT
    name,
    punch_time,
    CASE
      WHEN 
        prev_punch_time IS NULL OR 
        DATE_TRUNC('day', punch_time) - DATE_TRUNC('day', prev_punch_time) > INTERVAL '1 day'
      THEN 1
      ELSE 0
    END AS is_start_of_group
  FROM
    RankedPunches
)
SELECT
  name,
  MIN(punch_time) AS start_date,
  MAX(punch_time) AS end_date,
  COUNT(DISTINCT punch_time) AS consecutive_days
FROM (
  SELECT
    name,
    punch_time,
    SUM(is_start_of_group) OVER (PARTITION BY name ORDER BY punch_time) AS grp
  FROM
    GroupedData
) GroupedDataWithGroups
GROUP BY
  name, grp
ORDER BY
  name, start_date;

执行结果

分析:

当我们要查找每个人的连续打卡情况时，我们首先需要确定哪些记录被认为是连续的。在这个查询中，我们的目标是找到每个人的每组连续的打卡记录。我们通过以下步骤实现：

RankedPunches CTE：使用 LAG 函数来获取每个人每次打卡的前一次打卡时间。这是为了后续比较当前打卡时间与前一次打卡时间的日期差异。
GroupedData CTE：在这一步中，我们使用 CASE 语句标记哪些记录被认为是一组中的起始。具体判断的逻辑如下：
- 如果前一次打卡时间为 NULL，即这是第一次打卡，或者当前打卡时间与前一次打卡时间的日期差异超过一天，那么我们认为这是一组中的起始。
- 否则，我们将 is_start_of_group 标记为 0。
GroupedDataWithGroups Subquery：使用 SUM 函数计算每组的标志。这里使用窗口函数的目的是为了给每条记录分配一个组号 (grp)，以便后续的分组聚合。
最终查询：在这一步中，我们使用 GROUP BY 和聚合函数来计算每组的最早打卡时间、最晚打卡时间和打卡天数。我们通过 HAVING 子句过滤掉打卡天数不足的组，并按照姓名和起始时间排序结果。

总体来说，这个查询的逻辑是首先标记每个人的打卡记录中哪些是一组中的起始，然后计算每组的相关统计信息。这种方法可以处理中间某天没有记录的情况，确保连续性的判断不受这些间隔的影响。

LAG函数：

LAG 函数是一种窗口函数，用于访问当前行之前的行的数据。在这个上下文中，LAG 函数用于获取每个人每次打卡的前一次打卡时间。

基本语法如下：

LAG(expression [, offset [, default_value]]) OVER (PARTITION BY partition_expression ORDER BY sort_expression)
expression：要获取前一行值的表达式。
offset：可选参数，表示要访问的前几行，默认为 1。
default_value：可选参数，在没有前一行数据时使用的默认值。

在我们的查询中，expression 是 punch_time，offset 是默认的 1，表示获取前一行的值。PARTITION BY 子句将数据集按照 name 字段进行分区，ORDER BY 子句则按照 punch_time 字段排序。

举个例子，假设有如下数据：

| id | name | punch_time |

|----|-------|---------------------|

| 1 | Alice | 2023-10-11 08:00:00 |

| 2 | Alice | 2023-10-12 08:15:00 |

| 3 | Alice | 2023-10-13 08:05:00 |