按年份保留唯一水果组合的SQL实现问题

原数据表 #

name1  name2 year1 year2
 apple   pear  2010  2001
 apple   pear  2011  2002
  pear  apple  2010  2003
  pear  apple  2011  2004
 apple   null  2009  2005
  pear orange  2008  2006
 apple   pear  2010  2007
 apple  grape  2010  2008

需求说明 #

在每个year1分组内，水果组合需保持唯一（例如apple pear 2010与pear apple 2010视为同一组合），仅保留每组重复记录中首次出现的条目（按year2排序的第一条）。预期输出如下：

name1  name2 year1 year2
 apple   pear  2010  2001
 apple   pear  2011  2002
 apple   null  2009  2005
  pear orange  2008  2006
 apple  grape  2010  2008

错误代码分析 #

最初尝试的SQL代码无法正确去重，因为分区条件PARTITION BY name1, name2, year1会将apple pear和pear apple视为不同的分组，导致这两条记录同时被保留：

WITH ranked_names AS (
  SELECT 
    name1,
    name2,
    year1,
    year2,
    ROW_NUMBER() OVER (PARTITION BY name1, name2, year1 ORDER BY year2) AS rn
  FROM 
    fruits
)
SELECT 
  name1,
  name2,
  year1,
  year2
FROM 
  ranked_names
WHERE 
  rn = 1;

修正方案 #

要解决这个问题，核心是让同一水果组合（不管顺序）进入同一个分区。可以通过将两个水果名称按固定规则排序来实现，比如字典序。以下是可行的修正代码：

WITH ranked_names AS (
    SELECT 
        name1,
        name2,
        year1,
        year2,
        ROW_NUMBER() OVER (PARTITION BY year1, 
                                       CASE WHEN name1 < name2 THEN name1 ELSE name2 END, 
                                       CASE WHEN name1 < name2 THEN name2 ELSE name1 END 
                          ORDER BY year2) AS rn
    FROM 
        fruits
)
SELECT 
    name1,
    name2,
    year1,
    year2
FROM 
    ranked_names
WHERE 
    rn = 1;

代码原理 #

• 通过CASE WHEN判断两个水果名称的字典序，将较小的名称作为第一个分区字段，较大的作为第二个分区字段。这样apple pear和pear apple会被分到同一个year1下的同一分区。
• 再用ROW_NUMBER()按year2排序，取每个分区的第一条记录（rn=1），即为该组合首次出现的条目。

关于不使用GREATEST和LEAST的可行性 #

完全可以不使用GREATEST和LEAST函数实现需求，上面的CASE WHEN写法就是典型的替代方案。这种方式更灵活，还能针对性处理NULL值的情况（比如当其中一个名称为NULL时，可以单独定义排序规则，避免数据库对NULL比较的不同处理逻辑）。

内容的提问来源于stack exchange，提问作者stats_noob