Удобная функция UNNEST в PostgreSQL для работы

Всем привет! Решила поделиться тут с вами одной полезной функцией, которую как раз недавно использовала на работе.

У нас есть массив устройств у пользователей. Но если мы хотим:
✔️посчитать устройства
✔️сделать join
✔️агрегировать
✔️считать DISTINCT
то массив нужно разложить в строки.

Представим, что у нас есть данные, где у пользователя несколько устройств лежат в массиве.
Было: 1 строка = массив устройств
Должно быть: несколько строк с пользователем = одно устройство в строке

Сначала создадим это прямо в Python
import duckdb
import pandas as pd

df = pd.DataFrame({
"user_id": [1, 2, 3],
"device_ids": [
["dev_a", "dev_b"],
["dev_c"],
["dev_d", "dev_e", "dev_f"]
]
})

И вот тут-то мы разворачиваем массив через UNNEST
query = """
SELECT user_id, d AS device_id
FROM df
CROSS JOIN UNNEST(device_ids) AS t(d)
ORDER BY 1, 2
"""
result = duckdb.query(query).to_df()

Разберем необычную конструкцию CROSS JOIN UNNEST(device_ids) AS t(d)
Что вообще означает AS t(d)?
🟢UNNEST(device_ids) - разворачивает массив
🟢t - имя временной таблицы
🟢d - имя колонки внутри неё

После этого d — обычная колонка, с которой можно работать как с любой другой.

Главное потом случайно не задвоить метрику когда будем соединять таблицу с таблицей заказов, например.
orders = pd.DataFrame({
"user_id": [1, 2],
"revenue": [100, 200]
})

Теперь делаем join после UNNEST:
query = """
SELECT df.user_id, d AS device_id, o.revenue
FROM df
CROSS JOIN UNNEST(device_ids) AS t(d)
LEFT JOIN orders o
ON df.user_id = o.user_id
ORDER BY 1, 2
"""
result2 = duckdb.query(query).to_df()
User 1 имел revenue = 100
Но теперь эта сотка появилась два раза! 😱

Поэтому нужно быть внимательным и понимать для чего мы это делаем.

❌ Нельзя после этого просто делать SUM(revenue) по этой таблице - выручка продублируется на каждое устройство и итог “раздуется”.

✅ Но зато расшив девайсы и приджойнив user-level выручку мы можем посмотреть её рядом на строках устройств.
💡 Зачем это может быть нужно?

Например:
сколько устройств у платящих пользователей
есть ли различия между single-device и multi-device users
сколько выручки приходится на пользователей с 3+ устройствами

Мы не делим revenue по устройствам. Мы просто используем её как атрибут пользователя.

P.S Кстати, я перешла на VS Code - мне нравится. Красивый же черный фон, да? Ну и удобно видеть в структуре файлы проекта.