Перекрестное соединение в Улье

#sql #datetime #join #hive #window-functions

#sql #дата и время #Присоединиться #hive #окно-функции

Вопрос:

Я пытаюсь создать новый столбец time_period во время выполнения приведенного ниже запроса. Если разница в дате между данной транзакцией и самой последней транзакцией в справочной таблице составляет менее 7 дней, то пометьте ее как recent транзакцию, в противном случае пометьте как old транзакцию.

Однако приведенный ниже запрос генерирует ошибку в подзапросе, связанном с перекрестным соединением. Ошибка «Не удается распознать ввод рядом с ‘select’ ‘(«max’

 SELECT
    c.*
    FROM(
        SELECT
         a.acct_nb,
         a.txn_date,
         a.txn_amt,
         (CASE WHEN datediff(b.most_recent_txn,a.txn_date)<7 THEN 'recent' ELSE 'old' END) AS time_period
         FROM db.t1 a
         CROSS JOIN(
              SELECT max(txn_date) AS most_recent_txn --ERROR OCCURS HERE
              FROM db.t1 b)
        )c
        WHERE c.time_period='new';

Что может быть причиной этой ошибки?

1. Возможно, вам не хватает запятой после a.txn_amt

2. Спасибо, просто опечатка при написании сокращенной версии запроса для SO. Исправил сообщение.

3. Похоже, ошибка не соответствует вашему запросу — круглые скобки находятся в другом положении

Ответ №1:

Для этого вам не нужен join . Вы можете просто использовать оконные функции:

 SELECT 
    acct_nb,
    txn_date, 
    txn_amt,
    CASE WHEN DATEDIFF(MAX(txn_date) OVER(), txn_date) < 7 
        THEN 'recent' 
        ELSE 'old' 
    END AS time_period
FROM db.t1

Если вам нужно фильтровать только по недавним транзакциям, то вы можете использовать подзапрос:

 SELECT *
FROM (
    SELECT acct_nb, txn_date, txn_amt, MAX(txn_date) OVER() max_txn_date
    FROM db.t1
) t
WHERE DATEDIFF(MAX(txn_date) OVER(), txn_date) < 7

1. @Caerus . . . Оконные функции почти всегда являются лучшим решением, если существует решение, использующее их.

2. @GMB спасибо — очень элегантное решение. Чтобы убедиться, что я правильно понимаю, когда вы не указываете поле после инструкции OVER(), отображается ли окно по умолчанию для всей таблицы?

3. @Caerus: да, именно.

Ответ №2:

Псевдоним b должен быть применен к перекрестно соединяемому подзапросу, а не к таблице db.t1 внутри подзапроса:

 SELECT c.*
FROM (
  SELECT a.acct_nb, a.txn_date, a.txn_amt,
         CASE WHEN datediff(b.most_recent_txn, a.txn_date) < 7 THEN 'recent' ELSE 'old' END AS time_period
  FROM db.t1 a
  CROSS JOIN (
    SELECT max(txn_date) AS most_recent_txn
    FROM db.t1 
  ) b
) c
WHERE c.time_period='new';

Кроме того, в вашем CASE выражении нет возвращаемой ветви 'new' , поэтому последнее WHERE предложение отфильтрует все строки.