如何用SQL基于表中MMM-YY格式日期列提取最新日期前6个月的数据

实现思路 #

核心逻辑分两步：

1. 将Col列的MMM-YY格式字符串转换为标准日期类型，默认补每月1号作为日期的日部分（因为不涉及日维度的筛选）
2. 先查询出Col列对应的最大日期，再筛选所有日期 >= 最大日期往前推5个月的记录（包含最大月份本身，刚好覆盖近6个月范围）

不同数据库的实现语句 #

注意要和你实际连接的数据库类型匹配使用，以下均默认数据库语言为英文，可以正常识别DEC/NOV这类英文月份缩写。

MySQL #

SELECT * 
FROM `table`
WHERE STR_TO_DATE(CONCAT('01-', Col), '%d-%b-%y') >= 
    (SELECT DATE_SUB(MAX(STR_TO_DATE(CONCAT('01-', Col), '%d-%b-%y')), INTERVAL 5 MONTH) FROM `table`);

PostgreSQL #

SELECT *
FROM "table"
WHERE TO_DATE(Col, 'Mon-YY') >= 
    (SELECT TO_DATE(MAX(Col), 'Mon-YY') - INTERVAL '5 months' FROM "table");

SQL Server #

SELECT *
FROM [table]
WHERE CONVERT(DATE, '01-' + Col, 6) >= 
    (SELECT DATEADD(MONTH, -5, MAX(CONVERT(DATE, '01-' + Col, 6))) FROM [table]);

Oracle #

SELECT *
FROM "TABLE"
WHERE TO_DATE(Col, 'MON-YY', 'NLS_DATE_LANGUAGE = AMERICAN') >= 
    (SELECT ADD_MONTHS(MAX(TO_DATE(Col, 'MON-YY', 'NLS_DATE_LANGUAGE = AMERICAN')), -5) FROM "TABLE");

注意事项 #

• table是SQL关键字，上述语句中用对应数据库的转义符包裹，如果你实际的表名不是table可以直接替换，不需要转义
• 该逻辑所有计算都在数据库侧完成，不需要拉取全量数据到R的DataFrame再处理，数据量大的时候效率优势明显
• 如果数据库默认语言不是英文，需要在日期转换时额外指定英文语言参数，避免月份缩写解析失败

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