基于分析条件的基于行的时差 [英] Row based time difference based on condition for Analytics

问题描述

我在名为 TABLE 的表中具有以下数据：



编辑：添加了另外几行带有 Characterid：26052013030101 的行，该行被忽略了。

I have the following data in a table called TABLE:

EDIT: Added another couple rows with Characterid: 26052013030101, which was missed.

    /------------------------------------------------------------------------\
    |      CharacterID     |     EVENTTYPE     |        TRIGGERTIME          |
    |----------------------+-------------------+-----------------------------|
    |    11052016190101    |    START          |   2017-06-01 13:35:38.000   |
    |    11052016190101    |    END            |   2017-01-06 08:05:18.620   |
    |    01012016170101    |    START          |   2017-06-01 13:33:18.000   |
    |    01012016170101    |    Player Left    |   2017-06-01 13:35:21.000   |
    |    01012016170101    |    END            |   2017-06-01 13:38:22.000   |
    |    26052013030101    |    START          |   2017-06-01 13:35:39.000   |
    |    26052013030101    |    RESET          |   2017-06-01 13:35:50.000   |
    \------------------------------------------------------------------------/

我已编写此查询来获取基于 START 和 END 值的时差> EVENTTYPE ：

I have written this query to to get the time difference based on START and END values of EVENTTYPE:

SELECT
    cp_start.characterid,
    MAX(cp_start.triggertime) AS start_time,
    cp_end.triggertime AS end_time,
    datediff(second, MAX(cp_start.triggertime), cp_end.triggertime)
FROM
    TABLE AS cp_start
INNER JOIN
    TABLE AS cp_end ON (
        cp_start.CharacterID= cp_end.CharacterID
    AND
        cp_end.triggertime > cp_start.triggertime)
WHERE cp_start.eventtype = 'START'
AND cp_end.eventtype = 'END'
GROUP BY cp_start.characterid, cp_Start.TriggerTime, cp_end.TriggerTime

但是，我们想要得到上述条件的时差-即 START 和 END -一个nd如果在 START 和 END 之间还有其他事件，那么我们需要跳过特定的 CharacterID 。

However, what we want is to get the time difference for the above condition - i.e. START and END - and if there is any other event between START and END then we need to skip that particular CharacterID.

在上面的示例中，看到 CharacterID = 01012016170101 在 START 和 END EVENTTYPE ='Player Left'行 EVENTTYPE 的c $ c>值， 需要跳过或不被考虑 。

In the above example see CharacterID = 01012016170101, there is a row with EVENTTYPE='Player Left' row between the rows with START and END values for EVENTTYPE which need to be skipped or not to be considered.

编辑：在上面，characterid = 26052013030101，只有START，没有END。它具有RESET，这意味着我们在显示结果时不应考虑该值。
编辑结束

EDIT: In the above, characterid = 26052013030101, has only START but no END. It has RESET, meaning we should not consider this value while displaying the result. END OF EDIT

我们如何实现这一目标？

How do we achieve this?

第二，有没有简单的方法要在POWERBI中实现此功能并显示计数和时间差？

Secondly, Is there any easy method to achieve this in POWERBI and display the count and time difference?

推荐答案

这将找到每个 START 记录，无论CharacterID启动会话多少次，然后找到以下 END 记录：

This will find every START record regardless of how many times a CharacterID starts a session and then finds the following END record:

declare @t table(CharacterID bigint,EVENTTYPE nvarchar(100),TRIGGERTIME datetime);
insert into @t values
 (11052016190101,'START','2017-01-01 13:35:38.000')
,(11052016190101,'END','2017-01-06 08:05:18.620')
,(01012013010101,'START','2017-06-01 13:33:18.000')
,(01012013010101,'Player Left','2017-06-01 13:35:21.000')
,(01012013010101,'END','2017-06-01 13:38:22.000')
,(01012013010101,'START','2017-07-01 13:33:18.000')
,(01012013010101,'Player Left','2017-07-01 13:35:21.000')
,(01012013010101,'END','2017-07-01 13:38:22.000');

with Starts as
(
    select CharacterID
            ,EVENTTYPE
            ,TRIGGERTIME
    from @t
    where EVENTTYPE = 'START'
)
select s.CharacterID
        ,s.TRIGGERTIME as StartTime
        ,e.TRIGGERTIME as EndTime
from Starts s
    outer apply (select top 1 TRIGGERTIME
                    from @t
                    where CharacterID = s.CharacterID
                        and TRIGGERTIME > s.TRIGGERTIME
                        and EVENTTYPE = 'END'
                    order by TRIGGERTIME
                ) e
order by CharacterID
        ,StartTime;

我对您的测试数据进行了些微更改，使它确实有意义，但是使用了脚本中的数据上面的输出如下：

I have slightly changed your test data so that it actually makes sense, but using the data in the script above, the output is as follows:

CharacterID    | StartTime               | EndTime
---------------+-------------------------+------------------------
1012013010101  | 2017-06-01 13:33:18.000 | 2017-06-01 13:38:22.000
1012013010101  | 2017-07-01 13:33:18.000 | 2017-07-01 13:38:22.000
11052016190101 | 2017-01-01 13:35:38.000 | 2017-01-06 08:05:18.620

这篇关于基于分析条件的基于行的时差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！