基于PySpark实现按I/X/U优先级筛选最新日期记录的需求

需求说明 #

现有一张百万级记录的表，ID为唯一标识，Type字段仅包含U、X、I三个取值，需按以下规则输出单条记录：

• 若表中存在Type = 'I'的记录，输出该类型中Date最新的条目
• 若不存在Type = 'I'的记录，输出Type = 'X'的最新日期条目
• 若I和X类型均不存在，输出Type = 'U'的最新日期条目

示例数据 #

源表 #

输出结果 #

• 存在I类型时的输出：

• 不存在I类型时的输出：

• I和X均不存在时的输出：

高效SQL解决方案 #

针对百万级数据，优先保证查询性能，避免全表扫描，以下是两种实用方案：

方案一：优先级排序取首行 #

给不同Type分配优先级权重，按优先级降序、日期降序排序后取第一条记录，兼容MySQL、PostgreSQL、SQL Server等主流数据库：

SELECT ID, Type, Value, Date
FROM your_table_name
ORDER BY 
    CASE Type
        WHEN 'I' THEN 3
        WHEN 'X' THEN 2
        WHEN 'U' THEN 1
    END DESC,
    Date DESC
LIMIT 1;

方案二：分步降级查询（性能更优） #

先检查高优先级类型是否存在，存在则直接取该类型最新记录，否则依次降级查询，减少不必要的数据扫描：

-- 尝试获取I类型最新记录
SELECT ID, Type, Value, Date
FROM your_table_name
WHERE Type = 'I'
ORDER BY Date DESC
LIMIT 1

UNION ALL

-- I类型不存在时，获取X类型最新记录
SELECT ID, Type, Value, Date
FROM your_table_name
WHERE Type = 'X'
  AND NOT EXISTS (SELECT 1 FROM your_table_name WHERE Type = 'I')
ORDER BY Date DESC
LIMIT 1

UNION ALL

-- I和X都不存在时，获取U类型最新记录
SELECT ID, Type, Value, Date
FROM your_table_name
WHERE Type = 'U'
  AND NOT EXISTS (SELECT 1 FROM your_table_name WHERE Type IN ('I', 'X'))
ORDER BY Date DESC
LIMIT 1

LIMIT 1;

性能优化建议 #

为百万级表创建复合索引，让数据库快速定位指定Type下的最新日期记录，避免全表扫描：

CREATE INDEX idx_type_date ON your_table_name (Type, Date DESC);

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