Странная ошибка в запросе BigQuery SQL, ищу объяснение.

Содержание

Вопрос или проблема
Ответ или решение
Проблематика
Причина проблемы
Решение
Заключение

Вопрос или проблема

SELECT 
    DISTINCT 
    CONCAT(ue.vb_name, ue.b_name) AS Uber_Eats_Slug,
    CONCAT(gh.vb_name, gh.b_name) AS Grub_Hub_Slug,
    FORMAT_TIMESTAMP('%Y-%m-%d %H:%M', CAST(ue.timestamp AS TIMESTAMP)) AS uber_formatted_datetime,
    FORMAT_TIMESTAMP('%Y-%m-%d %H:%M', CAST(gh.timestamp AS TIMESTAMP)) AS grubhub_formatted_datetime
FROM 
    `arboreal-vision-339901.take_home_v2.virtual_kitchen_ubereats_hours` AS ue,
    `arboreal-vision-339901.take_home_v2.virtual_kitchen_grubhub_hours` AS gh

GROUP BY 
    Uber_Eats_Slug, Grub_Hub_Slug, uber_formatted_datetime,grubhub_formatted_datetime
LIMIT 1000;

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

DISTINCT означает, что в результирующем наборе не будет двух записей, у которых все столбцы совпадают. У вас есть

    DISTINCT 
    CONCAT(ue.vb_name, ue.b_name) AS Uber_Eats_Slug,
    CONCAT(gh.vb_name, gh.b_name) AS Grub_Hub_Slug,
    FORMAT_TIMESTAMP('%Y-%m-%d %H:%M', CAST(ue.timestamp AS TIMESTAMP)) AS uber_formatted_datetime,
    FORMAT_TIMESTAMP('%Y-%m-%d %H:%M', CAST(gh.timestamp AS TIMESTAMP)) AS grubhub_formatted_datetime

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

Ваш GROUP BY это

GROUP BY 
    Uber_Eats_Slug, Grub_Hub_Slug, uber_formatted_datetime,grubhub_formatted_datetime

что гарантирует, что четыре поля будут сгруппированы. Если вы хотите GROUP BY Uber_Eats_Slug, uber_formatted_datetime, то сделайте это. Тем не менее, похоже, что желание заключалось в том, чтобы сгруппировать Uber на одной стороне, а Grub на другой и соединить их. Вы можете достичь этого, разделив их, сделав один запрос для Uber, другой для Grub, использовать их как подзапросы и соединить два результирующих набора любым удобным вам способом.

Ответ или решение

Вопрос о том, почему ваш запрос в BigQuery возвращает одинаковые значения для Uber_Hub_Slug и uber_formatted_datetime, даже несмотря на использование функции DISTINCT, требует детального обсуждения. Давайте рассмотрим ключевые моменты, которые могут помочь вам разобраться с данной проблемой.

Проблематика

Запрос выстраивается следующим образом:

SELECT 
    DISTINCT 
    CONCAT(ue.vb_name, ue.b_name) AS Uber_Eats_Slug,
    CONCAT(gh.vb_name, gh.b_name) AS Grub_Hub_Slug,
    FORMAT_TIMESTAMP('%Y-%m-%d %H:%M', CAST(ue.timestamp AS TIMESTAMP)) AS uber_formatted_datetime,
    FORMAT_TIMESTAMP('%Y-%m-%d %H:%M', CAST(gh.timestamp AS TIMESTAMP)) AS grubhub_formatted_datetime
FROM 
    `arboreal-vision-339901.take_home_v2.virtual_kitchen_ubereats_hours` AS ue,
    `arboreal-vision-339901.take_home_v2.virtual_kitchen_grubhub_hours` AS gh
GROUP BY 
    Uber_Eats_Slug, Grub_Hub_Slug, uber_formatted_datetime, grubhub_formatted_datetime
LIMIT 1000;

Причина проблемы

Функция DISTINCT работает в рамках всех полей, указанных в запросе, а GROUP BY также применяется ко всем перечисленным параметрам. Если данные в таблицах uber_eats_hours и grubhub_hours не предполагают разнообразия для остальных полей запроса, вы получите лишь одно уникальное сочетание значений. То есть, если, например, для всех записей Uber_Eats_Slug и uber_formatted_datetime генерируются одинаковые значения, даже несмотря на наличие нескольких записей, вы получите одни и те же результаты.

Решение

Чтобы обойти данную проблему и получить необходимые результаты, можно использовать один из следующих подходов:

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

WITH ue_data AS (
    SELECT 
        CONCAT(vb_name, b_name) AS Uber_Eats_Slug,
        FORMAT_TIMESTAMP('%Y-%m-%d %H:%M', CAST(timestamp AS TIMESTAMP)) AS uber_formatted_datetime
    FROM 
        `arboreal-vision-339901.take_home_v2.virtual_kitchen_ubereats_hours`
),
gh_data AS (
    SELECT 
        CONCAT(vb_name, b_name) AS Grub_Hub_Slug,
        FORMAT_TIMESTAMP('%Y-%m-%d %H:%M', CAST(timestamp AS TIMESTAMP)) AS grubhub_formatted_datetime
    FROM 
        `arboreal-vision-339901.take_home_v2.virtual_kitchen_grubhub_hours`
)
SELECT 
    DISTINCT ue.Uber_Eats_Slug,
    gh.Grub_Hub_Slug,
    ue.uber_formatted_datetime,
    gh.grubhub_formatted_datetime
FROM 
    ue_data ue
JOIN 
    gh_data gh ON <условие для соединения>;

Проверка данных: Убедитесь, что в ваших исходных таблицах действительно есть различия. Выполните отдельные выборки из обеих таблиц, чтобы выяснить, что именно мешает разнообразию в результатах.
Работа с фильтрами: Если вы знаете, что вам нужны только определенные записи (например, по дате или другим параметрам), добавьте условия в ваш запрос, чтобы сократить объем данных и повысить вероятность уникальности результатов.

Заключение

Для получения различных значений в вашем SQL-запросе необходимо учитывать, как обрабатываются данные из обеих таблиц. Подход с подзапросами и объединениями предоставит вам более глубокое понимание структуры данных и поможет избежать ограничения, связано с работой функции DISTINCT и GROUP BY. Эти подходы не только устранят вашу текущую проблему, но и улучшат читаемость и поддерживаемость вашего кода в будущем.

Если у вас возникнут дополнительные вопросы по оптимизации запросов в BigQuery или по решению других схожих проблем, не стесняйтесь обращаться за помощью!