SQL Server Rank() by group

fhg3lkii 于 2023-06-28 发布在 SQL Server

关注(0)|答案(2)|浏览(127)

I have a data table like this:

Employee1   Product1    ProductGroup1   Quantity    SalesDate
Employee1   Product1    ProductGroup1   Quantity    SalesDate
Employee1   Product2    ProductGroup1   Quantity    SalesDate
Employee1   Product2    ProductGroup1   Quantity    SalesDate
Employee2   Product1    ProductGroup1   Quantity    SalesDate
Employee2   Product1    ProductGroup1   Quantity    SalesDate
Employee2   Product1    ProductGroup1   Quantity    SalesDate
Employee2   Product1    ProductGroup1   Quantity    SalesDate
Employee2   Product2    ProductGroup1   Quantity    SalesDate
Employee2   Product2    ProductGroup1   Quantity    SalesDate

There are multiple employees, multiple products, multiple product groups, multiple sales dates. In Reporting servises i have a matrix, where parent group is employee, child group is Product and column group is sales date. I need to rank products, in order to get first 5 and put other in other list. Problem is, that i have to rank product inside employee group and product can have multiple sales dates, while i need to evalue everything. In SQL now i have: Rank() Over (partition by DataTable.ProductGroup1, DataTable.Employee Order by Sum(Quantity) desc) as Rank But that gives me wrong result, because same product has different rank value, because rank function ranks using quantity in different sales dates. how should i write sql, so it returns me data wilth all sales dates, bus ranks using quantity summed from all dates?

EDIT:
Some datasets to explain what i get and what i need.

//DATA I HAVE
Employee_col    Product_col ProductGroup_col    Quantity_col    SalesDate_col
Employee1       Product1    ProductGroup1       100             2012-01
Employee1       Product1    ProductGroup1       200             2012-02
Employee1       Product2    ProductGroup1       50              2012-01
Employee1       Product2    ProductGroup1       80              2012-02
Employee2       Product1    ProductGroup1       200             2012-01
Employee2       Product1    ProductGroup1       70              2012-02
Employee2       Product2    ProductGroup1       20              2012-01
Employee2       Product2    ProductGroup1       450             2012-02

//RESULT I GET
Employee_col    Product_col ProductGroup_col    Quantity_col    SalesDate_col   Rank_col
Employee1       Product1    ProductGroup1       100             2012-01         2
Employee1       Product1    ProductGroup1       200             2012-02         1
Employee1       Product2    ProductGroup1       50              2012-01         4
Employee1       Product2    ProductGroup1       80              2012-02         3
Employee2       Product1    ProductGroup1       200             2012-01         2
Employee2       Product1    ProductGroup1       70              2012-02         3
Employee2       Product2    ProductGroup1       20              2012-01         4
Employee2       Product2    ProductGroup1       450             2012-02         1

//RESULT I NEED
Employee_col    Product_col ProductGroup_col    Quantity_col    SalesDate_col   Rank_col
Employee1       Product1    ProductGroup1       100             2012-01         1
Employee1       Product1    ProductGroup1       200             2012-02         1
Employee1       Product2    ProductGroup1       50              2012-01         2
Employee1       Product2    ProductGroup1       80              2012-02         2
Employee2       Product1    ProductGroup1       200             2012-01         2
Employee2       Product1    ProductGroup1       70              2012-02         2
Employee2       Product2    ProductGroup1       20              2012-01         1
Employee2       Product2    ProductGroup1       450             2012-02         1

sql-server

来源：https://stackoverflow.com/questions/11518354/sql-server-rank-by-group

2条答案

按热度按时间

6jjcrrmo1#

Try this query

select
#t.*, salesrank
from #t
inner join 
(
     select Employee, Product, RANK() over (partition by employee order by sq desc) as salesrank
     from
     (select Employee, Product , SUM (Quantity) sq from #t group by Employee, product) v
) v 
    on #t.product = v.product
    and #t.Employee =v.Employee

赞(0）回复(0）举报 2023-06-28

nr9pn0ug2#

RANK() over (partition by Employee_col, Product_col, SalesDate_col order by Quantity_col ASC )

赞(0）回复(0）举报 2023-06-28