如何查询每个客户每月的最新记录？Timestamp无法用substr如何解决

问题核心原因&修正点 #

1. substr是字符串处理函数，无法直接作用于Timestamp类型字段，需要用数据库内置的日期函数先把时间戳转换为年月维度的分组标识
2. 原有SQL的partition by漏加了id、name维度，会导致所有客户同月的记录混排，输出结果不符合"每个客户每个月最新记录"的需求

实现方案（主流数据库通用逻辑） #

核心逻辑是按「客户ID+客户姓名+记录所属年月」分组，每组内按时间倒序取第一条，最后将日期格式化为当月最后一天的YYYYMMDD格式即可。

1. Hive/SparkSQL 实现

SELECT 
    id,
    name,
    number,
    date_format(last_day(`date`), 'yyyyMMdd') AS Date
FROM (
    SELECT 
        t1.id, 
        t1.name, 
        t1.number, 
        t1.`date`,
        ROW_NUMBER() OVER (
            PARTITION BY id, name, date_format(`date`, 'yyyyMM') 
            ORDER BY `date` DESC
        ) AS Rowrank
    FROM tableA t1
) sub 
WHERE Rowrank = 1
ORDER BY id, Date;

2. MySQL 实现

和Hive写法基本一致，仅需注意低版本MySQL如果date_format对Timestamp支持不好，可以先用cast(date as char)转成字符串后再处理。

3. Oracle 实现

SELECT 
    id,
    name,
    number,
    to_char(last_day("DATE"), 'yyyymmdd') AS Date
FROM (
    SELECT 
        t1.id, 
        t1.name, 
        t1.number, 
        t1."DATE",
        ROW_NUMBER() OVER (
            PARTITION BY id, name, trunc("DATE", 'MM') 
            ORDER BY "DATE" DESC
        ) AS Rowrank
    FROM tableA t1
) sub 
WHERE Rowrank = 1
ORDER BY id, Date;

4. PostgreSQL 实现

SELECT 
    id,
    name,
    number,
    to_char((date_trunc('month', date) + INTERVAL '1 month - 1 day')::date, 'yyyymmdd') AS Date
FROM (
    SELECT 
        t1.id, 
        t1.name, 
        t1.number, 
        t1.date,
        ROW_NUMBER() OVER (
            PARTITION BY id, name, date_trunc('month', date) 
            ORDER BY t1.date DESC
        ) AS Rowrank
    FROM tableA t1
) sub 
WHERE Rowrank = 1
ORDER BY id, Date;

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