Многократное полное внешнее объединение (из данных в одной таблице)

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

бревна

+---------------------+---------+---------+------+
|        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 (у нас есть две)

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

агрегирование

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

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

+---------+------+---------+---------+--------+-----------+-----------+--------+-----------+-----------+
| api_key | hits | hits_v1 | hits_v2 | hits_1 | hits_1_v1 | hits_1_v2 | hits_7 | hits_7_v1 | hits_7_v2 |
+---------+------+---------+---------+--------+-----------+-----------+--------+-----------+-----------+
| 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. Да, мне нужно сохранить эту таблицу агрегации - она ​​может быть нехорошей, но целый ряд другого кода полагается на то, что так оно и есть, поэтому он не может измениться.

Источник

2 ответа

Решение

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

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;
Источник

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

Если вы хотите получить 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/

Источник
Другие вопросы по тегам