Как запросом убрать дублирующиеся данные?

Можно использовать ключевое слово DISTINCT, которое отфильтрует дублирующиеся значения из набора результатов, то есть вернёт только уникальные строки.

Пример выборки только уникальных id из таблицы:

SELECT DISTINCT id 
  FROM table_name;

Другой вариант — это использование GROUP BY, чтобы сгруппировать дублирующиеся значения.

SELECT id 
  FROM table_name
 GROUP BY id;

Что использовать и есть ли разница?

Если посмотреть план запросов выше, то можно увидеть, что в обоих случаях под капотом происходит группировка данных и скорость выполнения запросов одинаковая. Однако, для лучшей читаемости кода, DISTINCT стоит использовать там, где вы хотите убрать дублирование строк в результатах, а GROUP BY — для группировки в явном виде и дальнейшего использования агрегатных функций.

#sql

Я есть CRUD

Команды CRUD (Create, Read, Update, Delete) используются для работы с данными.

— CREATE - создание (insert - вставка данных)
— READ - чтение (select - выборка данных)
— UPDATE - изменение (update - обновление)
— DELETE - удаление (delete)

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

#sql

Доки должны быть качественными

Ранее я писала о важности документации, теперь пришло время поговорить о её качестве. Очевидно, что не вся документация хорошая и полезная. Чем более запутано и неструктурированно написан документ, тем больше проблем он принесёт при реализации.

При написании доков можно следовать чек-листу из требований к качеству.

Свойства хорошей документации:
☑ Осуществимость
☑ Полнота
☑ Краткость
☑ Непротиворечивость
☑ Атомарность
☑ Однозначность
☑ Понятность
☑ Приоритизированность
☑ Тестируемость

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

#документация

Сжатие данных в Greenplum

Опции хранения данных определяются на этапе создания таблиц.

Уровень сжатия данных:
— на уровне таблицы (table-level) — применяется ко всей таблице. Доступно для AOT-таблиц как со строковой (row-oriented), так и с колоночной (column-oriented) ориентацией данных.
— на уровне столбца (column-level) — применяется к отдельному столбцу. Позволяет использовать различные алгоритмы сжатия для разных столбцов одной таблицы. Этот тип сжатия доступен только для AOT-таблиц с колоночной ориентацией данных.

Независимо от уровня, на котором применяется сжатие данных, для его настройки можно использовать следующие параметры:
— compresstype – тип сжатия данных. Возможные значения: ZLIB, ZSTD и RLE_TYPE. По умолчанию используется значение none, при котором сжатие не применяется.
— compresslevel  – уровень сжатия данных. Уровни с наименьшими номерами соответствуют самой быстрой, но при этом наименьшей компрессии данных.

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

Пример создания AOT-таблицы с колоночной ориентацией и zstd-хранением:

create table [schema_name].<table_name>
             (<columns_list>)
with (appendoptimized = true,
      orientation = column,
      compresstype = zstd,
      compresslevel = 3
);

#greenplum

Быть или не быть. Важность формулирования вопросов.

Как часто бывало, что вы стопорились на каком-то моменте в решении задачи, никак не могли двинуться дальше, но, как только начинали задавать вопросы коллеге или другу, у вас в голове загоралась лампочка и находился ответ?

Со мной это случается постоянно 😅

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

Помните, что правильная формулировка уже содержит в себе половину ответа и часто истина открывается тогда, когда вы пытаетесь что-то кому-нибудь объяснить.

#soft_skills

Целочисленные типы в Clickhouse

Кроме привычного Integer, Clickhouse также поддерживает неотрицательные целые числа, которые представлены префиксом U.

Целое число:

Int8 - [-128 : 127] - 8 bit
Int16 - [-32768 : 32767] - 16 bit
Int32 - [-2147483648 : 2147483647] - 32 bit
Int64 - [-9223372036854775808 : 9223372036854775807] - 64 bit
...

Целое неотрицательное число:

UInt8 - [0 : 255] - 8 bit
UInt16 - [0 : 65535] - 16 bit
UInt32 - [0 : 4294967295] - 32 bit
UInt64 - [0 : 18446744073709551615] - 64 bit
...

Исходя из названия понятно, что UInit используется там, где значения никогда не могут быть отрицательными.

Что важно понимать? Прямое преобразование из UInt в Int чаще всего невозможно, так как Int не вместит в себя весь диапозон UInt. Например, в строке типа UInt8 могут содержаться числа от 0 до 255, в то время как в строке типа Int8 от -128 до 127. Аналогично и с обратным преобразованием, поэтому нужно быть внимательным при переносе данных между разными источниками или внутри БД. Ну и, конечно, всегда изучать не только типы столбцов, но и сами данные и их бизнес-смысл.

Подробнее об ёмкости типов в доках CH.

#clickhouse

Качества документации: ТЗ должно быть полным, но кратким

Всем известна фраза "краткость — сестра таланта" и она отлично ложится на написание документации. Сплошную стену текста одним абзацем с пространными рассуждениями никто читать не будет или будет, но по диагонали и крайне невнимательно. Что обязательно скажется на скорости и качестве разработки, либо её поддержке.

Всегда цените своё время и время коллег.

ТЗ должно содержать минимум воды, только технические факты. Но шутка в том, что писать кратко =/= писать мало. В ТЗ не должно быть моментов "а вот это итак очевидно, указывать не буду". То, что очевидно сегодня, будет никому неизвестно завтра, когда придёт другой сотрудник или что-то просто забудется.

Поэтому важно соблюдать баланс между многабукаф и "ничего не понятно".

Лайфхаки:
— Проработайте шаблоны под типичные для компании типы ТЗ.
— Логику процессов прописывайте пошаговым списком.
— Вместо текста, там где это возможно, используйте таблицы, графики, майнд-карты.
— При написании ТЗ ставьте себя на место разработчика. Всё ли вам понятно?
— Используйте заголовки и оглавление.
— Выделяйте важный текст и не бойтесь подчеркиваний. Но без фанатизма. Система выделений и цветов не должна пестрить и должна быть понятной.
— Дописав, сделайте паузу на чай и/или другую задачу. Вернитесь и перечитайте.

#документация

Партиционирование (partitioning) в Greenplum

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

Партицировать маленькие таблицы не имеет смысла!

Партиционирование может быть указано только при создании таблицы, однако удалять/добавлять/изменять партиции в дальнейшем можно. Чтобы сделать добавить партиционирование в таблицу, нужно сделать новую таблицу с партициями и перенести данные из непартиционированной.

Пример создания партиционированной таблицы:

create table [schema_name].<table_name>
             (<columns_list>)
[with (<storage_options>)]
distributed <distribution_policy>
partition by <partition_spec>;

Важно! Загрузка данных в партиционированные таблицы крайне неэффективна. Поэтому рекомендуется загружать данные в промежуточную (staging) таблицу и затем применять к партиционированной таблице команду EXCHANGE PARTITION.

#greenplum

Isolation — Изолированность

Изолированность отвечает за то, что транзакции не должны оказывать влияния на другие параллельные транзакции.

Большинство БД поддерживает 4 уровня изоляции:
1. Read Uncommitted
2. Read Committed
3. Repeatable Read
4. Serializable

Чем ниже уровень, тем слабее изоляция, но тем меньше тратится ресурсов.

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

#sql #acid

Шпаргалка: NULL и логические операции

Я уже рассказывала про сравнение с Null, но это не все особенности работы с ним, которые важно знать при анализе данных.

Null — третье логическое значение (кроме True и False), оно обозначает неизвестность. Исходя из этого определения легко вывести следующее:

Null AND True = Null
Null AND False = False
Null AND Null = Null

Null OR True = True
Null OR False = Null
Null OR Null = Null

NOT (Null) = Null
NOT (NOT(Null)) = Null

Во всех случаях стоит помнить, что Null в результатах не равен Null в условии. Это несравнимая неизвестность.

#sql #null

Data Governance или забота о данных

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

Data Governance — это как следить за порядком в своей комнате, только в мире данных.

Почему это важно?
— Это помогает сохранить данные в порядке. Хаос в данных может привести к неверным выводам. Data Governance говорит нам, как следить за чистотой данных.

— Это помогает нам соблюдать законы. Мы должны защищать данные и соблюдать законодательство той страны, в которой работаем. Data Governance учит нас, как это делать.

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

Data Governance — это организационный фундамент, на котором строится надёжная, безопасная и эффективная работа с данными.

#data_governance

Не бойтесь "глупых" вопросов

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

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

Конечно, это не значит, что нужно бегать за каждым забытым хоткеем по чатам. Сначала всё-таки сходите в старый добрый гугл и приложите хотя бы минимальные усилия на поиск ответа.

Сначала думаем, потом формулируем, гуглим, затем спрашиваем.

#soft_skills

Группировка данных в SQL: суть и применение

Группировка данных — это инструмент анализа в SQL, который позволяет агрегировать данные для получения ценных инсайтов. Давайте разберемся, как это работает.

GROUP BY — основной метод группировки данных. Он позволяет сгруппировать строки в результатах запроса по значениям в одном или нескольких столбцах. Например, вы можете группировать продажи по датам и/или по категориям товаров. Это особенно полезно для создания агрегированных отчетов.

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

SUM() — для суммирования числовых значений в группе, таких как общая сумма продаж по категории товаров.

AVG() — для вычисления среднего значения числовых данных в группе. Например, средний размер заказа.

MAX() и MIN() — определяют максимальное и минимальное значение в группе. Это может быть полезно, например, для определения самой дорогой или дешевой покупки в каждой категории товаров.

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

Пример группировки, который выводит среднюю цену товара в каждой категории с фильтрацией по среднему:

SELECT category, AVG(price) as average_price
FROM products
GROUP BY category
HAVING AVG(price) > 100;

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

Группировка данных полезна для создания сводных таблиц, отчетов и анализа больших объемов информации. Она помогает выделить общие закономерности и тренды, что делает ее неотъемлемой частью работы с данными в DWH.

#sql

Виды партиций в Greenplum

— partition by range – осуществляет разделение данных на основе числовых или временных (date/timestamp) диапазонов. Интервалы для партиций указываются используя ключевые слова START и END. Выражения INCLUSIVE и EXCLUSIVE используются в связке с START и END для указания того, должны ли попадать в соответствующий диапазон граничные значения. По умолчанию значения, указанные с помощью START, включаются в диапазон; значения, определенные с помощью END — нет. Партиции можно указывать как автоматически, так и вручную.

Автоматически:

create table [schema_name].<table_name>
             (<columns_list>)
[with (<storage_options>)]
distributed <distribution_policy>
partition by range(<column name>)
     (partition monthly start (date 'ХХХХ-ХХ-ХХ') inclusive end (date 'ХХХХ-ХХ-ХХ') exclusive every (interval '1 month'));

Вручную:

create table [schema_name].<table_name>
             (<columns_list>)
[with (<storage_options>)]
distributed <distribution_policy>
partition by range(<column name>)
     (partition Nov23 start(date '2023-11-01') inclusive,
      partition Dec23 start(date '2023-12-01') inclusive end(date '2024-01-01') exclusive);

— partition by list – на основе списков значений.

create table [schema_name].<table_name>
             (<columns_list>)
[with (<storage_options>)]
distributed <distribution_policy>
partition by list (pet)
    (partition cats values ('Cat'),
     partition dogs values ('Dog'),
     default partition other);

#greenplum

DWH и анализ данных

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

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

Некоторые составляющие анализа:

1. Понимание данных и их структуры (здесь поможет data-каталог или иная качественная документация к хранилищу).

2. Формулирование вопросов к бизнесу, процессам и данным. Например, "Какие продукты наиболее популярны у клиентов и есть ли зависимости от дня недели и времени суток?" или "Какие маркетинговые кампании приводят к наибольшим продажам и что между ними общего?".

3. SQL. Без этого инструмента (пока что) никуда.

4. Визуализация данных. Графики и диаграммы помогают наглядно представить результаты анализа и лучше понять данные.

5. Обнаружение паттернов, тенденции и аномалии может помочь в принятии решений, определении стратегии и оптимизации процессов.

6. Прогнозирование на основе данных из хранилища и разработка прогностических моделей.

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

#dwh

Дайджест статей за сентябрь

Лучше поздно, чем никогда :)

Общее о профессии и DWH:
Системный аналитик — кто он?
Насколько большая эта ваша Big data? 
Коротко о DWH
Data Lakehouse
Зачем нам нужен ETL?

БД:
ACID: atomicity, consistency, isolation and durability
Atomicity — Атомарность
Consistency — Согласованность
CHAR и VARCHAR — что будем использовать? Или всё-таки TEXT?
Почему UUID лучше, чем автоинкрементные идентификаторы. И лучше ли?

Немного о доках:
Без внятного ТЗ и результат ХЗ

ClickHouse:
Коротко о ClickHouse
Основные отличия ClickHouse от других MPP-систем
Шардирование данных в ClickHouse

Greenplum:
Коротко о Greenplum
Хранение данных в таблицах в Greenplum
Распределение (distribution) в Greenplum

Разное из SQL:
NULL != NULL — это True?
ALTER и UPDATE в SQL. В чём разница?

#дайджест

Хороший тон партиционирования в Greenplum

— Делить на партиции нужно только большие AOT-таблицы.
— Не нужно разбивать таблицы на очень маленькие партиции.
— Ключ партиционирования должен использоваться в запросах в условии where.
— Ключ должен позволять разбить таблицу на примерно одинаковые части.
— Таблицы, созданные с использованием политики распределения данных DISTRIBUTED REPLICATED, не могут быть партиционированы.

#greenplum

Метод '5 почему' поможет раскрыть истинные потребности бизнеса

Поговорим о том, как понять, что именно нужно бизнесу, когда он приходит с вопросом "хочу того — не знаю чего". 

Рассмотрим сценарий: продуктовый аналитик требуют какие-то метрики, основанные на данных, которые вроде бы есть, а вроде и нет в хранилище. Как удовлетворить потребности максимально точно? Есть метод, который помогает раскрывать настоящие потребности заказчика. Этот метод — "5 почему".

Суть метода заключается в том, чтобы понять, что именно бизнес хочет получить задавая вопросы. Конечно, не обязательно спрашивать именно "Почему". Вместо этого, перефразируем вопросы в соответствии с запросом заказчика. 

Возможные шаги:
— Определяем запрос бизнеса. Просим заказчика ясно сформулировать, что именно ему нужно.
— Задаем вопросы, которые позволят выявить детали запроса. Подходим к делу креативно.
— Создаём цепочку из вопросов (не ставим строгую планку "пять", как в оригинале метода). Постепенно детализируем каждый аспект запроса, записывая ответы, чтобы лучше видеть картину. При сложных запросах здесь классно поможет использование майнд-карт.
— Проверяем формулировку вопросов на точность. Если вдруг застреваем на каком-то этапе, перефразируем вопросы.
— При необходимости привлекаем других заинтересованных лиц. Разнообразные точки зрения и мнения — это сила.

Так благодаря системному анализу и методу "5 почему," построение DWH становится более точным и эффективным, помогая бизнесу получить от данных то, что действительно нужно.

#soft_skills #системный_анализ

Множественные комбинации: перекрёстное соединение в SQL

Мы ещё не затрагивали тему соединений, которая является очень важной и обширной. И начну я, пожалуй, с одного из наиболее редких соединений. Это CROSS JOIN. Несмотря на его не частое использование, важно понимать как он работает.

По своей сути cross join — это декартово произведение. Каждая строка таблицы A объединяется с каждой строкой таблицы B. Число строк набора результатов будет равно произведению количества строк таблиц, которые будут объединены. Это отличный инструмент для создания всевозможных комбинаций.

Синтаксис:
SELECT id_a, id_b
FROM table_a
CROSS JOIN table_b;

Аналогично сработает:
SELECT id_a, id_b
FROM table_a, table_b

Давайте рассмотрим как будет выглядеть результат перекрёстного объединения двух таблиц:
table_a
1
3
NULL

table_b
1
2
NULL

Результат:
1 1
1 2
1 NULL
3 1
3 2
3 NULL
NULL 1
NULL 2
NULL NULL

Обратите внимание на то, как произошло объединение по NULL — это полноценное значение поля и забывать о нём не нужно.

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

#sql #join