MySQL 依据扫码记录统计人员进出区域的时间

最新推荐文章于 2024-07-24 20:39:38 发布

崇文馆居士

最新推荐文章于 2024-07-24 20:39:38 发布

阅读量257

点赞数 1

文章标签： MySQL

本文链接：https://blog.csdn.net/qq_34677276/article/details/138575826

版权

解决这样一个问题：
如下图人员在区域的扫码记录，进出区域的时候都必须扫码，中间扫码的不算，统计每个人员进入区域、出区域的时间。

在这里插入图片描述
有了人员在区域停留的组号，还差一步group by（在最后），为了方便观察中间结果。

WITH t_seq
AS
(SELECT
      t1.*,
      ROW_NUMBER() OVER (PARTITION BY person_name
      ORDER BY id) seq
    FROM scan_record t1), t_group
AS
(SELECT
      t1.*,
      IFNULL(LAG(t1.person_name) OVER (PARTITION BY person_name
      ORDER BY seq), 'none') lead_person_name,
      IFNULL(LAG(t1.area_name) OVER (PARTITION BY person_name
      ORDER BY seq), 'none') lead_area_name
    FROM t_seq t1
    ORDER BY t1.person_name, seq), t_flag
AS
(SELECT
      t1.*,
      CASE WHEN t1.person_name <> t1.lead_person_name OR
          t1.area_name <> t1.lead_area_name THEN 1 ELSE 0 END flag_number
    FROM t_group t1), result
AS
(SELECT
      t1.*,
      SUM(flag_number) OVER (PARTITION BY t1.person_name
      ORDER BY t1.seq) group_num
    FROM t_flag t1)
SELECT
  *
FROM result;

在这里插入图片描述
建表语句

CREATE TABLE scan_record (
  id bigint NOT NULL AUTO_INCREMENT,
  person_name varbinary(50) DEFAULT NULL,
  area_name varchar(50) DEFAULT NULL,
  scan_time datetime DEFAULT NULL,
  PRIMARY KEY (id)
)
ENGINE = INNODB,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_0900_ai_ci;

完整答案

WITH t_seq
AS
(SELECT
      t1.*,
      ROW_NUMBER() OVER (PARTITION BY person_name
      ORDER BY id) seq
    FROM scan_record t1), t_group
AS
(SELECT
      t1.*,
      IFNULL(LAG(t1.person_name) OVER (PARTITION BY person_name
      ORDER BY seq), 'none') lead_person_name,
      IFNULL(LAG(t1.area_name) OVER (PARTITION BY person_name
      ORDER BY seq), 'none') lead_area_name
    FROM t_seq t1
    ORDER BY t1.person_name, seq), t_flag
AS
(SELECT
      t1.*,
      CASE WHEN t1.person_name <> t1.lead_person_name OR
          t1.area_name <> t1.lead_area_name THEN 1 ELSE 0 END flag_number
    FROM t_group t1),result AS
    (
 SELECT
      t1.*,
      SUM(flag_number) OVER (PARTITION BY t1.person_name
      ORDER BY t1.seq) group_num
    FROM t_flag t1
    )
SELECT t1.person_name,t1.area_name
,min(t1.scan_time) start_time
,MAX(t1.scan_time) end_time
,t1.group_num
FROM result t1
GROUP BY t1.person_name,t1.area_name,t1.group_num;