【原创】历史轨迹业务--查询过滤相邻没有变更的数据

liangrun521

已于 2023-11-20 17:34:40 修改

阅读量61

点赞数 1

文章标签： java 数据库 sql 后端数据库开发

于 2023-10-20 18:11:17 首次发布

本文链接：https://blog.csdn.net/weixin_42306823/article/details/133951795

版权

问题：历史轨迹的业务中每次编辑都会存储，查询需要过滤相邻没有变更的数据

-- 数据操作过程（简略描述）
       主键     操作    数据      MD5
第1次：100	新增    'a'       '9'
第2次：200	编辑    'b'       '10'
第3次：300	编辑    'b'       '10'
第4次：400	编辑    'c'       '11'
第5次：500	编辑    'b'       '10'
第6次：600	编辑    'd'       '12'

-- 期望结果：
-- operate_time	operator	res_type	action	res_id	md5	last_md5	new_value	old_value
-- 100	系统管理员	2	新增	335891161551028224	9	0	a	null
-- 200	系统管理员	2	编辑	335891161551028224	10	11	b	a
-- 400	系统管理员	2	编辑	335891161551028224	11	10	c	b
-- 500	系统管理员	2	编辑	335891161551028224	10	11	b	c
-- 600	系统管理员	2	编辑	335891161551028224	12	11	d	c

测试数据准备

-- table
CREATE TABLE id_t_history_trace (
  id bigint NOT NULL COMMENT '主键',
  operate_time bigint DEFAULT NULL COMMENT '操作时间',
  operator varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '操作员名',
  res_type tinyint DEFAULT NULL COMMENT '资源类型,1:用户，2:机构 3: 设备',
  action int DEFAULT NULL COMMENT '历史轨迹动作，100, "添加"，101, "编辑"，102, "注销"',
  old_value varchar(5000) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '老数据',
  new_value varchar(5000) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '新数据',
  md5 varchar(512) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT 'md5值',
  tenant_id bigint DEFAULT NULL COMMENT '租户id',
  res_id bigint DEFAULT NULL COMMENT '资源ID',
  PRIMARY KEY (id) USING BTREE,
  KEY id_t_history_trace_res_id_index (res_id) USING BTREE COMMENT '查询是按照"res_id"列进行筛选和分组的，因此在该列上创建索引可以减少扫描的行数，从而提高查询性能。',
  KEY id_t_history_trace_operate_time_index (operate_time) USING BTREE COMMENT '查询需要按照"operate_time"列进行排序，因此在该列上创建索引可以提高排序性能并减少内存使用。'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- ALTER TABLE id_t_history_trace
-- ADD INDEX id_t_history_trace_res_id_index(res_id) USING BTREE COMMENT '查询是按照 res_id 列进行筛选和分组的，因此在该列上创建索引可以减少扫描的行数，从而提高查询性能',
-- ADD INDEX id_t_history_trace_operate_time_index(operate_time) USING BTREE COMMENT '查询需要按照 operate_time 列进行排序，因此在该列上创建索引可以提高排序性能并减少内存使用'

INSERT INTO id_t_history_trace (id, operate_time, operator, res_type, action, old_value, new_value, md5, tenant_id, res_id) VALUES (100, 100, '系统管理员', 2, 100, NULL, 'a', '9', 1, 335891161551028224);
INSERT INTO id_t_history_trace (id, operate_time, operator, res_type, action, old_value, new_value, md5, tenant_id, res_id) VALUES (200, 200, '系统管理员', 2, 101, NULL, 'b', '10', 1, 335891161551028224);
INSERT INTO id_t_history_trace (id, operate_time, operator, res_type, action, old_value, new_value, md5, tenant_id, res_id) VALUES (300, 300, '系统管理员', 2, 101, NULL, 'b', '10', 1, 335891161551028224);
INSERT INTO id_t_history_trace (id, operate_time, operator, res_type, action, old_value, new_value, md5, tenant_id, res_id) VALUES (400, 400, '系统管理员', 2, 101, NULL, 'c', '11', 1, 335891161551028224);
INSERT INTO id_t_history_trace (id, operate_time, operator, res_type, action, old_value, new_value, md5, tenant_id, res_id) VALUES (500, 500, '系统管理员', 2, 101, NULL, 'b', '10', 1, 335891161551028224);
INSERT INTO id_t_history_trace (id, operate_time, operator, res_type, action, old_value, new_value, md5, tenant_id, res_id) VALUES (600, 600, '系统管理员', 2, 102, NULL, 'd', '12', 1, 335891161551028224);

lag()函数处理

思路参考了：https://blog.csdn.net/qq_41404557/article/details/126298624

-- lag()函数：LAG()函数是标准SQL函数，MySQL 8.0及以上版本支持，Oracle，SQL Server，PostgreSQL，SQLite3.25.0及以上版本支持
-- LAG()函数的语法：LAG(column, offset, default) OVER (ORDER BY column) 
-- 其中，column是要查找的列，offset是要向前查找的行数，default是当没有前一行时返回的值。ORDER BY子句指定了要对哪一列进行排序。
-- LAG()函数可以支持多列。在使用LAG()函数时，我们可以使用PARTITION BY子句来指定多列作为分组依据。

-- 根据条件查询去重上一条重复数据，利用lag()函数增加一列“上一个数据”，利用本次访问的页面不等于上一个访问的页面作为条件，取出要求的结果，思路与第一个思路一致。

-- 注意：lag( h.md5, 1, 0 ) over ( PARTITION BY h.res_id ORDER BY h.operate_time ASC ) 中ORDER By的顺序，ASC为获取前一行数据，DESC为获取后一行数据
-- 使用中发现人大金仓数据量lag函数不支持LAG(column, offset, default)，支持LAG(column)，即去掉lag函数后两个参数，得到结果也是一致的
-- Mysql经过实测lag可以不要后两个参数也是可以的

SELECT
    history.operate_time,
    history.operator,
    history.res_type,
    history.action,
    history.res_id,
    history.md5,
    history.last_md5,
    history.new_value,
    history.old_value 
FROM
    (
    SELECT
        h.operate_time,
        h.operator,
        h.res_type,
        h.action,
        h.new_value,
        h.res_id,
        h.tenant_id,
        h.md5,
        lag( h.md5, 1, 0 ) over ( PARTITION BY h.res_id ORDER BY h.operate_time ASC ) AS last_md5,
        lag( h.new_value, 1, NULL ) over ( PARTITION BY h.res_id ORDER BY h.operate_time ASC ) AS old_value 
    FROM
        id_t_history_trace h 
    WHERE
        h.res_id = 335891161551028224 
		ORDER BY
        h.operate_time
    ) history 
WHERE
    history.res_id = 335891161551028224 
    AND history.last_md5 IS NULL 
    OR history.md5 != history.last_md5 
ORDER BY
    history.operate_time
    LIMIT 0,
    101;

关联子查询处理

-- 使用带有关联子查询的子查询的示例查询：

-- 这个查询使用了带有关联子查询的子查询，将前一行的"new_value"值作为子查询的结果列。
-- 注意，这个查询中使用了NOT EXISTS子查询来检查是否有前一行。如果没有前一行，则子查询的结果为NULL，因此需要在SELECT语句中使用COALESCE函数将其转换为适当的值。

SELECT
    current.operate_time,
    current.operator,
    current.res_type,
    current.action,
    current.res_id,
    current.md5,
    current.new_value,
    (
        SELECT
            previous.new_value
        FROM
            id_t_history_trace previous
        WHERE
            previous.res_id = current.res_id
            AND previous.operate_time < current.operate_time
        ORDER BY
            previous.operate_time DESC
        LIMIT
            1
    ) AS old_value
FROM
    id_t_history_trace current
WHERE
    current.res_id = 335891161551028224
    AND (
        NOT EXISTS (
            SELECT
                *
            FROM
                id_t_history_trace previous
            WHERE
                previous.res_id = current.res_id
                AND previous.operate_time < current.operate_time
        ) OR current.md5 != (
            SELECT
                previous.md5
            FROM
                id_t_history_trace previous
            WHERE
                previous.res_id = current.res_id
                AND previous.operate_time < current.operate_time
            ORDER BY
                previous.operate_time DESC 
            LIMIT
                1
        )
    )
ORDER BY
    current.operate_time
LIMIT
    0, 101;

业务层面处理

-- 原来业务处理，每次查询多查询一条，后端处理旧数据,数据少一条，数据为3条，缺失1条数据
SELECT
    history.operate_time,
    history.operator,
    history.res_type,
    history.action,
    history.new_value,
    history.res_id,
    history.tenant_id,
    history.md5 
FROM
    id_t_history_trace history
    INNER JOIN ( SELECT max( id ) AS id FROM id_t_history_trace WHERE res_type = 2  AND res_id=335891161551028224
    GROUP BY md5 ) temp ON history.id = temp.id 
ORDER BY
    history.operate_time DESC 
    LIMIT 0,
    101;

-- 这里其实可以优化：每次入库查询上次编辑内容，进行对比，没有变化，则不进行入库（利用一次查询换取存储空间和查询简化）