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

问题描述

我有一个数据表，我想根据排序的日期顺序从第一组名称开始输出名称的density_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 字段标识具有相同名称。


• 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：上面的查询处理名称为的不连续字段，即具有相同名称的非连续字段。

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

注意2：不同的名称值共享相同的日期值的情况。

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

此处演示

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