Data Science. SQL hub

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

На 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

🖥 Структура 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 запрос 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

🖥 Оператор 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

🔎 13 Инструкций SQL для решения 90% ваших задач по обработке данных

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

▪ Статья

@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

🖥 Задача

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

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

Также клиент может сам устанавливать себе лимит, пример: 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

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

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

Задание

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

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

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

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

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

@sqlhub

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

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

@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

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

Дано
Даны две таблицы 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 запросы c датафреймом Pandas

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

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

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

▪ Статья

@sqlhub

CTE. Обобщенное табличное выражение SQL и способы его использования

CTE, или Common Table Expressions — один из видов запросов в системах управления базами данных. На русском языке они называются обобщенными табличными выражениями. Результаты табличных выражений можно временно сохранять в памяти и обращаться к ним повторно.

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

▪ Читать

@sqlhub

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

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

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

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

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

@sqlhub

🖥 Скрипт для открытия txt файла и создания бд SQL lite, где каждая строка txt ячейка в SQl

import sqlite3

con = sqlite3.connect('BD88.db')
cursorObj = con.cursor()

cursorObj.execute('CREATE TABLE IF NOT EXISTS tabl (adr text)')
con.commit()

with open("2.txt", 'r', encoding="utf-8") as file:
for line in file.readlines():
s = line.strip()

cursorObj.execute("""INSERT INTO tabl (adr) VALUES (?);""", (s,))
con.commit()

Подробный гайд по работе с SQL lite на Python с примерами кода.

@sqlhub

🖥 Распространенные ошибки SQL в хранимых процедурах и запросах

Распространенные ошибки SQL в хранимых процедурах и запросах

Я не буду описывать совсем банальные вроде ошибки синтаксиса (talbe вместо table). В статье мы рассмотрим досадные ошибки sql server, которые снижают скорость нашей разработки.

📌 Читать

@sqlhub

🖥 Агрегатные функции в SQL: объяснение с примерами запросов и задачами.

▪Читать

@sqlhub

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

hr.employees:

EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
100 Steven King SKING 515.123.4567 17-JUN-03 AD_PRES 24000 - - 90

hr.departments:

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
10 Administration 200 1700


Задача: Путем соединения таблиц HR.DEPARTMENTS и HR.EMPLOYEES получить список департаментов, указав по каждому департаменту среднюю зарплату сотрудников и количество сотрудников, получающих комиссионную надбавку.

select
d.department_id
, department_name
, d.manager_id
, d.location_id
from hr.departments d
left join hr.employees e on e.department_id = d.department_id
group by d.department_id
, department_name
, d.manager_id
, d.location_id
having min(e.salary) < 5000

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

@sqlhub