нужно найти время начала и окончания событий, которые все еще не активны

#sql #apache-spark-sql #time-series

#sql #apache-spark-sql #временные ряды

Вопрос:

У меня есть таблица с активной меткой времени 3 coulmne eventid

временная метка	eventid	активный
2020-02-01 22:44:23	E1	верно
2020-02-01 22:45:23	E1	false
2020-02-01 22:46:23	E1	верно
2020-02-01 22:47:23	E1	false
2020-02-01 22:44:23	E2	верно
2020-02-01 22:45:23	E2	false
2020-02-01 22:46:23	E2	верно

необходимо найти время начала и окончания событий здесь событие e2 не будет учитываться, поскольку оно все еще активно.

Start_Time	End_Time	eventid
2020-02-01 22:44:23	2020-02-01 22:47:23	E1

Обновление: удалось найти решение с помощью Pyspark см. Мой ответ ниже

1. Отредактируйте свой вопрос и покажите нужные вам результаты. Также пометьте тегом базу данных, которую вы используете.

2. @ Grdon обновил вопрос, касающийся базы данных, я работаю над spark sql, но любой общий sql (любая база данных) будет работать

Ответ №1:

  ;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY eventid ORDER BY timestamp DESC) AS rn
   FROM yourTable
)
SELECT min(yt.timestamp) as Start_Time, max(yt.timestamp) as End_Time,yt.eventid
FROM yourTable yt
inner join cte
On cte.eventid=yt.eventid
WHERE 
cte.rn = 1 and cte.active="false"
group by yt.eventid

Таким образом, вы не увидите E2, потому что он все еще активен.

1. Как возможно, чтобы max (timestamp) возвращал false?

2. тип данных timestamp не является логическим. Таким образом, max(timestamp) вернет временную метку, а не true или false.

3. Я не уверен, следовал ли я ему полностью, но, насколько я понимаю, «cte.rn = 1 и cte.active =»false»» исправит все, что я предполагаю. так что не уверен

4. вы можете попробовать, это будет то, что вы хотите. когда cte.rn =1 и cte.active =»false» означает, что закрыто. Таким образом, только «не активный» результат будет объединен с другой таблицей.

Ответ №2:

Используя функцию Lead, я смог найти завершенные события.

 window = Window.partitionBy('eventid').orderBy('timestamp')
eventWithLead = dataFrame.withColumn('endtime',lead(col('timestamp'), 1).over(window))
                         .withColumn('nextstatus',lead(col('active'), 1).over(window))
                         .select('eventid', 'active','endtime', 'timestamp', 'nextstatus')
   
openEvents = eventWithLead.select('eventId').filter(col('active')==True)
                                            .filter(col('nextstatus').isNull())

errorWithLeadFinal = eventWithLead
                         .join(openEvents, on=['eventid'], how='left_anti')
                         .filter(col('active')!= False)
                         .filter(col('nextstatus')!= True)

Ответ №3:

Это то, что вы хотите?

 select eventid, min(timestamp) as starttime, max(timestamp) as endtime
from t
group by eventid
having max(case when active = 'true' then timestamp else 0 end) = max(timestamp);

Это возвращает события, которые активны в самой последней временной метке.