Teradata SQL - Группировка наблюдений, близких по времени друг к другу?

#sql #teradata

Вопрос:

У меня есть таблица, в которой есть транзакции позиций по счету, дате и времени (два отдельных поля), но для каждой отдельной транзакции нет уникального идентификатора. Я хочу сделать наилучшее предположение об этом, сгруппировав кампании, которые выполняются примерно в одно и то же время, скажем, с интервалом в час друг от друга, чтобы быть консервативным. Моей первоначальной мыслью было извлечь час из поля time и использовать его, но бывают случаи, когда транзакция может превышать час. Например, строка 1 была выполнена в 8: 57 утра, а строка 2 — в 9:01 утра. Я бы хотел, чтобы они были сгруппированы вместе, но простое использование часа этого не достигло бы.

Есть мысли? Ниже приведены примеры данных с добавленным полем, показывающим, буду ли я группировать их вместе или нет.

 Account | Date   | Time      | Group
A       | 1/1/16 | 8:57:00   | 1
A       | 1/1/16 | 9:01:00   | 1
A       | 1/1/16 | 9:16:00   | 1
A       | 1/1/16 | 12:15:00  | 2
A       | 1/1/16 | 12:32:00  | 2
B       | 1/2/16 | 7:23:00   | 3
B       | 1/2/16 | 7:24:00   | 3
B       | 1/3/16 | 9:18:00   | 4
B       | 1/3/16 | 11:24:00  | 5

1. являются ли дата и время отдельными столбцами в таблице?

2. Да, это отдельные столбцы

3. Вам нужно определить границы, по которым вы будете округлять до следующего часа. Отсюда следует комбинация использования EXTRACT(HOUR FROM {Time}) и EXTRACT(MINUTE FROM {Time}) в CASE инструкции с некоторой логикой для решения проблемы округления до полуночи и перехода к следующему DAY .

4. Как насчет 8:00/8:30/9:00/9:30? Все ли они попадут в одну группу?

Ответ №1:

 select      "Account"
           ,"Date"
           ,"Time"
           ,count (is_new_group) over (partition by "Account" order by ts rows unbounded preceding)   1     as "Group"


from       (select      "Account"
                       ,"Date"
                       ,"Time"
                       ,cast ("Date" as timestamp)   (("Time" - time '00:00:00') hour to second)                                                                        as ts 
                       ,case when ts > min (ts) over (partition by "Account" order by ts rows between 1 preceding and 1 preceding)   interval '1' hour then 'Y' end     as is_new_group

            from        t
            )
            t
;