DENSE_RANK 根据特定顺序 [英] DENSE_RANK according to particular order

问题描述

我有一个数据表，我想根据排序的日期顺序从第一组名称开始输出名称的dense_rank.例如

Hi I have a table of data I want to output the dense_rank of the names starting from the first group of names according to sorted dates order. e.g.

DROP TABLE MyTable
SELECT * INTO MyTable FROM (
  VALUES ('2015-12-23', 'ccc'),('2015-12-21', 'aaa'),
         ('2015-12-20', 'bbb'),('2015-12-22', 'aaa')
) t (date, name)
SELECT DENSE_RANK() OVER (ORDER BY name) rank, * FROM MyTable ORDER BY date

对于上面的查询，我得到了

For the query above I got

rank    date    name
2   2015-12-20  bbb
1   2015-12-21  aaa
1   2015-12-22  aaa
3   2015-12-23  ccc

您可以看到日期已排序(好)，排名被分配给组中的名称(好)，但排名不是从第一组名称开始的，例如我想要

You can see the dates are sorted (good), ranks are assigned to names in group (good), but the ranks do not start from the first group of name, e.g. I want

rank    date    name
1   2015-12-20  bbb
2   2015-12-21  aaa
2   2015-12-22  aaa
3   2015-12-23  ccc

您将如何更正查询?如果有多个工作答案，将选择最简单/最短的一个作为答案.谢谢.

How would you correct the query? If there are multiple working answers, the simplest/shortest one will be picked as the answer. Thanks.

感谢 @lad2025 和 @GiorgosBetsos 澄清我的问题.排序是直接根据日期和日期在我的情况下是独一无二的.名称可以不连续地重复和出现.所以使用 ('2015-12-24', 'aaa')，输出是

Thanks @lad2025 and @GiorgosBetsos for clarifying my question. Sorting is straightly according to dates and dates are unique in my case. Names can repeat and appear non-consecutively. So with ('2015-12-24', 'aaa'), the output is

rank    date    name
1   2015-12-20  bbb
2   2015-12-21  aaa
2   2015-12-22  aaa
3   2015-12-23  ccc
4   2015-12-24  aaa

推荐答案

你可以使用:

SELECT DENSE_RANK() OVER (ORDER BY minGrpDate),
       [date], name
FROM (
  SELECT MIN([date]) OVER (PARTITION BY grp) AS minGrpDate,
         [date], name
  FROM (       
    SELECT [date], name,
           ROW_NUMBER() OVER (ORDER BY [date])
           -
           ROW_NUMBER() OVER (PARTITION BY name ORDER BY [date]) AS grp
    FROM mytable) AS t ) AS s
ORDER BY Date

说明:

• grp 字段标识具有相同name 的连续记录岛.
• minGrpDate，使用grp计算，是每个岛屿的最小日期.
• 使用 minGrpDate 我们现在可以应用 DENSE_RANK() 来获得所需的排名.

• grp field identifies islands of consecutive records having the same name.
• minGrpDate, which is calculated using grp, is the minimum date of each island.
• Using minGrpDate we can now apply DENSE_RANK() to get required rank.

注意1:上述查询处理name字段中的不连续性，即具有相同名称的非连续字段的情况.

Note1: The above query handles discontinuities in name field, i.e. the case of non-consecutive fields having the same name.

注意2:查询不处理不同name值共享相同date值的情况.

Note2: The query does not handle the case of different name values sharing the same date value.

在这里演示

这篇关于DENSE_RANK 根据特定顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！