如何用Impala SQL获取各服务器每月最新扫描记录？

这事儿好办！要实现这个需求，核心思路是按服务器+月份分组，然后在每组里筛选出日期最新的那条记录。Impala支持窗口函数，用它来处理这种分组排序取TopN的场景非常顺手，下面给你两种可行的实现方案：

方案一：使用ROW_NUMBER()窗口函数（推荐） #

这种方法代码简洁，逻辑清晰，处理效率也不错。我们先给每条记录按「服务器+月份」分区，然后在分区内按日期倒序排号，最新的记录会被标记为行号1，最后筛选出行号为1的记录即可。

假设你的表名为server_scans，字段分别是data（日期字符串，格式MM/DD/YYYY）、server（服务器名称）、size（记录大小），对应的SQL如下：

WITH ranked_scans AS (
    SELECT
        data,
        server,
        size,
        -- 按服务器和月份分区，分区内按日期降序排序，最新记录行号为1
        ROW_NUMBER() OVER (
            PARTITION BY 
                server, 
                DATE_TRUNC('month', TO_DATE(data, 'MM/DD/YYYY'))
            ORDER BY TO_DATE(data, 'MM/DD/YYYY') DESC
        ) AS record_rank
    FROM server_scans
)
-- 筛选出每个服务器每月的最新记录
SELECT data, server, size
FROM ranked_scans
WHERE record_rank = 1
ORDER BY server, data;

代码解释： #

• TO_DATE(data, 'MM/DD/YYYY')：把字符串格式的日期转换为Impala可识别的日期类型，确保日期排序的准确性
• DATE_TRUNC('month', ...)：将日期截取到月份维度，用来作为分组的依据（比如2017-11-04和2017-11-25会被归为同一个11月分组）
• ROW_NUMBER() OVER (...)：给每个「服务器-月份」分组内的记录按日期倒序分配行号，最新的记录行号为1
• 最后通过WHERE record_rank = 1筛选出目标记录

如果你的data字段本身就是Impala的日期类型（而非字符串），可以去掉TO_DATE()转换，直接使用data即可。

方案二：子查询关联（兼容旧版本Impala） #

如果你的Impala版本不支持窗口函数（虽然现在大部分版本都支持了），可以用子查询先找到每个服务器每月的最大日期，再关联原表获取对应记录：

SELECT s.data, s.server, s.size
FROM server_scans s
INNER JOIN (
    -- 先找出每个服务器每月的最新日期
    SELECT
        server,
        DATE_TRUNC('month', TO_DATE(data, 'MM/DD/YYYY')) AS month_group,
        MAX(TO_DATE(data, 'MM/DD/YYYY')) AS latest_scan_date
    FROM server_scans
    GROUP BY server, DATE_TRUNC('month', TO_DATE(data, 'MM/DD/YYYY'))
) t 
ON s.server = t.server 
AND TO_DATE(s.data, 'MM/DD/YYYY') = t.latest_scan_date
ORDER BY s.server, s.data;

注意事项： #

• 如果同一个服务器在同一个月的同一天有多条扫描记录，方案一的ROW_NUMBER()会随机保留其中一条；如果需要保留所有同日期的记录，可以换成RANK()或DENSE_RANK()函数
• 确保日期格式转换的参数和你的实际数据格式一致，比如如果你的日期是DD/MM/YYYY，要把TO_DATE的格式参数改成'DD/MM/YYYY'

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