бигдата. прошу прощения,если я не в ту тему вопрос создаю .
Есть таблица TABLE , в которой три столбца. выгрузка за сутки.
msisdn ( это номер телефона, строка), starttimesecond ( это время в юникс формате, строка, время ,в которое человек перешел на новую страничку) , app( это хост,строка, там vk.com и ok.com)


то есть для каждого номера телефона есть куча starttimesecond, может быть 30 за минуту, . а в какое-то время, когда не заходил на этот хост- нет. а в общей сложности - выгрузка за сутки.

Нужно посчитать по каждому домену кол-во хитов и уникальных пользователей хотя бы с одним хитом , где хит - это наличие трафика в 15 минутный диапазон.


думаю, нужно смотреть, есть ли хотя бы одна активность за минуту, и такие минуты должны идти подряд. а если их больше 15, то делить на несколько хитов по 15 . Буду безмерно благодарна , если поможете!

AkinaКластеризация на SQL - занятие неблагодарное... попробуйте такой подход.

Для начала сделайте компрессию таблицы - ужмите её до дискретности в минуту. Создаём временную таблицу той же структуры, с единственным уникальным индексом из всех трёх полей, после чего копируем туда все данные, округляя время до минут, с игнором дубликатов.

Далее постройте запрос, который для отберёт записи, для которых нет записи с теми же телефоном-сайтом, но на 1 минуту меньше. Ну то есть фактически записи начал кластеров. Аналогично постройте запрос получения концов кластеров.

Поскольку исходные данные для этих запросов одинаковы и неизменяемы, количество записей в выборках равно, а при сортировке по группе (телефон-сайт-время) порядковый номер начала периода и конца периода в выходном наборе совпадают. Остаётся, пользуясь знанием этого факта, для каждого периода посчитать его продолжительность и перевести её в хиты (деление с округлением вверх).

Всё.

Спасибо ОГРОМНОЕ вам за ответ! Алгоритм поняла, отличный!
Постараюсь это все на sql теперь перевести.
Вопрос такой.
Не могли бы вы написать на sql запрос, который отберёт записи, для которых нет записи с теми же телефоном-сайтом, но на 1 минуту меньше. Другие запросы, надеюсь мне под силу будут)

Akinailydsql запрос, который отберёт записи, для которых нет записи с теми же телефоном-сайтом, но на 1 минуту меньше.



недавно работаю и туго соображаю (

t1- это какая таблица? исходная?
какие там столбы, если не исходная? просто они сравниваются по времени, а время в разных форматах

t2- это таблица с id, это я поняла по полю.
столбы таблицы t2 - это id ,msisdn , time ( уже переведенное в обычный формат), app
правильно я поняла?

изначально я подумала ,что таблица с идентификатором будет вида :один столбец- идентификатор, второй столбец - склеенные данные с трех столбцов в один столбец

Akinat1 и t2 - это две (независимые) копии одной и той же таблицы table. RTFM alias (алиас, псевдоним).фууух, пишу и исправляю, ну вообщем
добралась наконец-то до реализации
и пишет


время у меня в timestamp

как поправить?

кусочек таблички для примера

Akina, hive. но насколько я поняла, язык тут sql .
не злитесь, пожалуйста, я бестолочь еще та, но как-то и когда-то надо начинать разбираться. (
Надо искать где-то форум по хайву?
вы не знаете,как ипсравить?

AkinaSQL - это канва. А диалектов у того языка аки блох на собаке, и каждый со своими выпендронами.
ilydНадо искать где-то форум по хайву?Ну вообще-то да... или хотя бы явно указывать в вопросе. какой именно диалект (а порой и версия сервера) используется. А для начала надо искать официальную документацию. В частности, по функциям даты-времени это вот тут .

А затем надо строить замену. Номер дня недели придётся получать, наверное, используя форматирование по шаблону (см. тут ). Ну а затем вычитание и минус неделя без дня (ибо понедельник - 1). Т.е. получится нечто вроде



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

.Спасибо за пояснение! я сразу извинилась,если не в ту тему пишу и благодарю вас лично за помощь!
не поняла, почему предлагаете через день недели считать?
у меня выгрузка за сутки, когда человек заходил на хост. Отсортированная до минут. Мне теперь , как вы и советовали, нужно найти первую минуту, для которой в предыдущую минуту нет записи.
а в общем и целом задание- посчитать количество за день сессий по 15 минут, то есть мне надо выделить выделить начало блоков и концы блоков. и посчитать их длину.

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

они в форматеidtimenumberhost1072016-11-24 01:40:007965533976ok1082016-11-24 01:41:007965533976ok1092016-11-24 01:09:007965536541ok1102016-11-24 01:10:007965536541ok1112016-11-24 01:11:007965536541ok1122016-11-24 01:12:007965536541ok
мне нужно найти все начала непрерывных соединений длиной в 4 минуты(блоки) по каждому абоненту .
для этого мне надо найти начала блоков и концы блоков и их длительность.
я ищу начало таких блоков таких путем, где t1 и t2 - копии, время в timestamp
находит строки, с которых начались блоки,но не все. видимо смотрит время по всем абонентам, а не по одному
подскажите, плиз, как добавить условие,чтобы считались записи стартов блоков для каждого абонента.

чет пытаюсь вставить условие на num и туда и сюда и никак... Модератор: Не надо плодить темы по одному и тому же вопросу