SQL-запросы. Диаграммы Венна (Эйлера-Венна)

Визуализация объединений таблиц в SQL

Чтобы понять как будут объединены таблицы через SQL на JOIN, сначала нужно посмотреть и разобраться как объединяются данные на простых примерах и лучше конечно с визуализацией. Для этого можно использовать Jupyter Notebook, простую БД на локали SQLite и пару библиотек. Открываем ноутбук, пишем...

# Импортируем библиотеки
import matplotlib.pyplot as plt
import sqlite3
import pandas as pd

from sqlalchemy import create_engine
from matplotlib import pyplot as plt
from matplotlib_venn import venn2, venn2_circles

Функция и настройки для постройки диаграмм Венна

# Настройка стиля
plt.figure(figsize=(15, 10))
plt.suptitle('SQL-соединения в диаграммах Венна', fontsize=16)

# Цветовые параметры
BORDER_COLOR = '#0e7cd6' # Цвет обводки кругов
FILL_COLOR = '#0e7cd6' # Цвет заливки результирующих областей
BG_COLOR = 'white' # Фоновый цвет

# Функция для создания диаграммы
def create_venn(ax, subsets, set_labels, active_regions, title):
# Создаем диаграмму
···v = venn2(subsets=subsets, set_labels=set_labels, ax=ax)
# Убираем цифры
···for text in v.subset_labels:
······if text is not None:
·········text.set_text('')

# Настраиваем обводку кругов
···c = venn2_circles(subsets=subsets, linestyle='solid', linewidth=2, color=BORDER_COLOR, ax=ax)

# Закрашиваем все области белым по умолчанию
···for region in ['10', '01', '11']:
······if v.get_patch_by_id(region):
·········v.get_patch_by_id(region).set_color(BG_COLOR)
·········v.get_patch_by_id(region).set_alpha(1.0)

# Закрашиваем активные области синим
···for region in active_regions:
······if v.get_patch_by_id(region):
·········v.get_patch_by_id(region).set_color(FILL_COLOR)
·········v.get_patch_by_id(region).set_alpha(0.5) # Полупрозрачность

···ax.set_title(title)

Пробные датафреймы

Таблица users

users = pd.DataFrame({'id': [1, 2, 3, 4, 5, 6, 7, 8, ], 'name': ['Геннадий Серый', 'Шаурман Белый', 'Дени Духовкин', 'Игорь Поттов', 'Рома Патисон', 'Николай Теслов', 'Илья Масковский', 'Фёдор Сумкин']})
display(users)

Таблица items

items = pd.DataFrame({'user_id': [1, 3, 5, 1, 7], 'item_name': ['Посох', 'Кольцо', 'Свиток', 'Шляпа', 'Шпатель'], 'value': [25, 50, 12, 34, 9]})
display(items)

Вывод таблиц через display

Вывод таблиц через display

Для работы с БД нужно для начала занести данные в эту самую БД. Для этого нужна функция

Запись датафрейма в таблицу

def excel_to_sqlite(df, db_path, table_name):
# Подключаемся к базе
···with sqlite3.connect(db_path) as conn:

# Записываем данные в таблицу
······df.to_sql(table_name, conn, if_exists='replace', index=False)

# Проверяем что данные записались
······check = pd.read_sql(f"SELECT * FROM {table_name}", conn)
······print('Проверка записи')
······display(check)

# Запись таблицы users

excel_to_sqlite(users, 'demo3.db', 'users')

# Запись таблицы items

excel_to_sqlite(items, 'demo3.db', 'items')

Запись таблиц в БД

Для отправки SQL запросов в БД необходима функция. Пишем функцию, которая может отправлять один запрос в БД. Один запрос, это значит то, что, например, функция может выполнить SELECT, но потом не сможет выполнить CREATE т.к. это уже будет вторая операция на создание таблицы и эти команды разделяются в SQL точкой с запятой.

# Функция для запросов в БД SQLite
def query_sql(query_str):
···conn = sqlite3.connect('demo3.db')
···print(pd.read_sql(query_str, conn))
···conn.close()

Узнать какие таблицы есть в БД

Проверим какие таблицы записались в БД.

# Запрос для получения списка всех таблиц в базе данных
tables_query = "SELECT name FROM sqlite_master WHERE type='table';"

# Используем функцию query_sql для одного запроса
query_sql(tables_query)

Смотрим какие таблицы есть в БД

Теперь можно приступать к изучению различных запросов SQL...

1. LEFT INCLUSIVE JOIN (это просто LEFT JOIN)

Возвращает все записи из левой таблицы (users) и совпадающие записи из правой (items). Если совпадений нет, поля из правой таблицы будут NULL.

sql = '''
SELECT t.*, i.item_name
FROM users t
LEFT JOIN items i ON t.id = i.user_id
'''

# LEFT INCLUSIVE JOIN (LEFT JOIN)
create_venn(plt.subplot(2, 4, 1),
subsets=(1, 1, 1),
set_labels=('A', 'B'),
active_regions=['10', '11'],
title='LEFT INCLUSIVE JOIN\n(Всё A + пересечение A с В)')

# Выполнение запроса
query_sql(sql)

Вывод результирующей таблицы и диаграммы Венна:

LEFT INCLUSIVE JOIN (LEFT JOIN)

2. LEFT EXCLUSIVE JOIN

Возвращает только записи из левой таблицы (users), которые НЕ имеют совпадений в правой (items).

sql = '''
SELECT t.*
FROM users t
LEFT JOIN items i ON t.id = i.user_id
WHERE i.user_id IS NULL
'''

# LEFT EXCLUSIVE JOIN
create_venn(plt.subplot(2, 4, 2),
subsets=(1, 1, 1),
set_labels=('A', 'B'),
active_regions=['10'],
title='LEFT EXCLUSIVE JOIN\n(Только A не в B)')

# Выполнение запроса
query_sql(sql)

Вывод результирующей таблицы и диаграммы Венна:

LEFT EXCLUSIVE JOIN

3. FULL OUTER INCLUSIVE JOIN (эмулируется в SQLite)

Возвращает все записи из обеих таблиц, включая те, у которых нет совпадений (NULL).

sql = '''
SELECT t.*, i.item_name
FROM users t
LEFT JOIN items i ON t.id = i.user_id
UNION ALL
SELECT t.*, i.item_name
FROM items i
LEFT JOIN users t ON t.id = i.user_id
WHERE t.id IS NULL

'''

# FULL OUTER INCLUSIVE JOIN
create_venn(plt.subplot(2, 4, 6),
subsets=(1, 1, 1),
set_labels=('A', 'B'),
active_regions=['10', '01', '11'],
title='FULL OUTER INCLUSIVE\n(Все записи А и В)')

# Выполнение запроса
query_sql(sql)

Вывод результирующей таблицы и диаграммы Венна:

FULL OUTER INCLUSIVE JOIN

4. INNER JOIN

Возвращает только совпадающие записи из обеих таблиц.

sql = '''
SELECT t.*, i.item_name
FROM users t
INNER JOIN items i ON t.id = i.user_id
'''

# INNER JOIN
create_venn(plt.subplot(2, 4, 3),
subsets=(1, 1, 1),
set_labels=('A', 'B'),
active_regions=['11'],
title='INNER JOIN\n(Только пересечение А и В)')

# Выполнение запроса
query_sql(sql)

Вывод результирующей таблицы и диаграммы Венна:

INNER JOIN

5. RIGHT INCLUSIVE JOIN (эмулируется в SQLite)

sql = '''
SELECT t.*, i.item_name
FROM items i
LEFT JOIN users t ON i.user_id = t.id
'''

# RIGHT INCLUSIVE JOIN
create_venn(plt.subplot(2, 4, 4),
subsets=(1, 1, 1),
set_labels=('A', 'B'),
active_regions=['01', '11'],
title='RIGHT INCLUSIVE JOIN\n(Всё B + пересечение В с А)')

# Выполнение запроса
query_sql(sql)

Вывод результирующей таблицы и диаграммы Венна:

RIGHT INCLUSIVE JOIN

6. RIGHT EXCLUSIVE JOIN (эмулируется в SQLite)

Возвращает только записи из правой таблицы (items), которые НЕ имеют совпадений в левой (users).

sql = '''
SELECT i.*
FROM items i
LEFT JOIN users t ON i.user_id = t.id
WHERE t.id IS NULL
'''

# RIGHT EXCLUSIVE JOIN
create_venn(plt.subplot(2, 4, 5),
subsets=(1, 1, 1),
set_labels=('A', 'B'),
active_regions=['01'],
title='RIGHT EXCLUSIVE JOIN\n(Только B не в A)')

# Выполнение запроса
query_sql(sql)

Вывод результирующей таблицы и диаграммы Венна:

RIGHT EXCLUSIVE JOIN

7. FULL OUTER EXCLUSIVE JOIN (эмулируется в SQLite)

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

sql = '''
-- Пользователи без товаров (LEFT EXCLUSIVE)
SELECT t.id, t.name, NULL as item_name, NULL as value
FROM users t
LEFT JOIN items i ON t.id = i.user_id
WHERE i.user_id IS NULL
UNION ALL
-- Товары без пользователей (RIGHT EXCLUSIVE)
SELECT NULL as id, NULL as name, i.item_name, i.value
FROM items i
LEFT JOIN users t ON i.user_id = t.id
WHERE t.id IS NULL
'''

# FULL OUTER EXCLUSIVE JOIN
create_venn(plt.subplot(2, 4, 7),
subsets=(1, 1, 1),
set_labels=('A', 'B'),
active_regions=['10', '01'],
title='FULL OUTER EXCLUSIVE\n(Всё вне пересечения A с B)')

# Выполнение запроса
query_sql(sql)

Вывод результирующей таблицы и диаграммы Венна:

Все диаграммы на одной картинке с сохранением в PNG-файл

# Настройка стиля
plt.figure(figsize=(15, 10))
plt.suptitle('SQL-соединения в диаграммах Венна', fontsize=16)

# Цветовые параметры
BORDER_COLOR = '#0e7cd6' # Цвет обводки кругов
FILL_COLOR = '#0e7cd6' # Цвет заливки результирующих областей
BG_COLOR = 'white' # Фоновый цвет

# Функция для создания диаграммы
def create_venn(ax, subsets, set_labels, active_regions, title):

# Создаем диаграмму
···v = venn2(subsets=subsets, set_labels=set_labels, ax=ax)

# Убираем цифры
···for text in v.subset_labels:
······if text is not None:
·········text.set_text('')

# Настраиваем обводку кругов
···c = venn2_circles(subsets=subsets, linestyle='solid', linewidth=2, color=BORDER_COLOR, ax=ax)

# Закрашиваем все области белым по умолчанию
···for region in ['10', '01', '11']:
······if v.get_patch_by_id(region):
·········v.get_patch_by_id(region).set_color(BG_COLOR)
·········v.get_patch_by_id(region).set_alpha(1.0)

# Закрашиваем активные области синим
···for region in active_regions:
······if v.get_patch_by_id(region):
·········v.get_patch_by_id(region).set_color(FILL_COLOR)
·········v.get_patch_by_id(region).set_alpha(0.5) # Полупрозрачность

···ax.set_title(title)

# LEFT INCLUSIVE JOIN (LEFT JOIN)
create_venn(plt.subplot(2, 4, 1),
subsets=(1, 1, 1),
set_labels=('A', 'B'),
active_regions=['10', '11'],
title='LEFT INCLUSIVE JOIN\n(Всё A + пересечение A с В)')

# LEFT EXCLUSIVE JOIN
create_venn(plt.subplot(2, 4, 2),
subsets=(1, 1, 1),
set_labels=('A', 'B'),
active_regions=['10'],
title='LEFT EXCLUSIVE JOIN\n(Только A не в B)')

# INNER JOIN
create_venn(plt.subplot(2, 4, 3),
subsets=(1, 1, 1),
set_labels=('A', 'B'),
active_regions=['11'],
title='INNER JOIN\n(Только пересечение А и В)')

# RIGHT INCLUSIVE JOIN
create_venn(plt.subplot(2, 4, 4),
subsets=(1, 1, 1),
set_labels=('A', 'B'),
active_regions=['01', '11'],
title='RIGHT INCLUSIVE JOIN\n(Всё B + пересечение В с А)')

# RIGHT EXCLUSIVE JOIN
create_venn(plt.subplot(2, 4, 5),
subsets=(1, 1, 1),
set_labels=('A', 'B'),
active_regions=['01'],
title='RIGHT EXCLUSIVE JOIN\n(Только B не в A)')

# FULL OUTER INCLUSIVE JOIN
create_venn(plt.subplot(2, 4, 6),
subsets=(1, 1, 1),
set_labels=('A', 'B'),
active_regions=['10', '01', '11'],
title='FULL OUTER INCLUSIVE\n(Все записи А и В)')

# FULL OUTER EXCLUSIVE JOIN
create_venn(plt.subplot(2, 4, 7),
subsets=(1, 1, 1),
set_labels=('A', 'B'),
active_regions=['10', '01'],
title='FULL OUTER EXCLUSIVE\n(Всё вне пересечения A с B)')

# Убираем пустой субплот
plt.delaxes(plt.subplot(2, 4, 8))
plt.tight_layout()
plt.savefig('venn_diagrams.png', dpi=300, bbox_inches='tight')
plt.show()

🐈‍⬛Весь код на GitHub.