Блог

У меня есть несколько (10 м строк) таблиц фактов в красном смещении, каждая с естественным ключом memberid и каждая со столбцом timestamp . Допустим, у меня есть три таблицы: transactions , messages , app_opens , transactions , которые выглядят примерно так (все остальные таблицы имеют аналогичную структуру).:

Моя цель состоит в том, чтобы создать ежедневную таблицу агрегации по идентификаторам участников, которая выглядит так, как это, со строкой для каждого идентификатора участника и даты:

SQL, который я в настоящее время использую для этого, заключается в следующем, который включает объединение отдельных подзапросов:

 SELECT memberid,
       date,
       max(NVL(daily_revenue,0)) daily_revenue,
       max(NVL(daily_app_opens,0)) daily_app_opens,
       max(NVL(daily_messages,0)) daily_messages
FROM 
 (
 SELECT memberid,
        trunc(timestamp) as date,
        sum(revenue) daily_revenue,
        NULL AS daily_app_opens,
        NULL AS daily_messages
 FROM transactions
 GROUP BY 1,2

 UNION ALL

 SELECT memberid,
        trunc(timestamp) as date,
        NULL AS daily_revenue,
        count(*) daily_app_opens,
        NULL AS daily_messages
 FROM app_opens
 GROUP BY 1,2

 UNION ALL

 SELECT memberid,
        trunc(timestamp) as date,
        NULL AS daily_revenue,
        NULL AS daily_app_opens,
        count(*) daily_messages
 FROM messages
 GROUP BY 1,2
)
GROUP BY memberid, date
 

Это работает нормально и выдает ожидаемый результат, но мне интересно, является ли это наиболее эффективным способом выполнения такого рода запросов. У меня также есть использование FULL OUTER JOIN вместо UNION ALL , но производительность по существу идентична.

Каков наиболее эффективный способ добиться этого в Красном смещении?

Просмотр плана ОБЪЯСНЕНИЯ поможет, так как он позволит нам увидеть, каковы наиболее дорогостоящие части запроса. Основываясь на быстром чтении SQL, это выглядит довольно хорошо. Стоимость сканирования таблиц фактов, вероятно, имеет смысл, но это затраты, которые вам придется вынести. Если вы можете ограничить объем считываемых данных с помощью предложения where, это может быть уменьшено, но это может не соответствовать вашим потребностям.

Одно место, которое вам следует просмотреть, — это распределение этих таблиц. Поскольку вы группируетесь по учетной записи, наличие этого ключа в качестве ключа распространения ускорит этот процесс. Для группировки потребуется объединить строки с одинаковым значением accountid, распределение по этим значениям значительно сократит сетевой трафик внутри кластера.

При больших размерах данных и при оптимизации всего остального я бы ожидал, что UNION ALL выполнит ПОЛНОЕ ВНЕШНЕЕ ОБЪЕДИНЕНИЕ, но это будет зависеть от ряда факторов (например, насколько размер данных уменьшается за счет агрегирования учетных записей). 10 М строк не очень велики в терминах красного смещения (у меня 160 м строк широких данных в минимальном кластере), поэтому я не думаю, что вы увидите большую разницу между этими планами при таких размерах.