sql - 如何为每个 Y 找到最相关的 X？

Question

我有一个可以运行的查询，它会生成如下行:

 ID | category | property_A | property_B
----+----------+------------+------------
  1 |        X |       tall |        old
  2 |        X |      short |        old
  3 |        X |       tall |        old
  4 |        X |      short |      young
  5 |        Y |      short |        old
  6 |        Y |      short |        old
  7 |        Y |       tall |        old

我想为每个 category 和 property_B 找到最常见的 property_A 是什么，并将其放入另一个表中某处供以后使用。所以在这里我想知道，在 X 类中，老年人倾向于高而年轻人矮，而在 Y 类中，老年人倾向于矮。

每列的域是有限的，而且不是太大 - 大约有 200 个类别，以及十几个 property_A 和 property_B。所以我可以在我的客户端上写一个愚蠢的脚本，它在有限的查询中查询数据库 200 * 12 * 12 次，但这似乎一定是错误的方法，而且考虑到生成这个表的成本很高而且很浪费然后扔掉大部分。

但我什至不知道要查找哪些词才能找到正确的方法:“sql find correlated rows”展示了如何查找整数相关性，但我对整数不感兴趣。那么我该怎么做呢？

Answer 1

您可以使用聚合和窗口/分析函数轻松完成此操作。你想要按计数排名第一的。以下返回最受欢迎的 A:

select category, property_b, property_a as MostPopularA
from (select category, property_b, property_a, count(*) as cnt,
             row_number() over (partition by category, property_b order by count(*) desc) as seqnum
      from table t
      group by category, property_b, property_a
     ) t
where seqnum = 1;

如果您想在出现平局时获取所有值，请使用 dense_rank() 而不是 row_number()。