🖥 Как обновить или удалить миллионы записей в таблице не блокируя все записи и не нагружая БД?

Используйте процедуру loop_execute() для обработки строк в больших таблицах (тысячи и миллионы строк) с контролируемым временем блокировки строк на запись. Принцип работы — выполняет в цикле CTE DML запрос, который добавляет, обновляет или удаляет записи в таблице. В завершении каждого цикла изменения фиксируются (либо откатываются для целей тестирования, это настраивается).
Автоматически адаптируется под нагрузку на БД. На реплику данные передаются постепенно небольшими порциями, а не одним огромным куском.

В процессе обработки показывает в psql консоли:

▪количество модифицированных и обработанных записей в таблице
▪сколько времени прошло, сколько примерно времени осталось до завершения, прогресс выполнения в процентах
Прогресс выполнения в процентах для работающего процесса отображается ещё в колонке pg_stat_activity.application_name!

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

@sqlhub

🖥 SQL запросы c датафреймом Pandas

Если вы хотите отфильтровать свои данные, чтобы найти релевантную информацию с помощь SQL запросов, используя датафрейм Pandas, вы можете воспользоваться встроенной функции 𝗾𝘂𝗲𝗿𝘆() .

Функция выполняет запросы на основе логических выражений, как если бы вы писали запрос на естественном языке!

В этой статье мы расскажем, как с помощью Pandas добиться на Python такого же результата, как в SQL-запросах.

▪ Статья

@sqlhub

🖥 Сделать из «длинной» таблицы «широкую»

Дано
Даны две таблицы LongTable и WideTable:

LongTable:

+---------+--------+-----------------------+
| Name | key | value |
+---------+--------+-----------------------+
| Ivanov | FIO | Иванов Иван Иванович |
+---------+--------+-----------------------+
| Ivanov | Phone | +(7) 111-1111111 |
+---------+--------+-----------------------+
| Ivanov | Email | ivanov@ivanov.com |
+---------+--------+-----------------------+
| Petrov | FIO | Петров Петр Петрович |
+---------+--------+-----------------------+
| Petrov | Phone | +(7) 222-2222222 |
+---------+--------+-----------------------+
| Petrov | Email | petrov@petrov.com |
+---------+--------+-----------------------+
WideTable:


+---------+----------------------+------------------+-------------------+
| Name | FIO | Phone | Email |
+---------+----------------------+------------------+-------------------+
| Ivanov | Иванов Иван Иванович | +(7) 111-1111111 | ivanov@ivanov.com |
+---------+----------------------+------------------+-------------------+
| Petrov | Петров Петр Петрович | +(7) 222-2222222 | petrov@petrov.com |
+---------+----------------------+------------------+-------------------+
Задание
Как из таблицы LongTable получить WideTable?

Примечание. Предполагается чтение таблицы один раз и отсутствие соединений.

Столбцы в результате
Name
fio
phone
email

Важно: Обратите внимание, что название столбцов в вашем ответе должно в точности совпадать с условием.

Сортировка
Результат отсортируйте по возрастанию поля Name.

Пишите свое решение в комментариях👇

@sqlhub

🖥 Автоматизация выполнения запросов в SQL с помощью Python

Основная идея работы скрипта – генерация запросов с учетом различных параметров и последовательное их выполнение для вставки данных в существующую таблицу или выгрузка данных в файл.
Сама программа состоит из 3 частей:

1) Соединение c базой данных.
2)Определение варьируемых параметров.
3)Выполнение запросов к базе (структура построения запросов позволяет выполнять запросы последовательно или параллельно, что позволяет управлять скоростью загрузки/выгрузки данных с сервера).

Соединение с БД определяется фабрикой, в которой содержатся параметры соединения с определенным сервером и определены ссылки на классы для работы с БД.

db = DatabaseFactory().build('*наименование сервера*')

Сами объекты для работы с БД содержат 3 метода:

▪collect– запускает запрос с помощью метода read_sql библиотеки pandas и возвращает DataFrame, содержащий результат выполненного запроса;
▪execute– запускает запросы типа CREATE, UPDATE, DELETE\TRUNCATE\DROP;
▪execute_many – используется в основном для загрузки данных внутрь БД. Сама загрузка производится с помощью BULK вставки.

db.collect('select top 100 * from table')
db.execute('insert into table select * from another_table')
db. execute_many ('insert into from table (id, name, age) values (?,?,?)', [1,’Jhon’, 25])

Далее пользователь может задать параметры запроса с помощью метода add_var класса SqlContext. Данный метод принимает 4 параметра: наименование колонки, значения данной переменной, условие (=, <=, >=, between и т.п.) и разделитель (под разделителем понимаются команды AND и OR).

context = SqlContext()
context.add_var('col_name’, [1,2,3,4,5], separator='AND', condition='=')
context.add_var('col_name_1’, [[‘a’,’b’,’v’], [‘a1’,’b2’,’v3’],] , separator='AND', condition='in')

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

После того, как мы определили варьируемые параметры необходимо задать сам sql запрос. Для этого создаем объект SqlBuilder и вызываем метод custom_sql внутрь которого помещаем сам запрос:

builder = SqlBuilder()
builder.custom_sql('''
INSERT INTO insertable_table
SELECT
*
FROM table
WHERE 1=1
AND col1 in (1, 2,10,98,34)
AND col2 = 9
AND col3 between ‘20200101’ and ‘20200201’
''')
или можно воспользоваться встроенными в объект методами для генерации sql (select, insert_into, create_table и т.д.):

builder = SqlBuilder()
builder.select([‘col1’, ‘col2’, ‘col3’]).from(‘table’)
Для запуска скрипта необходимо создать объект класса SqlGenerator, объекты SqlBuilder и SqlContext и с помощью цикла запустить обработку запроса (в качестве примера был взят вариант последовательного исполнения запроса):

generator = SqlGenerator(builder, context)
for sql in tqdm(generator.generate()):
t = time.time()
db.execute(sql)
print('Итоговое время работы запроса: ' + str(time.time()-t))

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

Весь исходный код опубликован на github.

@sqlhub

🖥 Большой гайд/шпаргалка по SQL с примерами запросов и объяснением

🔥Доступ в Библиотеку бесплатных КНИГ, шпаргалок и лекций по базам данным.

@sqlhub

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

Дана cлудующая структура таблиц.

Задание

Вывести третий анализ по количеству продаж за весь период.

Столбцы в результате

▪an_id - ID анализа
▪an_name - название анализа
▪cnt - количество продаж
▪rn - ранг анализа в зависимости от продаж

Важно: Обратите внимание, что название столбцов в вашем ответе должно в точности совпадать с условием.

Пишите свое решение в комментариях👇

@sqlhub

🖥 Задача

Существует банковская система, где клиент может совершать транзакции и устанавливать лимит на свой аккаунт.

Необходимо составить запрос, который выводит список транзакций, превысившие определенный месячный лимит.

Также клиент может сам устанавливать себе лимит, пример: 01.01.2023 клиент установил лимит в 1000руб. 02.01.2023 совершил транзакцию на 900руб, остаток лимита составляет 100руб, 03.01.2023 совершает транзакцию на 500руб, остаток лимита равен -400, соответственно лимит превышен (limit_exceed = true). 10.01.2023 клиент устанавливает лимит в 2000руб, остаток лимита = 1600, лимит не превышен.

Решение

select *
from solva.transactions tsolva.transactions t
left join(
select *
,lead(setting_date,1,current_date)
over(partition by user_account order by setting_date)
- interval 'days 1' to_date
from limits
) l on l.user_account = t.account_from
and t.date_time between l.setting_date and l.to_date
where
t.limit_exceeded = true
--теперь не нужно and t.account_from = l.user_account
and t.date_time between :startdate and :enddate;

Пишите свое решение в комментариях👇


@sqlhub

🖥 Задание. Самые заказываемые пары товаров

Проанализируйте поведение покупателей и определите, какие продукты часто покупаются вместе.

Напишите запрос, который возвращает пары товаров, которые чаще всего приобретались вместе, а также сколько раз эти товары были приобретены вместе.

Не включайте пары одного и того же продукта или пары, которые уже были перечислены (например, если вы перечисляете пару продуктов A и B, не перечисляйте также пару продуктов B и A).

Пример результирующей таблицы:

| id1 | id2 | count |
|-----|-----|-------|
| 17 | 63 | 4 |
| 38 | 40 | 3 |
| 49 | 72 | 3 |
| 2 | 88 | 2 |

Столбцы в результате

▪id1 - id первого товара из пары
▪id2 - id второго товара
▪count - количество раз, когда эту пару товаров заказывали в одном заказе

Пишите свое решение в комментариях👇

@sqlhub

🖥 Оператор LIKE в SQL: примеры и синтаксис

LIKE SQL — это оператор, который используется для поиска строк, содержащих определённый шаблон символов.

Синтаксис LIKE SQL
Использовать оператор LIKE в SQL очень просто: достаточно прописать стандартную команду выбора SELECT * FROM и задать условие через WHERE, где и будет использован оператор LIKE.

Шаблон LIKE SQL:

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

Существует два подстановочных знака, которые используются в сочетании с оператором LIKE:

% — знак процента представляет собой ноль, один или несколько символов;
_ — подчёркивание представляет собой один символ.
Примеры использования LIKE SQL

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

SELECT * FROM table_name WHERE name LIKE 'J%';

В данном случае символ % используется для указания любого количества символов после J. Таким образом, запрос найдёт все имена, которые начинаются с буквы J, независимо от того, какие символы следуют за ней.

Ещё один пример — поиск всех адресов электронной почты, содержащих слово gmail. Для этого можно использовать следующий запрос:

SELECT * FROM table_name WHERE email LIKE '%gmail%';

Здесь символы % используются для указания, что слово gmail может быть в любом месте в адресе электронной почты.

Также можно использовать символ _ для указания одного символа. Например, запрос ниже найдет все имена, состоящие из шести символов. Эти имена должны начинаться с буквы J и заканчиваться буквой n:

SELECT * FROM table_name WHERE name LIKE 'J____n';

Здесь каждый символ _ указывает на любой один символ.

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

SELECT * FROM table_name WHERE name LIKE '%\%%';

@sqlhub

🖥 SQL запрос SELECT: значение и примеры

SELECT SQL — оператор запроса, который возвращающает определённый набор данных из базы данных. Список столбцов выборки задаётся в части оператора, которая называется предложением оператора SELECT.

Синтаксис SELECT SQL

SELECT column1, column2, ...
FROM table_name;

Здесь column1, column2, … — это имена полей таблицы, из которой выбираются данные. Если вы хотите выбрать все поля, доступные в таблице, используйте следующий синтаксис:

SELECT * FROM table_name;

Примеры использования SELECT SQL
Если у вас есть таблица employees со столбцами id, name, age, department, salary, вы можете выбрать только имена и возраст всех сотрудников следующим образом:

SELECT name, age FROM employees;

Чтобы выбрать все столбцы из таблицы, вы можете использовать символ *:

SELECT * FROM employees;

Вы также можете использовать оператор WHERE для фильтрации данных по определенным условиям. Например, чтобы выбрать только имена и возраст сотрудников младше 30 лет, используйте такой запрос:

SELECT name, age FROM employees WHERE age < 30;

Кроме того, есть ORDER BY для сортировки результатов по определённому столбцу. Например, можно отсортировать сотрудников по возрасту в порядке убывания:

SELECT * FROM employees ORDER BY age DESC;

SQL SELECT может использоваться для выполнения вычислительных операций над столбцами данных, например, для вычисления общей суммы или среднего значения. Чтобы вычислить среднюю зарплату всех сотрудников, напишем такой запрос:
SELECT AVG(salary) FROM employees;

Все эти примеры демонстрируют основы оператора SELECT в SQL. Также вы можете почитать о других основных командах SQL.

@sqlhub

🖥 Структура sql-запросов

Общая структура запроса выглядит следующим образом:

SELECT ('столбцы или * для выбора всех столбцов; обязательно')
FROM ('таблица; обязательно')
WHERE ('условие/фильтрация, например, city = 'Moscow'; необязательно')
GROUP BY ('столбец, по которому хотим сгруппировать данные; необязательно')
HAVING ('условие/фильтрация на уровне сгруппированных данных; необязательно')
ORDER BY ('столбец, по которому хотим отсортировать вывод; необязательно')

SELECT, FROM

SELECT, FROM — обязательные элементы запроса, которые определяют выбранные столбцы, их порядок и источник данных.

Выбрать все (обозначается как *) из таблицы Customers:

SELECT * FROM Customers

Выбрать столбцы CustomerID, CustomerName из таблицы Customers:

SELECT CustomerID, CustomerName FROM Customers

WHERE

WHERE — необязательный элемент запроса, который используется, когда нужно отфильтровать данные по нужному условию. Очень часто внутри элемента where используются IN / NOT IN для фильтрации столбца по нескольким значениям, AND / OR для фильтрации таблицы по нескольким столбцам.

Фильтрация по одному условию и одному значению:

select * from Customers
WHERE City = 'London'

Фильтрация по одному условию и нескольким значениям с применением IN (включение) или NOT IN (исключение):

select * from Customers
where City IN ('London', 'Berlin')

select * from Customers
where City NOT IN ('Madrid', 'Berlin','Bern')

Фильтрация по нескольким условиям с применением AND (выполняются все условия) или OR (выполняется хотя бы одно условие) и нескольким значениям:

select * from Customers
where Country = 'Germany' AND City not in ('Berlin', 'Aachen') AND CustomerID > 15

select * from Customers
where City in ('London', 'Berlin') OR CustomerID > 4

GROUP BY

GROUP BY — необязательный элемент запроса, с помощью которого можно задать агрегацию по нужному столбцу (например, если нужно узнать какое количество клиентов живет в каждом из городов).

При использовании GROUP BY обязательно:

перечень столбцов, по которым делается разрез, был одинаковым внутри SELECT и внутри GROUP BY,
агрегатные функции (SUM, AVG, COUNT, MAX, MIN) должны быть также указаны внутри SELECT с указанием столбца, к которому такая функция применяется.

Группировка количества клиентов по городу:

select City, count(CustomerID) from Customers
GROUP BY City

Группировка количества клиентов по стране и городу:

select Country, City, count(CustomerID) from Customers
GROUP BY Country, City

Группировка продаж по ID товара с разными агрегатными функциями: количество заказов с данным товаром и количество проданных штук товара:

select ProductID, COUNT(OrderID), SUM(Quantity) from OrderDetails
GROUP BY ProductID

Группировка продаж с фильтрацией исходной таблицы. В данном случае на выходе будет таблица с количеством клиентов по городам Германии:

select City, count(CustomerID) from Customers
WHERE Country = 'Germany'
GROUP BY City

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

select City, count(CustomerID) AS Number_of_clients from Customers
group by City

@sqlhub

🖥 Как откатить часть транзакции внутри функции или процедуры?

На SQL вы можете сделать так:

BEGIN;
INSERT INTO table1 VALUES (1);
SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (2);
ROLLBACK TO SAVEPOINT my_savepoint; --rollback previous command
INSERT INTO table1 VALUES (3);
COMMIT;

Внутри функции или процедуры код выше завершится с ошибкой, например в is_sql.sql. Но вы можете откатить часть SQL команд в транзакции через подтранзакции:

DO $TEST$
BEGIN
-- here you can write DDL commands, for example, adding or deleting a table or its section
-- and/or
-- here you can write DML commands that modify data in tables and, thus, check the operation of triggers

-- rollback all test queries
raise exception using errcode = 'query_canceled';

EXCEPTION WHEN query_canceled THEN
--don't do anything
END
$TEST$;

#postgre

@sqlhub

🖥 MySQL ускорение SQL запросов

Ускорение SQL запросов в MySQL может быть достигнуто следующими способами:

1. Индексы: использование индексов может ускорить поиск и сортировку данных в ваших таблицах.

2. Оптимизация структуры таблиц: важно убедиться, что структура таблиц оптимизирована для выполнения запросов.

3. Оптимизация запросов: проверьте ваши запросы на оптимизацию, убедитесь, что вы используете правильные операторы JOIN и индексы для улучшения производительности.

4. Кэширование данных: использование кэширования данных может ускорить выполнение повторяющихся запросов.

5. Использование представлений: использование представлений может упростить запросы и улучшить их читаемость.

6. Ограничение размера выборки: используйте оператор LIMIT, чтобы выбрать только необходимые данные, это уменьшит время выполнения запроса.

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

8. Оптимизация памяти: оптимизируйте использование памяти вашей базы данных, чтобы уменьшить время обработки запросов.

9. Мониторинг производительности: важен для определения причин низкой производительности и для поиска способов ее улучшения. Он включает в себя слежение за показателями, такими как загруженность процессора, использование памяти, время ответа на запросы и т. д.

10. Использование индексов: используйте индексы, чтобы ускорить поиск данных в таблице. Обеспечьте, чтобы ваши индексы были актуальными и эффективными.

11. Оптимизация объединений: используйте оптимальные методы объединения, такие как внутреннее или внешнее объединение, чтобы ускорить выполнение запросов.

12. Использование хранимых процедур: хранимые процедуры могут ускорить выполнение повторяющихся запросов.

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

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

15. Оптимизация структуры таблиц: периодически оценивайте структуру таблиц и выполняйте необходимые изменения, чтобы улучшить производительность.

16. Оптимизация запросов: проверяйте и оптимизируйте свои SQL-запросы, чтобы улучшить их производительность.

17. Ограничение данных: ограничивайте количество возвращаемых данных, чтобы улучшить производительность.

18. Мониторинг производительности: мониторинг производительности поможет вам выявить проблемы и найти способы их устранения.

19. Обновление ПО: регулярно обновляйте ПО, используемое вашей базой данных, чтобы воспользоваться последними улучшениями производительности.

20. Использование индексов: правильное использование индексов может существенно улучшить производительность SQL-запросов.

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

@sqlhub

🖥 Оператор SQL IN на понятных примерах

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

Синтаксис SQL IN

Синтаксис оператора SQL IN очень прост и понятен. Он выглядит следующим образом:

SELECT column1, column2, ...
FROM table_name
WHERE column_name IN (value1, value2, ...);

где column1, column2, ... — названия столбцов таблицы, которые нужно выбрать, table_name — название таблицы, column_name — название столбца, по которому нужно выполнить фильтрацию, а value1, value2, ... — значения, которые нужно найти.

Примеры использования SQL IN
Для лучшего понимания работы оператора SQL IN, рассмотрим несколько примеров.

Допустим, у нас есть таблица employees с полями id, name, department и salary. Мы хотим выбрать всех сотрудников из отдела sales и marketing. Мы можем сделать это, используя оператор SQL IN следующим образом:

SELECT * FROM employees WHERE department IN ('sales', 'marketing');

Этот запрос вернет все строки из таблицы employees, где значение столбца department равно sales или marketing.

Оператор SQL IN также может быть использован с подзапросами. К примеру, мы можем выбрать всех сотрудников, чья зарплата выше среднего значения по всей таблице:

SELECT * FROM employees WHERE salary IN (SELECT AVG(salary) FROM employees);

Этот запрос выберет все строки из таблицы employees, где значение столбца salary равно средней зарплате по всей таблице «employees».

@sqlhub

💨10 практик по именованию таблиц и полей в БД

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

Чтобы облегчить работу с БД себе и коллегам, попробуйте эти простые правила:

1. Разделять слова подчеркиванием, если имя атрибута или таблицы БД состоит из 2-х и более слов. Это понятнее стиля camelCase, улучшает читаемость и снижает зависимость от платформы. Например, word_count.

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

3. Писать название атрибута со строчной буквы, чтобы избежать путаницы с ключевыми словами SQL в верхнем регистре. Это также повысит скорость набора текста.

4. Не использовать цифры в названии таблиц и столбцов.

5. Называть таблицы понятно, но кратко.

6. Называть таблицы и столбцы в единственном числе. Например, author вместо authors

7. Называть таблицы-связки в алфавитном порядке. Например, author_book

8. При создании индекса называть его по имени таблицы и столбца. Например, CREATE INDEX person_ix_first_name_last_name ON person (first_name, last_name);

9. Для столбцов типа Boolean к имени добавлять префикс is_ или has_ . Например, is_admin или has_membership.

10. Для столбцов типа Date-Time к имени добавлять суффикс _at или _time. Например, ordered_at или order_time.

@sqlhub