Hive中基于左外连接/WHERE EXISTS的多层嵌套子查询实现问询

我来帮你把这个多层嵌套的Hive SQL补全并梳理清楚逻辑，完全贴合你需求的三层筛选：

完整实现SQL #

SELECT 
    U.session_id, 
    U.session_date, 
    U.email 
FROM data.usage U 
LEFT OUTER JOIN (
    -- 第二步：获取符合条件的session_id列表
    SELECT DISTINCT M.session_id 
    FROM data.usage M 
    WHERE M.email LIKE '%gmail.com%' 
      AND M.data_date >= '20180101' 
      AND M.name IN (
          -- 第一步：筛选角色为Person的用户小写名称
          SELECT lower(name) 
          FROM data.users 
          WHERE role = 'Person'
      )
) AS filtered_sessions 
ON U.session_id = filtered_sessions.session_id;

逻辑拆解 #

• 最内层子查询：从data.users中精准筛选角色为Person的用户，通过lower(name)统一名称的大小写格式，确保后续匹配不会因为大小写差异出错。
• 中间层子查询：基于data.usage表，先过滤出邮箱包含gmail.com、数据日期在2018年1月1日及之后的记录，再关联内层得到的用户名称列表，最后用DISTINCT去重得到唯一的session_id集合。
• 最外层查询：将原始的data.usage表和筛选后的session_id集合做左连接，这样会保留原始表的所有记录（如果某条记录的session_id不在筛选集合中，关联字段会显示为NULL）。

可选简化写法（按需选择） #

如果你只需要保留符合条件的session记录，不需要原始表的全部数据，可以改用IN子查询的写法，更简洁直观：

SELECT 
    session_id, 
    session_date, 
    email 
FROM data.usage 
WHERE session_id IN (
    SELECT DISTINCT session_id 
    FROM data.usage 
    WHERE email LIKE '%gmail.com%' 
      AND data_date >= '20180101' 
      AND name IN (
          SELECT lower(name) 
          FROM data.users 
          WHERE role = 'Person'
      )
);

注意事项 #

• 确认data.users表的role字段值和你要筛选的Person大小写一致（Hive默认大小写敏感），如果需要不区分大小写，可以用lower(role) = 'person'。
• 如果data.usage表的name字段本身已经是小写，内层的lower(name)可以省略，但保留的话兼容性更强。

内容的提问来源于stack exchange，提问作者Matt W.