PostgreSQL12 按特定规则过滤行:获取各ID序列的最新更新记录
PostgreSQL12 生产线关联表ID迭代版本查询逻辑
业务背景与编码规则
当前数据表关联1号线、2号线两条业务生产线,ID编码与版本迭代规则固定:
- 初始ID结构:格式为
x21xxxx,首位数x固定为1(对应1号线)或2(对应2号线),中间两位为固定年份标识21,最后三位为对应生产线内连续递增的流水号(两条生产线流水号独立计数,存在重复可能)。初始状态典型ID样例:
1210001 2210001 1210002 1210003 2210002
- 迭代更新规则:存量记录重新加工生成新版本时,仅对原ID的首位数执行**+2**操作生成新版本ID,单条原始记录最多经历2-3次迭代。迭代后的全量ID样例集合:
1210001 2210001 1210002 1210003 2210002 3210001 -- 由1210001迭代生成 4210001 -- 由2210001迭代生成 5210001 -- 由3210001迭代生成 7210001 -- 由5210001迭代生成
核心查询需求
从全量存储的ID记录中,筛选出每一条ID迭代链路的最终最新版本,剔除所有被迭代替换的旧版本ID。对应上例数据的期望输出结果:
1210002 1210003 2210002 4210001 7210001
查询实现逻辑
迭代过程存在两个固定不变的核心特征,可直接用于匹配同链路记录、区分不同链路:
- 迭代仅修改ID首位数,剩余字段(固定2位年份标识、3位流水号)全程保持不变,可作为链路关联的基础匹配字段。
- 因每次迭代对首位数+2,同一初始记录的首位数奇偶性永久不变:1号线初始首位数为1(奇数),所有迭代版本的首位数始终为奇数(1、3、5、7...);2号线初始首位数为2(偶数),所有迭代版本的首位数始终为偶数(2、4、6、8...)——该特征可用于区分两条独立生产线的同流水号记录,避免分组混同。
基于上述特征,筛选最新版本的逻辑为: - 提取两个组合分组键:一是首位数的奇偶性(区分所属生产线,解决两条线流水号独立重复的问题),二是去掉首位数的剩余ID部分(匹配同一条生产线内的不同迭代版本)。
- 按组合分组键分组,取每组内数值最大的ID,即为该迭代链路的最终最新版本(因迭代仅对首位数+2,数值越大代表版本越新)。
- PostgreSQL12 可直接执行的参考查询:
-- 假设存储全量ID的表名为t_biz_record,ID字段名为record_id(固定7位长度) SELECT MAX(record_id) AS latest_record_id FROM t_biz_record GROUP BY -- 分组键1:首位数奇偶性,区分1号线(奇数)和2号线(偶数)的同流水号记录 SUBSTRING(record_id FROM 1 FOR 1)::INT % 2, -- 分组键2:去掉首位数的剩余部分,匹配同一条生产线内的迭代版本 SUBSTRING(record_id FROM 2) ORDER BY latest_record_id;
逻辑校验:对上例中的ID集合执行上述查询,结果与预期完全匹配:
- 1号线、流水号001链路(首位数奇数,剩余部分'210001')包含1210001、3210001、5210001、7210001,最大值为7210001,即为该链路的最终版本
- 2号线、流水号001链路(首位数偶数,剩余部分'210001')包含2210001、4210001,最大值为4210001,即为该链路的最终版本
- 1号线、流水号002链路(首位数奇数,剩余部分'210002')仅包含1210002,无迭代记录,直接作为最终版本输出
- 1号线、流水号003链路(首位数奇数,剩余部分'210003')仅包含1210003,无迭代记录,直接作为最终版本输出
- 2号线、流水号002链路(首位数偶数,剩余部分'210002')仅包含2210002,无迭代记录,直接作为最终版本输出
最终输出结果与需求预期完全一致。
内容的提问来源于stack exchange,提问作者Dani V
相关产品推荐
相关产品推荐

