如何在Athena(Presto)中关联两表且仅取元数据表每月最高日对应行

实现逻辑 #

先从元数据表中筛选每个year、month、id分组下当月day值最大的唯一记录，再将过滤后的元数据与账单表直接关联即可，逻辑可直接封装为视图使用。

推荐查询语句（支持创建视图） #

以下方案使用Presto内置的MAX_BY函数实现，写法简洁执行效率更高，将示例中的metadata_table替换为实际元数据表名、bill_table替换为实际账单表名、bill_with_label替换为你需要的视图名即可：

CREATE VIEW bill_with_label AS
WITH latest_metadata AS (
    SELECT 
        year,
        month,
        id,
        -- 取同组内day最大时对应的label1、label2值
        MAX_BY(label1, day) AS label1,
        MAX_BY(label2, day) AS label2
    FROM metadata_table
    GROUP BY year, month, id
)
SELECT 
    b.year,
    b.month,
    b.id,
    b.cost,
    lm.label1,
    lm.label2
FROM bill_table b
-- 若确认所有账单记录都能匹配到元数据，可将LEFT JOIN改为INNER JOIN
LEFT JOIN latest_metadata lm 
    ON b.year = lm.year 
    AND b.month = lm.month 
    AND b.id = lm.id

使用说明 #

视图创建完成后，直接执行SELECT * FROM bill_with_label即可获得符合要求的输出结果，和你给出的示例输出完全一致。

如果你更习惯使用窗口函数实现，也可以使用如下写法，效果完全相同：

CREATE VIEW bill_with_label AS
WITH latest_metadata AS (
    SELECT 
        year,
        month,
        id,
        label1,
        label2
    FROM (
        SELECT 
            year,
            month,
            id,
            label1,
            label2,
            ROW_NUMBER() OVER (PARTITION BY year, month, id ORDER BY day DESC) AS rn
        FROM metadata_table
    ) t
    WHERE rn = 1
)
SELECT 
    b.year,
    b.month,
    b.id,
    b.cost,
    lm.label1,
    lm.label2
FROM bill_table b
LEFT JOIN latest_metadata lm 
    ON b.year = lm.year 
    AND b.month = lm.month 
    AND b.id = lm.id

内容的提问来源于stack exchange，提问作者M. Glatki