Находим первое, N-ое или последнее значение в SQL

FIRST_VALUE ( val ) - Start val
NTH_VALUE ( val, N ) - Val at row N
LAST_VALUE ( val ) - Final val

Условие OVER определяет порядок

Будьте осторожны с использованием значения по умолчанию - оно останавливает NTH и LAST на текущем значении => неожиданные результаты

#db #sql

👉 @database_info

Практические примеры по оптимизации запросов в PostgeSQL
Иван Чувашов

источник

#db #sql #postgesql

👉 @database_info

Облачные базы данных: Шпаргалка

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

Структурированные базы данных📌

Структурированные базы данных организуют данные в предопределенные схемы и модели.

Реляционные базы данных, такие как MySQL и PostgreSQL, хранят данные в таблицах со строками и столбцами.

Колоночные базы данных, такие как Amazon Redshift и Google BigQuery, также имеют структурированную модель данных, но хранят их по-другому, оптимизируя для аналитических запросов.

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

Примеры использования: CRM-системы, управление запасами, бухгалтерский учет, аналитика


Полуструктурированные базы данных📌

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

Примеры включают в себя документ-базы данных, такие как MongoDB, графовые базы данных, наподобие Neptune, широкие колоночные хранилища, такие как ScyllaDB, и хранилища ключ-значение, такие как DynamoDB.

Преимущества:
- Гибкость для изменяющихся данных
- Масштабируемость на разных серверах

Примеры использования: Электронная коммерция, ленты социальных сетей, данные IoT


Неструктурированные базы данных📌

Неструктурированные базы данных оптимизированы для хранения и обработки огромных объемов разнородных данных, таких как документы, изображения, видео. Примеры: AWS S3, Azure Blob Storage.

Преимущества:
- Хранение огромных объемов данных
- Высокая масштабируемость

Примеры использования: Медиарепозитории, управление контентом, океаны данных, журнальные данные, резервное копирование.

#db

👉 @database_info

SQL

С помощью всего нескольких базовых команд можно решить 80% повседневных задач, связанных с данными.
Сосредоточьтесь на понимании JOIN, SELECT, UPDATE, DELETE и некоторых других фундаментальных команд.

#db

👉 @database_info

Команды SQL - основы

#db

👉 @database_info

Преобразование файлов CSV в базу данных SQLite


Конвертируйте файлы CSV в базу данных SQLite. Просмотр и публикация базы данных SQLite с помощью Datasette.
Базовое использование:
csvs-to-sqlite myfile.csv mydatabase.db

В результате будет создана новая база данных SQLite под названием mydatabase.db с одной таблицей myfile, содержащей содержимое CSV.
Вы можете предоставить несколько CSV-файлов:
csvs-to-sqlite one.csv two.csv bundle.db


База данных bundle.db будет содержать две таблицы, one и two.

Это означает, что вы можете использовать wildcards:
csvs-to-sqlite ~/Downloads/*.csv my-downloads.db


Если вы укажете путь к одной или нескольким директориям, скрипт будет рекурсивно искать CSV-файлы в этих директориях и создавать таблицы для каждого из них.
csvs-to-sqlite ~/path/to/directory all-my-csvs.db

https://github.com/simonw/csvs-to-sqlite

#db

👉 @database_info

Dbchaos

Проведите стресс-тестирование базы данных с помощью заранее заданных запросов. Генерируйте исходные данные и события статически или с помощью GPT.

https://github.com/adaptive-scale/dbchaos

#db

👉 @database_info

DBDiagram

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

https://dbdiagram.io/home

👉 @database_info

Руководство по стилю SQL · SQL Style Guide

https://www.sqlstyle.guide/ru/

#db

👉 @database_info

Процедуры в Postgres

источник

#db

👉 @database_info

7 обязательных стратегий для масштабирования вашей базы данных.

1 - Индексация:
Проверьте шаблоны запросов вашего приложения и создайте подходящие индексы.

2 - Материализованные представления:
Предварительно вычислите результаты сложных запросов и сохраните их для быстрого доступа.

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

4 - Вертикальное масштабирование:
Увеличьте мощность вашего сервера базы данных, добавив больше ЦП, оперативной памяти или хранилища.

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

6 - Репликация:
Создайте реплики вашей основной базы данных на разных серверах для масштабирования чтений.

7 - Шардинг:
Разделите таблицы базы данных на более мелкие части и распределите их по серверам. Используется для масштабирования как записей, так и чтений.


#db

👉 @database_info

Antares SQL Client

Современный, быстрый и ориентированный на продуктивность SQL-клиент с акцентом на пользовательский опыт (UX).

Текущие ключевые функции:
- Подключение к нескольким базам данных одновременно.
- Управление базами данных (добавление/редактирование/удаление).
- Полное управление таблицами, включая индексы и внешние ключи.
- Управление представлениями, триггерами, хранимыми процедурами, функциями и планировщиками (добавление/редактирование/удаление).
- Современная и удобная система вкладок; держите открытыми все необходимые вкладки в вашем рабочем пространстве.
- Заполнение тестовых данных в таблицах для генерации большого объема данных.
- Подсказки и автозаполнение запросов.
- История запросов: поиск по последним 1000 запросам.
- Сохранение запросов, заметок или задач.
- Поддержка SSH-туннелей.
- Режим ручного выполнения транзакций.
- Импорт и экспорт дампов баз данных.
- Настраиваемые горячие клавиши.
- Темная и светлая тема.
- Темы редактора.

https://github.com/antares-sql/antares

#db

👉 @database_info

Anyquer

Это CLI-инструмент для выполнения SQL-запросов к любому источнику данных, будь то файл, API, логи или локальное приложение. Ознакомьтесь с интеграциями, чтобы узнать обо всех возможностях.

Поддерживает запросы JSON, CSV, Parquet, Airtable, Google Sheets, баз данных Notion, Gmail и многого другого. Он использует SQLite под капотом и может выступать в качестве сервера MySQL, чтобы вы могли подключать инструменты BI.

https://anyquery.dev/

#db

👉 @database_info

И/или

Условия в WHERE могут быть написаны с использованием логических операторов (AND/OR) и математические операторы сравнения (=, <, >, <=, >=, <>).

К примеру, у нас есть табличка, в которой записаны данные о 4 самых продаваемых музыкальных альбомах всех времён. Давайте выведем только те, жанром которых является рок, а продажи были меньше, чем 50 миллионов копий.

#db

👉 @database_info

Instant - клиентская база данных в режиме реального времени

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

Code Examples https://www.instantdb.com/examples

https://github.com/instantdb/instant


#db

👉 @database_info

Как выполнять периодические обновления данных в PostgreSQL?

Для выполнения периодических обновлений данных в PostgreSQL можно использовать несколько подходов, в зависимости от требований к частоте обновлений и архитектуры системы. Вот основные методы:


1. CRON и SQL скрипты
- CRON (или аналогичный планировщик задач) — это системный планировщик задач, который может запускать SQL-скрипты в определенное время или с определенной периодичностью.
- Вы создаете SQL-скрипт, который выполняет необходимые обновления данных, и настраиваете CRON для его запуска.
- Пример команды в CRON:

     * * * * * psql -U user -d database -c "CALL your_update_function();"
     

Этот пример выполняет задачу каждую минуту.

2. pg_cron
- pg_cron — это расширение для PostgreSQL, которое позволяет планировать задания непосредственно из базы данных, используя SQL-синтаксис для задания расписания.
- Пример:

     SELECT cron.schedule('0 * * * *', $$CALL your_update_function();$$);
     

Это задание выполняет функцию каждый час.

3. pgAgent
- pgAgent — это более сложный планировщик задач, который интегрируется с PostgreSQL. Он позволяет создавать и управлять заданиями через интерфейс PgAdmin или SQL-команды.
- Пример задания:

     CREATE JOB update_job
     STEP 1
     WHEN '0 * * * *'
     EXECUTE PROCEDURE your_update_function();
     

4. Встроенные средства PostgreSQL
- Если задача достаточно простая, и вы хотите обойтись без внешних инструментов, можно использовать механизм LISTEN/NOTIFY или bgworker (бэкграундные воркеры).
- LISTEN/NOTIFY: используется для уведомления процессов о событиях в базе данных. Рабочие процессы могут запускаться при получении уведомления.
- bgworker: позволяет создавать фоновый процесс, который будет периодически выполнять необходимые задачи.

5. Сервисные задачи на стороне приложения
- Если ваше приложение подключается к базе данных PostgreSQL, оно может содержать задачи, которые будут запускать обновления данных через определенные промежутки времени.
- Это подход особенно удобен, если вы хотите, чтобы приложение контролировало выполнение обновлений, а также легко интегрировало бизнес-логику.

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

7. Materialized Views
- Если вам нужно обновлять агрегированные или сложные запросы, можно использовать материализованные представления с автоматическим обновлением.
- Вы можете создать материализованное представление и настроить его автоматическое обновление через CRON или pg_cron:

     REFRESH MATERIALIZED VIEW CONCURRENTLY your_view;
     

Заключение
Выбор метода зависит от конкретных требований и доступных инструментов. Если вам нужно периодически выполнять задачи непосредственно в базе данных и с минимальными усилиями, pg_cron и pgAgent — отличные варианты. Если хотите контролировать задачи с уровня операционной системы, то CRON или системные задачи на стороне приложения будут лучшим решением.

#db

👉 @database_info

Детализированные стратегии кэширования динамических запросов

Сегодня я хотел бы поговорить о стратегиях кэширования для совокупных запросов к часто обновляемым данным, основанным на времени. На предыдущем месте работы я провел немало «мозговых циклов» и с удовольствием поделюсь некоторыми своими находками.

https://jensrantil.github.io/posts/fast-aggregate-queries-on-dynamic-data/

#db

👉 @database_info

SQLFlow

SQLFlow — простой инструмент для визуализации SQL-запросов и отображения зависимостей. Позволяет отслеживать data lineage-происхождение и трансформации в данных при исполнении запросов.

https://sqlflow.gudusoft.com/

#db

👉 @database_info