代码之家 › 专栏 › 技术社区 › ilija veselica

SQL-对不匹配的行应用相同的ROW_NUMBER

row-number sql

ilija veselica · 技术社区 · 9 年前

我使用此查询计算特定日期范围的工作日数:

WITH cte AS (
 SELECT [Date] AS WorkingDay,
 ROW_NUMBER() OVER (ORDER BY [Date] ASC) AS RN
 FROM DimDate
 WHERE IsHolidayUSA = 0
 AND IsWeekday = 1
)
SELECT
 DateStarted,
 DateCompleted,
 c2.RN - c1.RN AS CycleTime
FROM MyTable t
INNER JOIN cte c1
 ON t.DateStarted=c1.WorkingDay
INNER JOIN cte c2
 ON t.DateCompleted=c2.WorkingDay

如果DateStartedand和DateCompleted都是工作日,则此操作正常。如果其中一个为空,则结果也为空:

因此,建议将下一个工作日row_number应用于周末/假日日期。例如:

Date        RN
2015-02-23  1 -- Mon
2015-02-24  2 -- Tue
2015-02-25  3 -- Wed
2015-02-26  4 -- Thu
2015-02-27  5 -- Fri
2015-02-28  6 -- Sat (applied row number of next business day) 
2015-03-01  6 -- Sun (applied row number of next business day)
2015-03-02  6 -- Mon
2015-03-03  7 -- Tue
2015-03-04  8 -- Wed
2015-03-05  9 -- Thu

编辑:

提取ROW_NUMBER查询并指向需要处理的部分:

select Date as WorkingDay,

RN = 
    CASE WHEN IsHolidayUSA = 0 AND IsWeekday = 1
    THEN ROW_NUMBER() OVER (ORDER BY [Date] ASC)
    ELSE 1 -- need to modify this one
    END 
from DimDate

4 回复 | 直到 9 年前

dotjoe 9 年前

您仍然需要仅在工作日获取row_number(),但诀窍是将所有日期加入到这个工作日cte,并查找下一个工作日的非工作日。(令人困惑)。。。

with dn as (

    select 
        *,
        IsWorkingDay = cast(case when IsHolidayUSA = 0 AND IsWeekday = 1 then 1 else 0 end as bit)
    from DimDate
    where [Date] between '2/23/2015' and '3/5/2015'

), wd as (
    select 
        [Date],
        WorkingDayNum = row_number() OVER (ORDER BY [Date] ASC)
    from dn
    where IsWorkingDay = 1

), d as (

    select 
        dn.[Date], 
        [WorkingDayNum] = coalesce(wd.WorkingDayNum, n.WorkingDayNum)
    from
        dn
        left outer join wd on wd.[Date] = dn.[Date]
        outer apply (
            select top 1 wd.WorkingDayNum 
            from wd 
            where wd.[Date] > dn.[Date]
            order by wd.[Date]
        ) n
)


select * from d order by Date

Hart CO 9 年前

您可以使用 LEAD() 函数来拉动 RN 值,而不是基于假日/工作日字段排除日期,您只需有条件地应用 ROW_NUMBER() 给他们:

;WITH cte AS (SELECT [Date] AS WorkingDay
                    , CASE WHEN IsHolidayUSA <> 0  AND IsWeekday <> 1 THEN NULL
                           ELSE ROW_NUMBER() OVER(PARTITION BY CASE WHEN IsHolidayUSA <> 0  AND IsWeekday <> 1 THEN 1 END ORDER BY [Date])
                           END AS RN
             FROM DimDate
            )
SELECT *,RN = COALESCE(RN,LEAD(RN,1) OVER(ORDER BY WorkingDay) ,LEAD(RN,2) OVER(ORDER BY WorkingDay))
FROM  cte
ORDER BY WorkingDay

你可以添加更多 引线() 如果需要,可容纳3天或4天周末。

下面是一个在非现有表上演示的工作示例:

;WITH cal AS (SELECT CAST('2013-03-01' AS DATE) dt
              UNION  ALL
              SELECT DATEADD(DAY,1,dt)
              FROM cal
              WHERE dt < '2013-03-31')
     ,RN AS (SELECT *,CASE WHEN DATENAME(WEEKDAY,dt) IN ('Saturday','Sunday') THEN NULL
                           ELSE ROW_NUMBER() OVER(PARTITION BY CASE WHEN DATENAME(WEEKDAY,dt) IN ('Saturday','Sunday') THEN 1 END ORDER BY dt)
                           END AS RN
             FROM  cal
             )
SELECT *,RN = COALESCE(RN,LEAD(RN,1) OVER(ORDER BY dt) ,LEAD(RN,2) OVER(ORDER BY dt))
FROM  RN
ORDER BY dt

Gordon Linoff 9 年前

通过累积和,你会非常接近你想要的。类似于:

select Date as WorkingDay,
       SUM(case when IsHolidayUSA = 0 and IsWeekday = 1 then 1 else 0 end) over
              (order by [date] asc) as rn
from DimDate;

问题是,这使得周末和假日的数量等于 以前的 工作日而不是下一天。因此,在某些情况下,通过添加1进行修改:

select Date as WorkingDay,
       (SUM(case when IsHolidayUSA = 0 and IsWeekday = 1 then 1 else 0 end) over
              (order by [date] asc)
        (case when IsHolidayUSA = 0 and IsWeekday = 1 then 0 else 1 end)) as rn
from DimDate;

Joel Coehoorn 9 年前

尝试 DENSE_RANK() 而不是ROW_NUMBER()。