Rank() 结束了多个分区

Question

我试图找出工单更改所有者组的次数。我正在使用这个查询：

SELECT        
TICKETID, CHANGEDATE, OWNERGROUP, 
RANK() OVER (Partition by TICKETID, OWNERGROUP ORDER BY CHANGEDATE) rk
FROM TKSTATUS where TICKETID= '12345' order by CHANGEDATE

并得到这个结果集：在此处输入图像描述

在我数了1...并得到4之后。

我期待这个结果，但找不到我该怎么做：

RK
1
1
2
1
2
3
1
1
1
2
3

结果为 6。

我知道这不是这样做的方法，因为它将TICKETID分组，并在排名之前OWNERGROUP。

我会继续找到一种方法来做到这一点，但我想看看是否有人有一些想法。

Answer 1

> 在 SQL Server 2012 中，您可以使用lag()通过将当前行值与上一个行值进行比较来查看OwnerGroup更改的OwnerGroup数。

select 
    TicketId
  , ChangeDate
  , OwnerGroup
  , OwnerGroupChange = case 
      when coalesce(OwnerGroup,'') 
          = lag(coalesce(OwnerGroup,'')) over (partition by TicketId order by ChangeDate)
        then 0 
    else 1 
    end
from TkStatus
where TicketId = '12345'
order by ChangeDate

---

要只获取更改数，您可以将上述内容用作子查询或公用表表达式：

select 
    TicketId
  , OwnerGroupChange = sum(OwnerGroupChange) 
from (
  select 
      TicketId
    , ChangeDate
    , OwnerGroup
    , OwnerGroupChange = case 
        when coalesce(OwnerGroup,'') 
            = lag(coalesce(OwnerGroup,'')) over (partition by TicketId order by ChangeDate)
          then 0 
      else 1 
      end
  from TkStatus
  where TicketId = '12345'
  ) s

---

在 SQL Server 2012 之前，您可以使用 outer apply() 来获取以前的OwnerGroup，如下所示：

select 
    t.TicketId
  , t.ChangeDate
  , t.OwnerGroup
  , OwnerGroupChange = case 
      when coalesce(t.OwnerGroup,'') = coalesce(x.OwnerGroup,'')
        then 0 
    else 1 
    end
from TkStatus t
  outer apply (
    select top 1
      i.OwnerGroup
    from TkStatus i
    where i.TicketId = t.TicketId
      and i.ChangeDate < t.ChangeDate
    order by i.ChangeDate desc
    ) x
where t.TicketId = '12345'
order by t.ChangeDate

Answer 2

    SELECT        
    TICKETID, CHANGEDATE, OWNERGROUP, 
    DENSE_RANK() OVER (Partition by TICKETID ORDER BY CHANGEDATE) as RK
    FROM TKSTATUS where TICKETID= '12345' order by CHANGEDATE

Answer 3

你可以像这样使用CTE和LAG函数：

WITH CTE_Tickets as 
(  SELECT        
        TICKETID, CHANGEDATE, OWNERGROUP, 
        LAG(OWNERGROUP, 1) OVER (Partition by OWNERGROUP ORDER BY CHANGEDATE, OWNERGROUP ) as Prev
        FROM TKSTATUS where TICKETID= '12345')

    SELECT        
       TICKETID, CHANGEDATE, OWNERGROUP, Prev ,
       IIF(OWNERGROUP=Prev, 0, 1)  as changestatus
       FROM CTE_Tickets
       ORDER BY CHANGEDATE  , OWNERGROUP