#mysql #hive #aggregate-functions #window-functions #analytic-functions
#mysql #улей #агрегатные функции #оконные функции #аналитические функции
Вопрос:
У меня есть две таблицы, которые я хочу объединить с определенной логикой.
Table_1 (S_No, ID, Date1, Date2)
S_No ID Date1 Date2
1 id1 2014-05-01 2014-07-03
2 id1 2015-03-23 2016-06-18
3 id1 2016-06-21 2016-07-29
Table_2 (S_No_New, ID_New, Date_New )
S_No_New ID_New Date_New
2_1 id1 2014-04-25
2_2 id1 2014-06-14
2_3 id1 2015-01-10
2_4 id1 2015-02-15
2_5 id1 2015-05-17
2_6 id1 2016-04-24
2_7 id1 2016-06-19
2_8 id1 2016-06-25
2_9 id1 2016-07-11
2_10 id1 2016-08-11
2_11 id1 2016-08-16
Я хочу объединить две таблицы выше таким образом, чтобы я мог подсчитать, сколько строк в table_2 до Date1 и между Date1 и Date2, а затем, когда мы переходим к следующей строке, мы должны использовать данные, которые еще не подсчитаны для того же идентификатора.
И если у нас есть запись даты в table_2 после последней записи Date2 в таблице 1, тогда нам нужно добавить новую строку с добавлением ‘ 1″ в S_No и аналогичными оставшимися деталями столбца.
В целом эту проблему можно разделить на две части:
1) Получение столбца подсчетов
2) Добавление дополнительных строк (S_No «4» в этом примере)
Пожалуйста, напишите ответ, если вы знаете решение для любого из двух.
Конечный результат :
S_No ID Date1 Date2 Count_pre Count_Between
1 id1 2014-05-01 2014-07-03 1 1
2 id1 2015-03-23 2016-06-18 2 2
3 id1 2016-06-21 2016-07-29 1 2
4 id1 NULL NULL 2 0
Логические :
S_No 1 :
Count_Pre = Даты до 2014-05-01
Count_between = Даты между 2014-05-01 и 2014-07-03
S_No 2:
Count_Pre = Даты между 2014-07-03 и 2015-03-23
Count_between = Даты между 2015-03-23 и 2016-06-18
и так далее
Промежуточная таблица должна выглядеть примерно так:
S_No ID Date Date2 S_No_New Date_New
1 id1 2014-05-01 2014-07-03 2_1 2014-04-25
1 id1 2014-05-01 2014-07-03 2_2 2014-06-14
2 id1 2015-03-23 2016-06-18 2_3 2015-01-10
2 id1 2015-03-23 2016-06-18 2_4 2015-02-15
2 id1 2015-03-23 2016-06-18 2_5 2015-05-17
2 id1 2015-03-23 2016-06-18 2_6 2016-04-24
3 id1 2016-06-21 2016-07-29 2_7 2016-06-19
3 id1 2016-06-21 2016-07-29 2_8 2016-06-25
3 id1 2016-06-21 2016-07-29 2_9 2016-07-11
4 id1 NULL NULL 2_10 2016-08-11
4 id1 NULL NULL 2_11 2016-08-16
Я пробовал разные функции управления окнами и аналитики, но не смог решить эту проблему.
Возможно ли выполнить такое объединение в hive (базовый sql)?
ПРИМЕЧАНИЕ: РЕДАКТИРОВАТЬ 2: мне нужно реализовать это в hive, и он поддерживает все встроенные функции, но не переменные из mysql. Он поддерживает агрегированные, оконные и аналитические функции.
РЕДАКТИРОВАТЬ : изменен формат даты с дд / мм / гггг на гггг-мм-дд
Комментарии:
1. mysql или sql-сервер?
2. На самом деле это MySQL.
3. 1. Сохраняйте даты как даты
4. Вы можете изменить или преобразовать столбец в соответствии с требованиями. Можете ли вы предоставить более подробную информацию о своем комментарии.
5. Таким образом, date_new = 2014-06-14 следует учитывать один раз как count_between для S_no = 1, но не учитывать как count_pre для s_no = 2?
Ответ №1:
SELECT t.t1s_no,t.date1,t.date2,
sum(case when t.srce = 'P' then 1 else 0 end) as 'prev',
sum(case when t.srce = 'B' then 1 else 0 end) as 'between',
sum(case when t.srce = 'X' then 1 else 0 end) as 'missing'
FROM
(
SELECT S.*,
ROW_NUMBER() OVER (PARTITION BY S.DATE_NEW ORDER BY s.srce ,S.DATE1) RN
FROM
(SELECT 'P' AS SRCE,T1.S_NO T1S_NO,T1.ID T1ID,T1.DATE1 DATE1,T1.DATE2 DATE2,T2.DATE_NEW
FROM TABLE_1 T1
JOIN TABLE_2 T2 ON T2.DATE_NEW < T1.DATE1
UNION
SELECT 'B' AS SRCE,T1.S_NO T1S_NO,T1.ID T1ID,T1.DATE1 DATE1,T1.DATE2 DATE2,T2.DATE_NEW
FROM TABLE_1 T1
JOIN TABLE_2 T2 ON T2.DATE_NEW BETWEEN T1.DATE1 AND T1.DATE2
UNION
SELECT 'X' AS SRCE,4 T1S_NO,T1.ID T1ID,T1.DATE1 DATE1,T1.DATE2 DATE2,T2.DATE_NEW
FROM TABLE_2 T2
left JOIN TABLE_1 T1 ON (T2.DATE_NEW BETWEEN T1.DATE1 AND T1.DATE2) or (t2.date_new < t1.date1)
where t1.date1 is null
) S
) T
WHERE T.RN = 1
group by t.t1s_no,t.date1,t.date2
ORDER BY T.T1S_NO, T.DATE1
;
Результат
t1s_no date1 date2 prev between missing
----------- ---------------- ---------------- ----------- ----------- -----------
1 2014-05-01 2014-07-03 1 1 0
2 2015-03-23 2016-06-18 2 2 0
3 2016-06-21 2016-07-29 1 2 0
4 NULL NULL 0 0 2
(4 rows affected)
Комментарии:
1. Для S_No = 2 фактически должно быть по 2 для каждой. Обновлено в вопросе. Я хотел использовать его в hive, и он не поддерживает переменные, возможно ли это сделать без использования переменных. Пожалуйста, прочитайте редактирование для получения дополнительных обновлений
2. Извините, что я не согласен с обновлением. Следовало бы указать это ранее относительно реализации hive.
3. Я не знаю hive, но, похоже, у него есть функция row_number(), которая, возможно, была бы эквивалентна переменному методу, который я использовал. Если функция hive row_number() выглядит как эта row_number() поверх (разделение по … порядок по …), то я мог бы обновить свой ответ с помощью t-sql. Дайте мне знать, если вы хотите, чтобы я это сделал.
4. Да, улей поддерживает функцию row_number(), подобную функции row_number(), поверх (разделение по … порядку по …). Было бы действительно полезно, если бы вы могли изменить приведенный выше запрос. Заранее спасибо.