Postgres умеет очень просто агрегировать данные по датам через date_trunc — просто передай day, week, month, quarter и сделай GROUP BY по этому же полю.

SELECT
    date_trunc('month', order_date) AS month,
    COUNT(*) AS total_orders,
    SUM(total_amount) AS monthly_total
FROM
    orders
GROUP BY
    date_trunc('month', order_date)
ORDER BY
    month;

👉 @SQLPortal

SQL Studio вышел в публичную бету

Современный кроссплатформенный SQL-клиент, сделанный с упором на скорость и простоту.

SQLite уже поддерживается. Остальные диалекты в пути.

Баги прилагаются 🪲

[https://sql.studio]

👉 @SQLPortal

Почему SQL считается общим языком для работы с данными

SQL настолько важен, что даже внутри Python у тебя всё равно появляется SQL.

Да, даже в pandas — по сути тот же подход.

Да, даже в PySpark — тоже SQL.

Pandas просто заимствовал базовую логику: SELECT, WHERE, GROUP BY, JOIN и завернул её в методы и функции. Синтаксис другой, мышление то же.

Если начать с SQL, потом быстрее въезжаешь в pandas и PySpark — порог входа заметно ниже.

Если уже работаешь с pandas, то выучить SQL тоже будет довольно просто.

Вывод: если ты занимаешься данными, SQL обязателен.

👉 @SQLPortal

Копался во внутренностях MySQL и наткнулся на интересную штуку — doublewrite buffer. Это аккуратный хак против порчи данных. Коротко о том, как он работает.

Когда в MySQL (InnoDB) происходит запись, движок не пишет страницу сразу на диск в её финальное место. Вместо этого он делает так:

- сначала пишет страницу в doublewrite buffer на диске
- fsync — чтобы гарантировать, что данные легли
- потом копирует страницу в её финальное место в data file

Зачем этот лишний шаг? Он даёт атомарность и защиту от крэшей.

Представь, что сервер падает ровно в момент записи страницы в data file. Мы переписываем старые байты новыми, и если запись порвётся посередине — файл данных может оказаться повреждённым. Восстановиться будет уже некуда.

Но так как InnoDB сначала пишет в отдельный буфер, у нас есть прочная копия страницы с последними изменениями. После рестарта движку остаётся просто скопировать её на место.

Если порча произошла на финальном шаге, InnoDB просто восстанавливает страницу из doublewrite buffer.

Цена — небольшой дополнительный I/O, но выигрыш — высокая надёжность, crash recovery и защита от повреждений. Очень крутое решение с инженерной точки зрения.

👉 @SQLPortal

Функции и процедуры на самом деле очень похожи, и многие называют их как попало, но в Postgres это разные вещи.

Functions:

- Считают и возвращают значения
- Не управляют транзакциями
- Можно использовать внутри SELECT или WHERE
- SELECT function()

Procedures:

- Действия и батчи
- COMMIT и ROLLBACK
- Нельзя вызывать внутри SQL
- CALL procedure()

👉 @SQLPortal

Data Modeling

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

Теперь у нас есть таблица с одной строкой на каждую строку файла. Как сгруппировать такие данные? Группировку похожих строк обычно делают через window-функции. В нашем случае нужно «складывать» строки до тех пор, пока не встретится пустая строка, после чего начинать новую группу. Создадим псевдоколонку и будем увеличивать sequence только когда значение в calories_count пустое. Также вызовем setval() для задания начального значения, чтобы функция currval() работала.

CREATE SEQUENCE aoc;
SELECT setval('aoc', 1);
SELECT calories_count,
       CASE WHEN calories_count = ''  THEN nextval('aoc')
            ELSE currval('aoc') END
FROM calories LIMIT 10;

Результат:

calories_count | currval 
---------------+---------
 9686          |       1
 10178         |       1
 3375          |       1
 9638          |       1
 6318          |       1
 4978          |       1
 5988          |       1
 6712          |       1
               |       2
 10422         |       2

Как видно, currval меняется, когда sequence обнаруживает новую группу. Используем это свойство, чтобы посчитать сумму по каждой группе. Поскольку у нас текстовый столбец, придется привести его к int перед суммированием. Дополнительно пустая строка не может быть приведена к int, поэтому явно детектируем строку-разделитель и считаем ее значение равным 0.

👉 @SQLPortal

Group and Sum

SELECT SUM(calories_count) OVER(partition by currval) FROM
  (SELECT CASE WHEN calories_count = '' THEN 0
               ELSE calories_count :: int END AS calories_count,
          CASE WHEN calories_count = '' THEN nextval('aoc')
               ELSE currval('aoc') END
   FROM calories
  ) x LIMIT 10;

Результат:

sum  
-------
 56873
 56873
 56873
 56873
 56873
 56873
 56873
 56873
 43456
 43456

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

👉 @SQLPortal

Дан запрос:

SELECT department, AVG(salary) as avg_salary
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY department
HAVING AVG(salary) > 60000
ORDER BY avg_salary DESC
LIMIT 5;

Вопрос: какой реальный порядок выполнения?

Варианты:

A) FROM → WHERE → SELECT → GROUP BY → HAVING → ORDER BY → LIMIT
B) FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
C) FROM → WHERE → GROUP BY → SELECT → HAVING → ORDER BY → LIMIT
D) WHERE → FROM → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

👉 @SQLPortal

Руководство по SQL

Это руководство охватывает различные аспекты, начиная с основ SQL и до более сложных тем

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

Сохраняйте, чтобы не потерять

👉 @SQLPortal

Повышение производительности PostgreSQL: пошаговое руководство по использованию pg_hint_plan

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

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

👉 @SQLPortal