SQL排名重复组
问题描述:
我有以下数据按开始日期排序。我想要做的是排名产品名称,如出现例如产品号27675是第1行,并在第一次发现这个产品,因此排名1 ...再次这个产品是第5排排名2.SQL排名重复组
然后我想要选择没有日期和分组,按行,产品和排名的数据。
SECTION_NAME ROW_NAME PRODUCT NAME start_date DESIRED RANK
SEW LS-1-A_1 27675 02/03/2016 14:39 1
SEW LS-1-A_1 26298 06/04/2016 16:56 1
SEW LS-1-A_1 26298 16/05/2016 16:56 1
SEW LS-1-A_1 26298 04/06/2016 09:26 1
SEW LS-1-A_1 27675 16/06/2016 16:22 2
SEW LS-1-A_1 18969 17/07/2016 12:54 1
SEW LS-1-A_1 26298 01/08/2016 10:52 2
SEW LS-1-A_1 27675 29/08/2016 08:38 3
SEW LS-1-A_1 LEVI F17 SMS 12/09/2016 13:39 1
我生产秩为:
SECTION_NAME ROW_NAME PRODUCT NAME start_date RANK
SEW LS-1-A_1 27675 02/03/2016 14:39 1
SEW LS-1-A_1 26298 06/04/2016 16:56 1
SEW LS-1-A_1 26298 16/05/2016 16:56 2
SEW LS-1-A_1 26298 04/06/2016 09:26 3
SEW LS-1-A_1 27675 16/06/2016 16:22 2
SEW LS-1-A_1 18969 17/07/2016 12:54 1
SEW LS-1-A_1 26298 01/08/2016 10:52 4
SEW LS-1-A_1 27675 29/08/2016 08:38 3
SEW LS-1-A_1 LEVI F17 SMS 12/09/2016 13:39 1
SELECT
SECTION_NAME,
ROW_NAME,
PRODUCT_NAME,
start_date,
dense_rank() OVER (PARTITION BY product_name ORDER BY START_DATE)RANK
FROM
TABLES
Order by
SECTION_NAME,ROW_NAME,START_DATE
UPDATE ----
使用以下:
SELECT
SECTION_NAME ,
ROW_NAME,
PRODUCT_NAME [Style],
(START_DATE) as [Start dt],
SUM(isChange) OVER (PARTITION BY SECTION_NAME , ROW_NAME,product_name ORDER BY START_DATE) as rank_
FROM (SELECT SECTION_NAME,
ROW_NAME,
PRODUCT_NAME,
sd.START_DATE,
(CASE WHEN lag(PR.ROW_NAME) over (order by SD.START_DATE) = PR.ROW_NAME
THEN 0
ELSE 1
END) as IsChange
FROM
TABLES_t
) t
Order by SECTION_NAME, ROW_NAME, START_DATE;
产生以下:(在情况下的产品每行都是一样的(10,11,12),我期望它们有相同的排名
答
需要通过校正分区中的顺序。
SELECT
SECTION_NAME ,
ROW_NAME,
PRODUCT_NAME [Style],
(START_DATE) ,
SUM(isChange) OVER (PARTITION BY ROW_NAME,product_name ORDER BY START_DATE) as rank_
,IsChange
FROM (SELECT SECTION_NAME,
ROW_NAME,
PRODUCT_NAME,
sd.START_DATE,
(CASE WHEN lag(product_name) over (order by SECTION_NAME, ROW_NAME, sd.START_DATE) = product_name
THEN 0
ELSE 1
END) as IsChange
FROM
Table_ t
) t
Order by SECTION_NAME, ROW_NAME, START_DATE;
答
这实际上与您的其他question类似。不同的是,计数需要重新开始,每个product_name
。所以,这里是一个方法:
SELECT PS.SECTION_NAME [Planning Group], PR.ROW_NAME as [Planning Row],
P.PRODUCT_NAME [Style], (SD.START_DATE) as [Start dt],
SUM(isChange) OVER (PARTITION BY product_name ORDER BY start_dt) as rank_
FROM (SELECT t.*,
(CASE WHEN lag(row) over (order by start_dt) = row
THEN 0
ELSE 1
END) as IsChange
FROM tables_ t
) t
Order by PS.SECTION_NAME, PR.ROW_NAME, SD.START_DATE;
注意:这个问题在前面的一个,我不明白的SECTION_NAME
和ROW_NAME
的作用。该版本忽略这些列。如果您希望查询重新开始每个组中的编号,请将它们包含在partition by
语句中。
+0
我已经更新了问题的结果 – Chris
编辑期望等级列 – Chris
行2,3,4为什么要有1级? – vtuhtan