时间戳

首页 > 解决方案 > 获取没有不同键的两次之间没有信息的行

sql - 获取没有不同键的两次之间没有信息的行

问题描述

我有一张表，其中包含员工在与客户交易期间执行的操作。在执行交易时，他们应该始终遵循一定的流程。

我试图弄清楚如何在员工没有执行正确步骤的情况下捕获交易的开始。

该过程应始终以 Action_Taken = 1 开始，以 Action_Taken = 3 结束，但在这两者之间应该有第三个（或更多）Action_Taken（此 Action_taken 可以等于任何数字，但永远不能再为 1 或 3）。当缺少中间动作时问题就来了，这就是我想要捕捉的。

例如：

Date_time              Employee Action_Taken  Client
2019-10-01 01:21:08.000    1        1           73
2019-10-01 01:30:50.000    1        2           73
2019-10-01 01:46:21.000    1        4           73
2019-10-01 01:52:41.000    1        3           73
2019-10-03 03:24:40.000    2        1           61
2019-10-03 03:53:28.000    2        3           61
2019-10-03 04:29:15.000    2        2           61
2019-10-01 11:43:55.000    3        1           54
2019-10-01 11:47:54.000    3        2           54
2019-10-01 11:52:00.000    3        3           54
2019-10-02 05:36:27.000    1        1           76
2019-10-02 05:59:00.000    1        3           76
2019-10-03 06:18:00.000    2        1           67
2019-10-03 06:25:56.000    2        2           67
2019-10-03 06:34:34.000    2        3           67

我希望得到第 5 行和第 11 行的输出：

Date_time              Employee Action_Taken  Client
2019-10-03 03:24:40.000    2        1           61
2019-10-01 01:21:08.000    1        1           73

这是我已经尝试过的：

select *, concat(convert(date, date_time), Employee, client) AS actionkey
into #Trans_Start
from #Table
where Action_Taken = 1


select *,concat(convert(date, date_time), Employee, client) AS actionkey
into #Trans_Actions
from #test
where Action_Taken = 2

select * 
from #Trans_Start as start
where start.actionkey not in (select act.actionkey from #Trans_actions  as act  where act.actionkey = start.actionkey)

它接近我需要的并且只输出一行：

Date_time              Employee Action_Taken  Client
2019-10-01 01:21:08.000    1        1           73

由于我的操作键的定义方式，我知道它只输出这一行。它丢失是因为员工 2 最终做了一个动作，但它的顺序错误（他们新的错误并试图修复它）。

如果他们最终以不正确的顺序发布操作，我似乎无法弄清楚如何将事务捕获到表中。

任何帮助将不胜感激，这可能是与我所采取的完全不同的方法。

标签： sqlsql-server

解决方案

我不会假设员工/客户元组确定组。在现实生活中，一个特定的员工可能会多次为同一个客户服务。

这是一种仅使用窗口函数而不使用聚合的方法，其工作原理如下：

  1. 构建连续动作组：对于给定的员工/客户，每次满足动作 1 时都会启动一个新组。

  2. 对每个组实施以下控制：

    • 一个组必须包含一个且只有一个动作 3
    • 动作 3 必须是组中的最后一个动作
    • 组中必须至少存在 1 或 3 以外的一项操作

  3. 展示每个未通过检查的组中的第一条记录

询问：

select *
from (
    select 
        t.*,
        sum(case when action_taken = 3 then 1 else 0 end) 
            over(partition by employee, client, grp) cnt_3,
        sum(case when action_taken not in (1, 3) then 1 else 0 end) 
            over(partition by employee, client, grp) cnt_others,
        last_value(action_taken) 
            over(partition by employee, client, grp order by employee) last_action
    from (
        select
            t.*,
            sum(case when action_taken = 1 then 1 else 0 end) 
                over(partition by employee, client order by date_time) grp
        from mytable t
    ) t
) t
where 
    action_taken = 1
    and not (cnt_3 = 1 and last_action = 3 and cnt_others > 0)
order by employee, client, date_time

最内部的子查询进行窗口求和以识别组 ( grp)。下一个外部查询计算组中有多少动作 3，有多少其他动作，并检索最后一个动作。最后，外部查询过滤每个组的第一条记录和检查结果。

在这个 db fiddle与您的示例数据中，这将返回：

日期时间 | 员工 | 采取行动 | 客户 | grp | cnt_3 | cnt_others | 最后一个动作
:----------------- | --------: | ------------: | -----: | --: | ----: | ---------: | ----------:
2019 年 2 月 10 日 05:36:27 | 1 | 1 | 76 | 1 | 1 | 0 | 3
2019 年 3 月 10 日 03:24:40 | 2 | 1 | 61 | 1 | 1 | 1 | 2

请注意，输出中的其他列还为您提供了标记组的原因。第一条记录缺少其他操作，第二条记录具有最后一个操作 2 而不是 3。

推荐阅读