每个组/分区的MySQL密集等级 [英] MySQL dense rank for each group/partition

问题描述

我正在尝试构建一种密集等级的功能，但不完全是密集等级.我想对每个组进行排名，但是在每个组中，我想对相同的值保持相同的排名.我编写了以下查询，为我提供了一种密集等级的功能

I am trying to build a dense rank kind of functionality but not exactly dense rank. I want to rank each group but within each group I want to keep the rank same for same values. I've written the following query which gives me a dense rank kind of functionality

SELECT  id, item_code
    @curRank := @curRank + 1 AS rank
FROM table t, (SELECT @curRank := 0) r
WHERE <several filters>

我想要这个

我找到的最接近的问题是这个 MySQL为每个组赋予一个等级

The closest question I found is this one MySQL give a rank to each group

请帮助.

推荐答案

使用变量的正确方法是:

The correct method using variables is:

SELECT id,item_code,
       (@rn := if(@id = id,
                  if(@ic = item_code, @rn,  -- do not increment
                     if(@ic := item_code, @rn + 1, @rn + 1)
                    ),
                  if(@id := id,
                     if(@ic := @item_code, 1, 1                        
                       ), 1
                     if(@ic := @item_code, 1, 1                        
                       )
                    )
                 )                         
       ) as dense_rank
FROM table t CROSS JOIN
     (SELECT @rn := 0, @id := '', @ic := '') params
WHERE <several filters>
ORDER BY item_code;

两件事:

• 在一个表达式中分配变量并在另一个表达式中使用它可能会导致问题.您需要在单个表达式中分配和使用变量.MySQL不保证表达式的求值顺序.
• ORDER BY 非常重要.

您还可以使用相关子查询来表达这一点:

You can also express this using a correlated subquery:

SELECT id, item_code
        (SELECT COUNT(DISTINCT item_code)
         FROM table t2
         WHERE . . . AND
               t2.item_code <= t.item_code
        ) as rank
FROM table t
WHERE <several filters>

这篇关于每个组/分区的MySQL密集等级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！