SQL для науки про дані: розуміння та використання об’єднань – KDnuggets

Зображення автора
 

Наука про дані – це міждисциплінарна галузь, яка значною мірою покладається на отримання інформації та прийняття обґрунтованих рішень на основі величезних масивів даних. Одним із основних інструментів у набір інструментів спеціаліста з обробки даних є SQL (мова структурованих запитів), мова програмування, призначена для керування та маніпулювання реляційними базами даних.

У цій статті я зосереджуся на одній із найпотужніших функцій SQL: об’єднаннях.

Об’єднання SQL дозволяють об’єднувати дані з кількох таблиць бази даних на основі спільних стовпців. Таким чином ви можете об’єднати інформацію та створити значущі зв’язки між пов’язаними наборами даних.

Є кілька типи об'єднань SQL:

• Внутрішнє з'єднання
• Ліве зовнішнє з'єднання
• Правий зовнішній з’єднання
• Повне зовнішнє з'єднання
• Хрест з'єднати

Пояснимо кожен тип.

Внутрішнє об’єднання повертає лише ті рядки, у яких є збіг в обох таблицях, що об’єднуються. Він об’єднує рядки з двох таблиць на основі спільного ключа або стовпця, відкидаючи невідповідні рядки.

Ми візуалізуємо це таким чином.
 

Зображення автора
 

У SQL цей тип об’єднання виконується за допомогою ключових слів JOIN або INNER JOIN.

Ліве зовнішнє об’єднання повертає всі рядки з лівої (або першої) таблиці та відповідні рядки з правої (або другої) таблиці. Якщо збігів немає, він повертає значення NULL для стовпців із правої таблиці.

Ми можемо візуалізувати це так.

Зображення автора
 

Якщо ви хочете використовувати це об’єднання в SQL, ви можете зробити це за допомогою ключових слів LEFT OUTER JOIN або LEFT JOIN. Ось стаття, яка розповідає про ліве з’єднання проти лівого зовнішнього з’єднання.

Праве з’єднання є протилежністю лівого з’єднання. Він повертає всі рядки з правої таблиці та відповідні рядки з лівої таблиці. Якщо збігів немає, він повертає значення NULL для стовпців з лівої таблиці.

Зображення автора
 

У SQL цей тип об’єднання виконується за допомогою ключових слів RIGHT OUTER JOIN або RIGHT JOIN.

Повне зовнішнє об’єднання повертає всі рядки з обох таблиць, збігаючи рядки, де це можливо, і заповнюючи значення NULL для невідповідних рядків.

Зображення автора
 

Ключові слова в SQL для цього об’єднання: FULL OUTER JOIN або FULL JOIN.

Цей тип об’єднання об’єднує всі рядки однієї таблиці з усіма рядками другої таблиці. Іншими словами, він повертає декартів добуток, тобто всі можливі комбінації рядків двох таблиць.

Ось візуалізація, яка полегшить розуміння.

Зображення автора
 

Під час перехресного з’єднання в SQL ключовим словом є CROSS JOIN.

Щоб виконати об’єднання в SQL, вам потрібно вказати таблиці, які ми хочемо об’єднати, стовпці, які використовуються для зіставлення, і тип об’єднання, який ми хочемо виконати. Основний синтаксис для об’єднання таблиць у SQL такий:

SELECT columns
FROM table1
JOIN table2
ON table1.column = table2.column;

У цьому прикладі показано, як використовувати JOIN.

Ви посилаєтеся на першу (або ліву) таблицю в реченні FROM. Потім слідуєте за ним за допомогою JOIN і посилаєтеся на другу (або праву) таблицю.

Потім з’являється умова приєднання в реченні ON. Тут ви вказуєте, які стовпці використовуватимете для об’єднання двох таблиць. Зазвичай це спільний стовпець, який є первинним ключем в одній таблиці та зовнішнім ключем у другій таблиці.

Примітка. Первинний ключ — це унікальний ідентифікатор кожного запису в таблиці. Зовнішній ключ встановлює зв’язок між двома таблицями, тобто це стовпець у другій таблиці, який посилається на першу таблицю. Ми покажемо вам на прикладах, що це означає.

Якщо ви хочете використовувати LEFT JOIN, RIGHT JOIN або FULL JOIN, ви просто використовуєте ці ключові слова замість JOIN – усе інше в коді точно так само!

З CROSS JOIN справи йдуть трохи інакше. Його природа полягає в об’єднанні всіх комбінацій рядків з обох таблиць. Ось чому пропозиція ON не потрібна, а синтаксис виглядає так.

SELECT columns
FROM table1
CROSS JOIN table2;

Іншими словами, ви просто посилаєтеся на одну таблицю у FROM, а на другу – у CROSS JOIN.

Крім того, ви можете посилатися на обидві таблиці в FROM і розділяти їх комою – це скорочення для CROSS JOIN.

SELECT columns
FROM table1, table2;

Існує також один специфічний спосіб об’єднання столів – об’єднання таблиці з собою. Це також називається самоприєднанням до таблиці.

Це не зовсім окремий тип об’єднання, оскільки будь-який із вищезгаданих типів об’єднань також можна використовувати для самостійного об’єднання.

Синтаксис самооб’єднання подібний до того, що я показав вам раніше. Основна відмінність полягає в тому, що в FROM і JOIN є посилання на ту саму таблицю.

SELECT columns
FROM table1 t1
JOIN table1 t2
ON t1.column = t2.column;

Крім того, вам потрібно дати таблиці два псевдоніми, щоб розрізняти їх. Що ви робите, це об’єднуєте таблицю саму з собою та розглядаєте її як дві таблиці.

Я просто хотів згадати про це тут, але не буду вдаватися в подробиці. Якщо ви зацікавлені в самостійному приєднанні, перегляньте цей ілюстрований посібник самооб’єднання в SQL.

Настав час показати вам, як усе, що я згадав, працює на практиці. Я буду використовувати Питання для співбесіди SQL JOIN від StrataScratch, щоб продемонструвати кожен окремий тип об’єднання в SQL.

1. Приклад JOIN

Це питання від Microsoft хоче, щоб ви перерахували кожен проект і розрахували бюджет проекту працівником.

Дорогі проекти

«Отримавши список проектів і співробітників, віднесених до кожного проекту, обчисліть суму бюджету проекту, виділеного кожному співробітнику. Результат повинен містити назву проекту та бюджет проекту, округлений до найближчого цілого числа. Спершу впорядкуйте свій список за проектами з найвищим бюджетом на одного працівника».

дані

Питання дає дві таблиці.

ms_projects

ms_emp_projects

Тепер ідентифікатор стовпця в таблиці ms_projects є первинним ключем таблиці. Таку ж колонку можна знайти в таблиці ms_emp_projects, хоча й з іншою назвою: project_id. Це зовнішній ключ таблиці, який посилається на першу таблицю.

Я використаю ці два стовпці, щоб об’єднати таблиці у своєму рішенні.

код

SELECT title AS project, ROUND((budget/COUNT(emp_id)::FLOAT)::NUMERIC, 0) AS budget_emp_ratio
FROM ms_projects a
JOIN ms_emp_projects b ON a.id = b.project_id
GROUP BY title, budget
ORDER BY budget_emp_ratio DESC;

Я об’єднав дві таблиці за допомогою JOIN. Стіл ms_projects посилається на FROM, а ms_emp_projects посилається після JOIN. Я дав обом таблицям псевдоніми, що дозволило мені не використовувати довгі імена таблиць пізніше.

Тепер мені потрібно вказати стовпці, у яких я хочу об’єднати таблиці. Я вже згадував, які стовпці є первинним ключем в одній таблиці та зовнішнім ключем в іншій таблиці, тому я використаю їх тут.

Я порівнюю ці два стовпці, тому що хочу отримати всі дані з однаковим ідентифікатором проекту. Я також використовував псевдоніми таблиць перед кожним стовпцем.

Тепер, коли я маю доступ до даних в обох таблицях, я можу перерахувати стовпці в SELECT. Перший стовпець – це назва проекту, а другий – обчислення.

У цьому розрахунку використовується функція COUNT() для підрахунку кількості співробітників за кожним проектом. Потім я ділю бюджет кожного проекту на кількість працівників. Я також конвертую результат у десяткові значення та округлюю його до нуля десяткових знаків.

Вихід

Ось що повертає запит.

2. Приклад LEFT JOIN

Давайте відпрацюємо це об’єднання на Питання на співбесіді через Airbnb. Вам потрібно знайти кількість замовлень, кількість клієнтів і загальну вартість замовлень для кожного міста.

Замовлення клієнтів і деталі

«Знайти кількість замовлень, кількість клієнтів і загальну вартість замовлень для кожного міста. Включайте лише міста, які зробили принаймні 5 замовлень, і враховуйте всіх клієнтів у кожному місті, навіть якщо вони не розміщували замовлення.

Виведіть кожне обчислення разом із назвою відповідного міста».

дані

Вам надано таблиці клієнти, та замовлень.

клієнтів

замовлень

Спільні стовпці ідентифікуються з таблиці клієнтів і cust_id з таблиці замовлень. Я буду використовувати ці стовпці для об’єднання таблиць.

код

Ось як вирішити це питання за допомогою LEFT JOIN.

SELECT c.city, COUNT(DISTINCT o.id) AS orders_per_city, COUNT(DISTINCT c.id) AS customers_per_city, SUM(o.total_order_cost) AS orders_cost_per_city
FROM customers c
LEFT JOIN orders o ON c.id = o.cust_id
GROUP BY c.city
HAVING COUNT(o.id) >=5;

Посилаюся на таблицю клієнтів у FROM (це наша ліва таблиця) і LEFT JOIN з нею замовлень у стовпцях ідентифікатора клієнта.

Тепер я можу вибрати місто, використовувати COUNT(), щоб отримати кількість замовлень і клієнтів за містом, і використовувати SUM(), щоб обчислити загальну вартість замовлень за містом.

Щоб отримати всі ці розрахунки за містами, я групую вихідні дані за містами.

У питанні є один додатковий запит: «Включати лише міста, які зробили принаймні 5 замовлень…» Я використовую HAVING, щоб показати лише міста з п’ятьма або більше замовленнями, щоб досягти цього.

Питання в тому, чому я використовував LEFT JOIN і ні РЕЄСТРАЦІЯ? Підказка в питанні: «…і підрахувати всіх клієнтів у кожному місті, навіть якщо вони не робили замовлення». Можливо, не всі клієнти зробили замовлення. Це означає, що я хочу показати всіх клієнтів зі столу клієнтів, що ідеально відповідає визначенню LEFT JOIN.

Якби я використав JOIN, результат був би неправильним, оскільки я б пропустив клієнтів, які не розмістили жодного замовлення.

Примітка. Складність об’єднань у SQL відображається не в їх синтаксисі, а в семантиці! Як ви бачили, кожне об’єднання записується однаково, змінюється лише ключове слово. Однак кожне об’єднання працює по-різному і, отже, може виводити різні результати залежно від даних. Через це вкрай важливо, щоб ви повністю розуміли, що робить кожне об’єднання, і вибирали те, яке повертатиме саме те, що ви хочете!

Вихід

Тепер давайте подивимося на результат.

3. Приклад RIGHT JOIN

RIGHT JOIN є дзеркальним відображенням LEFT JOIN. Ось чому я міг би легко вирішити попередню проблему за допомогою RIGHT JOIN. Дозвольте мені показати вам, як це зробити.

дані

Столи залишаються незмінними; Я просто використаю інший тип об’єднання.

код

SELECT c.city, COUNT(DISTINCT o.id) AS orders_per_city, COUNT(DISTINCT c.id) AS customers_per_city, SUM(o.total_order_cost) AS orders_cost_per_city
FROM orders o
RIGHT JOIN customers c ON o.cust_id = c.id GROUP BY c.city
HAVING COUNT(o.id) >=5;

Ось що змінилося. Оскільки я використовую RIGHT JOIN, я змінив порядок таблиць. Тепер стіл замовлень стає лівою, а стіл клієнтів правильний. Умова приєднання залишається незмінною. Я просто змінив порядок стовпців, щоб відобразити порядок таблиць, але це не обов’язково робити.

Змінюючи порядок таблиць і використовуючи RIGHT JOIN, я знову виведу всіх клієнтів, навіть якщо вони не розмістили жодних замовлень.

Решта запиту така ж, як і в попередньому прикладі. Те саме стосується виходу.

Примітка. На практиці ПРАВИЛЬНИЙ ПРИЄДН використовується порівняно рідко. LEFT JOIN здається більш природним для користувачів SQL, тому вони використовують його набагато частіше. Усе, що можна зробити за допомогою RIGHT JOIN, також можна зробити за допомогою LEFT JOIN. Через це немає конкретної ситуації, коли RIGHT JOIN може бути кращим.

Вихід

4. Приклад повного приєднання

Питання від Salesforce і Tesla хоче, щоб ви підрахували чисту різницю між кількістю компаній, що випускають продукти, запущених у 2020 році, та кількістю компаній, що випускають продукти в попередньому році.

Продукти

«Вам надається таблиця випусків продуктів компаніями за роками. Напишіть запит, щоб підрахувати чисту різницю між кількістю компаній-продуктів, запущених у 2020 році, та кількістю компаній-продуктів, запущених у попередньому році. Виведіть назви компаній і чисту різницю чистих продуктів, випущених у 2020 році, порівняно з попереднім роком».

дані

Запитання містить одну таблицю з такими стовпцями.

car_launches

Як, у біса, я буду об’єднувати столи, коли є лише один стіл? Хм, давайте подивимось і це!

код

Цей запит трохи складніший, тому розкриватиму його поступово.

SELECT company_name, product_name AS brand_2020
FROM car_launches
WHERE YEAR = 2020;

Перший оператор SELECT знаходить компанію та назву продукту в 2020 році. Пізніше цей запит буде перетворено на підзапит.

Запитання вимагає від вас знайти різницю між 2020 і 2019 роками. Тож давайте напишемо той самий запит, але для 2019 року.

SELECT company_name, product_name AS brand_2019
FROM car_launches
WHERE YEAR = 2019;

Тепер я зроблю ці запити підзапитами та об’єднаю їх за допомогою FULL OUTER JOIN.

SELECT *
FROM (SELECT company_name, product_name AS brand_2020 FROM car_launches WHERE YEAR = 2020) a
FULL OUTER JOIN (SELECT company_name, product_name AS brand_2019 FROM car_launches WHERE YEAR = 2019) b ON a.company_name = b.company_name;

Підзапити можна розглядати як таблиці і, отже, об’єднувати. Першому підзапиту я дав псевдонім і розмістив його в реченні FROM. Потім я використовую FULL OUTER JOIN, щоб об’єднати його з другим підзапитом у стовпці назви компанії.

Використовуючи цей тип об’єднання SQL, я зможу об’єднати всі компанії та продукти у 2020 році з усіма компаніями та продуктами у 2019 році.

Тепер я можу завершити свій запит. Давайте виберемо назву компанії. Крім того, я скористаюся функцією COUNT(), щоб знайти кількість продуктів, запущених на рік, а потім відніму її, щоб отримати різницю. Нарешті, я згрупую результати за компаніями та відсортую їх також за компаніями в алфавітному порядку.

Ось і весь запит.

SELECT a.company_name, (COUNT(DISTINCT a.brand_2020)-COUNT(DISTINCT b.brand_2019)) AS net_products
FROM (SELECT company_name, product_name AS brand_2020 FROM car_launches WHERE YEAR = 2020) a
FULL OUTER JOIN (SELECT company_name, product_name AS brand_2019 FROM car_launches WHERE YEAR = 2019) b ON a.company_name = b.company_name
GROUP BY a.company_name
ORDER BY company_name;

Вихід

Ось список компаній і різниця між запущеними продуктами між 2020 та 2019 роками.

5. Приклад CROSS JOIN

Це питання Deloitte чудово показує, як працює CROSS JOIN.

Максимум два числа

«Для одного стовпця чисел розгляньте всі можливі перестановки двох чисел, припускаючи, що пари чисел (x,y) і (y,x) є двома різними перестановками. Потім для кожної перестановки знайдіть максимальне з двох чисел.

Виведіть три стовпчики: перше число, друге число і максимальне з двох.”

Запитання вимагає від вас знайти всі можливі перестановки двох чисел, припускаючи, що пари чисел (x,y) і (y,x) є двома різними перестановками. Потім нам потрібно знайти максимальне число для кожної перестановки.

дані

Запитання дає нам одну таблицю з одним стовпцем.

deloitte_numbers

код

Цей код є прикладом CROSS JOIN, а також самооб’єднання.

SELECT dn1.number AS number1, dn2.number AS number2, CASE WHEN dn1.number > dn2.number THEN dn1.number ELSE dn2.number END AS max_number
FROM deloitte_numbers AS dn1
CROSS JOIN deloitte_numbers AS dn2;

Я посилаюся на таблицю у FROM і даю їй один псевдонім. Потім я CROSS JOIN її саму, посилаючись на неї після CROSS JOIN і надаючи таблиці інший псевдонім.

Тепер можна використовувати одну таблицю як дві. Я вибираю номер стовпця з кожної таблиці. Потім я використовую оператор CASE, щоб встановити умову, яка показуватиме максимальну кількість із двох чисел.

Чому тут використовується CROSS JOIN? Пам’ятайте, що це тип об’єднання SQL, який показуватиме всі комбінації всіх рядків із усіх таблиць. Це саме те, що запитує!

Вихід

Ось знімок усіх комбінацій і більшого числа з двох.

Тепер, коли ви знаєте, як використовувати об’єднання SQL, питання полягає в тому, як використовувати ці знання в науці про дані.

Об’єднання SQL відіграють вирішальну роль у таких завданнях, як дослідження даних, очищення даних і розробка функцій.

Ось кілька прикладів того, як можна використовувати об’єднання SQL:

1. Об'єднання даних: Об’єднання таблиць дає змогу об’єднувати різні джерела даних, що дає змогу аналізувати зв’язки та кореляції в кількох наборах даних. Наприклад, об’єднання таблиці клієнтів із таблицею транзакцій може надати розуміння поведінки клієнтів і моделей купівлі.

2. Перевірка даних: Об’єднання можна використовувати для перевірки якості та цілісності даних. Порівнюючи дані з різних таблиць, ви можете виявити невідповідності, відсутні значення або викиди. Це допоможе вам очищати дані та гарантує, що дані, які використовуються для аналізу, є точними та надійними.

3. Особливість інженерії: Об’єднання можуть допомогти у створенні нових функцій для моделей машинного навчання. Об’єднуючи релевантні таблиці, ви можете отримувати значущу інформацію та створювати функції, які фіксують важливі зв’язки в даних. Це може збільшити прогностичну силу ваших моделей.

4. Агрегація та аналіз: Об’єднання дають змогу виконувати складні агрегації та аналізи в кількох таблицях. Поєднуючи дані з різних джерел, ви можете отримати повне уявлення про дані та отримати цінну інформацію. Наприклад, об’єднання таблиці продажів із таблицею продуктів може допомогти вам проаналізувати показники продажів за категоріями продуктів або регіонами.

Як я вже згадував, складність об’єднань не відображається в їх синтаксисі. Ви бачили, що синтаксис відносно простий.

Найкращі практики щодо об’єднань також відображають це, оскільки вони стосуються не самого кодування, а того, що робить об’єднання та як воно працює.

Щоб отримати максимальну віддачу від об’єднань у SQL, розгляньте наведені нижче практичні поради.

1. Зрозумійте свої дані: Ознайомтеся зі структурою та взаємозв’язками в своїх даних. Це допоможе вам вибрати відповідний тип об’єднання та вибрати потрібні стовпці для відповідності.

2. Використовуйте індекси: Якщо ваші таблиці великі або часто об’єднуються, подумайте про додавання індексів до стовпців, які використовуються для об’єднання. Індекси можуть значно покращити продуктивність запитів.

3. Будьте уважні до продуктивності: Об’єднання великих або кількох таблиць може бути дорогим у плані обчислень. Оптимізуйте свої запити, фільтруючи дані, використовуючи відповідні типи об’єднань і розглядаючи використання тимчасових таблиць або підзапитів.

4. Перевірити та підтвердити: Завжди перевіряйте результати об’єднання, щоб переконатися в правильності. Виконайте перевірку працездатності та переконайтеся, що об’єднані дані відповідають вашим очікуванням і бізнес-логіці.

Об’єднання SQL — це фундаментальна концепція, яка дає вам як спеціалісту з обробки даних можливість об’єднувати та аналізувати дані з багатьох джерел. Розуміючи різні типи об’єднань SQL, опановуючи їхній синтаксис і ефективно використовуючи їх, спеціалісти з даних можуть отримати цінну інформацію, підтвердити якість даних і керувати прийняттям рішень на основі даних.

Я показав вам, як це зробити, на п’яти прикладах. Тепер вам належить використовувати потужність SQL і об’єднань для своїх проектів із вивчення даних і досягати кращих результатів.
 
 
Нейт Розіді є фахівцем із даних та стратегією продукту. Він також є ад’юнкт-професором, який викладає аналітику, і є засновником StrataScratch, платформа, яка допомагає науковцям з даних готуватися до інтерв’ю з реальними запитаннями для інтерв’ю від провідних компаній. Зв'яжіться з ним Twitter: StrataScratch or LinkedIn.
 

• Розповсюдження контенту та PR на основі SEO. Отримайте посилення сьогодні.
• PlatoData.Network Vertical Generative Ai. Додайте собі сили. Доступ тут.
• PlatoAiStream. Web3 Intelligence. Розширення знань. Доступ тут.
• ПлатонЕСГ. Автомобільні / електромобілі, вуглець, CleanTech, Енергія, Навколишнє середовище, Сонячна, Поводження з відходами. Доступ тут.
• BlockOffsets. Модернізація екологічної компенсаційної власності. Доступ тут.
• джерело: https://www.kdnuggets.com/2023/08/sql-data-science-understanding-leveraging-joins.html?utm_source=rss&utm_medium=rss&utm_campaign=sql-for-data-science-understanding-and-leveraging-joins