Warning: Undefined array key 0 in /var/www/tgoop/function.php on line 65

Warning: Trying to access array offset on value of type null in /var/www/tgoop/function.php on line 65
101 - Telegram Web
Telegram Web
MPP — ключ к эффективной обработке больших данных

Я неоднократно упоминала в своих статьях аббревиатуру MPP, но не рассказывала, что же это такое. Даже если вы уже знакомы с этим термином, давайте всё равно освежим ваши знания.

MPP (Massive Parallel Processing) — это архитектурный подход к обработке данных, широко применяемый в хранилищах данных. Его суть заключается в распределении и параллельной обработке данных на нескольких серверах (узлах) одновременно, что обеспечивает высокую производительность и масштабируемость. В результате общее время выполнения операций сокращается в 10-100 раз по сравнению с традиционными СУБД.

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

Ключевые преимущества MPP:
— Высокая производительность: распределенная обработка данных на узлах кластера обеспечивает быстрое выполнение запросов.
— Масштабируемость: простота добавления новых узлов для обработки дополнительных объемов данных.
— Эффективное использование ресурсов: параллельная обработка данных на нескольких серверах повышает общую эффективность.

Важный момент. MPP-системы предназначены для обработки и анализа больших объемов данных, но не эффективны для обработки единичных OLTP -транзакций, таких как частые операции вставки, обновления и удаления отдельных строк данных.

#dwh
1👍1
Путешествия во времени вместе со Snowflake

Одна из крутых функций Snowflake — это Time Travel, позволяющая "путешествовать во времени" для восстановления данных, которые были изменены или удалены в прошлом. Теперь уничтожить данные безвозвратно будет не так просто 😅

Основные возможности Time Travel
Запрос данных из прошлого.
Можно выполнять запросы к данным, которые были обновлены или удалены. Это уникальная возможность анализа и восстановления информации на разных исторических этапах БД.

Пример запроса, вытягивающего исторические данные по состоянию на 10 минут назад:
SELECT * 
FROM table_name AT(OFFSET => -60*10);


Создание клонов таблиц, схем и БД. Time Travel позволяет создавать клонированные копии на определенный момент в прошлом. Это полезно для анализа и восстановления состояния данных за конкретный временной отрезок.

Пример создания клона таблицы с указанной меткой времени:
CREATE TABLE restored_table CLONE table_name
AT(TIMESTAMP => 'Fri, 29 Dec 2023 00:00:00 +0500'::timestamp_tz);


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

Пример просмотра удалённых таблиц:
SHOW TABLES HISTORY LIKE 'old%' IN db_name.schema_name;


Восстановление удалённой таблицы:
UNDROP TABLE table_name;

! Если объект с таким же именем уже есть в базе, то восстановление невозможно, необходимо переименовать новый объект до восстановления.

Как это работает?
Snowflake сохраняет состояние данных перед выполнением операций над ними.

Сколько хранятся данные?
Всё зависит от версии подписки на Snowflake. Для Standard срок хранения составляет всего 1 день. А для Enterprise-версии — от 1 до 90 дней для стандартных таблиц. Snowflake позволяет настроить срок хранения на уровне объекта.

Time Travel — это инструмент для обеспечения целостности и восстановления данных, который предоставляет уникальные возможности работы с исторической информацией.

Дополнительную информацию о функции Time Travel можно прочитать в доке.

#snowflake
Please open Telegram to view this post
VIEW IN TELEGRAM
1
Ключи в базах данных: коротко о важном

При работе с БД важно понимать разницу между ключами.

Primary Key (PK) – уникальный идентификатор для каждой записи в таблице, гарантирует целостность данных. Эффективное использование этого ключа требует тщательного выбора типа данных. Использование слишком длинных строк (VARCHAR(MAX) и подобных) или сложных типов может существенно повлиять на производительность запросов при соединении по ключу.
Если нужен уникальный идентификатор, который может быть сгенерирован в любом месте без возможности конфликта, можно рассмотреть использование UUID или hash от строки (выбор конкретного метода зависит от целей и типа БД). Также PK поддерживает auto increment.

Foreign Key (FK) создает связь между двумя таблицами, ссылаясь на PK в другой таблице. FK обеспечивает целостность связей между таблицами и логически структурирует данные. Именование полей — это важный аспект проектирования БД, который способствует легкости в понимании структуры данных. Хороший тон в наименовании FK — использование стандартных сокращений и отражение связи с PK.

Unique Key (UK) – ограничение базы данных, которое гарантирует уникальность значений в столбце или группе столбцов. Это позволяет исключить дубликаты и обеспечить целостность данных. UK может содержать одно NULL значение.
При создании индексов, PK — это кластерный индекс, а UK — некластеризованный.

Резюме:
— PK: уникальный идентификатор для записей, обеспечивает единственность.
— UK: обеспечивает уникальность значений без строгой идентификации.
— FK: создает отношения между таблицами.
— В таблице может быть только 1 PK и несколько UK.
— Именование любых ключей должно быть осмысленным.

#databasedesign
1
NULL и агрегатные функции

И вновь разговоры про NULL. Особое внимание заслуживает работа с агрегатными функциями в выражениях, включающих NULL-значения.

NULL представляет собой отсутствие значения или неизвестное состояние, и поэтому добавляет сложности при взаимодействии с агрегатными функциями. Распространенной ошибкой является предположение, что NULL ведет себя как обычное значение. Но это не так!

Вернёмся к основам. Одним из фундаментальных аспектов SQL является трехзначная логика (3VL), различающая TRUE, FALSE и UNKNOWN. И при применении агрегатных функций к выражениям с NULL-значениями важно учитывать состояние UNKNOWN, которое вносит NULL. Ничего не понятно? 😁 Рассмотрим на конкретных примерах.

Предположим у нас есть таблица table_for_count:
column_1
49
60
NULL
12
50
11
NULL
5


select * from table_for_count

Результат: 8

select count(column_1) from table_for_count

Результат: 6 ☹️

В то время как COUNT(*) посчитает все строки, независимо от наличия значений NULL, COUNT(column_1) исключит в подсчёте строки, где указанный столбец равен NULL.

Аналогично, работают функции SUM, AVG, MAX и MIN. Я отдельно выделила AVG, чтобы вы обратили на него внимание.

table_for_avg:
column_2
1
NULL
2


select avg(column_2) from table_for_avg

Результат: 1.5 (в некоторых расчётах — это будет верным, но может быть и нет — учитывайте контекст запроса!)


Что делать?
Хорошая практика — использование функций COALESCE или NVL для замены NULL-значений на значения по умолчанию или определенные пользователем до применения агрегатной функции. Важно устанавливать значения осмысленно, иначе это внесёт ещё больше хаоса в результаты запроса.
Ещё один вариант — использование оператора CASE для создания условной логики и обработки NULL значений.
Выбор метода зависит от конкретной бизнес-задачи и, конечно же, СУБД (и не забывайте проверять план выполнения запроса для лучшего решения).

select count(coalesce(column_1, 0)) from table_for_count;

Результат: 8 😍

Отсутствие понимания взаимодействия агрегатных функций с выражениями, включающими NULL значения, может привести к появлению некачественной отчетности и интерпретации данных, что впоследствии подорвёт доверие ко всей аналитике. Поэтому будьте на чеку и учитывайте NULL-значения там, где это необходимо.

Другие заметки про работу с NULL-значениями можно найти по хэштегу 👇

#null
Please open Telegram to view this post
VIEW IN TELEGRAM
1
Forwarded from Осторожно, карьерные работы! (Simon Osipov)
Try to reserve judgement and observe. Ask a lot of questions. Be the dumbest person in the room. Instead of "Wow that's a dumb way of doing it" say "Huh that's not how I've done it in the past, what constraints led you to this design?"

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

И ты сразу успокаиваешься ❤️

@career_works
1
Explicit is better than implicit: не используйте SELECT *

Один из моих любимых принципов в The Zen of Python (PEP-20) — это "Explicit is better than implicit" (явное — лучше неявного). Он актуален не только для питона, но и для любого кода, документации и в целом жизни. Сегодня я хочу поговорить о примере использования этого принципа.

Часто для сокращения sql-кода используется конструкция SELECT *, которая выгружает информацию сразу из всех колонок. Согласитесь, если у вас таблица с 200 столбцами, то вместо того, чтобы писать колбасу из перечислений, куда проще просто поставить *. Ведь мы на 100% уверены, что все столбцы нужны.

Почему так делать не нужно?

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

2. Снижение производительности
SELECT * в запросах извлекает все столбцы из указанных объектов, включая те, которые не требуются. Это может значительно увеличить объем получаемых данных и снизить скорость выполнения запроса и его производительность (это особенно актуально для колоночного хранения).

3. Сложность в понимании логики запроса
Используя неявное указание, мы усложняем дальнейшую поддержку запроса другими людьми. Явное перечисление — это самодокументирующийся код с четким пониманием смыслов.

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

Для четкой понимании логики запроса, оптимизации запросов и упрощения рефакторинга всегда указывайте столбцы явно (разве что речь не идёт об ad-hoc запросах).

#sql
🔥21
Принцип DRY при написании документации

В разработке принцип "Не повторяйся" (Don't Repeat Yourself, DRY) является одним из ключевых для эффективности и качества итогового продукта. Его же можно использовать и в подготовке документации.

Основной смысл: каждая часть знания должна иметь единственное, непротиворечивое и авторитетное представление в рамках системы.

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

Мои советы по использованию этого принципа:

1. Внедрение шаблонов
Большинство документов подлежит стандартизации. Используйте шаблоны не только для сокращения бесполезного труда, но и для систематизации. Хорошо структурированный документ всегда понятнее и чаще всего оставляет меньше простора для явных ошибок.

2. Использование ссылок
Вместо дублирования информации, старайтесь использовать ссылки на уже существующие материалы. Тогда и исправлять придётся не по всему пространству, и концы найти будет проще.

3. Контроль версий
Системы контроля версий актуальны не только для кода и разработки, но и для поддержания информации в актуальном виде — мы всегда можем "откатиться" к старой версии или понять кто, что и когда изменял (и пойти к этому человеку с вопросами). Сейчас контроль версий есть в большинстве систем для работы с информацией (Notion, Confluence, Google Docs, etc).

Ключ к хорошей документации — в постоянном стремлении к упрощению и оптимизации процесса её написания. В конце концов, некачественная документация столь же бесполезна, как и её полное отсутствие.

#документация
🔥21
Группировка NULL-значений

Мы уже рассмотрели множество особенностей NULL, но это ещё не всё.
На очереди группировка данных, включающих NULL-значения.

Как думаете, будут ли NULL сгруппированы или каждый будет считаться самостоятельной единицей?

В этот раз, если вы читали мои предыдущие статьи, ответ может вас удивить. При использовании GROUP BY, строки с NULL в группирующем столбце объединяются в одну группу. Казалось бы NULL обозначает "неизвестное" значение и как мы можем его группировать? Но факт остаётся фактом, при агрегации все NULL считаются равными между собой и формируют единую группу.

Чтобы сделать это поведение более очевидным при аналитике, мы можем использовать функции COALESCE или CASE, чтобы заменить NULL на значение, которое ясно указывает на отсутствие данных, например, на 'Неизвестно'.

#sql #null
1
Ребята из Data Secrets выложили отличные карточки, рассказывающие про манипуляцию данными. Очень рекомендую ознакомиться, запомнить и не использовать визуальный обман при построении своих отчетов.
Ну и, конечно же, развивайте критическое мышление, чтобы и самим не попадаться на чужие уловки.
1
Pomodoro: когда время — наш союзник

Кто из нас не искал волшебный ключик к продуктивности? Часто кажется, что время начинает бежать быстрее, когда до дедлайна остается чуть меньше, чем ничего. И при этом отвлекающих факторов вокруг разом становится всё больше и мессенджеры разрываются от новых сообщений. Внимание расфокусируется, мы начинаем торопиться и совершать ошибки.

Что может повысить эффективность? Один из моих помощников — метод Pomodoro.

Идея проста — сосредоточенно работать по 25 минут, закрыв все отвлекающие чаты и поставив телефон в режим "Не беспокоить", затем делать короткий перерыв на 5-10 минут, а через несколько рабочих 25-минуток сделать большой перерыв в 15-20 минут. Эти интервалы названы «помидорами», по кухонному таймеру в форме помидора, который использовал создатель метода, Франческо Чирилло, когда был студентом.

Как это работает? Во-первых, 25 минут — идеальное время для сосредоточенной работы без отвлекающих факторов. Больше нашему мозгу уже сложновато. Во-вторых, короткие перерывы помогают отдохнуть и перезагрузиться, чтобы затем с новыми силами взяться за следующий «помидор». Не обязательно подчинять этому методу все 8 часов каждого своего рабочего дня — будьте гибкими.

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

А ещё, как и в случае с методом Утёнка о котором я писала ранее, Помодоро добавляет элемент игры в наш рабочий процесс. Как будто мы не просто выполняем задачу, а участвуем в каком-то захватывающем челлендже.

Если вы, как и я, иногда ощущаете спады эффективности, попробуйте метод Помодоро. Может оказаться, что именно эти 25-минутные интервалы — то, что нужно для буста вашей продуктивности 🚀

#soft_skills
Please open Telegram to view this post
VIEW IN TELEGRAM
1👍1🍾1
Традиционные методы загрузки данных всё ещё актуальны: полная, инкрементальная и дельта-загрузка в DWH

При организации работы хранилища данных важно выбрать оптимальный метод загрузки. Существует множество современных способов переноса данных из источников, например, Change Data Capture (CDC) или прямая передача данных без необходимости их временного хранения. Они предлагают продвинутые возможности для репликации данных в реальном времени с отслеживанием историчности. Но иногда данные нужно перенести быстро или в силу бизнес-требований нет необходимости использовать трудозатратные способы. Тогда мы выбираем традиционные методы репликации.

Полная загрузка — это перенос всех данных из источника (объекта) в хранилище за один раз. Каждый раз, когда нам нужно обновить данные, мы снова перезагружаем объект целиком. Этот метод прост и надежен, если объем данных невелик (н-р, это актуально для редко обновляемых справочников) или есть строгое требование к целостности данных (а других способов гарантировать её нет). Однако, с увеличением количества данных, полная загрузка становится всё более времязатратной и ресурсоемкой.

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

Дельта-загрузка, или загрузка с частичной перезагрузкой, — более продвинутый метод. Он включает в себя загрузку не только новых записей (Insert), но и обновление существующих данных (Update), а иногда и удаление устаревших записей (Delete). Дельта-загрузка требует сложной логики отслеживания изменений в источнике, что позволяет синхронизировать хранилище с актуальным состоянием данных с высокой точностью. Если, конечно, система-источник может предоставить необходимую информацию об изменениях.

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

Примеры объектов и вариантов их репликации:
1. Справочник стран (обновляется редко, небольшой объем) — полная загрузка
2. Логи (старые данные не изменяются, только приходят новые) — инкрементальная загрузка
3. Текущее состояние заказа (данные в полях обновляются, есть отслеживание изменений) — дельта-загрузка

Мой совет: всегда ориентируйтесь именно на ваши бизнес-требования, внимательно изучайте источник, а не просто слепо следуйте "лучшим практикам". Гибкость в выборе метода загрузки — ваш ключ к эффективному управлению данными.

#dwh
🔥21
Обзор традиционных методологий разработки в DWH: ищем свой путь в мире данных

Начнем с простого, Data Warehouse (DWH) — это специальные системы для хранения огромных объемов информации, собранной из различных источников. Она нужна для анализа и принятия обоснованных решений.

Разработка DWH — это сложный процесс, требующий глубоких знаний и опыта в области баз данных, а также понимания бизнес-потребностей. Существует несколько подходов, и каждый из них имеет свои особенности, преимущества и, конечно, ситуации, в которых он лучше всего работает. Эта тема очень обширная, поэтому сегодня рассмотрим традиционные методологии.

Методология Инмона
Уильям Х. Инмон считается одним из основателей концепции DWH. Он предложил создавать системы, где все данные будут храниться в одном месте, аккуратно организованы и легко доступны.

Основные принципы подхода Инмона:
Централизованное хранилище: все данные собираются в одном месте, что обеспечивает единый источник правды для всей организации.
Нормализованная модель: данные хранятся в нормализованной форме (3NF или выше), что минимизирует дублирование и обеспечивает высокую целостность данных.
Историческая точность: хранилище содержит исторические данные, что позволяет анализировать изменения во времени.
Больше времени и ресурсов на разработку: подход Инмона часто связан с большими затратами времени и ресурсов на начальном этапе из-за сложности интеграции и нормализации данных.

Методология Кимбалла
Ральф Кимбалл предложил более простой и понятный способ создания DWH, сосредоточенный на конкретных задачах бизнеса. Его идея в том, чтобы строить DWH по частям, используя схему "звезда" (поговорим об этом в отдельном посте). Этот подход позволяет быстрее запускать проекты и обеспечивает легкость внесения изменений.

Ключевые аспекты методологии Кимбалла:
Моделирование "звезда": используется денормализованная модель данных (таблицы измерений и фактов), что упрощает запросы и анализ.
Ориентация на бизнес-процессы: каждая схема строится вокруг конкретного бизнес-процесса, что облегчает разработку и понимание данных.
Быстрая доставка: методология подразумевает итеративную разработку и доставку, позволяя бизнесу быстро получать ценность от данных.
Гибкость в изменениях: Добавление новых данных или изменение существующих процессов проще в денормализованной среде.

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

Выбор между подходами зависит от конкретных потребностей и возможностей организации, а также от желаемой скорости реализации проекта и готовности к управлению изменениями.

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

#dwh
5
Используете USING? Тогда мы идём к вам

Ранее я писала про то, почему не стоит использовать SELECT *, а сегодня хочу поговорить об USING.

USING — ключевое слово, которое используется в SQL-запросах для упрощения синтаксиса объединения таблиц по одноимённым столбцам.

Например, вместо:
SELECT o.order_id, o.amount, c.customer_surname, c.customer_name
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id

Можно написать:
SELECT o.order_id, o.amount, c.customer_surname, c.customer_name
FROM orders o
JOIN customers c
USING (customer_id)

В этом случае USING объединяет таблицы по столбцам с одинаковыми именами, указанными в условии.

Почему так делать не нужно?
1. USING хоть и добавляет компактности, но убирает ясность в коде (это особенно заметно при множественном объединении таблиц), а следовательно снижает его поддерживаемость.

2. При использовании USING, столбец, по которому происходит объединение, возвращается в результирующем наборе только один раз. Какой из нужных? Звучит неоднозначно, особенно при использовании не только INNER JOIN. Это может создать сложности при необходимости разделения значений из каждой таблицы в дальнейшем анализе.

3. Изменения неизбежны в любой базе данных. Столбцы могут быть переименованы, добавлены или удалены. И при явном указании столбцов объединения, мы легко найдём ошибку. В случае же использования USING, могут возникнуть ситуации, когда будет переименовано несколько столбцов в разных таблицах и логика объединения нарушится, превратив результаты запроса в мусор.

4. Использование USING иногда может ограничить возможности оптимизатора выбирать наиболее эффективный план выполнения.

5. Особенности отдельны СУБД. Если JOIN ON работает предсказуемо, то использование USING может привнести сюрпризы.
Например, в Snowflake при определённых условиях множественные USING по одинаковым столбцам могут вообще игнорироваться. Совет: всегда изучайте документацию.

USING — это синтаксический сахар и его использование далеко не всегда оправдано.

Как я уже говорила ранее, помните, что явное всегда лучше неявного. Точные условия объединения с использованием ON делают зависимости между таблицами понятными, тем самым повышая стабильность, эффективность и надежность запросов, улучшая их масштабируемость.

#sql
1
Управление документацией: превращаем хаос в порядок

Мы много говорили о том, как писать документацию. Пора рассмотрет, как можно обеспечить удобное хранение и использование.

Ещё не так давно никто знать не знал о системах управления документацией и чаще всего все использовали Word (кучи файлов на сетевом диске) или Google Docs. Минус такого подхода очевиден: проблемы с поиском, навигацией и версионностью. Важна доступность, а не просто наличие документов.

Системы управления документацией, такие как Confluence и Notion, представляют собой комплексные платформы, позволяющие создавать, организовывать и делиться знаниями. Они обеспечивают не только хранение документов, но и управление проектами, интеграцию с другими сервисами и поддержку совместной работы. Уже сложно представить работу современной компании без общей базы знаний.

Confluence, часть экосистемы Atlassian, идеален для технических команд, которым нужны мощные функции для создания сложной документации и управления знаниями. Он предлагает интеграцию с JIRA и Bitbucket, что упрощает трекинг задач и управление кодом.

Notion выделяется своей универсальностью, гибкостью по доступной цене (плюс есть бесплатные версии). Он позволяет комбинировать заметки, базы знаний, канбан-доски и документы в одном месте. Это отличный выбор для маленьких и средних команд, которым нужен один инструмент для всего.

Выбирайте инструмент, исходя из нужд команды, важности интеграции с другими сервисами и предпочтений в работе друг с другом. Confluence идеален для крупных проектов, Notion — для гибкой работы над разнообразными задачами в командах малого и среднего размеров. Google Docs я предпочитаю использовать в случаях крайней необходимости. Место для хранения знаний должно быть централизованным.

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

#документация
1
Немного об оконных функциях

Рассказывать о простом SQL можно много и долго, но давайте перейдем к более глубоким темам. Например, поговорим об оконных функциях. И перед тем как перейти к деталям, выясним, что же такое окошки?

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

Окно — набор строк, который указанным образом связан с текущей строкой.


Оконные функции обрабатывают данные в рамках окна, которое задается с помощью параметров:
1. Разделение на группы — PARTITION BY.
2. Упорядочивание данных внутри каждой группы — ORDER BY.
3. Определение диапазона строк для группы, над которыми будет производиться вычисление (ROWS BETWEEN) — поддерживается не всеми оконками.

Ниже показан расчет средней зарплаты по отделам. Каждая строка результата будет содержать данные о сотруднике, его отделе, зарплате и средней зарплате по его отделу.

SELECT
employee_id,
department,
salary,
AVG(salary) OVER(PARTITION BY department) as department_salary_avg
FROM employees;

Оконные функции предоставляют возможности для решения большого количества различных аналитических задач, например таких как:
— Анализ трендов и изменений
— Вычисление всевозможных рейтингов (ранжирование)
— Расчет скользящего среднего
— Сравнение со смещением
— Всевозможные агрегации
— Расчет кумулятивных сумм
— Определение первого/последнего значения в группе
— ...

Окошки незаменимы для аналитиков данных, и крайне важно понимать принцип их работы. А если вы умеете использовать не только стандартные агрегаты и ранжирование, а также владеете фреймами, то это в разы повысит вашу ценность как специалиста.

Следите за каналом) в следующих статьях рассмотрим применение конкретных оконных функций под различные задачи.

#sql #оконные_функции
1❤‍🔥1
Противостояние CTE и подзапросов

В области анализа данных есть два классных инструмента — общие табличные выражения (CTE) и подзапросы, играющие важную роль в структурировании и обработке информации. Их правильное применение может значительно повысить эффективность ваших запросов.

CTE — это временные именованные наборы результатов, к которым можно обращаться внутри операторов SELECT, INSERT, UPDATE или DELETE. Они обеспечивают структурированный подход и делают код более читаемым и легким для анализа.

Плюсы:
— Повышают читаемость и структурированность кода.
— Их можно многократно использовать в одном запросе, сокращая повторения и облегчая отладку.
— Незаменимы для рекурсивных запросов и работы с иерархическими данными.

Минусы:
— Могут быть менее эффективны при неоптимальном использовании, особенно в больших базах данных из-за временного хранения результатов.

Пример:
WITH category_sales AS (
SELECT p.category,
SUM(s.quantity) AS total_quantity
FROM sales s
JOIN products p
ON s.product_id = p.product_id
GROUP BY p.category
)
SELECT p.product_name,
p.category,
SUM(s.quantity) AS product_quantity,
cs.total_quantity AS category_total_quantity
FROM sales s
JOIN products p ON s.product_id = p.product_id
JOIN category_sales cs ON p.category = cs.category
GROUP BY p.product_name, p.category, cs.total_quantity;

В этом примере в CTE category_sales вычисляется общее количество продаж товаров для каждой категории. Затем основной запрос использует эту информацию для вывода количества продаж по каждому продукту вместе с общим количеством продаж по его категории. Таким образом, мы можем увидеть как общие продажи по категориям, так и детальные продажи по каждому продукту. Это облегчает анализ эффективности продаж в разрезе категорий. Этот пример также можно реализовать с помощью оконных функций, но об этом в другой раз.
Чтобы поэкспериментировать самостоятельно, используйте песочницу https://sqliteonline.com и мой код для создания таблиц из вложения.

Подзапросы – это запросы, вложенные в другой запрос. Они предоставляют набор результатов для использования в основном запросе. Звучит сложно? На самом деле всё просто, сначала делается выборка по подзапросу, а результаты этой выборки используются для дальнейших вычислений.

Плюсы:
— Простота использования и понимания.
— Идеально подходят для извлечения данных в рамках ограниченного контекста.

Минусы:
— Ограниченные возможности повторного использования и рекурсии по сравнению с CTE.
— Могут привести к ухудшению читаемости кода при сложных вложениях.

Пример:
SELECT p.product_name, p.price
FROM products p
WHERE p.price > (
SELECT AVG(price) -- Вычисление средней цены по категории
FROM products
WHERE category = p.category
);

В этом примере подзапрос в WHERE-части вычисляет среднюю цену по категориям. Затем основной запрос сравнивает цену товара и выводит только ту, чья цена выше средней.
Чтобы поэкспериментировать самостоятельно, используйте песочницу https://sqliteonline.com и мой код для создания таблиц из вложения.

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

#sql
cte_and_subquery.txt
2.2 KB
Противостояние CTE и подзапросов
В статье выше я рассказываю про разницу между CTE и подзапросами.

Чтобы поэкспериментировать самостоятельно, используйте песочницу https://sqliteonline.com и мой код для создания таблиц из вложения.
1
2025/07/09 19:52:55
Back to Top
HTML Embed Code: