Множественные запросы в Preset.io/Superset

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

SELECT p.*,
       LEAST(runtime_1, runtime_2, runtime_3) as fastest_runtime,
       LEAST(ridetime_1, ridetime_2, ridetime3) as fastest_ridetime,
       i.interest
FROM people AS p
LEFT JOIN people_interests AS pi ON p.id = pi.people_id
LEFT JOIN interests_i AS i ON pi.interests_id = i.id

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

Многие-ко-многие запросы в Preset.io/Superset: Решения и Подходы

При работе с реляционными базами данных и BI-инструментами, такими как Preset.io и Superset, нередко возникает проблема обработки данных с отношениями многие-ко-многим (M:M). В данном случае мы имеем три таблицы: People, Interests и People_Interests, где одна и та же запись в таблице People может относиться к нескольким записям в таблице Interests, что создает дублирование данных при выполнении SQL-запросов. Решение данной проблемы требует понимания структуры данных и применения соответствующих агрегаций.

Более детальный анализ проблемы

На основе предоставленного примера, у нас есть следующая структура:

1. Таблица People: содержит информацию о людях и их показателях времени.
2. Таблица Interests: описывает интересы.
3. Таблица People_Interests: связывает людей с их интересами.

Ваш первоначальный запрос:

SELECT p.*,
       LEAST(runtime_1, runtime_2, runtime_3) AS fastest_runtime,
       LEAST(ridetime_1, ridetime_2, ridetime_3) AS fastest_ridetime,
       i.interest
FROM people AS p
LEFT JOIN people_interests AS pi ON p.id = pi.people_id
LEFT JOIN interests AS i ON pi.interest_id = i.id

Этот запрос корректно соединяет таблицы, но приводит к дублированию строк для John, поскольку у него два интереса. Это затрудняет дальнейшую агрегацию данных, поскольку при вычислении средних значений получается искажение.

Решение проблемы дублирования

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

WITH FastestTimes AS (
    SELECT 
        p.id,
        p.name,
        LEAST(runtime_1, runtime_2, runtime_3) AS fastest_runtime,
        LEAST(ridetime_1, ridetime_2, ridetime_3) AS fastest_ridetime
    FROM 
        people AS p
)
SELECT 
    ft.*,
    AVG(ft.fastest_runtime) OVER () AS avg_fastest_runtime,
    AVG(ft.fastest_ridetime) OVER () AS avg_fastest_ridetime,
    i.interest
FROM 
    FastestTimes AS ft
LEFT JOIN 
    people_interests AS pi ON ft.id = pi.people_id
LEFT JOIN 
    interests AS i ON pi.interest_id = i.id

Объяснение структуры запроса

1. CTE (Common Table Expression): Использование с Common Table Expression позволяет сначала извлечь значения fastest_runtime и fastest_ridetime для каждого человека, что помогает избежать дублирования на этом этапе.

2. Агрегация: Подсчет среднего значения выполняется с использованием оконной функции AVG() OVER (). Это позволяет рассчитать средние значения по всем полученным интересам без дублирования по людям.

3. Фильтрация интересов: После того как у вас есть список людей с их наилучшими значениями времени и интересами, вы можете применить необходимые фильтры к столбцу interest в интерфейсе Preset.io/Superset для визуализации данных.

Заключение

В решениях для работы с M:M связями в BI-инструментах, таких как Preset.io и Superset, ключевым моментом является правильная агрегация и группировка данных. Использование подзапросов или CTE помогает избежать дублирующихся результатов и обеспечивает точные расчеты для средних значений. С помощью представленного подхода вы сможете эффективно управлять данными и визуализировать результаты, сохраняя высокий уровень аналитики.