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

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

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

Не включайте пары одного и того же продукта или пары, которые уже были перечислены (например, если вы перечисляете пару продуктов 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

🔥 Top it channels

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

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

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

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

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

🖥 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 - Английский для программистов

💡 ChatGpt bot
@Chatgpturbobot - бесплатный бот ChatGpt

🖥 Тонкости дедубликации с DISTINCT

Исключить дубли из выборки можно просто добавив к SQL-запросу ключевое слово DISTINCT. Однако, это простое решение не всегда будет верным.

Чтобы гарантировать отсутствие дубликатов в наборе данных, СУБД необходимо сравнить все строки друг с другом, отсеяв повторы. Это требует много ресурсов ЦП и памяти для хранения всех строк, т.к. их нужно сравнивать друг с другом в памяти, даже если на низком уровне идет работа с хэшем. Кроме того, DISTINCT уменьшает параллелизм вычислений, снижая скорость выполнения запроса.

DISTINCT удаляет дубликаты, но не разрешает неправильные соединения и фильтры, которые на практике чаще всего и приводят к повторам, например, из-за CROSS JOIN или использования RANK вместо ROW_NUMBER, что приводит к дублированию из-за плохо определенного окна раздела.

Подробности с примерами кода смотрите здесь: https://jmarquesdatabeyond.medium.com/sql-like-a-pro-please-stop-using-distinct-31bdb6481256

@sqlhub

🖥 Пересаживание учеников

Мэри — учительница в средней школе. У неё есть таблица seat, хранящая имена учеников и сведениях об их местах в классе. Значение id в этой таблице постоянно возрастает. Мэри хочет поменять местами соседних учеников.

Вот таблица исходного размещения учеников:

+---------+---------+
| id | student |
+---------+---------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+---------+---------+

Вот что должно получиться после пересаживания соседних учеников:

+---------+---------+
| id | student |
+---------+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+---------+---------+

Напишите запрос, который позволит учительнице решить вышеописанную задачу.

Обратите внимание на то, что если количество учеников является нечётным — последнего ученика никуда пересаживать не надо.

▍Решение: использование оператора WHEN

SQL-конструкцию CASE WHEN THEN можно рассматривать как оператор if в программировании.

В нашем случае первый оператор WHEN используется для проверки того, назначен ли последней строке в таблице нечётный идентификатор. Если это так — строка не подвергается изменениям. Второй оператор WHEN отвечает за добавление 1 к каждому нечётному идентификатору (например — 1, 3, 5 превращается в 2, 4, 6) и за вычитание 1 из каждого чётного идентификатора (2, 4, 6 превращаются в 1, 3, 5).

SELECT
CASE
WHEN((SELECT MAX(id) FROM seat)%2 = 1) AND id = (SELECT MAX(id) FROM seat) THEN id
WHEN id%2 = 1 THEN id + 1
ELSE id - 1
END AS id, student
FROM seat
ORDER BY id

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


@sqlhub

🖥 Задача.Cамая высокая зарплата в подразделении.

В таблице Employee хранятся сведения о сотрудниках компании. В каждой записи этой таблицы содержатся сведения об идентификаторе (Id) сотрудника, о его имени (Name), о зарплате (Salary) и о подразделении компании, где он работает (Department).

+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
+----+-------+--------+--------------+

В таблице Department содержатся сведения о подразделениях компании.

+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+

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

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+

▍Решение: команда IN

Команда IN позволяет задавать в инструкции WHERE условия, соответствующие использованию нескольких команд OR. Например, две следующие конструкции идентичны:

WHERE country = ‘Canada’ OR country = ‘USA’
WHERE country IN (‘Canada’, ’USA’).

Здесь мы хотим получить таблицу, содержащую название подразделения (Department), имя сотрудника (Employee) и его заработную плату (Salary). Для этого мы формируем таблицу, в которой содержатся сведения об идентификаторе подразделения (DepartmentID) и о максимальной зарплате по этому подразделению. Далее мы объединяем две таблицы по условию, в соответствии с которым записи в результирующую таблицу попадают только в том случае, если DepartmentID и Salary есть в ранее сформированной таблице.

SELECT
Department.name AS 'Department',
Employee.name AS 'Employee',
Salary
FROM Employee
INNER JOIN Department ON Employee.DepartmentId = Department.Id
WHERE (DepartmentId , Salary)
IN
( SELECT
DepartmentId, MAX(Salary)
FROM
Employee
GROUP BY DepartmentId
)

@sqlhub