После урока по SQL хочется где-нибудь порешать задачи. Ставить ради этого базу и придумывать себе данные бывает лень, особенно когда ещё путаешься в JOIN.
Собрал шесть тренажёров, к которым можно перейти сразу после теории. Есть обычный задачник, несколько игр и мой бот в Telegram. Пишу с уклоном в QA: я работаю QA-лидом и обучаю тестированию, поэтому смотрю в первую очередь на то, какие задания можно использовать при подготовке.
Общая сумма платежей совпадает, а суммы по дням расходятся. Причиной может быть часовой пояс подключения к PostgreSQL. На трёх платежах покажу, как paid_at::date меняет результат группировки и как явно задать часовой пояс отчёта — и для дат, и для границ периода.
Два обработчика могут успешно принять разные оплаты, но продлить подписку только один раз. На примере PostgreSQL разбираем, как теряется обновление, что меняет прибавление внутри UPDATE и какой результат нужно проверять в тесте.
Мы в Modus делаем ETL и BI, и видим чужие базы данных изнутри: на каждом внедрении, на техническом консультировании, на разборах кейсов и на каждой миграции. Навидались всякого. Но одна ошибка попадается так часто и прячется так ловко, что заслуживает отдельного разбора… и почётного звания антипаттерна. Начинаем цикл статей, посвящённых антипаттернам проектирования баз данных именно с неё.
Представьте: миграция позади. Заказы, товары и склады на месте. Число строк сошлось, проверки горят зелёным, можно закрывать задачу. Но только никто — ни аналитик, ни разработчик, ни самый хитрый SQL-запрос, — уже не скажет, с какого склада уехал конкретный товар.
Не удалено ни одного значения. А данные протеряны.
Это сбой миграции? Ошибка в запросе? Нет!
Это ошибка проектирования, которая очень похожа на безобидную нормализацию. Давайте разберём, откуда она берётся, почему стандартные проверки её не видят и почему исправить её задним числом уже нельзя. Не «дорого и сложно», а вообще невозможно математически.
А в конце — задачка для тех, кто (как и мы когда-то) уверен, что опыт и знания надёжно ограждают его от таких новичковых ошибок.
Утром в отчёте 25% продлений за вчера, но у части подписчиков ещё есть время на оплату. На примере PostgreSQL покажу, как сравнивать дни с полным 48-часовым окном, засчитывать предоплату и обрабатывать платёж ровно на границе срока.
За последние два года мне несколько раз пришлось восстанавливать завёрнутый код Oracle версий 7.2-9.2.
Имевшаяся утилита 1993 года, рассчитанная на PL/SQL 2.2, помогла несильно: код нужно было довести до уровня, пригодного для отладки. Я погрузился в тему, и эта статья - сводка того, что удалось выяснить о формате V1 в ходе реализации законченного unwrapper-а.
При создании кейса на в моем тренажере, который я рассказывал ранее, возникло следующее: добавление пустой колонки выглядит безобидной миграцией: переписывать данные не нужно. Но такой ALTER TABLE может остановить новые чтения таблицы ещё до изменения её структуры. Достаточно старой транзакции, которую забыли завершить. Поясню эту цепочку на отдельном PostgreSQL 17.11 с таблицей orders из одной строки.
Сначала первая сессия выполняет BEGIN и SELECT id FROM orders, после чего остаётся открытой. Сам запрос уже завершён, но транзакция продолжает держать AccessShareLock. Это не мешает другому обычному SELECT: такие блокировки совместимы. Проверка до запуска миграции возвращает строку без ожидания старой транзакции.
Затем вторая сессия запускает ALTER TABLE orders ADD COLUMN note text. Для этого изменения нужна AccessExclusiveLock, несовместимая с блокировкой первой сессии. Миграция встаёт в очередь. Если теперь третья сессия выполнит SELECT id FROM orders, она тоже будет ждать. Её AccessShareLock совместима с уже выданной блокировкой первой сессии, однако впереди стоит запрос миграции на исключительный доступ.
На снимке консоли видна вся цепочка. Сессия old_tx находится в состоянии idle in transaction и держит выданную блокировку: granted = t. У migration блокировка ещё не выдана, а blocked_by указывает на old_tx. Новый запрос api также имеет granted = f, но его blocked_by указывает уже на migration. Таким образом, источник ожидания и непосредственный блокировщик запроса — разные сессии. После ROLLBACK в old_tx миграция завершается, а новый SELECT получает результат.
Наша панель для управления серверами хранит метрики в таблице server_metrics. В октябре в ней было 2,26 млн строк, и на проде страницы со списком серверов открывались по несколько секунд. Виноватым оказался один запрос. С подставленными значениями он выполнялся за 4-8 мс, а с биндингами, как его отправляет приложение, за 2,3-3,3 секунды.
Как сделать отчет в Excel, который будут читать руководители: сводные таблицы, Power Pivot, примеры дашбордов для продаж, финансов и маркетинга. Работа с шаблонами и автоматизация.
Отчёт в PostgreSQL может менять результаты от запуска к запуску, даже если исходные данные остались прежними. Причина может скрываться в порядке сложения чисел с плавающей точкой, сортировке строк с одинаковым временем или разных снимках данных, которые получают запросы дашборда. В статье разбираем три таких сценария на практических экспериментах и выясняем, как сделать результаты запросов предсказуемыми.
UPDATE, который выполнялся около 10 тысяч раз в секунду, почти исчез. Выборка из истории статусов у самой загруженной группы на зеркале базы ускорилась с 274 мс при прогретом кэше до 0,4 мс. Такие места автор нашёл вместе с кодинг-агентом: подключил его к трейсам продакшена Sentry и короткими вопросами направлял поиск. Вместе правки убрали сотни миллионов запросов в неделю. В статье показано, какие подсказки давал человек, как изменения проверялись через EXPLAIN ANALYZE и какие цифры получились.
Средний чек за период: как усреднение дневных значений меняет результат
В дневном отчёте уже есть средний чек. Можно ли получить показатель за месяц, просто усреднив значения по дням?
Разберём на условном примере за два дня. Учитываем только оплаченные заказы в рублях, без возвратов. Каждому заказу соответствует одна итоговая сумма.
1 ноября:
Оплаченных заказов — 1.
Общая сумма заказов — 1 000 ₽.
Средний чек — 1 000 ₽.
2 ноября:
Оплаченных заказов — 9.
Общая сумма заказов — 900 ₽.
Средний чек — 100 ₽.
Если взять среднее арифметическое двух дневных значений, получим:
(1 000 + 100) / 2 = 550 ₽.
Но за весь период покупатели оплатили 10 заказов на общую сумму 1 900 ₽.
Правильный средний чек за период:
1 900 / 10 = 190 ₽.
Разница существенная: 550 ₽ против 190 ₽.
Почему результаты отличаются? При усреднении дневных показателей каждый день получает одинаковый вес. День с одним заказом влияет на результат так же сильно, как день с девятью.
Оба результата математически корректны, но описывают разные показатели. 550 ₽ — среднее арифметическое дневных средних чеков. 190 ₽ — средний чек по всем оплаченным заказам за период.
Если нужен второй показатель, простое усреднение дневных значений может дать неверный ответ. Иногда результаты совпадут, например при одинаковом количестве заказов каждый день. Но рассчитывать на такое совпадение нельзя.
В SQL эта ошибка возникает, когда сначала считают средний чек по дням, а затем применяют AVG к полученному столбцу. Функция усредняет дневные значения, не учитывая, сколько заказов стоит за каждым из них.
Есть в инфобезе сценарий, который повторяется с завидным постоянством. Инцидент вроде бы закрыли, отчитались, выдохнули — а через пару недель выясняется, что атакующий никуда не уходил. Он просто переждал. Именно так и произошло здесь, и именно поэтому эта история стоит того, чтобы её разобрать не по верхам, а по-настоящему.
Два сайта — appeal и new портал на 1С-Битрикс — несли один и тот же веб-шелл WebShellOrb (WSO). В первый раз инцидент обнаружили по логам Nginx, а вот дальше началось самое неприятное. Через некоторое время злоумышленник снова был внутри. Инцидент растянулся почти на три месяца, и повторная компрометация случилась уже после того, как были приняты меры.
И вот тут очень легко ошибиться в выводах. Соблазнительно сказать: «ну, значит, меры сделали плохо». Но это было бы неправдой. Дальше — почему меры не сработали и что оказалось «под капотом».
Куда пропали клиенты без покупок? Ловушка WHERE после LEFT JOIN
Нужно вывести всех клиентов и их оплаченные заказы. Даже если оплаченных заказов нет, клиент должен остаться в отчёте.
Допустим, у нас три клиента:
Про data mapping простыми словами: что такое маппинг данных, как выглядит процесс, какие инструменты используют и кто занимается маппингом. Примеры для аналитиков, инженеров и продактов.
Документация SQLite говорит, что -wal перестаёт расти, только если у читателей, которые видят кадры из -wal, бывают общие паузы, но не говорит, какой длины. Я замерил в контейнере на SQLite 3.46.1: на моём стенде хватало единиц миллисекунд — 2 мс при 50 коммитах/с, 5 мс при 1000/с, и быстрой записи понадобилась пауза шире. Без паузы три читателя по 50 мс раздувают -wal так же, как один на 20 секунд, а случайные запросы по 5 мс почти не мешали, пока база была свободна хотя бы 13% времени.
Один и тот же набор из десяти заказов, а p90 получается от 3900 до 12 500 рублей. В ноутбуке аналитика одна цифра, на дашборде другая, и даже повторный запуск запроса иногда меняет результат. Причина может скрываться в самой функции расчёта перцентиля — разберёмся, как разные инструменты выбирают значения и какой метод использовать, чтобы метрики можно было сравнивать.
Про N+1 в Laravel обычно пишут три строки про with() и расходятся. Я собрал небольшой стенд на Laravel 13 и SQLite, воспроизвёл проблему, посчитал запросы для каждого способа лечения и проверил, что «не ловится» режимом запрета ленивой загрузки. Отдельно разобрал места, где eager loading не помогает или сам создаёт нагрузку. Все числа в статье получены на этом стенде на искусственных данных.
Представим: в отчёт добавили новые регистрации, и общий D30 retention снизился с 20% до 10%. При этом ни один из пользователей, для которых D30 уже наступил, не изменил своё поведение. Ошибка может оказаться в знаменателе: туда попали люди, чей тридцатый день ещё впереди.
Разберём эту ситуацию на условных данных и воспроизведём расчёт в SQLite. Отдельно проверим три случая: пользователь вернулся, пользователь не вернулся и результат пока нельзя измерить.
Семь строк в SQL — ещё не семь дней. Как пропуски дат меняют скользящую сумму
Для показателя «за последние семь дней» легко написать:
