Запрос Apache Hive HiveQL

Я изучаю Hive и хочу написать оптимизированный запрос HiveQL/SQL.

Моя таблица выглядит так:

CREATE TABLE sales (dealer VARCHAR(25), make VARCHAR(25), type VARCHAR(25), day INT);
INSERT INTO sales (dealer, make, type, day) VALUES
("Xyz", "Highlander", "SUV", "0"),
("Xyz", "Prius", "HATCH", "1"),
("Xyz", "Prius", "HATCH", "2"),
("Xyz", "Prius", "HATCH", "3"),
("Xyz", "Versa", "HATCH", "1"),
("Xyz", "Versa", "HATCH", "2"),
("Xyz", "Versa", "HATCH", "3"),
("Xyz", "S3", "SEDAN", "1"),
("Xyz", "S3", "SEDAN", "2"),
("Abc", "Forrester", "SUV", "1");

Имея "дилера" D, я хочу вычислить максимальное N "производство" для каждого "типа" за последние X дней, в один запрос.

SELECT dealer, make, type, COUNT(*) AS frequency FROM sales
WHERE day > 0 AND dealer LIKE 'Xyz' GROUP BY make, type
ORDER BY frequency DESC LIMIT 5

Проблема в том, что при использовании GROUP BY для «make» и «type» для top 1 я получу только:

DEALER, MAKE, TYPE, COUNT
Xyz, Prius, Hatch, 3
Xyz, Versa, Hatch, 3
Xyz, S3, Sedan, 2
...

Но я хочу

Xyz, Prius, Hatch, 3
Xyz, S3, Sedan, 2
...

для КАЖДЫЙ "введите" верхний N.

Может ли кто-нибудь помочь мне понять, как написать такой запрос?

Скрипт SQL http://sqlfiddle.com/#!2/df9304 /5

****Обновлять****

Похоже, rank() был бы полезен:

Hive получает первые n записей в группе по запросу

https://blogs.oracle.com/taylor22/entry/hive_0_11_may_15

HiveQL и rank()


sql hadoop hive hiveql top-n
person foobarometer    schedule 23.08.2014    source источник
comment
Какой запрос вы выполнили, чтобы получить результат, который вы указали?   -  person J Maurer    schedule 24.08.2014
comment
Я не запускал запрос, но мое понимание документов: ВЫБЕРИТЕ дилера, сделайте, введите, СЧЕТЧИК (*) КАК частота ИЗ таблицы ГДЕ день › 0 И дилер == 'Xyz' ГРУППА ПО марке, введите ЗАКАЗАТЬ ПО частоте DESC LIMIT 5   -  person foobarometer    schedule 24.08.2014
comment
Скажем, вы хотите вычислить 5 лучших производителей для каждого типа... как xyz, versa, hatch, 3 не включен в этот список?   -  person gobrewers14    schedule 25.08.2014
comment
@ GoBrewers14, да, вы правы, я привел пример для топ-1. Обновлено в вопросе. См. rank(), кажется, это будет полезно.   -  person foobarometer    schedule 25.08.2014

Ответы (1)


arrow_upward
0
arrow_downward

После прочтения еще нескольких документов и подсказок из связанных вопросов:

SELECT dealer, make, rank, type FROM (
    SELECT dealer, make, rank() OVER (PARTITION BY type ORDER BY count DESC) AS rank, type FROM (
        SELECT dealer, make, count(*) AS count, type FROM Sales WHERE dealer = "Xyz" GROUP BY dealer, type, make
    ) CountedSales
) RankedSales
WHERE RankedSales.rank < 3;

Внутренний запрос выполняет подсчет, средний запрос выполняет rank(), а внешний запрос ограничивает ранг.

Содержимое таблицы продаж

hive> select * from Sales;
OK
Xyz      Highlander      SUV    NULL
Xyz      Highlander      SUV    NULL
Xyz      Rouge   SUV    NULL
Xyz      Rouge   SUV    NULL
Xyz      Prius   HATCH  NULL
Xyz      Prius   HATCH  NULL
Xyz      Prius   HATCH  NULL
Xyz      Versa   HATCH  NULL
Xyz      S3      SEDAN  NULL
Xyz      S3      SEDAN  NULL
Xyz      S3      SEDAN  NULL
Xyz      A8      SEDAN  NULL
Xyz      A8      SEDAN  NULL
Xyz      A8      SEDAN  NULL
Xyz      A8      SEDAN  NULL
Time taken: 0.054 seconds, Fetched: 15 row(s)

Теперь собственно запрос.

hive> SELECT dealer, make, rank, type FROM (                                                                          
    >     SELECT dealer, make, rank() OVER (PARTITION BY type ORDER BY count DESC) AS rank, type FROM (
    >         SELECT dealer, make, count(*) AS count, type FROM Sales WHERE dealer = "Xyz" GROUP BY dealer, type, make
    >     ) CountedSales
    > ) RankedSales
    > WHERE RankedSales.rank < 3;
...
Execution completed successfully
MapredLocal task succeeded
OK
Xyz      Prius  1        HATCH
Xyz      Versa  2        HATCH
Xyz      A8     1        SEDAN
Xyz      S3     2        SEDAN
Xyz      Rouge  1        SUV
Xyz      Highlander     1        SUV
Time taken: 28.491 seconds, Fetched: 6 row(s)
person foobarometer    schedule 26.08.2014