Как ускорить ClickHouse?

Обсуждали с одним де перформанс систем и связку Trino + ClickHouse. Моя первая мысль - серьезно? КХ и так быстрый, куда еще к нему Trino накручивать?? Моя вторая мысль - тестим! Дисклеймер - мои тесты не претендуют на супер истину, но дают определенное представление

💻 Я взяла ноут, подняла кх с 1 нодой и трино с кх каталогом. Создала тестовые таблички с 1к, 10к, 100к, 1млн, 10млн строк. Больше уже не отрабатывало

Придумала такой запрос:

with cte as (
  select
    id,
    concat(cast(id, 'String'), '_', cast(id, 'String')),
    sum(id),
    row_number() over (order by id) as rn
  from test.tbl1000
  group by 1, 2
)
select * from cte
where rn in (14, 49)
order by rn desc

Тут есть работа со строками, агрегация, оконка и т.д. - поэтому тестим в какой-то мере тяжелую операцию

Запрос я запустила по 50 раз на каждом размере данных для кх и трино. Потом так же погоняла insert моей cte в таблицу

Изначально сходила в system.query_log и посчитала длительность, строки, байты, память в среднем по всем запускам. Потом поняла, что для трино они будут отражать только кусок работы в кх, поэтому сравнение будет некорректным

По итогу решила сравнить время: query_duration_ms vs elapsedTime

Какие выводы я могу сделать:

1. Трино поверх КХ проигрывает КХ. Так получилось при моих данных и в моих условиях. Хотя есть мнение, что на большом объеме данных он работает быстрее бд (~100гб)

2. При insert трино проигрывает еще значительнее, потому что сначала пишет данные во временную таблицу, потом перекладывает в основную:

INSERT INTO "test"."result1000" ("id", "str", "sum_", "rn") SELECT "id", "str", "sum_", "rn" FROM "test"."tmp_trino_c5cb30af" temp_table

3. В query_log по Трино не собирается часть метрик: user_aggregate_functions, used_functions, used_data_type_families. А также отличаются result_rows, query. Если это важно для мониторинга, то использовать не получится

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

SETTINGS log_comment = 'test'

Но в трино нет такого функционала, так что многие необходимые кликхаусные штучки просто не будут работать

@data_engineerette

Понедельничная мантра

Всем прекрасной рабочей недели🐾

Как движки вас газлайтят?

Расскажу историю, которая на днях прозошла на нашем кластере

💋Что надо сделать? Создать табличку и запустить пайплайн заливки данных. Казалось бы - что может быть проще?

1. Создаем таблицу в Trino:

CREATE TABLE iceberg.raw.first_table (
    my_beautiful_column varchar
)
WITH (
   format = 'PARQUET',
   format_version = 2
)

2. Запускаем пайплайн - создается веточка:

ALTER TABLE raw.first_table
CREATE OR REPLACE BRANCH my_branch

SELECT *
FROM iceberg.raw."first_table$refs"
--main      BRANCH
--my_branch BRANCH

Пайплайн падает, не успев записать данные. Но я правлю ошибку и перезапускаю - теперь все прекрасно 🙌 Ужастики так-то тоже начинаются с размеренной жизни

Но в один момент я создаю таблицу через Spark:

CREATE TABLE raw.second_table (
    my_beautiful_column STRING
)
USING iceberg

Снова запускаю пайплайн, и он снова падает. Я правлю ошибку и перезапускаю...

Exception in thread "main" java.lang.IllegalArgumentException: Cannot complete replace branch operation on raw.second_table, main has no snapshot

Что случилось? Таблица точно такая же, код точно такой же. Просчитался, но... где? 🤔

😑 Тут начинаются раскопки. Создаю табличку через трино и через спарк, иду на s3 смотреть файл с метаданными

Оказывается, что трино создает отдельный снепшот с добавлением 0 строк:
🤍"current-snapshot-id" : 677178324561195060
🤍таблица raw.first_table.history имеет 1 строку

А спарк такого не делает - он их создает, только когда данные меняются, компактятся:
🤍"current-snapshot-id" : -1
🤍таблица raw.second_table.history пустая

Теперь идем в репку айсберга, а там такое:

if (create && replace && !refExists) {
  safeCreateBranch()
} else if (replace) {
  Preconditions.checkArgument(snapshotId != null,
  "Cannot complete replace branch operation on %s, main has no snapshot", ident)
  manageSnapshots.replaceBranch(branch, snapshotId)
}

🩵Какой ход событий получается:

1. Попадаем в условие if: create есть, replace есть, ветки еще нет. Ветка создается
2. Пытаемся записать данные в ветку
3. Код падает, а ветка остается(!)
4. Перезапускаем и в условие if уже не попадаем - ветка уже есть
5. Попадаем в else if (replace) - вспоминаем, что через спарк никакой снепшот не создается, поэтому snapshotId у нас null

➡️ Как решаем проблему

Перед созданием ветки нужно дропнуть существующую, если вдруг пайплайн упал и не успел почистить ветку:

ALTER TABLE raw.second_table
DROP BRANCH IF EXISTS my_branch

Либо добавить catch/except, который дропнет ее в самом конце

Общий вывод такой: при работе с разными движками любой кусок может работать по-разному. Даже самая простейшая штука, которую вы не ожидали🥺

@data_engineerette

SQL Advent. Day 1

Сегодня стартовал адвент по sql! К сожалению, орг прошлогоднего адвента решил в этом году его не делать 😪
Но я нашла еще один!

🤩всего 24 дня
🤩задания открываются в 17:00 по мск

Первый день супер легкий, погнали вместе проходить)

📍 Полезные ссылки

Адвент календарь (с впн)

Мои решения

SQL Advent. Day 2

Сегодня задание на джойн)

Заметила, что ллмка анализирует твое решение и пишет в чат моменты на улучшение:

To make sure you only get unique toy IDs and names (in case a toy was delivered multiple times), you might want to consider using DISTINCT. Also, ordering the results can make the output easier to read.

📍 Полезные ссылки

Адвент календарь

Мои решения

SQL Advent. Day 3

Уровень hard - уже похоже на задачки с собесов. На скрине одна из таких - когда нужно найти топ по каждой категории

📍 Полезные ссылки

Адвент календарь

Мои решения

Тимлидский митап

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

1️⃣ "Поедатели времени команды"

Этот доклад я слушала особенно внимательно, он в меня попал на 100%. Я выделила по несколько моментов, которые особенно меня зацепили

➡️ затянутые стендапы

➡️ постоянное переключение контекста

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

Сюда еще относится слишком много экспертизы в одних руках - это забавно)) А также несколько встреч с перерывом в 30мин/1ч. В таких случаях я чувствую, что надо и водички попить, и на сообщение ответить, и задачку доделать, и вопросы к новой встрече просмотреть, и подготовить свою часть - и вот уже надо снова брать наушники и подключаться. А время-то куда делось?

Из особо полезного:
- договориться о фокус-времени - вся команда работает с 11 до 14 и друг друга не отвлекает
- просматривать входящие по расписанию - раз в 1-2ч, а не сразу

➡️ проблема неделегируемых задач

- bus factor - что будет, если дать задачу другому?
- составить список уникальных умений и людей, выбрать наследника 👑

➡️ долгие обсуждения и затянутые ревью

- если много вопросов, делить на несколько встреч
- ограничить размер PR
- нечетное количество ревьюеров - чтобы одно из полярных мнений подкрепил кто-то еще
- SLA на время реакции на ревью

Мне особенно понравился последний совет. Людям может быть лень смотреть, когда есть и свои задачи. SLA прошел, все: молчание - знак согласия 👍

2️⃣ "Как удобрять инженера, чтобы вырос крепкий лид"

Тут 2 проблемные ситуации:
🤩Есть желающие -> нет ставки
🤩Нужен лид -> нет желающих

Сначала нужно договориться, кого отдаем, какие задачи делегируем (1:1, решение проблем команды, отслеживание метрик, отчеты по работе). После определенного срока собираем ОС. В итоге сняли немного инженерных задач, накинули менеджерских

Растить лида внутри или брать снаружи?
🤩Сложные задачи, кризис-менеджмент - опытный с рынка/из соседних подразделений
🤩Есть время на сопровождение, обучение - свой

Также спикер упомянула про ресурсы для взвращивания лида. Я попросила ссылочку

3️⃣ "Собираем Dream Team"

Про работу с рекрутером и как ускорить процесс найма. Картинка взята из этого доклада

4️⃣ "Сообщество как ресурс руководителя"

Как сообщества ускоряют онбординг, повышают компетенции, облегчают работу лида, унифицируют стек и процессы

Youtube
VK

@data_engineerette

Data Value Design Patterns

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

🎨 Data Enrichment

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

Pattern: Static Joiner - обычный джойн двух таблиц
Pattern: Dynamic Joiner - джойн двух потоков с временным окном

🤩 Data Decoration

Бывает нужно добавить к нашим данным дополнительные поля, и вот что предлагается

1️⃣Pattern: Wrapper

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

Мы такой подход используем. Но если требования часто меняются, то я бы все доп поля вынесла в один struct/json

2️⃣Pattern: Metadata Decorator

Мы хотим делать все то же самое, но не показывать пользователям технические поля. Тогда:

🤩Для бд создать вьюшку без тех полей или запретить чтение определенных столбцов на уровне прав
🤩Для s3 есть штука с тегами, их можно навесить на весь файл. Теги - это пара ключ-значение: mytag=myvalue. Как работать с тегами можно полистать тут
🤩Для отправки в кафку писать в header

🌸 Data Aggregation

Как под капотом происходит агрегация данных

1️⃣Pattern: Distributed Aggregator

Это про шаффлы в распределенных системах. В контексте шафла важно учитывать data skew. С ним можно бороться, например, такими способами:

🤩добавить соль, т.е. мы подмешиваем рандомное значение, чтобы равномернее распределить поле

dataset.withColumn("salt", (rand() * 3).cast("int"))
.groupBy("group_key", "salt").agg(...)
.groupBy("group_key").agg(...)

🤩включить AQE - адаптивку в спарке. Ниже полезные конфиги, с которыми мы работаем:

spark.sql.adaptive.enabled - включает адаптивку

spark.sql.adaptive.advisoryPartitionSizeInBytes - объединяет мелкие партиции или разделяет крупные

spark.sql.adaptive.skewJoin.enabled - разделяет партиции в джойнах

spark.sql.adaptive.skewJoin.skewedPartitionFactor - кэф для определения крупной партиции

spark.sql.adaptive.skewJoin.skewedPartitionThresholdInBytes - то же самое, только в байтах

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

External Shuffle Service в Spark как раз хранит данные шафла отдельно. Когда посчиталось, ресурсы ноды могут отправиться другому приложению, потому что данные шафла в безопасности

2️⃣Pattern: Local Aggregator

В контексте спарка мне показалось, что это про broadcast

Дальше в главе рассказывалось, как строить витрины с сессиями пользователей, про особые виды сортировок - думаю, к этому можно будет вернуться, когда реально понадобится, для себя пока не вижу применения

#depatterns

SQL Advent. Days 4-7

✨ Решила объединить, потому что в уровне easy (4 и 7 день) нужно написать всего по 3-4 строчки

✨ День 5 потребовал от меня несколько попыток, потому что условие было неполным. Пыталась ориентироваться на подсказки, но они как будто уводили меня еще дальше))

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

coalesce(max(return_date), 'Still resting')

✨ День 6 потребовал разложить горнолыжки по 4 бакетам в зависимости от уровня снега. По-моему, впервые применила функцию ntile:

ntile(4) over (order by total_snowfall_inches)

📍 Полезные ссылки

Адвент календарь

Мои решения

Мысли с матемаркетинга

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

🤩"Эволюция аналитических платформ в XXI веке"

"Если ваша команда дата инженеров ковыряет лейкхаус, то ваш стек отстал года на 4. А если нет - то, возможно, и на все 10"

"Строить лейкхаус-платформу - это все равно, что построить заново СУБД"

В своих заметках еще нашла такую подходящую:

"Когда новая технология накатывает на вас как асфальтоукладчик и вы не у него за рулем, вы становитесь частью дороги"

Еще спикер упомянул про агентскую платформу от Databricks и The Data Engineering Agent от Google

Меня немножко задемотивировало, а вы что думаете? Мы тут все отстающие или стабильные? 😁

🤩"Как найти и как себя вести с сотрудниками, которые не перформят"

Социальная лень - когда один человек понимает, что ответственность размазана по коллективу, и снижает планку => все снижают планку

Токсик продуктивити - много шума, реального результата мало

Когда человек не перформит, есть несколько вопросов к себе на подумать:

🤩Если бы человек ушел - вы бы обрадовались или расстроились?
🤩Сколько денег дают на новую вакансию?
🤩Ок, если 3-4 месяца будем искать?
🤩А если уже на деньги мидла можно найти только джуна?
🤩Что думают доверенные сеньоры про онбординг нового человека?

Видела как-то цитатку, но не могу ее найти. Вольная трактовка такая: если в нашей команде человек не перформит и хочет уйти, то мы отпускаем с радостью, чтобы он приносил пользу в другом [более подходящем] месте

А когда мы отбирали кандидатов на собес или решали по офферу, то руководствовались принципом: сомнение = нет

🤩"Сложные кейсы: как тимлиду грамотно расстаться с неэффективным сотрудником"

Если вы руководитель, то будьте готовы, что вас постоянно записывают. Сюда относятся записи из онлайн встреч, экспорт чата, личные чаты, диктофонные записи. Получается, работа лида - это как разведка, ничего лишнего болтать не надо 🤭

Если вдруг у сотрудника отрубили доступы или перестали ставить задачи - то это необеспечение работой и принуждение к увольнению. А это уже нарушение закона 🤓

🤩"Новый потребитель: как появился сегмент, которого не должно было быть"

Тут рассказывали про покупателей с "неадекватным" поведением в 2022-2024 в отдельных регионах. Они внезапно получили много денег, которых у них никогда не было, и начали покупать то, что никогда не покупали

Еще рассказывали про другой подход к ритейлу - если раньше в магазинах брали много, то сейчас люди делают несколько заказов по 100 рублей (я сама так делаю, хотя чувствую, что неразумно)

🤩"Виртуальная очередь в Яндекс Go: как мы сделали поиск такси предсказуемым в пике"

Этот доклад про оптимизацию, когда много людей уезжает одновременно со стадиона/из театра

Раньше было как:
- первый человек делает заказ
- остальные 100 человек делают заказ на сантиметр ближе к водителю
- первый человек ждет до талого, пока ближайшие не разъедутся

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

Лайк за доклад, очень все понятно и применимо 👍 А так большинство докладов уже совсем не метчится с моими интересами🥲

Стартанул новый Advent of SQL!!!

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

Мне оочень понравился формат - они записывают видосы с заданием! И какой же харизматичный спикер, какой у него красивый акцент 🥰🥰

К видосу прилагается файл с данными. Данных прям много, около 500к строк для одной таблицы. Я как раз на днях перешла на DataGrip и теперь полноценно работаю с ним. Там можно просто тыкнуть SQL Scripts -> Run SQL Script... и выбрать скачанный файлик

У них также есть своя песочница, поднимается за 30 сек со всеми данными

Задание прикольное и приближенное к жизни: надо почистить и нормализовать столбец с новогодними желаниями. Правда, сабмиттить пока никуда не надо, потому что решения - это вторая часть видоса

🤩всего 15 дней
🤩задания открываются в 21:00 по мск

📍 Advent of SQL (с впн)
📍 SQL Advent Calendar (с впн)
📍 Мои решения

Advent of SQL. Days 2-3

📺 Видосы с каждым днем становятся длиннее, вчера было уже почти 12 минут! Задача была на использование ilike - это регистронезависимый like, in, case when. Не думала, что update/delete можно применять, но в этих задачках можно... Главный совет - обязательно перепроверить себя селектом перед изменением данных

🏴󠁧󠁢󠁥󠁮󠁧󠁿 Решила вести словарик с новыми фразами. Так что фразы дня:

🤩rowdy children - шумные дети
🤩interstitial break - промежуточный перерыв
🤩repentant kids - раскаивающиеся дети
🤩yoink - восклицание, когда человек что-то быстро берет

И мне особенно понравились такие комментарии автора:

Я мог бы написать where tag = '' or tag = '', но я не пещерный человек

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

Про конструкцию count(*) FILTER (WHERE ...) в Postgres:

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

📍 Advent of SQL (с впн)
📍 SQL Advent Calendar (с впн)
📍 Мои решения

@data_engineerette

Advent of SQL. Day 4

Задачка на приведение данных к единому формату и применение like, ilike, case when, union

В одном из case мне нужно было сделать assigned_task ilike '%snow%' or assigned_task ilike '%shovel%', но это так скучно)) Поэтому я пошла гуглить другие варианты, нашла такие:

where assigned_task ~* 'snow|shovel'

where lower(assigned_task) similar to '%(snow|shovel)%'

where assigned_task ilike any (array['%snow%', '%shovel%'])

where assigned_task ilike any('{%snow%,%shovel%}')

Раньше я пользовалась только ~~ вместо like. Но оказалось, для остальных тоже есть свои символы. not ilike - это вообще что за демон?

like        ~~
ilike       ~~*
not like    !~~
not ilike   !~~*

А также я узнала нагуглила функцию initcap. В одной из таблиц данные уже были в едином формате, их нужно было привести к другому: stage_setup -> Stage Setup. initcap как раз делает то, что нужно: она каждое слово в строке пишет с заглавной буквы:

select initcap(replace(role, '_', ' ')) as role

🏴󠁧󠁢󠁥󠁮󠁧󠁿 Фразы дня

wonky list/space - кривой список/пробел
volunteer roster - список/расписание волонтеров
handwarmer handout - раздача грелок для рук
studly case = pascal case = StudlyCaps - название в формате MyClassName
strong suit - сильная сторона

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

📍 Advent of SQL (с впн)
📍 SQL Advent Calendar (с впн)
📍 Мои решения

@data_engineerette

Advent of SQL. Days 5-6

На пятой задаче было топ-3 через row_number и cte - мы с подобной штукой уже встречались ранее:

row_number() over(partition by user_name order by count(1) desc) as rn

😇Но на 6й день я немного потерялась... Долго не могла понять суть задачи) Вот есть таблица с датами отправки подарков и надо найти даты, на которые забыли записать доставку подарков. Но откуда я должна взять даты, которых нет??

Перечитала summary дважды и посмотрела дважды видос. Решила послушать подсказку - спикер посоветовал использовать "эзотерический" джойн. И почему моя первая мысль была про asof join, я даже пошла гуглить примеры 😁

Потом решила, что не может быть такого задания, когда в предыдущем мы всего лишь применяли оконку🥺 В итоге формула решения такая: generate_series + cross join + left join

📍 Advent of SQL (с впн)
📍 SQL Advent Calendar (с впн)
📍 Мои решения

Advent of SQL. Days 7-8

☕️ На 7й день мы распределяли посетителей по разным какао-станциям на основе их вкусовых предпочтений. Для этого нужно было вспомнить про пересечения и агрегацию массивов

Получилось такое условие джойна, оно возвращает true, если массивы пересекаются:

left join available_cocoa_cars c
  on p.favorite_mixins && c.available_mixins

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

select
  passenger_name,
  array_agg(c.car_id order by c.car_id)
from ...
group by passenger_name

🌄 На 8й день произошло целое открытие для меня. Все это время существовала концепция named window, а я ни разу ее не видела??😱

Мы определяем окно в самом конце запроса типо как cte и можем переиспользовать название окна в расчетах столбцов:

select
  product_id,
  price as product_price,
  lead(price) over w as previous_price,
  row_number() over w as rn
from price_changes
window w as (
  partition by product_id order by effective_timestamp desc
)

Причем этот синтаксис работает просто везде: PostgreSQL, MySQL, ClickHouse, Spark SQL, Oracle, Vertica... Что? 😍 Почему это так удобно, но нигде не используется?

📍 Advent of SQL (с впн)
📍 SQL Advent Calendar (с впн)
📍 Мои решения

Хотите побыть Дедом Морозом?

Новый год - это пора чудес✨ И сегодня вы можете помочь исполнению новогоднего чуда!

У нас в ламоде проводится конкурс мерча. И если вы хотите, чтобы его получила я - то буду благодарна за вашу подписку 🙂🙂

А вообще в канале рассказывается много чего интересного:

🤩статьи на хабре
🤩записи с выступлений
🤩внутренние новости
🤩карточки с полезностями
🤩мемасики
🤩а также проводятся новогодние розыгрыши - сейчас есть активный до 22го декабря🎄

Advent of SQL. Day 9

🗓️ День 9: нужно вытащить из вложенного json нужные поля. Я так и не могу запомнить, как правильно это делается в постгре, поэтому делала по интуиции) Заодно познакомилась с новыми функциями

Как получилось у меня:

select
  order_data['gift']['wrapped']::boolean as gift_wrapped,
  trim(both '"' from order_data['risk']['flag']::text) as risk_flag
from orders

Я сделала в питонячем виде, и это сработало👍 Сначало было просто order_data['risk']['flag'], но мне не понравились лишние кавычки:

risk_flag
----------
"high"
"medium"

Перепроверила типы данных через pg_typeof. В первом случае тип столбца jsonb, а во втором - text:

select
  pg_typeof(order_data['risk']['flag']) as risk_flag,
  pg_typeof(order_data['risk']['flag']::text) as risk_flag2
from orders

Я кастанула, но кавычки не ушли. Пошла гуглить, наверняка есть хитрый trim без substring/replace. И такой есть! Вот эта прикольная конструкция позволяет нам удалять символы с разных сторон, общий синтаксис такой:

TRIM([LEADING | TRAILING | BOTH] trim_character
FROM source_string)

У спикера же получилось более канонично 🙂

select
  (order_data -> 'gift' ->> 'wrapped')::boolean as gift_wrapped,
  order_data -> 'risk' ->> 'flag' as risk_flag
from orders

-> достает по ключу json
->> достает по ключу строку

📍 Advent of SQL (с впн)
📍 SQL Advent Calendar (с впн)
📍 Мои решения

@data_engineerette

Advent of SQL. Day 10

🏰 Вот такую конструкцию я 200% ни разу не встречала и не подозревала о ней! В задаче нужно было перенести данные из таблицы1 в таблицу2 и вывести только перенесенные строчки. Но обязательно в одном запросе - эммм... а как?

Дали подсказку - использовать по максимуму возможности постгри. Что ж, по первой ссылке я нашла тред на stackoverflow

С помощью CTE и RETURNING можно возвращать вставленные/удаленные/обновленные строки и передавать их в следующий запрос. Наверное, можно навешивать несколько cte, строить цепочки таких перемещений и даже ETL из raw в витрину, но звучит как мазохизм 😁

В финальном запросе получилось так:

with moved_rows as (
  delete from deliveries
  where ...
  returning *
)
insert into misdelivered_presents
select
  *,
  date_trunc('second', LOCALTIMESTAMP) as flagged_at,
  'Invalid delivery location' as reason
from moved_rows
returning *

Мы из delete передаем строки в insert, а из insert выводим на экран - это мэджик какой-то 😍

🏴󠁧󠁢󠁥󠁮󠁧󠁿 Фразы дня

I'm feeling stingy - ощущаю себя скупым
inexplicably - необъяснимо
issue an edict - издать указ
meddle in - вмешиваться
muck it up - все испортить
plop - плюхнуться
sleigh is airborne - сани находятся в воздухе
volcano rim - край вулкана

📍 Advent of SQL (с впн)
📍 SQL Advent Calendar (с впн)
📍 Мои решения

@data_engineerette

Собрались как-то DeepSeek, Grok и Gemini...

Вам попадался видос, как несколько иишек играют в мафию? Я наконец посмотрела, и тут прям полноценный разбор, как каждая модель себя ведет. Кто-то более официозно, кто-то льет много воды, кто-то умеет строить хитрые планы, а кто-то не понял правила игры 😁

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

Advent of SQL. Days 11-14

🏂 Сначала вспомнили про скользящее среднее, его нужно было найти за 7 предыдущих дней. Для этого есть конструкция rows between:

avg(score) over (
  partition by child_id order by behavior_date
  rows between 6 preceding and current row
) as avg_rolling_7_days

Я про нее писала в начале года, но к этому моменту все равно забыла, что можно циферками задавать промежуток)

Дальше задачка на полнотекстовый поиск, такого я не ожидала😁 По итогу познакомилась с несколькими функциями и операторами:

to_tsvector
to_tsquery
ts_rank
setweight
@@

Потом пошли парсить xml и считать количество зарегавшихся пассажиров. Автор использовал cross join lateral, ну а я нашла другой способ, он мне нравится больше:

cardinality(xpath('/manifest/passengers/passenger', manifest_xml)) as passengers_count

В предпоследний день искали пути от одной точки до другой и задействовали recursive cte

Ну вот и почти все! Сегодня будет последнее задание 🥲

🏴󠁧󠁢󠁥󠁮󠁧󠁿 Фразы дня

barrel straight down the hill - нестись вниз по склону
circuitous path - окольный, окружной путь
mind-bending - сложный
schlep - волочиться
winding route - извилистый маршрут

📍 Advent of SQL (с впн)
📍 SQL Advent Calendar (с впн)
📍 Мои решения

@data_engineerette