Блог

У меня есть образец таблицы, в которой есть 2 столбца, идентификатор и дата-время.

 ID          Datetime            
123         12Sep2021 10:00       
123         12Sep2021 10:10
123         12Sep2021 10:25
123         12Sep2021 10:40
123         12Sep2021 10:52
123         12Sep2021 11:20
456         01Oct2021 09:00
456         01Oct2021 09:10
456         01Oct2021 09:40
 

Я хочу создать новую переменную count, которая будет обновляться на основе разницы в дате и времени следующим образом: для первой транзакции идентификатора количество будет равно 1. Он будет увеличиваться до тех пор, пока разница не составит менее 30 минут. Если разница превышает 30 минут, счетчик снова будет установлен на 1 , и для следующих транзакций с этим идентификатором разница будет взята из идентификатора, имеющего количество 1. Результирующий вывод будет следующим:

 ID          Datetime              Count      
123         12Sep2021 10:00        1
123         12Sep2021 10:10        2
123         12Sep2021 10:25        3
123         12Sep2021 10:40        1
123         12Sep2021 10:52        2 
123         12Sep2021 11:20        1
456         01Oct2021 09:00        1
456         01Oct2021 09:10        2
456         01Oct2021 09:40        1
 

Я не уверен, как это можно сделать в Impala SQL, так как для этого потребуется запустить цикл. Я создал тот же код на Python (Pandas), что и ниже, но проблема в том, что Pandas требует перемещения данных в память драйвера, что невозможно из-за большого объема данных. Поэтому я ищу эквивалентный код в SQL. Пожалуйста, помогите с этим запросом (это эквивалентный код Панды):

 d={i:j.Datetime.to_numpy() for i,j in  df.groupby("ID")}

    di=dict()
    
    for id in d.keys():
        n=1
        times=d[id]
        empty_list=list()
        first=times[0]
        for time in times:
            diff=time-first
            if diff >= np.timedelta64(30, 'm'):
                first=time
                n=1
            empty_list.append(n)
            di.update({id:empty_list})
            n=n 1
 

Я не уверен, как запустить такой цикл, есть ли какой-либо другой способ использования подзапросов для достижения результата в Impala SQL?

Вот один из способов сделать это в прямом sql. Я сравниваю значение предыдущего времени с текущим временем, и если оно превышает 30 минут, я начинаю новый пакет с использованием предложения row_number.

Затем я копирую значения для rnk, которые имеют интервал менее 30 минут.

Наконец, я получаю группы данных, которые группируются по grp,а затем использую логику счетчика(используя row_number над разделом по идентификатору, grp) для создания счетчика.

 select y.*,row_number() over(partition by id,grp order by datetime) as cnt
from (
select x.*
       ,max(rnk) over(partition by id order by datetime) as grp
from (
    select id
           ,datetime
           ,case when lag(datetime) over(partition by id order by datetime) is null 
                   or datediff(datetime,lag(datetime) over(partition by id order by datetime))*24*60>=30 
                 then
                     row_number() over(partition by id order by datetime)
             end rnk
       from table
      )x
  )y