SQL-select每个组WITH CONDITION的前3个值

I want to pull out top 3 selling products for different product category per tag. Data looks like this:

我想为每个标签提取不同产品类别的前3个销售产品。数据如下所示:

tag  | product_name | product_category | order_count
tag1 | product1     | category1        | 100
tag1 | product2     | category2        | 80
tag1 | product3     | category2        | 60
tag1 | product4     | category3        | 50
......

I know how to pull out top 3 selling products per tag using ROW_NUMBER(), but it will return product1,product2,product3. I don't want product3 because it belongs to the same category as product2. I want product4 instead. How to do this in SQL server?

我知道如何使用ROW_NUMBER()为每个标签提取前3个销售产品,但它将返回product1,product2,product3。我不想要product3,因为它与product2属于同一类别。我想要product4。如何在SQL Server中执行此操作?

3 个解决方案

#1

First ROW_NUMBER removes duplicate rows per tag and product_category, second ROW_NUMBER selects top 3 selling products per tag

第一个ROW_NUMBER删除每个标记和product_category的重复行,第二个ROW_NUMBER选择每个标记的前3个销售产品

;WITH cte AS
 (SELECT *, ROW_NUMBER() OVER(PARTITION BY tag, product_category ORDER BY order_count DESC) AS rn
  FROM yourtable
  ), cte2 AS
  (SELECT *, ROW_NUMBER() OVER(PARTITION BY tag ORDER BY order_count DESC) AS rn2
   FROM cte
   WHERE rn = 1
   )
   SELECT *
   FROM cte2
   WHERE rn2 <= 3

Demo on SQLFiddle

在SQLFiddle上演示

Next one uses a derived table

下一个使用派生表

;WITH cte AS
 (SELECT t2.tag, t2.product_name, t2.product_category, t2.order_count,
         ROW_NUMBER() OVER(PARTITION BY t2.tag ORDER BY order_count DESC) AS rn
  FROM (SELECT tag, product_category, MAX(order_count) AS maxCount
        FROM yourtable
        GROUP BY tag, product_category
        ) t1 JOIN yourtable t2 ON t1.tag = t2.tag 
          AND t1.product_category = t2.product_category
          AND maxCount = order_count
  )
  SELECT *
  FROM cte
  WHERE rn <= 3

Demo on SQLFiddle

在SQLFiddle上演示

#2

You can use RANK() (or ROW_NUMBER()) as long as you use PARTITION BY. This in combination with TOP() should work well assuming you're using SQL Server 2005+:

只要使用PARTITION BY,就可以使用RANK()(或ROW_NUMBER())。假设您正在使用SQL Server 2005+,这与TOP()结合使用应该很好:

with cte as (
  select tag, 
    product_name, 
    product_category, 
    order_count, 
    rank() over (partition by product_category 
                 order by product_category, order_count desc) rnk
  from yourtable
  )
select top 3 tag, product_name, product_category, order_count
from cte 
where rnk = 1
order by order_count desc

• SQL Fiddle Demo

SQL小提琴演示

This will produce the following:

这将产生以下结果:

TAG     PRODUCT_NAME   PRODUCT_CATEGORY  ORDER_COUNT
tag1    product1       category1         100
tag1    product2       category2         80
tag1    product4       category3         50

#3

I would suggest doing it with one select only

我建议只用一个选择

declare @t table (
    tag                 varchar(10),
    product_name        varchar(10),
    product_category    varchar(10),
    order_count         int
);

insert into @t values
('tag1', 'product1', 'category1', 100),
('tag1', 'product2', 'category2', 80 ),
('tag1', 'product3', 'category2', 60 ),
('tag1', 'product4', 'category3', 50 ),
('tag1', 'product5', 'category4', 40 );


select top 3
    *
from
    @t
order by
    row_number() over(partition by product_category order by order_count desc), 
    order_count desc;

SQL Fiddle