Разница во времени на основе строк в зависимости от условия для Analytics

У меня есть следующие данные в таблице с именем TABLE:

EDIT: добавлена ​​еще пара строк с Characterid: 26052013030101, которая была пропущена.

    /------------------------------------------------------------------------\
    |      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:

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, и если между START и END есть какое-либо другое событие, то нам нужно пропустить это конкретное CharacterID.

В приведенном выше примере см. CharacterID = 01012016170101, есть строка со строкой EVENTTYPE='Player Left' между строками со значениями START и END для EVENTTYPE, которые нужно пропустить или не учитывать.

EDIT: в приведенном выше примере characterid = 26052013030101 имеет только START, но не END. Он имеет RESET, что означает, что мы не должны учитывать это значение при отображении результата. КОНЕЦ РЕДАКТИРОВАНИЯ

Как мы этого добиваемся?

Во-вторых, есть ли простой способ добиться этого в POWERBI и отобразить счет и разницу во времени?


sql-server powerbi tsql powerbi-embedded
person aioracle    schedule 11.01.2017    source источник
comment
Может ли одно и то же CharacterId НАЧАТЬ и КОНЕЦ несколько раз? Или будет только одна запись на CharacterId с каждым значением EVENTTYPE START и END?   -  person 3N1GM4    schedule 11.01.2017
comment
Кроме того, есть ли опечатка в данных вашего примера или действительно должно быть 3 разных значения CharacterId? Похоже, что тот, что показан как 01012013010101, на самом деле должен быть 01012016170101, как две строки под ним?   -  person 3N1GM4    schedule 11.01.2017
comment
И правильно ли, что дата ОКОНЧАНИЯ для CharacterId=11052016190101 является более ранней датой, чем дата НАЧАЛА для того же самого CharacterId? Или эти значения DateTime в формате гггг-дд-мм или что-то в этом роде?   -  person 3N1GM4    schedule 11.01.2017
comment
Ожидаемые/желаемые выходные данные для примера входных данных, которые вы предоставили, были бы полезны, но я попытался ответить ниже, основываясь на некоторых предположениях.   -  person 3N1GM4    schedule 11.01.2017
comment
@ 3N1GM4 CharacterID будет один для каждого начала и конца   -  person aioracle    schedule 11.01.2017
comment
@ 3N1GM4 Я исправил идентификатор персонажа.   -  person aioracle    schedule 11.01.2017

Ответы (2)


arrow_upward
1
arrow_downward

Это найдет каждую запись START независимо от того, сколько раз CharacterID запускает сеанс, а затем находит следующую запись END:

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;

Я немного изменил ваши тестовые данные, чтобы они действительно имели смысл, но, используя данные в приведенном выше скрипте, вывод выглядит следующим образом:

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
person iamdave    schedule 11.01.2017
comment
решение выглядит хорошо, но по какой-то причине, если для одного и того же идентификатора есть START, но нет END, то приведенный выше запрос не дает наилучшего результата. - person aioracle; 12.01.2017
comment
Если самый последний START не имеет END, вы получаете null значение EndTime, которое вы можете заменить на любое значение с помощью функции isnull или исключить из where. Если у вас есть несколько записей START без записи END для одного и того же CharacterID, наверняка у вас есть проблемы с данными, которые необходимо решить перед выполнением этого анализа? - person iamdave; 12.01.2017

arrow_upward
1
arrow_downward

Исходя из некоторых предположений:

  • У вас всегда есть только одна запись на CharacterId для каждого из двух соответствующих значений EVENTTYPE ("START" и "END").
  • Любая запись со значением EVENTTYPE "END" всегда будет иметь более позднее значение даты и времени в TRIGGERTIME, чем запись для того же CharacterId с EVENTTYPE "START".

Вы можете использовать что-то вроде:

SELECT DISTINCT
    c.CharacterId,
    start.TRIGGERTIME AS StartTime,
    [end].TRIGGERTIME AS EndTime,
    DATEDIFF(s, start.TRIGGERTIME, [end].TRIGGERTIME) AS [TimeDiff(seconds)]
FROM [TABLE] c
OUTER APPLY
(
    SELECT TRIGGERTIME
    FROM [TABLE] s
    WHERE s.CharacterId = c.CharacterId
    AND s.EVENTTYPE = 'START'
) start
OUTER APPLY
(
    SELECT TRIGGERTIME
    FROM [TABLE] e
    WHERE e.CharacterId = c.CharacterId
    AND e.EVENTTYPE = 'END'
) [end]

Если вы хотите представить разницу во времени иначе, чем просто количество секунд, это можно обработать отдельно, и на SO есть много других вопросов, которые касаются этого.

Вы также можете переместить OUTER APPLY в подзапросы в предложении SELECT, но таким образом логике будет немного легче следовать ИМХО.

person 3N1GM4    schedule 11.01.2017