数据转换:主键字段重复时,依据某一字段做去重处理
使用背景
在数据转换由系统A表T1,转换到系统B表T2,可能由于业务设计差异,存在转换后T1表2条数据,对应T2唯一索引2条数据。此时需要依据某一字段,例如update字段,取2条中update最大的,作为最终的转换数据。
为了按照T2表唯一索引字段(例如 name,id,type)分组,并从每个组中选择update最大的那一条记录,可以使用SQL的窗口函数(如果数据库支持)或者通过分组后与子查询结合的方式来实现。这里,我将展示两种方法的示例。
方法一:使用窗口函数(推荐)
如果你使用的数据库支持窗口函数(如PostgreSQL, SQL Server, Oracle, MySQL 8.0+等),你可以使用ROW_NUMBER()
或RANK()
函数来实现:
WITH RankedData AS (
SELECT
name, id, type, update
-- 其他你需要的列...
ROW_NUMBER() OVER (
PARTITION BY name, id, type
ORDER BY update DESC
) AS rn
FROM
T1
)
SELECT
name, id, type, update
-- 其他你需要的列...
FROM
RankedData
WHERE
rn = 1;
在这个查询中,ROW_NUMBER()
窗口函数为每个分组内的行分配了一个唯一的序号(从1开始),其中序号的分配是基于update
降序排序的。然后,通过在外层查询中选择rn = 1
的行来只获取每组中update
最大的记录。
方法二:使用分组和子查询
如果你不能使用窗口函数,你可以通过子查询和分组来找到每个分组中update
最大的记录,然后与原始表进行连接来获取完整的记录:
SELECT
t.*
FROM
T1 t
INNER JOIN (
SELECT
name, id, type,
MAX(update) AS max_update
FROM
T1
GROUP BY
name, id, type,
) grouped_t ON t.name = grouped_t.name
AND t.id = grouped_t.id
AND t.type = grouped_t.type
AND t.update = grouped_t.max_update;
在这个查询中,子查询grouped_t
找出了每个分组中update
的最大值。然后,通过INNER JOIN
将原始表T1
与子查询结果连接起来,只选择那些update
等于分组内最大值的记录。