略有不同，每组最大的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，并获取每个ETL的最新记录。之后，它将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，因为它对优化器来说更清楚。

Answer 2

您可以使用左外部联接(获取尚未导入的最新记录)来获得更好的性能，然后计算这些记录的最大id ( HAVING子句中的子查询)。

SELECT  t1.address,
        t1.user_name_hash,
        MAX(id) AS maxid
FROM    my_table t1
LEFT JOIN my_hash_table th ON t1.user_name_hash = th.user_name_hash
WHERE th.user_name_hash IS NULL
GROUP BY t1.address,
         t1.user_name_hash
HAVING   MAX(id) = (SELECT MAX(id)
                    FROM my_table t1)