🖥 Сравнение производительности сжатия данных 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

🖥 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