T-SQL :row_number分区不重新启动计数

Question

我使用以下代码来获取我的数据的排名。附上我得到的结果与我需要的结果。我不知道我做错了什么，但任何帮助将不胜感激。

SELECT 
SHIPTO, NAV_PN, WQ_WORKED, CR_TS, ROW_COUNT,
ROW_NUMBER() OVER (PARTITION BY WQ_WORKED ORDER BY CR_TS) AS ROW_COUNT
FROM 
#TABLE

代码结果与所需结果

Answer 1

好的，根据您的图像，您希望按日期排序时计算连续的WQ_WORKED值。 为此，您首先需要建立分组(分区(标准，以便正确计算所需的值。

在这种情况下，您可以使用 Tabibitosan(旅行者(方法来标识系列中的组，方法是首先为记录集中按cr_ts排序的每条记录创建一个row_number(如果需要决胜局，则可能按另一个值排序(，然后从中减去具有相同顺序的row_number， 但按要作为分组依据的值进行分区。

然后，可以将此中间组 ID 与原始分区值一起使用，以计算分析结果。 在这种情况下，您的最终row_count。

使用提供的示例数据(基于所附图像和下面评论中的扩展数据(：

Drop Table #TABLE;
with DTA(SHIPTO, NAV_PN, WQ_WORKED, CR_TS) as (
select 7703556, '1817953C1', 1, cast('6/1/2018' as date) union all
select 7703556, '1817953C1', 1, cast('6/4/2018' as date) union all
select 7703556, '1817953C1', 0, cast('6/5/2018' as date) union all
select 7703556, '1817953C1', 0, cast('6/6/2018' as date) union all
select 7703556, '1817953C1', 1, cast('6/7/2018' as date) union all
select 7703556, '1817953C1', 0, cast('6/8/2018' as date) union all
--Expanded dataset below here
select 7703047, '1648518C1', 1, cast('6/12/2018' as date) union all --0 1 1
select 7703047, '1648518C1', 1, cast('6/13/2018' as date) union all --2 4 2
select 7703047, '2010358C1', 1, cast('6/12/2018' as date) union all --0 2 1
select 7703047, '2010358C1', 1, cast('6/13/2018' as date) union all --2 3 2
select 7813059, '1648518C1', 0, cast('6/12/2018' as date) union all --2 1 1
select 7813059, '1648518C1', 1, cast('6/13/2018' as date) union all --2 2 1
select 7813059, '2010358C1', 0, cast('6/12/2018' as date) union all --2 2 1
select 7813059, '2010358C1', 1, cast('6/13/2018' as date)           --2 1 1
)
select SHIPTO, NAV_PN, WQ_WORKED, CR_TS
into #TABLE
from dta;

以下查询返回预期结果(针对原始数据(：

with tabi as (
select dta.*
, row_number() over (order by cr_ts)
- row_number() over (partition by wq_worked order by cr_ts) grp
from #TABLE dta
where shipto = 7703556
)
select SHIPTO, NAV_PN, WQ_WORKED, CR_TS
, row_number() over (partition by wq_worked, grp order by cr_ts) ROW_COUNT
from tabi
order by cr_ts

结果：

SHIPTO  NAV_PN      WQ_WORKED   CR_TS       ROW_COUNT
7703556 1817953C1   1           2018-06-01  1
7703556 1817953C1   1           2018-06-04  2
7703556 1817953C1   0           2018-06-05  1
7703556 1817953C1   0           2018-06-06  2
7703556 1817953C1   1           2018-06-07  1
7703556 1817953C1   0           2018-06-08  1

使用下面注释中提供的扩展数据集，您需要向各种分析函数添加其他分区条件：

with tabi as (
select dta.*
, row_number() over (partition by SHIPTO, NAV_PN order by cr_ts)
- row_number() over (partition by SHIPTO, NAV_PN, WQ_WORKED order by cr_ts) grp
from #TABLE dta
)
select tabi.*
, row_number() over (partition by SHIPTO, NAV_PN, WQ_WORKED, grp order by cr_ts) ROW_COUNT
from tabi
order by SHIPTO, NAV_PN, WQ_WORKED, cr_ts