LEFT OUTER JOIN с DATEDIFF в Hive QL

У меня есть две таблицы: назовем их INSTALLS и EXECUTES.

Таблица INSTALLS имеет структуру: user_id BIGINT, install_ts BIGINT.

Таблица EXECUTES имеет ту же структуру: user_id BIGINT, exec_ts BIGINT.

User_id очевиден, поля _ts - это временные метки, выраженные в секундах, отсчитываемых от эпохи Unix.

Эти две таблицы заполняются следующим образом:

Каждый раз, когда пользователь устанавливает мое приложение, таблица INSTALLS заполняется. Затем он может удалить и переустановить, и в этом случае в этой таблице появится другая строка для того же пользователя (правда, разные ts). Для всего моего анализа мне нужно использовать самую старую метку времени установки.

Каждый раз, когда пользователь использует мое приложение, таблица EXECUTES заполняется - с user_id и временем выполнения.

Мне нужно создать сводную таблицу с такой структурой:

дата, количество установок на эту дату, количество использований на последующую дату

Вот как я подошел к проблеме:

  1. Получить самую старую дату установки:

    ВЫБЕРИТЕ user_id, DATE (MIN (install_ts)) AS install_date FROM INSTALLS GROUP BY user_id

  2. Получить даты выполнения (необходимо учитывать несколько выполнений в заданную дату):

    ВЫБРАТЬ user_id, DATE (exec_ts) AS exec_date FROM EXECUTES GROUP BY user_id, DATE (exec_ts)

  3. Объедините эти два:

    ВЫБЕРИТЕ a.install_date, COUNT (a.user_id) AS install_count, COUNT (b.user_id) AS usage_count FROM (SELECT user_id, DATE (MIN (install_ts)) AS install_date FROM INSTALLS GROUP BY user_id) a LEFT OUTER JOIN (SELECT user_id, DATE (exec_ts) AS exec_date FROM EXECUTES GROUP BY user_id, DATE (exec_ts)) b ON a.user_id = b.user_id GROUP BY a.install_date

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

Один из подходов, который я рассмотрел, заключался в наличии дополнительного условия JOIN. Когда я это сделаю, я получаю (сначала запрос, ниже ошибка):

SELECT a.install_date, COUNT(a.user_id) AS install_count, COUNT(b.user_id) AS usage_count
FROM
    (SELECT user_id, DATE(MIN(install_ts)) AS install_date FROM INSTALLS GROUP BY user_id) a
    LEFT OUTER JOIN
    (SELECT user_id, DATE(exec_ts) AS exec_date FROM EXECUTES GROUP BY user_id, DATE(exec_ts)) b
    ON a.user_id = b.user_id AND DATEDIFF(b.exec_date, a.install_date) = 1
GROUP BY a.install_date

И левый, и правый псевдонимы встречаются в JOIN '1'

Второй подход, который я рассмотрел, заключался в том, чтобы иметь DATEDIFF в предложении WHERE:

SELECT a.install_date, COUNT(a.user_id) AS install_count, COUNT(b.user_id) AS usage_count
FROM
    (SELECT user_id, DATE(MIN(install_ts)) AS install_date FROM INSTALLS GROUP BY user_id) a
    LEFT OUTER JOIN
    (SELECT user_id, DATE(exec_ts) AS exec_date FROM EXECUTES GROUP BY user_id, DATE(exec_ts)) b
    ON a.user_id = b.user_id
WHERE b.user_id IS NULL OR DATEDIFF(b.exec_date, a.install_date) = 1
GROUP BY a.install_date

Но я вижу, что это просто неправильно - если пользователь устанавливает в первый день и возвращается в третий день, он не будет засчитан в install_count (поскольку он не будет присутствовать в объединенной таблице). Так что теперь у меня закончились идеи. Я немного новичок в SQL, поэтому я буду благодарен за любую помощь.


sql hive join hiveql database-design
person FreeBird    schedule 25.09.2013    source источник
comment
проверьте это решение, используя ROW_NUMBER - dbforums.com /   -  person HuBeZa    schedule 25.03.2015

Ответы (2)


arrow_upward
2
arrow_downward

Если я полностью понимаю, строки результатов будут содержать дату, количество установок уникальными пользователями на дату и количество раз, когда программа была запущена на следующий день после установки < em> для всех пользователей .. итак, для каждой даты есть 2 разных вычисления.

Мое решение начинается с объединения двух таблиц (без использованной вами группировки), а затем последующие операции используют функции аналитики, а затем группируют. В моем тестировании в таблицах использовались строковые типы, поэтому даты выглядят как «2013-08-01» и т. Д., И можно использовать DATEDIFF.

Я создал промежуточную таблицу результатов соединения, но ее можно легко включить в окончательный запрос. Эта таблица соединения будет содержать строку для каждого пользователя с датой установки и 1 или 0, чтобы указать, было ли выполнение на следующий день.

create table i_e_join as
select i.user_id, i.install_ts,
       if (e.exec_ts is null OR (DATEDIFF(e.exec_ts,i.install_ts) > 1), 0,1)
         over (partition by i.user_id,i.install_ts) as has_exec
from tmp_installs i left outer join tmp_executes e on (i.user_id = e.user_id);

а затем простая группа, чтобы получить результат для каждого install_ts:

select install_ts, count(distinct user_id) as install_count, 
       sum(has_exec) as usage_count from i_e_join
group by install_ts;

Ключом к созданию объединенной таблицы является использование аналитической функции для вычисления поля has_exec, которое просматривает все строки пользователя в install_ts.

person libjack    schedule 26.09.2013

arrow_upward
1
arrow_downward

Я решил это сам. Вот как я это сделал:

SELECT x.install_date, COUNT(x.user_id) AS install_count, COUNT(y.user_id) AS usage_count
FROM (
    SELECT user_id, DATE(MIN(install_ts)) AS install_date FROM INSTALLS GROUP BY user_id
) x LEFT OUTER JOIN (
    SELECT a.user_id AS user_id, a.install_date AS install_date, b.exec_date AS exec_date
    FROM
        (SELECT user_id, DATE(MIN(install_ts)) AS install_date FROM INSTALLS GROUP BY user_id) a
        JOIN
        (SELECT user_id, DATE(exec_ts) AS exec_date FROM EXECUTES GROUP BY user_id, DATE(exec_ts)) b
        ON a.user_id = b.user_id
        WHERE DATEDIFF(b.exec_date, a.install_date) = 1
) y
GROUP BY x.install_date
person FreeBird    schedule 01.10.2013