👊А я сейчас вам покажу откуда на кандидата нападение готовилось

На позицию аналитика вас 100% будут спрашивать SQL.
Хочу показать вам задание, на которое отвечают только 2 из 10 кандидатов (по личной статистике). Очень популярная задачка, ей уже лет 5 как минимум, встречалась на собесах Яндекса, Сбера, ВТБ.

Вам дают таблицу. Допустим, вот такую:

id1 | id2 | id3
NULL 1 NULL
2 2 NULL
5 NULL NULL
NULL NULL NULL
2 3 NULL

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

select
max(id1) = 5
sum(id1) = 9
sum(id1+id2) = 9
sum(id3) = null
avg(id1) = 3
sum(8) = 40
count(*) = 5
count(1) = 5
count(id1) = 3
count(id3) = 0
count('id1') = 5
count(distinct id1) = 2
from table

Задание главным образом проверяет знания о том, как функции работают с null значениями и специфические конструкции.

1) В агрегатных функциях, таких как SUM, AVG, MAX и MIN, NULL обрабатывается следующим образом: для вычисления результата принимаются в рассмотрение только значения полей, не содержащие NULL.
2) Исключением является функция COUNT(), возвращающая общее количество строк в выборке. COUNT() не принимает на вход никакого выражения, а просто возвращает количество строк в выборке, независимо от наличия NULL значений.
3) Но COUNT(ИмяПоля) ведет себя как и остальные агрегатные функции, то есть считает записи, в которых значение указанного поля не является NULL.
4) COUNT(*) и COUNT(ИмяПоля) вернут 0, если нет записей в наборе данных. Другие агрегатные функции вернут NULL в таком случае.
5) У функции COUNT есть еще одна форма - COUNT(DISTINCT выражение). При такой форме записи функция вернет количество уникальных значений, при этом NULL значения по прежнему не учитываются.
6) Функция COUNT в SQL подсчитывает количество ненулевых значений в указанном столбце или количество строк, если передан аргумент, который не является столбцом.
7) Функция SUM(число) суммирует число для каждой строки, которая есть в таблице.

Подобный вопрос есть и в боте для подготовки к собеседованиям @DataismPrepBot. Пользуйтесь!

💃💃💃Оптимизируй!

Когда я работала в банке, у нас периодически приходило письмо с особо отличившимися сотрудниками, которые своими запросами бессовестно выжрали все ресурсы в одну мордочку.
Знаю, что в других банках за такое дают «желтую карточку», а если 2 раза провинился - ограничение доступа и иди учись на корп.курсах как правильно себя вести.
Так что советую для развития почитать книжку «Оптимизация запросов в PostgreSQL» (Домбровская Г.) и глянуть видео внизу поста.

Основные поинты из книги:
1. Селективность запроса - это соотношения количества строк, составляющих результат операции, к общему количеству строк в таблице.
2. Запрос является коротким, когда количество строк, необходимых для получения результата, невелико независимо от того, насколько велики задействованные таблицы. Короткие запросы могут считывать все строки из маленьких таблиц, но лишь небольшой процент строк из больших таблиц.
3. Запрос считается длинным, если селективность запроса высока по крайней мере для одной из больших таблиц; то есть результат, даже если он невелик, определяется почти всеми строками.
4. Когда мы оптимизируем короткий запрос, мы знаем, что в конечном итоге мы выбираем относительно небольшое количество записей. Это означает, что цель оптимизации – уменьшить размер результирующего множества как можно раньше. Если на первых этапах выполнения запроса применяется самый строгий критерий фильтрации, дальнейшие сортировки, группировки и даже соединения будут менее затратными. В плане выполнения не должно быть сканирований больших таблиц.
5. Если вы выполняете какие-то преобразования над полем индекса (функции lower, преобразование типов данных), то запрос не сможет воспользоваться таким индексом. Нужно либо переписывать условие в запросе без использования преобразования, либо создавать функциональный индекс.
6. В случае с длинными запросами используются две стратегии оптимизации: избегать многократных сканирований таблиц и уменьшать размер результата на как можно более ранней стадии.
7. Как правило, составной индекс по столбцам (X, Y, Z) будет использоваться для поиска по X, XY, XYZ и даже XZ, но не только по Y и не по YZ. Таким образом, при создании составного индекса недостаточно решить, какие столбцы в него включить; необходимо также учитывать их порядок.
8. Иногда попытка разработчиков SQL ускорить выполнение запроса, наоборот, может привести к замедлению. Такое часто случается, когда они решают использовать временные таблицы. Сразу возникает проблема с:
- индексами (мы не можем использовать индексы, созданные в исходной таблице, нам либо придется обойтись без индексов, либо создать новые во временных таблицах, что требует ресурсов)
- местом на диске (когда промежуточные результаты не помещаются в доступную оперативную память, временные таблицы сохраняются на диске), - операциями чтения-запись (дополнительно тратится время)
- статистикой (поскольку мы создали новую таблицу, оптимизатор не может использовать статистические данные о распределении значений из исходной таблицы (или таблиц), поэтому нам придется либо обойтись без статистики, либо выполнить команду ANALYZE для временной таблицы)

Неплохое видео про базовые принципы оптимизации от Евгения Кудашева ЦИАН:
https://www.youtube.com/watch?v=y6CWIBKEw_g

Еще интересная серия постов на хабре о том, как читать план запроса:
https://habr.com/ru/articles/275851/
https://habr.com/ru/articles/276973/
https://habr.com/ru/articles/279255/

А тут два моих поста про код-стайл SQL:
https://t.me/data1sm/215
https://t.me/data1sm/217

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

Но вот вопросики в бот @DataismPrepBot добавляются:)
Пополнила раздел с продуктовой аналитикой.
Теперь в боте всего порядка 500 вопросов.
Пользуйтесь и оставляйте фидбэк. А если еще и готовы поделиться вопросами с собесов, то вообще пушка-бомба-огонь-ракета и плюсик в карму?

?Алгоритмы в реальной жизни

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

И вот примеры.

Задача 1
Даны n отрезков на оси (Ox) a_i, b_i (где a_i начало отрезка, b_i конец отрезка). нужно найти максимально число отрезков, которые пересекаются в одной точке.
Задача с литкода, решение писать конечно же не буду.
Но как эта задача может применяться в жизни?
Представим, что у нас есть отель. В отель каждый день кто-то заезжает и выезжает. На каждого жителя мы заказываем еду. Хочется понять, какое максимально количество еды надо будет заказать в какой то из дней?
Задача 2 Поля Галуа из высшей алгебры.
Каждый раз, когда вы пытаетесь сканировать qr код не обходится без теории из высшей алгебры, хотя при узучении этой темы казалось, что это просто абстрактная теория.
Задача 3. Алгоритмы на графах. Дейкстра/Breadth-First Search (BFS)(нахождение кратчайшего пути в графе).
Представим, что нам надо купить билеты из города A в город B и мы хотим найти самый дешевый путь.
Переформулируем задачу в терминах графов. Есть Ориентированный граф, где вершины – это города. Если между городоми A и B есть прямой рейс то она будет ребром с весом равным цене билета.
После представления такого графа сразу приходит мысль писать дейкстру и находить путь.
Задача 4. Алгоритмы на графах. Depth-First Search (DFS)
Есть список библиотек и их зависимостей, например:
[{E: []}, {A: [B, С]}, {B, [C]}, {C: [D]}, {D: []}]
Распечатайте их в порядке, в котором их можно подгружать с учетом этих зависимостей.
Не зная графов быстро решение не придумать.
Но как только переформулировать задачу в терминах графов, то решение почти сразу на ладони.
Переформулируем.
Пусть вершины это библиотеки. Если библиотека A зависит от библиотеки B, то будем соединять эти две вершинки направленным ребром.
Теперь чтобы решить задачу нам надо выстроить все библиотеки в ряд, что бы ребра были только в одном направлении. А это просто топологическая сортировка! Чуть-чуть модифицируем dfs и радуемся.

Тут тоже описан пример литкодовской задачи и ее применение

Ребятки, у бота уже началась пятница ?, и он решил прилечь отдохнуть
Но обещал вернуться уже скоро:)

Всем хороших выходных?

UPD. Бот ожил:)