Hive/Presto不支持关联子查询的高效替代方案：查询用户最后观影记录

实现思路 #

• 先通过uid关联两张表，提前过滤出所有用户18岁生日前的观影记录，避免全量扫描tkting表的无效数据
• 利用Hive/Presto都支持的窗口函数row_number()，对同一个uid的观影记录按到访日期倒序排序，排序为1的记录就是该用户18岁生日前最后一次到访记录
• 全程无需使用关联子查询，完全适配现有引擎的语法限制

代码实现 #

WITH filtered_visit AS (
    SELECT
        people.*,
        tickets.date as visit_date,
        -- 同用户按到访日期倒序排序，最新的记录排在第一
        row_number() OVER(PARTITION BY people.uid ORDER BY tickets.date DESC) AS rn
    FROM all_customers people
    INNER JOIN tkting tickets
        ON people.uid = tickets.uid
        -- 提前过滤18岁生日前的记录，减少数据处理量
        AND tickets.date < people.birthday_18
)
SELECT 
    * EXCEPT(rn) -- 排除排序用的rn字段，Presto支持该语法，Hive可手动列出需要的字段替代
FROM filtered_visit
WHERE rn = 1;

注意：你原本的SQL中子查询使用order by lastvisit.date asc limit 1取到的是用户18岁前第一次到访的记录，如果确实需要取第一次到访，把窗口排序规则改为ORDER BY tickets.date ASC即可。

方案优势 #

• 性能损耗低：关联前就通过时间条件过滤掉大部分无效观影记录，不需要全量关联tkting表，数据处理量远小于普通全量关联方案
• 语法兼容性强：完全规避关联子查询的写法，Hive和Presto都可以直接运行
• 可扩展性好：如果后续需要取前N次到访，仅需要修改rn的过滤条件即可，不需要调整整体逻辑

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