sql - 查找不同时间间隔的票号的循环时间

Question

TicketNo    ActionDate                  OldStatus    NewStatus      CycleTime/Sec  
1001        2014-02-14 10:17:05.000     Assigned     InProgress     -
1001        2014-03-05 02:03:44.000     InProgress   Reply          1611999
1001        2014-03-11 10:00:14.000     Reply        Resolved       546990
1002        2015-03-20 04:44:14.000     InProgress   Reply          -
1002        2015-03-21 05:40:02.000     Reply        Resolved       89748

我必须为工单状态的每次变化计算周期时间。

在上面的示例中，我试图计算工单从旧状态路由到新状态时从操作日期算起的秒数。

我尝试使用排名功能，但没有得到想要的输出。

select * ,row_number() over (partition by a.ticketno, a.oldstatus order by a.actiondate) rn

从票务

如果有人能提出一些如何解决这个计算的想法，我将不胜感激。

标签： sqlsql-servertsqlsql-server-2008ranking-functions

Answer 1

我采用了 Sean 的出色解决方案并对其进行了调整以避免排序。我正在使用一个临时表，以便我可以从我即将发布的执行计划中排除临时变量创建/填充。

-- Temp table with sample data
IF OBJECT_ID('tempdb..#Something') IS NOT NULL DROP TABLE #Something;
CREATE TABLE #Something
(    TicketNo INT
    , ActionDate DATETIME
    , OldStatus  VARCHAR(50)
    , NewStatus  VARCHAR(50)
);
INSERT #Something VALUES
  (1001, '2014-02-14 10:17:05.000', 'Assigned', 'InProgress')
, (1001, '2014-03-05 02:03:44.000', 'InProgress', 'Reply')
, (1001, '2014-03-11 10:00:14.000', 'Reply', 'Resolved')
, (1002, '2015-03-20 04:44:14.000', 'InProgress', 'Reply')
, (1002, '2015-03-21 05:40:02.000', 'Reply', 'Resolved')

-- TOP (1) Solution
SELECT s.TicketNo, s.ActionDate, s.OldStatus, s.NewStatus,
       CycleTimeSeconds = DATEDIFF(SECOND, MyLag.ActionDate, s.ActionDate)
FROM   #Something AS s
OUTER APPLY
(
    SELECT TOP (1) ActionDate
    FROM     #Something s2 
    WHERE    s2.TicketNo   = s.TicketNo 
    AND      s2.ActionDate < s.ActionDate
    ORDER BY s2.ActionDate DESC
) AS MyLag;

-- Using MAX instead of TOP (1) to avoid a DESC sort operation
SELECT s.TicketNo, s.ActionDate, s.OldStatus, s.NewStatus,
       CycleTimeSeconds = DATEDIFF(SECOND, MyLag.ActionDate, s.ActionDate)
FROM #Something AS s
CROSS APPLY
(
    SELECT ActionDate = MAX(ActionDate)
    FROM   #Something s2 
    WHERE  s2.TicketNo   = s.TicketNo 
    AND    s2.ActionDate < s.ActionDate 
) AS MyLag;

由于子查询仅评估一列，我们可以利用不带GROUP BY. 因为我使用的是聚合 (MAX)，所以我总是会返回一行，这就是我将 OUTER APPLY 更改为 CROSS APPLY 的原因。OUTER APPLY 没有任何问题，但更改它会从这个执行计划中删除标量运算符 - 没有性能提升，只是一个更清晰的执行计划。

这种方法还有一个非常大的额外好处：它不仅避免了排序，而且还避免了DESCending排序。如果ActionDate优化器上有索引，则可以通过执行 *ordered-backward 扫描来利用它来避免 DESCending 排序。将 ActionDate 上的 UNIQUE 约束添加到原始临时变量（我不建议这样做，但它适用于本示例）运行TOP (1)按 ActionDate DESC 排序的查询。

declare @Something table
(
    TicketNo int
    , ActionDate datetime UNIQUE
    , OldStatus varchar(50)
    , NewStatus varchar(50)
)

insert @Something values
  (1001, '2014-02-14 10:17:05.000', 'Assigned', 'InProgress')
, (1001, '2014-03-05 02:03:44.000', 'InProgress', 'Reply')
, (1001, '2014-03-11 10:00:14.000', 'Reply', 'Resolved')
, (1002, '2015-03-20 04:44:14.000', 'InProgress', 'Reply')
, (1002, '2015-03-21 05:40:02.000', 'Reply', 'Resolved')

SELECT TOP (1) ActionDate
FROM     @Something AS s2 
ORDER BY s2.ActionDate DESC;

注意向后扫描：

向后扫描会杀死并行性。Itzik Ben-Gan 在这里讨论：避免使用降序排序。

有序前向扫描和有序后向扫描之间的一个区别是前者可以潜在地使用并行性，而后者目前在存储引擎中没有实现并行性

如果需要， APPLY 允许我们在优化器的后袋中进行并行处理的无排序操作。只是更多的证据表明APPLY 很棒......不需要任何进一步的证据;）