Множественное полное внешнее соединение (из данных в одной таблице)

У меня есть некоторые данные журнала, которые выглядят следующим образом

Журналы

+---------------------+---------+---------+------+
|        date         | api_key | version | data |
+---------------------+---------+---------+------+
| 2018-05-08 01:00:00 | AAA     | v1      | data |
| 2018-05-08 02:00:00 | AAA     | v2      | data |
| 2018-05-06 03:00:00 | AAA     | v2      | data |
| 2018-05-06 04:00:00 | BBB     | v1      | data |
+---------------------+---------+---------+------+
  • date — это дата вызова API.
  • api_key — используемый ключ API
  • version — версия используемого API (у нас их две)

Эти данные в настоящее время находятся в СУБД, мне нужно переместить данные в Athena.

Агрегация

Существует некоторый устаревший код, который периодически запускается для агрегирования журналов. Эти агрегированные данные затем отображаются на информационной панели. По сути, он превращает плоские данные журнала (выше) в данные, агрегированные с помощью ключа API, предоставляя количество вызовов, сделанных на основе версии и в разных временных окнах (за все время, в течение 1 дня, 7 дней и т. д.).

Например, в следующей сводной таблице показаны объединенные необработанные данные.

+---------+------+---------+---------+--------+-----------+-----------+--------+-----------+-----------+
| api_key | hits | hits_v1 | hits_v2 | hits_1 | hits_1_v1 | hits_1_v2 | hits_7 | hitsapi-keyv1 | hitsapi-keyv2 |
+---------+------+---------+---------+--------+-----------+-----------+--------+-----------+-----------+
| AAA     |    3 |       1 |       2 |      2 |         1 |         1 |      3 |         1 |         2 |
| BBB     |    1 |       1 |       0 |      0 |         0 |         0 |      1 |         1 |         0 |
+---------+------+---------+---------+--------+-----------+-----------+--------+-----------+-----------+

Вот что означают столбцы:

  • hits количество звонков за все время данным api-key
  • hits_v1 количество звонков за все время данным api-key за v1
  • hits_v2 количество звонков за все время данным api-key за v2
  • hits_1 количество звонков за последние сутки по данному api-key
  • hits_1_v1 количество v1 звонков за последние сутки данным api-key
  • hits_1_v2 количество v2 звонков за последние сутки данным api-key
  • hits_7 количество звонков за последние 7 дней по данному api-key
  • hits_7_v1 количество звонков v1 за последние 7 дней данным api-key
  • hits_7_v2 количество звонков v2 за последние 7 дней данным api-key

SQL

Ниже приведен SQL, который я использую для создания этой сводной таблицы.

SELECT coalesce(hits_v1.api_key, hits_v2.api_key, hits_1_v1.api_key, hits_1_v2.api_key) api_key,
    coalesce(hits_v1.hits_v1,0) + coalesce(hits_v2.hits_v2,0) hits, 
    coalesce(hits_v1.hits_v1,0) hits_v1, 
    coalesce(hits_v2.hits_v2,0) hits_v2,

    coalesce(hits_1_v1.hits_1_v1,0) + coalesce(hits_1_v2.hits_1_v2,0) hits_1, 
    coalesce(hits_1_v1.hits_1_v1,0) hits_1_v1, 
    coalesce(hits_1_v2.hits_1_v2,0) hits_1_v2,

    coalesce(hits_7_v1.hits_7_v1,0) + coalesce(hits_7_v2.hits_7_v2,0) hits_7, 
    coalesce(hits_7_v1.hits_7_v1,0) hits_7_v1, 
    coalesce(hits_7_v2.hits_7_v2,0) hits_7_v2
FROM 
(
    (select api_key,count(*) as hits_v1 from logs where (version='v1' or version='') group by api_key) hits_v1
    FULL OUTER JOIN
    (select api_key,count(*) as hits_v2 from logs where version='v2' group by api_key) hits_v2                                                                       on hits_v2.api_key   = hits_v1.api_key

    FULL OUTER JOIN 
    (select api_key,count(*) as hits_1_v1 from logs where (version='v1' or version='') and (date > localtimestamp - interval '1' day) group by api_key) hits_1_v1    on hits_1_v1.api_key = coalesce(hits_v1.api_key, hits_v2.api_key)
    FULL OUTER JOIN 
    (select api_key,count(*) as hits_1_v2 from logs where version='v2' and (date > localtimestamp - interval '1' day) group by api_key) hits_1_v2                    on hits_1_v2.api_key = coalesce(hits_v1.api_key, hits_v2.api_key, hits_1_v1.api_key)

    FULL OUTER JOIN 
    (select api_key,count(*) as hits_7_v1 from logs where (version='v1' or version='') and (date > localtimestamp - interval '7' day) group by api_key) hits_7_v1    on hits_7_v1.api_key = coalesce(hits_v1.api_key, hits_v2.api_key, hits_1_v1.api_key, hits_1_v2.api_key)
    FULL OUTER JOIN 
    (select api_key,count(*) as hits_7_v2 from logs where version='v2' and (date > localtimestamp - interval '7' day) group by api_key) hits_7_v2                    on hits_7_v2.api_key = coalesce(hits_v1.api_key, hits_v2.api_key, hits_1_v1.api_key, hits_1_v2.api_key, hits_7_v1.api_key)
)
order by api_key asc

Как видите, это довольно повторяющийся и многословный текст. Хуже того, чем больше столбцов мне нужно добавить (14 дней, 30 дней, 60 дней и т. д.), мне нужно каждый раз добавлять в предложение on, чтобы включить все предыдущие соединения.

Это работает, но я уверен, что должен быть более чистый способ сделать это. Кто-нибудь может помочь, пожалуйста?

PS. Да, мне нужно сохранить эту таблицу агрегации - это может быть нехорошо, но целая куча другого кода полагается на то, что она такая, поэтому она не может измениться.


sql join amazon-athena
person swalesong    schedule 08.05.2018    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Используйте условную агрегацию:

select api_key,
       sum(case when version = 'v1' or version = '' then 1 else 0 end) AS hits_v1,
       sum(case when version = 'v2' then 1 else 0 end) AS hits_v2,
       sum(case when (version = 'v1' or version = '') and (date > localtimestamp - interval '1' day) then 1 else 0 end) as hits_v1_1,
       . . .
from logs l
group by api_key;
person Gordon Linoff    schedule 08.05.2018
comment
Спасибо, Гордон, выглядит хорошо. Вы привели «hits_v1» и «hits_v2» в качестве примера, который хорошо работает, однако мне также нужны «hits», которые представляют собой сумму как «hits_v1», так и «hits_v2». Как бы я включил это в свой запрос? Спасибо еще раз. - person swalesong; 08.05.2018
comment
@swalesong . . . Просто включите все три условия в предложение when. - person Gordon Linoff; 09.05.2018
comment
дох.. Конечно, это имеет смысл. Мне также нравится предложение @D-Shih. Я приму этот ответ, так как думаю, что главное было использовать условную агрегацию, но оба были очень полезными. Большое спасибо вам обоим - вы сделали мой запрос намного более читабельным. - person swalesong; 09.05.2018

arrow_upward
0
arrow_downward

Вы можете использовать этот запрос вместо вашего подзапроса.

Если вы хотите получить hits

  • hits количество звонков за все время данным api-key

Вы можете выбрать count(1), чтобы получить все данные.

SELECT api_key,
       count(1) hits,
       SUM(CASE WHEN (version='v1' or version='') THEN 1 ELSE 0 END ) hits_v1,
       SUM(CASE WHEN (version = 'v2' or version='') THEN 1 ELSE 0 END ) hits_v2,
       SUM(CASE WHEN (date > localtimestamp - interval '1' day) THEN 1 ELSE 0 END) hits_1,
       SUM(CASE WHEN (date > localtimestamp - interval '1' day) and (version='v1' or version='') THEN 1 ELSE 0 END) hits_1_v1,
       SUM(CASE WHEN (date > localtimestamp - interval '1' day) and (version='v2' or version='') THEN 1 ELSE 0 END) hits_1_v2,
       SUM(CASE WHEN (date > localtimestamp - interval '7' day) THEN 1 ELSE 0 END) hits_7,
       SUM(CASE WHEN  (version='v1' or version='') and (date > localtimestamp - interval '7' day) THEN 1 ELSE 0 END) hits_7_v1,
       SUM(CASE WHEN  (version='v2' or version='') and (date > localtimestamp - interval '7' day) THEN 1 ELSE 0 END) hits_7_v2
FROM logs
group by api_key

sqlfiddle:http://sqlfiddle.com/#!9/be990/5

person D-Shih    schedule 08.05.2018