SQL Server : Find most popular category of products bought per user for use in subquery

Question

我有三个表:categories(id、name)、products(id、category_id、name))和purchases(id 、用户 ID、产品 ID)。 产品属于类别。用户可以购买许多产品。我的目的是找到每个用户最流行的类别。

但是，我需要使用查询的结果集作为子查询，因此不幸的是，由于 SQL Server 的限制(可怕的 ORDER BY 子句在 View 、内联函数、派生表和子查询中无效，除非还指定了 TOP。 错误)。

我的方法是创建每个用户每个类别的所有购买列表。然后，我有一个 MAX 函数来挑选最大购买量。我将该结果连接到原始查询(复制为子查询)以检索相关的 category_id，最后获取类别名称。

我的查询有两个问题:

1. 显然，我不希望在代码中两次使用相同的查询。但是，我不能依赖使用 CTE 或临时表，因为此查询的结果旨在链接到具有用户数据子集的 View ，并且 VIEW 代码旨在用于第三个-party 包，只能处理基本的 SQL 代码。
2. 如果出现平局(假设用户购买了 4 个产品，每个产品从 2 个类别购买了 2 个)，我最终会得到该用户的重复行。

fiddle :

http://sqlfiddle.com/#!6/8821b/5

如果有人能够帮助我找到一种方法来确保每个用户只返回一行，以及一种删除重复子查询的方法，我将不胜感激。

谢谢!

Answer 1

首先，感谢您提供 SQLFiddle 中的示例。它使帮助变得更加容易。

您可以使用 row_number 来更精确地获取“顶部”记录。在此示例中，我选择使用category_name作为计数之后的辅助排序标准。

SELECT user_id, category_name, category_count
FROM
(
  SELECT 
      user_id, COUNT(1) as category_count, category_name, 
      ROW_NUMBER() OVER (
          PARTITION BY user_id 
          ORDER BY COUNT(1) DESC, category_name ASC) 
          as ordinal_position
  FROM
      purchases p 
          JOIN products p2 ON p.product_id = p2.id
          JOIN categories c ON p2.category_id = c.id        
  GROUP BY user_id, category_name
 ) a
WHERE ordinal_position = 1
ORDER BY category_count DESC

Example at SQL Fiddle.