Лучший способ создания индексов в BigQuery

Использование GENERATE_ARRAY для гистограмм и др.

Когда вы наткнетесь на свою первую задачу по созданию гистограммы в BigQuery, вы можете подойти к ней с помощью простой GROUP BY, например:

SELECT
    hour,
    COUNT(*) AS num_accesses
FROM
    accesses
GROUP BY
    hour
ORDER BY
    hour

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

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

Общая идея состоит в том, чтобы сгенерировать массив всех возможных значений, преобразовать этот массив в строки, а затем использовать эти строки в качестве индекса для присоединения к вашим результатам — элементарно, как вы говорите! Давай попробуем это:

SELECT * FROM UNNEST(GENERATE_ARRAY(0, 23)) AS hour

Этот запрос сгенерирует массив с числами 0, 1,…, 23 для передачи в UNNEST, который принимает эти значения и помещает их в строки, в конце концов помеченные как час.

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

WITH index AS (SELECT * FROM UNNEST(GENERATE_ARRAY(0, 23)) AS hour),
hourly_counts AS (
    SELECT
        hour,
        COUNT(*) AS num_accesses
    FROM
        accesses
    GROUP BY
        hour
    )
SELECT
   hour,
   COALESCE(num_accesses, 0) AS num_accesses
FROM
    index
    LEFT JOIN hourly_counts USING (hour)
ORDER BY
    hour

Переменные границы

Вместо удобных предопределенных границ, которые мы использовали выше, у вас могут быть переменные границы. Например, вы пытаетесь построить гистограмму количества обращений за день. Опять же, вы можете попробовать применить GROUP BY, но у вас не будет отсутствующих индексов.

WITH daily_accesses AS (
    SELECT
        day,
        COUNT(*) AS num_accesses
    FROM
        accesses
    GROUP BY
        day
    )
SELECT
    num_accesses,
    COUNT(*) AS num_days
FROM
    daily_accesses
GROUP BY
    num_accesses
ORDER BY
    num_accesses

Не хорошо! Итак, давайте попробуем сгенерировать индекс еще раз, но на этот раз с использованием границ переменных, поскольку num_accesses в любой день не ограничен.

SELECT
    *
FROM
    UNNEST(GENERATE_ARRAY(
        (SELECT MIN(num_accesses) FROM daily_accesses)mi,
        (SELECT MAX(num_accesses) FROM daily_accesses)ma
        )) AS num_accesses

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

WITH daily_accesses AS (
    SELECT
        day,
        COUNT(*) AS num_accesses
    FROM
        accesses
    GROUP BY
        day
    ),
day_counts AS (
    SELECT
        num_accesses,
        COUNT(*) AS num_days
    FROM
        daily_accesses
    GROUP BY
        num_accesses
    ),
index AS (
    SELECT
        *
    FROM
        UNNEST(GENERATE_ARRAY(
            (SELECT MIN(num_accesses) FROM daily_accesses)mi,
            (SELECT MAX(num_accesses) FROM daily_accesses)ma
            )) AS num_accesses
    )
SELECT
    num_accesses,
    COALESCE(num_days, 0) AS num_days
FROM
    index
    LEFT JOIN day_counts USING (num_accesses)
ORDER BY
    num_accesses

Немного сложнее, чем в предыдущем примере, но принцип остается тем же.

Индексы даты

Наконец, вы, вероятно, столкнетесь с индексом дат во время путешествия к данным. Вы можете сгенерировать число с помощью GENERATE_ARRAY, а затем использовать что-то вроде DATE_ADD для получения дат, но Bigquery имеет встроенную функцию GENERATE_DATE_ARRAY.

А теперь подумайте, хотим ли мы получить ежедневное количество посещений нашего веб-сайта в 2020 году (время проверки!). Поскольку этот год был очень изменчивым, могут быть дни без доступа.

Опять же, наш основной запрос:

SELECT
    date,
    COUNT(*) AS num_accesses
FROM
    accesses
GROUP BY
    date
ORDER BY
    date

И мы создаем наш индекс, например:

SELECT * FROM UNNEST(GENERATE_DATE_ARRAY(
    '2020-01-01',
    CURRENT_DATE()
    )) AS date

Что будет создавать по одной дате на каждый день до сегодняшнего дня в 2020 году. Даем окончательный запрос.

WITH index AS (
    SELECT * FROM UNNEST(GENERATE_DATE_ARRAY(
        '2020-01-01',
        CURRENT_DATE()
        )) AS date
   ), daily_counts AS ( SELECT date, COUNT(*) AS num_accesses FROM accesses GROUP BY date ) SELECT date, COALESCE(num_accesses, 0) AS num_accesses FROM index LEFT JOIN daily_counts USING (date) ORDER BY date

Интервалы

До сих пор во всех примерах мы предполагали, что интервал равен 1 (или 1 ДНЮ), но вполне разумно использовать разные размеры шага, например каждые семь дней. Для этого вы можете передать дополнительный параметр функции GENERATE, которая затем вернет скорректированный индекс. В этом случае вам нужно будет немного сложнее объединить свои базовые данные, но это достижимо для решительного обработчика данных!

Надеюсь, этот простой подход к созданию индексов поможет вам в следующий раз, когда вам понадобится четко определенный индекс. Как всегда, загляните в Документы Bigquery, если хотите узнать больше.

Сообщите мне, если вы воспользуетесь другими интересными или удобными способами!