Скрытые ошибки SQL, допускаемые опытными аналитиками данных

Написание неправильного синтаксиса SQL не так страшно, потому что вы сразу же узнаете об этом, исправите то, что упустили, и вуаля, все работает, как ни в чем не бывало. А вот что действительно плохо, так это когда код выполняется и выдает результат, но не правильный, тогда вы можете не заметить проблему.

Чтобы продемонстрировать это, представьте, что у вас есть две таблицы, первая содержит клиентов вашего сайта, а вторая – заказы, сделанные на сайте.

customers table:
+-------------+---------------+-------------+
| customer_id | customer_name | visit_count |
+-------------+---------------+-------------+
|      1      |   John Doe    |      5      |
|      2      |  Jane Smith   |      8      |
|      3      | Mike Johnson  |      3      |
+-------------+---------------+-------------+

orders table:
+----------+-------------+------------+--------------+
| order_id | customer_id | order_date | total_amount |
+----------+-------------+------------+--------------+
|    1     |      1      | 2023-07-10 |    50.00     |
|    2     |      1      | 2023-07-12 |    75.00     |
|    3     |      2      | 2023-07-11 |   120.50     |
|    4     |      3      | 2023-07-15 |    25.75     |
+----------+-------------+------------+--------------+

Чаще всего мы работаем с таблицами не по отдельности, а с их объединениями. Приведенные выше таблицы можно объединить по столбцу customer_id.

Теперь, если мы захотим вычислить что-то простое, например, общее количество посещений сайта или общее количество посещений по пользователям, то, если мы не будем очень внимательны, то, сами того не замечая, получим неверные результаты.

SELECT SUM(c.visit_count) AS total_visits
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id

В результате будет получено 21 посещение, что неверно.

И даже когда мы пытаемся просуммировать общее количество посещений на одного пользователя :

SELECT c.customer_id, c.customer_name, SUM(c.visit_count) AS total_visits
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

Мы получаем неверные значения для одного из клиентов (клиента, имеющего более одного заказа):

customers table:
+-------------+---------------+--------------+
| customer_id | customer_name | total_visits |
+-------------+---------------+--------------+
|      1      |   John Doe    |      10      |
|      2      |  Jane Smith   |      8       |
|      3      | Mike Johnson  |      3       |
+-------------+---------------+--------------+

Мы можем увидеть, что пошло не так, взглянув на таблицу, полученную в результате объединения:

+-------------+---------------+--------------+----------+------------+--------------+
| customer_id | customer_name | visit_count  | order_id | order_date | total_amount |
+-------------+---------------+--------------+----------+------------+--------------+
|      1      |   John Doe    |     5        |    1     | 2023-07-10 |    50.00     |
|      1      |   John Doe    |     5        |    2     | 2023-07-12 |    75.00     |
|      2      |  Jane Smith   |     8        |    3     | 2023-07-11 |   120.50     |
|      3      | Mike Johnson  |     3        |    4     | 2023-07-15 |    25.75     |
+-------------+---------------+--------------+----------+------------+--------------+

Это явление называется fanout, и его трудно заметить, поскольку результаты получаются частично корректными. Например, если мы агрегируем по одному из других измерений (например, вычисляем количество посещений по клиентам), то результаты будут неверными только для клиентов, сделавших более одного заказа.

Вот весь SQL-код, который можно проверить самостоятельно:

CREATE TABLE customers (
  customer_id integer,
  customer_name varchar(255),
  visit_count integer
);

CREATE TABLE orders (
  order_id integer,
  customer_id integer,
  order_date date,
  total_amount decimal(10, 2)
);

INSERT INTO customers (customer_id, customer_name, visit_count) VALUES
(1, 'John Doe', 5),
(2, 'Jane Smith', 8),
(3, 'Mike Johnson', 3);

INSERT INTO orders (order_id, customer_id, order_date, total_amount) VALUES
(1, 1, '2023-07-10', 50.00),
(2, 1, '2023-07-12', 75.00),
(3, 2, '2023-07-11', 120.50),
(4, 3, '2023-07-15', 25.75);

SELECT c.customer_id, c.customer_name, SUM(c.visit_count) AS total_visits
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

Избежать подобных ошибок нелегко, так как в момент их возникновения они могут быть не очевидными. Но достаточно знать, что они существуют, и перепроверять результаты, либо применять агрегацию на исходной таблице, если объединение не требуется для вычислений.

Некоторые BI-инструменты, например Google Looker, устраняют эти ошибки за вас, применяя так называемые симметричные агрегаты, но в противном случае вам придется позаботиться об ошибках самостоятельно.