sql - 每组最大n个略有不同

Question

我读过这个comment这解释了 greatest-n-per-group 问题及其解决方案。不幸的是，我面临着一种略有不同的方法，而且我未能找到解决方案。

假设我有一个表格，其中包含有关用户的一些基本信息。由于实现，此信息可能会或可能不会重复:

+----+-------------------+----------------+---------------+
| id |     user_name     | user_name_hash |    address    |
+----+-------------------+----------------+---------------+
|  1 | peter_jhones      | 0xFF321345     | Some Av       |
|  2 | sally_whiterspoon | 0x98AB5454     | Certain St    |
|  3 | mark_jackobson    | 0x0102AB32     | Some Av       |
|  4 | mark_jackobson    | 0x0102AB32     | Particular St |
+----+-------------------+----------------+---------------+

如您所见，mark_jackobson 出现了两次，尽管每次出现的地址都不同。

ETL 过程时不时地查询新的 user_name 并获取每个的最新记录。之后，它将 user_name_hash 存储在一个表中以签名它已经导入了特定的 user_name

+----------------+
| user_name_hash |
+----------------+
| 0xFF321345     |
| 0x98AB5454     |
+----------------+

一切都以以下查询开始:

SELECT DISTINCT user_name_hash
FROM my_table
EXCEPT
SELECT user_name_hash
FROM my_hash_table

这样，我就可以从我的表中选择新的散列。由于我需要查询最近出现的哈希值，因此我将其包装为一个子查询:

SELECT MAX(id)
FROM my_table
WHERE user_name_hash IN (
  SELECT DISTINCT user_name_hash
  FROM my_table
  EXCEPT
  SELECT user_name_hash
  FROM my_hash_table)
GROUP BY user_name_hash

完美!使用新用户的 id，我可以按如下方式查询地址:

SELECT
  address,
  user_name_hash
FROM my_table
WHERE Id IN (
  SELECT MAX(id)
  FROM my_table
  WHERE user_name_hash IN (
    SELECT DISTINCT user_name_hash
    FROM my_table
    EXCEPT
    SELECT user_name_hash
    FROM my_hash_table)
  GROUP BY user_name_hash)

从我的角度来看，上述查询有效，但似乎不是最佳选择。阅读此 comment ，我注意到我可以使用连接查询相同的数据。由于我没有写出所需的查询，谁能帮我指出方向？

这是我尝试过的查询，但没有成功。

SELECT
  tb1.address,
  tb1.user_name_hash
FROM my_table tb1
  INNER JOIN my_table tb2
    ON tb1.user_name_hash = tb2.user_name_hash
  LEFT JOIN my_hash_table ht
    ON tb1.user_name_hash = ht.user_name_hash AND tb1.id > tb2.id
WHERE ht.user_name_hash IS NULL;

提前致谢。

编辑 > 我正在使用 PostgreSQL

Answer 1

我相信您正在寻找这样的东西:

SELECT
  address,
  user_name_hash
FROM my_table t1
JOIN (
  SELECT MAX(id) maxid
  FROM my_table t2
  WHERE NOT EXISTS (
    SELECT 1
    FROM my_hash_table t3
    WHERE t2.user_name_hash = t3.user_name_hash
  )
  GROUP BY user_name_hash
) t ON t1.ID = t.maxid

我使用 NOT EXISTS 而不是 EXCEPT 因为它对优化器来说更清晰。