Практическое руководство по сравнению операций соединения в обоих
SQL (язык структурированных запросов) используется для управления базами данных, в которых данные хранятся в табличной форме с помеченными строками и столбцами. NoSQL относится к разработке не-SQL или нереляционной базы данных. Он по-прежнему обеспечивает организованный способ хранения данных, но не в табличной форме.
Общие структуры, адаптированные базами данных NoSQL для хранения данных, - это пары ключ-значение, широкий столбец, граф или документ. Одна из популярных баз данных NoSQL - MongoDB, которая хранит данные в виде документов.
Документ в MongoDB состоит из пар «поле-значение». Документы организованы в структуру, называемую коллекцией. Чтобы провести аналогию, документ можно рассматривать как строку в таблице, а коллекцию можно рассматривать как всю таблицу.
В этой статье мы сравним базу данных SQL (MySQL) и базу данных NoSQL (MongoDB) с точки зрения операций соединения. Я также написал статью, демонстрирующую, как выполнять базовые операции для запросов к базам данных SQL и NoSQL.
Когда нам нужно получить данные из реляционной базы данных, нужные данные обычно распределяются по нескольким таблицам. В таких случаях мы используем соединения SQL, которые используются для обработки задач, включающих выбор строк из двух или более связанных таблиц.
В случае NoSQL данные для элемента (или точки данных) в основном хранятся в одной коллекции. Однако могут быть случаи, когда нам нужно охватить несколько коллекций, чтобы получить все необходимые данные.
Таким образом, запросы на соединение имеют решающее значение для обоих типов баз данных.
Я подготовил две таблицы и коллекции с одинаковыми данными. Первый содержит информацию о клиентах розничного бизнеса. Второй содержит информацию о заказах, сделанных этими покупателями.
Вот запись (то есть строка) в таблицах клиентов и заказов:
+---------+------+----------+--------+ | cust_id | age | location | gender | +---------+------+----------+--------+ | 1000 | 42 | Austin | female | +---------+------+----------+--------+ +----------+------------+--------+---------+ | order_id | date | amount | cust_id | +----------+------------+--------+---------+ | 1 | 2020-10-01 | 27.40 | 1001 | +----------+------------+--------+---------+
Вот документ в коллекциях клиентов и заказов:
{
"_id" : ObjectId("600e120b44284c416405dd7e"),
"cust_id" : "1000",
"age" : 42,
"location" : "Austin",
"gender" : "Female"
}
{
"_id" : ObjectId("600e141d44e046eb7c92c4fe"),
"order_id" : "1",
"date" : "2020-10-01",
"amount" : 27.4,
"cust_id" : "1001"
}
Эти две таблицы связаны друг с другом столбцом cust_id. Следующие примеры включают запросы, которые требуют использования операций соединения. Я выполню одни и те же задачи в обеих базах данных, чтобы мы могли увидеть различия и сходства.
Пример 1
Мы хотим видеть заказы от клиентов старше 40 лет.
NoSQL (MongoDB):
Мы можем выполнять операции соединения, используя ключевое слово «$ lookup» в агрегатных конвейерах. Агрегированные конвейеры очень полезны в MongoDB, поскольку они позволяют выполнять множество различных операций в одном конвейере, таких как фильтрация, сортировка, группировка, применение агрегирования данных и так далее.
В этом примере мы сначала фильтруем документы по возрасту клиентов с помощью ключевого слова «$ match», а затем выбираем документы из таблицы заказов, которые соответствуют отфильтрованному условию.
> db.customer.aggregate([
... { $match: { age: {$gt:40} }},
... { $lookup: { from: "orders",
... localField: "cust_id",
... foreignField: "cust_id",
... as: "orders_docs" }}
... ]).pretty()
{
"_id" : ObjectId("600e120b44284c416405dd7e"),
"cust_id" : "1000",
"age" : 42,
"location" : "Austin",
"gender" : "Female",
"orders_docs" : [
{
"_id" : ObjectId("600e141d44e046eb7c92c4ff"),
"order_id" : "2",
"date" : "2020-10-01",
"amount" : 36.2,
"cust_id" : "1000"
},
{
"_id" : ObjectId("600e157c44e046eb7c92c50a"),
"order_id" : "13",
"date" : "2020-10-03",
"amount" : 46.1,
"cust_id" : "1000"
}
]
}
Местные и внешние поля указывают имена полей, которые будут использоваться для объединения значений. Выходные данные содержат документы из коллекции клиентов, которые соответствуют указанному условию и заказам этих клиентов. Оказалось, что есть только одна покупательница старше 40 лет, и у нее два заказа.
SQL (MySQL)
Мы можем объединить две таблицы в запросе выбора, как показано ниже.
mysql> select orders.*
-> from customer
-> join orders
-> on customer.cust_id = orders.cust_id
-> where customer.age > 40;
+----------+------------+--------+---------+
| order_id | date | amount | cust_id |
+----------+------------+--------+---------+
| 2 | 2020-10-01 | 36.20 | 1000 |
| 13 | 2020-10-03 | 46.10 | 1000 |
+----------+------------+--------+---------+
В обычном операторе выбора мы пишем только имена выбираемых столбцов. Когда мы объединяем таблицы, столбцы указываются с именем таблицы, так что SQL знает, откуда берется столбец.
Затем мы пишем имена таблиц с ключевым словом join (например, заказы на присоединение клиентов). Ключевое слово «on» используется, чтобы указать, как эти таблицы связаны. Оператор where фильтрует строки на основе заданного условия.
Пример 2
Мы хотим видеть среднее количество заказов клиентов из каждого местоположения.
NoSQL (MongoDB):
Эта задача требует объединения двух коллекций, а затем применения агрегирования данных. И то, и другое может быть достигнуто в агрегатном конвейере с помощью этапов «$ lookup» и «$ group».
> db.customer.aggregate([
... { $lookup: { from: "orders",
... localField: "cust_id",
... foreignField: "cust_id",
... as: "orders_docs" }},
... { $group: { _id: "$location",
... avg_amount: { $avg: "$amount" }}}
... ])
{ "_id" : "Houston", "avg_amount" : 44.450000 }
{ "_id" : "Dallas", "avg_amount" : 34.591667 }
{ "_id" : "Austin", "avg_amount" : 33.333333 }
После операции соединения на этапе «$ lookup» мы группируем документы по местоположению, выбирая «$ location» в качестве идентификатора. Следующая часть определяет как функцию агрегирования, которая в нашем случае - «$ avg», так и поле для агрегирования.
SQL (MySQL)
Мы применяем функцию агрегирования при выборе столбца. Результаты группируются по местоположению с помощью предложения group by.
mysql> select customer.location, avg(orders.amount) as avg_amount
-> from customer
-> join orders
-> on customer.cust_id = orders.cust_id
-> group by customer.location;
+----------+------------+
| location | avg_amount |
+----------+------------+
| Austin | 33.333333 |
| Dallas | 34.591667 |
| Houston | 44.450000 |
+----------+------------+
Пример 3
В этом примере мы добавим критерий фильтрации к предыдущему примеру. Для каждого местоположения давайте посчитаем среднее количество заказов клиентов младше 30 лет.
NoSQL (MongoDB):
Нам просто нужно добавить этап «$ match» в начало конвейера, чтобы применить критерий фильтрации.
> db.customer.aggregate([
... { $match: { age: {$lt: 30} }},
... { $lookup: { from: "orders",
... localField: "cust_id",
... foreignField: "cust_id",
... as: "orders_docs" }},
... { $group: { _id: "$location",
... avg_amount: { $avg: "$amount" }}}
... ])
{ "_id" : "Houston", "avg_amount" : 35.625000 }
{ "_id" : "Dallas", "avg_amount" : 34.591667 }
{ "_id" : "Austin", "avg_amount" : 36.000000 }
На этапе «$ match» мы указываем условие вместе с именем поля для фильтрации.
SQL (MySQL)
Критерий фильтрации добавляется с помощью предложения where в операторе select.
mysql> select customer.location, avg(orders.amount) as avg_amount
-> from customer
-> join orders
-> on customer.cust_id = orders.cust_id
-> where customer.age < 30
-> group by customer.location;
+----------+------------+
| location | avg_amount |
+----------+------------+
| Austin | 36.000000 |
| Dallas | 34.591667 |
| Houston | 35.625000 |
+----------+------------+
Поскольку строки фильтруются до предложения group by, мы можем использовать предложение where. Если нам нужно применить фильтр, основанный на агрегированных значениях (например, avg_amount ›35), следует использовать предложение Have.
Вывод
Данные, которые нам нужно получить из реляционной (SQL) или нереляционной (NoSQL) базы данных, могут быть распределены по нескольким таблицам или коллекциям. Таким образом, очень важно иметь полное представление об операциях соединения.
Мы сделали три основных примера, чтобы продемонстрировать идею и реализацию операций соединения в базах данных SQL и NoSQL.
Спасибо за чтение. Пожалуйста, дайте мне знать, если у вас есть какие-либо отзывы.
