Удобная функция 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 - мне нравится. Красивый же черный фон, да? Ну и удобно видеть в структуре файлы проекта.