如何检查每个唯一ID的列值（按日期）的顺序？

问题描述：

| ID | Date of activity | activity | 
|----|---------------------|----------| 
| 1 | 2016-05-01T13:45:03 | a  | 
| 1 | 2016-05-02T13:45:03 | b  | 
| 1 | 2016-05-03T13:45:03 | a  | 
| 1 | 2016-05-04T13:45:03 | b  | 
| 2 | 2016-05-01T13:45:03 | b  | 
| 2 | 2016-05-02T13:45:03 | b  |

与此表：

| id | Right order | 
|----|-------------| 
| 1 | yes   | 
| 2 | no   |

我如何检查每一个ID，如果活动的顺序是sumiliar这个顺序的例子吗？

a b a b a b ..

当然

我会根据活动日期

我不清楚你问什么？ – ssn

请澄清你的问题。这有点混乱。向我们展示一个输出示例。 –

从您发布的数据来看，这是不可能的。根据定义，表格是一个无序集合，你没有任何东西可以提供一致的顺序。 –

答

在SQL Server 2012+你可以使用common table expression与lag()检查，然后case表达的min()后面，像这样你的逻辑：

;with cte as (
    select * 
     , prev_activity = lag(activity) over (partition by id order by date_of_activity) 
    from t 
) 
select id 
    , right_order = min(case 
     when activity = 'a' and isnull(prev_activity,'b')<>'b' then 'no' 
     when activity = 'b' and isnull(prev_activity,'b')<>'a' then 'no' 
     else 'yes' 
    end) 
from cte 
group by id

rextester演示：http://rextester.com/NQQF78056

回报：

+----+-------------+ 
| id | right_order | 
+----+-------------+ 
| 1 | yes   | 
| 2 | no   | 
+----+-------------+

之前的SQL Server 2012中您可以使用outer apply()获得以前的活动，而不是lag()像这样：

select id 
    , right_order = min(case 
     when activity = 'a' and isnull(prev_activity,'b')<>'b' then 'no' 
     when activity = 'b' and isnull(prev_activity,'b')<>'a' then 'no' 
     else 'yes' 
    end) 
from t 
    outer apply (
    select top 1 prev_activity = i.activity 
    from t as i 
    where i.id = t.id 
     and i.date_of_activity < t.date_of_activity 
    order by i.date_of_activity desc 
    ) x 
group by id

答

编辑 - 允许每个ID模式的变量数

也许另一方法

实施例基于以下逻辑具有解释

Declare @Pat varchar(max)='a b' 
Declare @Cnt int = 2 

Select ID 
     ,RightOrder = case when rtrim(replicate(@Pat+' ',Hits/@Cnt)) = (Select Stuff((Select ' ' +activity From t Where id=A.id order by date_of_activity For XML Path ('')),1,1,'')) then 'Yes' else 'No' end 
From (Select ID,hits=count(*) from t group by id) A

ID RightOrder 
1 Yes 
2 No

答

select id, 
case when sum(flag)=0 and cnt_per_id%2=0 
    and max(case when rnum=1 then activity end) = 'a' 
    and max(case when rnum=2 then activity end) = 'b' 
    and min_activity = 'a' and max_activity = 'b' 
then 'yes' else 'no' end as RightOrder 
from (select t.* 
     ,row_number() over(partition by id order by activitydate) as rnum 
     ,count(*) over(partition by id) as cnt_per_id 
     ,min(activity) over(partition by id) as min_activity 
     ,max(activity) over(partition by id) as max_activity 
     ,case when lag(activity) over(partition by id order by activitydate)=activity then 1 else 0 end as flag 
     from tbl t 
    ) t 
group by id,cnt_per_id,max_activity,min_activity

为rightorder来实现。

检查每个ID的行数是偶数（除去此条件是否存在可以是奇数像a行，B，A的或A，B，A，B，A等）
第一行包含a和第二行b，最小行为为a，最大行为为b。
总和标志（使用lag集）应该是0

如何检查每个唯一ID的列值（按日期）的顺序？

相关推荐