🖥 Советы как сделать SQL запросы читабельнее и понятнее

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

📌 Статья

@sqlhub

🖥 Советы по оптимизации SQL-запросов

Основные причины для оптимизации SQL-запросов :

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

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

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

📌 Читать

@sqlhub

🖥 Есть ли преимущество в использования IN вместо множества OR в sql?

Если говорить про абстрактный SQL - никакой разницы нет. Есть много способов попросить достать одни и те же данные. Впрочем, с точки зрения читаемости человеком in явно выигрывает из-за компактности.

А если рассматривать конкретные реализации - то различия могут быть. Например, мой локальный postgresql строит разные планы:

melkij=> explain select * from bigtable where id = 1 or id = 3 or id=4;
QUERY PLAN
----------------------------------------------------------------------------------
Bitmap Heap Scan on bigtable (cost=13.34..25.34 rows=3 width=12)
Recheck Cond: ((id = 1) OR (id = 3) OR (id = 4))
-> BitmapOr (cost=13.34..13.34 rows=3 width=0)
-> Bitmap Index Scan on bigtable_pkey (cost=0.00..4.45 rows=1 width=0)
Index Cond: (id = 1)
-> Bitmap Index Scan on bigtable_pkey (cost=0.00..4.45 rows=1 width=0)
Index Cond: (id = 3)
-> Bitmap Index Scan on bigtable_pkey (cost=0.00..4.45 rows=1 width=0)
Index Cond: (id = 4)

melkij=> explain select * from bigtable where id in (1,3,4);
QUERY PLAN
--------------------------------------------------------------------------------
Index Scan using bigtable_pkey on bigtable (cost=0.44..17.37 rows=3 width=12)
Index Cond: (id = ANY ('{1,3,4}'::integer[]))

Как можно заметить, in был переписан в другую форму и оценён дешевле, чем несколько эквивалентных or

Какие-то другие СУБД может быть переписывают оба запроса в идентичное представление, или же переписывают in в группу or. Вопросы производительности необходимо рассматривать только в рамках какой-то конкретной реализации.

MS SQL Server на оба запроса генерирует абсолютно одинаковые планы не зависимо от наличия индекса.

В MySQL, согласно документации in использует бинарный поиск, т.е. теоретически должен быть быстрее, чем or. На практике оказывается, что при наличии индекса разницы почти нет, а при отсутствии in работает примерно на 30% быстрее.

@sqlhub

🖥 Pandas + SQL. Запросы SQL для работы с датафреймами pandas.

Данная библиотека может быть полезна:

- специалистам, знающим SQL, но еще не знакомых с синтаксисом pandas, при этом уже готовый код будет для них более читабельным;

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

$ pip install -U pandasql

▪Читать
▪Примеры с кодом
▪Github

@sqlhub

🖥 Задача

Каким образом можно сократить количество взятых строк из таблицы Jobs, чтобы при выборе значений брались все значения из первой таблицы и только 10 значений из второй?

Решения
Вариант 1:

select *
from hr.job_history jh
left join (
select *
from hr.jobs jb
order by job_id
fetch first 10 rows only) jb on jb.job_id = jh.job_id

Вариант 2:

SELECT *
FROM HR.JOB_HISTORY JH
LEFT JOIN ( SELECT *,
ROW_NUMBER() OVER (PARTITION BY JB.job_id
ORDER BY JB.id -- обязательна уникальная сортировка
) rownum
FROM HR.JOBS JB ) JBS
ON JH.job_id = JBS.job_id
WHERE JBS.rownum < 10;

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

@sqlhub

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

Задча написать SQL запрос, который бы возвращал точно такой вывод:

id|
---
1 |
2 |
3 |
5 |

Представьте, что в БД нет ни одной таблицы, и создавать их нельзя.

mysql> SELECT 1 id UNION SELECT 2 id UNION SELECT 3 id UNION SELECT 5 id;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 5 |
+----+
4 rows in set (0,00 sec)

Существует ли более элегантное решение?

В MySQL единственный вариант (Алиас нужен только у первого числа):

select 1 ID union select 2 union select 3 union select 5

Самый красивый (для малого количества чисел) вариант можно написать в MS-SQL и Postrgess:

select * from (values (1),(2),(3),(5)) as t(id)

Oracle (С использованием системного типа в качестве коллекции):

select column_value ID from table(sys.odcinumberlist(1,2,3,5))

Если чисел значительно больше и они идут просто подряд, то почти универсальный (Из широко распространенных СУБД НЕ работает только в MySQL):

with Q as (
select 1 ID
union all
select ID+1 from Q where ID<5
) select * from Q

Самый лаконичный (IMHO) вариант для большого количества чисел подряд в Oracle:

select rownum id from DUAL connect by rownum<6

ЕщёВ Oracle можно воспользоваться XQuery последовательностью:

select to_number(column_value) id from xmlTable ('1 to 3, 5')
/
ID
----------
1
2
3
5

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

@sqlhub

🔥 Подборка каналов для разработчиков

⭐️ Нейронные сети
@vistehno - chatgpt ведет блог, решает любые задачи и отвечает на любые ваши вопросы.
@aigen - сети для генерации картинок. видео, музыки и многого другого.
@neural – погружение в нейросети.

🖥 Machine learning
@ai_ml – погружение в нейросети, ai, Chatgpt, midjourney, машинное обучение.
@datasc - дата сайнс обучаем самой востребованной профессии
@machinelearning_ru – машинное обучении на русском от новичка до профессионала.
@machinelearning_interview – подготовка к собеседованию.
@datascienceiot – бесплатные книги Machine learning
@ArtificialIntelligencedl – канал о искусственном интеллекте

@machinee_learning – чат о машинном обучении
@datascienceml_jobs - работа ds, ml
@Machinelearning_Jobs - чат работы мл

🖥 SQL базы данных

@sqlhub - Повышение эффективности кода с грамотным использованием бд.
@chat_sql - чат изучения бд.

🖥 Python

@pythonl - главный канал самого популярного языка программирования.
@pro_python_code – учим python с ментором.
@python_job_interview – подготовка к Python собеседованию.
@python_testit - проверочные тесты на python
@pythonlbooks - современные книги Python
@python_djangojobs - работа для Python программистов
@python_django_work - чат обсуждения вакансий


🖥 Javascript / front

@react_tg - - 40,14% разработчиков сайтов использовали React в 2022 году - это самая популярная библиотека для создания сайтов.
@javascript -канал для JS и FrontEnd разработчиков. Лучшие практики и примеры кода. Туториалы и фишки JS
@Js Tests - каверзные тесты JS
@hashdev - погружение в web разработку.
@javascriptjobjs - отборные вакансии и работа FrontEnd.
@jsspeak - чат поиска FrontEnd работы.

🖥 Java
@javatg - выучить Java с senior разработчиком по профессиональной методике.
@javachats - чат для ответов на вопросы по Java
@java_library - библиотека книг Java
@android_its - Android разработка
@java_quizes - тесты Java
@Java_workit - работа Java
@progersit - шпаргалки ит

👣 Golang
@Golang_google - восхитительный язык от Google, мощный и перспективный.
@golang_interview - вопросы и ответы с собеседований по Go. Для всех уровней разработчиков.
@golangtests - интересные тесты и задачи GO
@golangl - чат изучающих Go
@GolangJobsit - отборные вакансии и работа GO
@golang_jobsgo - чат для ищущих работу.
@golang_books - полезные книги Golang
@golang_speak - обсуждение языка Go

🖥 Linux
@linux -топ фишки, гайды, уроки по работе  с Linux.
@linux chat - чат linux для обучения и помощи.
@linux_read - бесплатные книги linux

👷‍♂️ IT работа

@hr_itwork -кураторский список актуальных ит-ваканнсии

🤡It memes
@memes_prog - ит-мемы

⚙️ Rust
@rust_code - Rust избавлен от болевых точек, которые есть во многих современных яп
@rust_chats - чат rust

#️⃣ c# c++
C# - объединяет лучшие идеи современных языков программирования
@csharp_cplus чат
С++ - Универсальность. Возможно, этот главный плюс C++.

📓 Книги

@programming_books_it - большая библиотека. программиста
@datascienceiot -ds книги
@pythonlbooks - python библиотека.
@golang_books - книги Golang
@frontendbooksit - front книги
@progersit - ит-шпаргалки
@linux_read - Linux books
@java_library - Java books

🖥 Github
@github_code - лучшие проекты с github
@bigdatai - инструменты по работе с данными

🖥 Devops
Devops - специалист общего профиля, которому нужны обширные знания в области разработки.

📢 English for coders

@english_forprogrammers - Английский для программистов

🖥 Влияние кол-ва запросов в транзакции на производительность

Нужно ли отправлять изменения в БД одной транзакцией, или каждый INSERT, UPDATE, DELETE лучше выполнять отдельной транзакцией? Что лучше при выполнении 10 несвязанных запросов (не нужно откатывать предыдущий запрос при ошибке последующего), отправить их одной транзакцией, или каждый по отдельности? Какой вариант лучше для производительности приложения и субд?

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

Если нужно удалить(вставить/поменять) действительно большое количество строк, в первом случае мы получаем большие расходы на открытие, закрытие большого количества транзакций.

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

Чтобы наглядно продемонстрируем, написал небольшой скрипт. MS SQL Server.

Кратко что он делает:

▪T_TABLE - таблица с данными
▪T_RESULT - таблица с результатами эксперимента
▪P_INSERT_ROWS @Count - вставляет в таблицу T_TABLE @Count строк
▪P_DALATE_ROWS @Count, @Pack - удаляет из таблицы
T_TABLE все строки в транзакциях по @Pack штук, и записывает затраченное время в таблицу T_RESULT.

Финальный скрипт запускает в цикле вставку 1 000 000 строк, и их удаления пачками по 1, 10 .. 1 000 000 штук. И затем выводит содержимое T_RESULT.

Скрипт:

USE tempdb;
GO
IF OBJECT_ID('P_INSERT_ROWS', 'P') IS NOT NULL DROP PROC P_INSERT_ROWS;
IF OBJECT_ID('P_DELETE_ROWS', 'P') IS NOT NULL DROP PROC P_DELETE_ROWS;
IF OBJECT_ID('T_TABLE', 'U') IS NOT NULL DROP TABLE T_TABLE;
IF OBJECT_ID('T_RESULT', 'U') IS NOT NULL DROP TABLE T_RESULT;
GO
CREATE TABLE T_TABLE (
id INT IDENTITY(1,1),
Number INT,
String NVARCHAR(4000)
)
CREATE INDEX IN_T_TABLE_NUMBER ON T_TABLE(Number ASC)
CREATE TABLE T_RESULT (
execute_time DATETIME,
Cnt INT,
Pack INT
)
GO
CREATE PROC P_INSERT_ROWS
@Count INT
AS
;WITH CTE AS(
SELECT 1 N UNION ALL SELECT N+1 FROM CTE WHERE N<@Count
)
INSERT T_TABLE
SELECT N, LEFT(REPLICATE(N, 100),4000)
FROM CTE
OPTION(MAXRECURSION 0)
GO
CREATE PROC P_DELETE_ROWS
@Count INT,
@Pack INT
AS
DECLARE @TTT DATETIME = GETDATE();
DECLARE @NumberStart INT = 1
WHILE @NumberStart < @Count
BEGIN
BEGIN TRAN;
DELETE FROM T_TABLE WHERE Number BETWEEN @NumberStart AND @NumberStart + @Pack - 1 OPTION(RECOMPILE);
COMMIT TRAN;
SET @NumberStart += @Pack;
END;
INSERT T_RESULT
SELECT GETDATE()-@TTT execute_time, @Count Cnt, @Pack Pack
GO
SET NOCOUNT ON;
DECLARE
@Count INT = 1000000,
@Pack INT = 1
WHILE @Pack <= @Count
BEGIN
EXEC P_INSERT_ROWS @Count
EXEC P_DELETE_ROWS @Count, @Pack
SET @Pack *= 10;
END
SELECT * FROM T_RESULT
GO
Содержимое T_RESULT:

execute_time Cnt Pack
1900-01-01 00:15:05.183 1000000 1
1900-01-01 00:01:56.003 1000000 10
1900-01-01 00:00:32.200 1000000 100
1900-01-01 00:00:22.370 1000000 1000
1900-01-01 00:00:21.940 1000000 10000
1900-01-01 00:00:22.663 1000000 100000
1900-01-01 00:01:10.327 1000000 1000000

Как видим, лучший результат по времени выполнения имеем, когда удаляется в одной транзакции пачками по 10000 штук, т.е. это намного эффективнее, чем удалять по одной записи в транзакции, и эффективнее, чем удалить все строки в одной транзакции. Эксперимент не строгий, запуски влияют друг на друга, но всё равно показательно.

UPD: На практике, транзакции в реальной жизни не часто бывают большими. Кроме запросов вида DELETE FROM T. В этом случае да, чаще всего если есть возможность обернуть несколько действий в одну транзакцию - лучше обернуть.

@sqlhub

🖥 Напишите SQL-запрос, выводящий max(count(...)) и другие поля таблицы, соответствующие max-параметру

Имеются следующие таблицы:

Person(поля Nom и др.) - информация о людях,
Profit(поля ID, Source, Moneys) - источники дохода,
Have_d(поля Nom, ID и др.) - связь между людьми и их доходами.
Каждый человек может иметь несколько источников дохода. Необходимо вывести всю информацию о самом популярном источнике дохода.

То есть необходимо подсчитать количество включений всех видов доходов, выбрать максимальное и вывести полученное число вместе со всеми полями таблицы Profit, соответствующими полученному максимуму.

Решение:

select top 1 t1.*
from (select profit.source,count(*) as expr1
from profit, have_d
where profit.id = have_d.id
group by profit.source) as t1
order by expr1 desc

Вариант2:

select t1.*
from (select profit.*,count(*) as expr1
from profit, have_d
where profit.id = have_d.id
group by profit.id
order by expr1 desc) as t1
limit 1

👉 Это не оптимальное решение. Пишите свое решение в комментариях👇

@sqlhub

🖥 Сравнение производительности сжатия данных MySQL на Java 2023

Каков лучший способ хранения двоичных данных в MySQL? Это вопрос, на который есть несколько ответов, в зависимости от ваших целей. Например, если вам нужно оптимизировать размер хранилища, вам, вероятно, потребуется использовать какой-либо алгоритм сжатия, который эффективно сжимает ваши данные. В моём случае мне действительно нужна высокая производительность, то есть максимально быстрое время отклика для извлечения большого двоичного объекта из MySQL.

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

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

➡️ Читать

@sqlhub

🖥 Как найти дубликаты записи в таблице по нескольким столбцам?

Вводные данные
Таблица со зданиями и координатами (в оригинальной таблице более 100 тыс. строк):

CREATE TABLE table1(
building_id int,
region varchar(55),
street varchar(55),
housenumber varchar(55),
pos_x float(100),
pos_y float(100)
);

INSERT ALL
INTO table1(building_id,region, street, housenumber, pos_x, pos_y ) VALUES(1, 'Moscow','Lenina', '1a', 45.45, 55.55 )
INTO table1(building_id,region, street, housenumber, pos_x, pos_y) VALUES(2, 'Spb','Mira', '20', 45.00, 55.00 )
INTO table1(building_id,region, street, housenumber, pos_x, pos_y) VALUES(3, 'Moscow','Lenina', '1a', 45.00, 55.00 )
INTO table1(building_id,region, street, housenumber, pos_x, pos_y) VALUES(4, 'Moscow', 'Lenina', '1a', 45.45, 55.55 )

SELECT * FROM dual;


Вывод
Building_ID region street housenumber pos_x pos_y
1 Moscow Lenina 1a 45.45 55.55
2 Spb Mira 20 45 55.55
3 Moscow Lenina 1a 45 55.55
4 Moscow Lenina 1a 45.45 55.55
Ожидаемый результат
Building_ID region street housenumber pos_x pos_y
1 Moscow Lenina 1a 45.45 55.55
4 Moscow Lenina 1a 45.45 55.55

Нужно вывести дубли по региону, улице, дому и координатам одновременно, т.е. должны остаться только ID 1 и 4. Для меня получилось проблемно, т.к. тип координат FLOAT и они никак не хотят фильтроваться.

С помощью HAVIG BY и EXISTS:

SELECT *
FROM table22 A
WHERE EXISTS (
SELECT COUNT(*)
FROM table22 t
WHERE a.region = t.region
AND a.street = t.street
AND a.housenumber = t.housenumber
AND a.pos_x = t.pos_x
AND a.pos_y = t.pos_y
HAVING COUNT(*) > 1
)

С помощью оконной фукнции

SELECT x.building_id, x.region, x.street, x.housenumber, x.pos_x, x.pos_y FROM (
SELECT
t.*
, COUNT(*) OVER(PARTITION BY t.street, t.region, t.housenumber, t.pos_x, t.pos_y ) cnt
FROM table22 t
) x
WHERE cnt > 1;

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

@sqlhub

🖥 Почему SELECT * и SELECT column возвращает строки в разном порядке?

Есть запрос №1:

WITH cte1 AS
(SELECT *,
ROW_NUMBER() OVER (ORDER BY column1) AS rn
FROM table)
SELECT column0
FROM cte1
WHERE rn = 1022;

есть запрос №2:

WITH cte1 AS
(SELECT column0,
ROW_NUMBER() OVER (ORDER BY column1) AS rn
FROM table)
SELECT column0
FROM cte1
WHERE rn = 1022;
PostgreSQL, в column0 нет NULL-ов, значения уникальны.

При использовании SELECT * и SELECT column0 , получаем разные значения на rn 1022 (и не только на 1022). Сортировка в cte1 одна и та же, ничего не меняется, кроме количества колонок. Почему так происходит ?

Это особенность SQL. Если явно не указана сортировка, то база данных может вернуть строки в любом порядке. Как ей захочется. И это может зависить не только от звездочки в запросе, но и просто от времени когда делается запрос.

Хотите избежать неоднозначности - указывайте сортировку в явном виде.

Проблема возникает из-за того, что сортировка по column1 в случае - неоднозначна т.е. в этом поле есть дубликаты и отсортировать по column1 можно разными способами, что БД и делает при разных запросах.

Вот пример:

column0 | column1
--------+---------
A | 1
B | 1

Проблема возникает из-за того, что сортировка по column1 в вашем случае неоднозначна т.е. в этом поле есть дубликаты и отсортировать по column1 можно разными способами, что БД и делает при разных запросах.

Вот пример:

column0 | column1
--------+---------
A | 1
B | 1

Если выполнить запрос из CTE, до любой порядок строк будет правильным и если мы выбираем, скажем, rn=1 то мы можем получить в результате и A и B.

Чтоб этого избежать нужно задавать сортировку по полям, которые однозначно задают порядок. Обычно достаточно включить последним (т.е. самым низкоприоритетным) полем в сортировке уникальное поле (например, первичный ключ), например:

SELECT column0,
ROW_NUMBER() OVER (ORDER BY column1, id) AS rn
FROM table

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

@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

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

▪Читать

@sqlhub

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

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

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

📌 Читать

@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

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

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

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

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

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

@sqlhub