非顺序 SQL 分区

Question

块引用

都

我正在尝试对一组数据进行分区，但我不希望我的分区是"顺序的"。

传统上，通过按ID分区，然后按销售阶段划分，我得到了这个。顺序分区。

ROW_NUMBER() OVER(PARTITION BY  ID,  sale stage ORDER BY  Converge, modified date desc)
ID               sale stage      modified date      partition
01_AAI_73133    Closed Lost        5/1/2015             1
01_AAI_73133    Closed Lost        4/26/2015            2
01_AAI_73133    Closed Lost        4/20/2015            3
01_AAI_73133    Locked and Loaded  4/5/2015             1
01_AAI_73133    Locked and Loaded  3/29/2015            2
01_AAI_73133    Pitching           3/7/2015             1
01_AAI_73133    Pitching           2/14/2015            2
01_AAI_73133    Pitching           2/1/2015             3
01_AAI_73134    Pitching           1/20/2015            4

我真正想要的是这个

ID               sale stage      modified date      partition
01_AAI_73133    Closed Lost        5/1/2015             1
01_AAI_73133    Closed Lost        4/26/2015            1
01_AAI_73133    Closed Lost        4/20/2015            1
01_AAI_73133    Locked and Loaded  4/5/2015             2
01_AAI_73133    Locked and Loaded  3/29/2015            2
01_AAI_73133    Pitching           3/7/2015             3
01_AAI_73133    Pitching           2/14/2015            3
01_AAI_73133    Pitching           2/1/2015             3
01_AAI_73134    Pitching           1/20/2015            3

有没有人知道如何实现这一目标？

谢谢

Answer 1

您可能正在寻找dense_rank()：

DENSE_RANK() OVER (ORDER BY ID, salestage)

如果您尝试按每个 id 和销售阶段的最大日期排序，则可能需要一个子查询：

select dense_rank() over (order by maxmd, id, salestate)
from (select . . .,
             MAX(ModifiedDate) OVER (PARTITION BY ID, salestage) as maxmd
      from . . .
     ) t;