Освоение SQL: Использование данных для решения сложных задач - UPROGER

Я решил подробно рассказать о том, как я подхожу к использованию SQL для запроса баз данных. Я принял участие в еженедельном конкурсе Danny’s SQL challenge, чтобы начать тематическое исследование по этой теме. Вся необходимая вам информация об этом испытании доступна здесь.

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

Прежде чем приступить к созданию SQL-запроса, убедитесь, что вы досконально понимаете вопрос и требования.
Используйте ясный и лаконичный язык для описания запроса, который вы пишете.
Всегда проверяйте свой запрос перед его отправкой. Проверьте результаты, чтобы убедиться, что они соответствуют требованиям вопроса.
Очень важно чётко описать процессы, которым вы следовали, чтобы достичь желаемого результата.
Сделайте ваш SQL-код более лёгким для чтения и понимания, правильно отформатировав его. Используйте комментарии, чтобы объяснить свой мыслительный процесс и причины, стоящие за вашим выбором.
Рассмотрите влияние вашего кода на производительность. Чтобы выполнить запрос как можно быстрее, сохраняя при этом соответствие критериям, попробуйте оптимизировать свои запросы.

ПРИМЕР №1 — Ужин у Дэнни

Вступление

Дэнни всерьёз любит японскую кухню, поэтому в начале 2021 года он решает на свой страх и риск открыть симпатичный маленький ресторанчик, в котором продаются 3 его любимых блюда: суши, карри и рамен.

Danny’s Diner нуждается в вашей помощи, чтобы помочь ресторану удержаться на плаву. Ресторан собрал некоторые очень важные данные за несколько месяцев своей работы, но понятия не имеет, как использовать их, чтобы помочь им вести бизнес.

Постановка задачи

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

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

Дэнни предоставил вам образец своих общих данных о клиентах из-за проблем с конфиденциальностью, но он надеется, что этих примеров будет достаточно, чтобы вы могли написать полностью функционирующие SQL-запросы, которые помогут ему ответить на его вопросы!

Дэнни поделился с вами 3 ключевыми наборами данных для этого тематического исследования:

sales (продажи)
menu (меню)
members (клиенты)

Освоение SQL: Использование данных для решения сложных задач

Neon, полностью управляемая безсерверная платформа PostgreSQL, будет использоваться для запроса к базе данных. Запрос для создания и заполнения таблиц набором данных можно найти по ссылке выше.

Без диаграммы взаимосвязей сущностей человек не будет знать о типах данных различных полей в каждой таблице. Первым делом нужно будет узнать типы данных каждого поля.

В PostgreSQL “information_schema” – это схема, которая содержит информацию обо всех объектах базы данных, включая таблицы, представления, столбцы, ограничения и так далее. Для доступа к таблицам и представлениям, в которых расположены метаданные, могут использоваться стандартные SQL-запросы. Многие другие системы управления реляционными базами данных используют эту структуру, которая является компонентом стандарта SQL.

-- Information regarding the data_type of the fields in the sales table.
SELECT column_name,data_type
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'sales';

-- Information regarding the data_type of the fields in the menu table.
SELECT column_name,data_type
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'menu';

-- Information regarding the data_type of the fields in the member table.
SELECT column_name,data_type
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'member';

1. Какова общая сумма, которую каждый клиент потратил в ресторане?

Я обратился к таблицам sales и menu, из которых извлёк customer_id и поле агрегированной цены, используя функцию SUM(). Я сгруппировал по customer_id и упорядочил по агрегированному столбцу в порядке убывания, чтобы получить наибольшую потраченную сумму в качестве первой записи.

-- 1. What is the total amount each customer spent at the restaurant
SELECT s.customer_id,  
       SUM(m.price) AS total_amount
FROM sales s
LEFT JOIN menu m
USING(product_id)
GROUP BY s.customer_id
ORDER BY total_amount DESC;

2. Сколько дней каждый клиент посещал ресторан?

Здесь используется ключевое слово DISTINCT, поскольку клиент, скорее всего, посещал ресторан более одного раза в день. Эти различные значения затем агрегируются с помощью функции COUNT() перед группировкой по customer_id.

---- 2. How many days has each customer visited the restaurant?
SELECT customer_id, COUNT(DISTINCT order_date) AS Number_of_days_visited
FROM sales
GROUP BY customer_id;

3. Какой первый пункт меню приобрёл каждый клиент?

Я большой поклонник common table expressions (cte), что вы неоднократно увидите дальше. Я сохранил первую дату, в которую каждый клиент посетил ресторан, в cte, а также присоединил таблицу продаж к cte, используя поле customer_id и date, что позволит извлекать записи, характерные для первого посещения клиента и покупки товаров в этот день.

--3.  What was the first item from the menu purchased by each customer?
WITH first_purchase AS (
    SELECT customer_id, MIN(order_date) AS first_purchase_date
    FROM sales
    GROUP BY 1
    )

  SELECT DISTINCT f.customer_id, 
        s.product_id, 
        m.product_name, 
        f.first_purchase_date
  FROM first_purchase f
  LEFT JOIN sales s
  ON f.customer_id = s.customer_id
  AND f.first_purchase_date = s.order_date
  LEFT JOIN menu m
  USING(product_id)
  ORDER BY customer_id

4. Какой самый покупаемый товар в меню и сколько раз его покупали все клиенты?

Я суммировал количество каждого product_item, упорядочил количество в порядке убывания и использовал ключевое слово limit для извлечения только первой записи. Таблица sales была присоединена к таблице menu, чтобы я мог получить product_name вместо product_id в таблице продаж.

-- 4. What is the most purchased item on the menu and how many times was it purchased by all customers?
SELECT product_name,  
       COUNT(*) AS count
FROM sales
JOIN menu 
USING(product_id)
GROUP BY product_name
ORDER BY count DESC
LIMIT 1;

5. Какой товар был самым популярным у каждого покупателя?

Сначала было найдено количество товаров, сгруппированных по customer_id и product_name, затем я использовал оконную функцию DENSE_RANK() для ранжирования количества в порядке убывания. Этот результат запроса затем сохраняется в cte, каждый рейтинг со значением 1 будет представлять наиболее популярный продукт для этого клиента.

--5. Which item was the most popular for each customer?
WITH ranked_fav_item AS (
          SELECT customer_id, 
                product_name,
                COUNT(*) AS count, 
                DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY COUNT(*) DESC) AS rank
          FROM sales s
          JOIN menu m
          ON s.product_id = m.product_id
          GROUP BY 1,2
          ORDER BY customer_id)
          
SELECT customer_id, product_name, count
FROM ranked_fav_item
WHERE rank = 1

6. Какой товар был приобретён покупателем после того, как он зарегистрировался в программе членства ресторана?

В таблице members перечислены клиенты, которые зарегистрировались в программе членства, а также дата, когда они это сделали. Идентификатор клиента будет использоваться для привязки таблицы members к таблице sales, которая вернёт только клиентов, являющихся участниками. Мы дополнительно привязываем sales к таблице members в полях даты (m.join date = s.order date), чтобы получить информацию о продукте, который был приобретён до или после того, как потребитель стал членом программы. Я использовал оконный метод dense rank() для ранжирования на основе поля даты. Я также подключил таблицу menu, чтобы получить доступ к столбцу с названием продукта. Как только этот запрос был сохранен в cte, создаётся окончательный запрос для получения соответствующих полей, когда ранг равен 1.

-- 6. Which item was purchased first by the customer after they became a member?
 
WITH ranked_table AS (
      SELECT m.customer_id, product_name,
            DENSE_RANk() OVER(PARTITION BY m.customer_id ORDER BY order_date) AS rank
     FROM members m
     JOIN sales s
     ON m.customer_id = s.customer_id
     AND m.join_date <= s.order_date
     JOIN menu 
     USING(product_id)
     ORDER BY customer_id)
     
  SELECT customer_id,product_name
  FROM ranked_table
  WHERE rank = 1;

7. Какой товар был приобретён непосредственно перед тем, как клиент зарегистрировался в программе членства ресторана?

Процесс такой же, как и в предыдущем вопросе, но мы изменим оператор сравнения, используемый для объединения полей даты, чтобы мы могли получать товары, купленные непосредственно до того, как потребитель стал участником программы (m.join date > s.order date)

-- 7. Which item was purchased just before the customer became a member?
 WITH ranked_table AS (
      SELECT m.customer_id, product_name,
            DENSE_RANk() OVER(PARTITION BY m.customer_id ORDER BY order_date DESC) AS rank
     FROM members m
     LEFT JOIN sales s
     ON m.customer_id = s.customer_id
     AND m.join_date > s.order_date
     LEFT JOIN menu 
     USING(product_id)
     ORDER BY customer_id)
     
  SELECT customer_id,product_name
  FROM ranked_table
  WHERE rank = 1;

8. Каково общее количество товаров и какова сумма, потраченные каждым участником до того, как он стал участником?

В этом случае мы получили агрегированное количество товаров и агрегированную сумму цен по каждому клиенту, но объединение таблиц было таким же, как в предыдущем запросе.

 -- 8. What is the total items and amount spent for each member before they became a member?
 SELECT s.customer_id, COUNT(DISTINCT s.product_id) AS unique_items, SUM(price) AS total_amount
FROM sales s
JOIN members  m
ON m.customer_id = s.customer_id
AND m.join_date > s.order_date
JOIN menu 
ON  menu.product_id = s.product_id
GROUP BY s.customer_id

9. Если каждый потраченный доллар приравнивается к 10 баллам, а множитель sushi увеличивать баллы в 2 раза, сколько баллов получит каждый клиент?

Оператор case используется для создания нового поля на основе предоставленных критериев начисления баллов, выходные данные последующего запроса сохраняются в cte, а затем для каждого клиента генерируется агрегированная сумма баллов.

-- 9.  If each $1 spent equates to 10 points and sushi has a 2x points multiplier - how many points would each customer have?
WITH points_table AS (
    SELECT customer_id, 
           product_name,
           price,
           CASE 
                WHEN product_name = 'sushi' THEN price*20 
                ELSE price*10 END AS point
    FROM sales
    JOIN menu
    USING(product_id)
  )
  
  SELECT customer_id, SUM(point) AS total_point
  FROM points_table
  GROUP BY customer_id

10. В течение первой недели после того, как клиент присоединяется к программе (включая дату своего присоединения), он зарабатывает 2 балла за все блюда, а не только за суши — сколько баллов у клиентов A и B на конец января?

Я включил пункт when в оператор case, где я определил диапазон дат в неделю, чтобы применить новый критерий начисления баллов, основанный на первой неделе присоединения к программе членства; для этого я добавил интервал в 6 дней со дня, когда потребитель стал участником.

-- 10. In the first week after a customer joins the program (including their join date) they earn 2x points on all items, not just sushi - how many points do customer A and B have at the end of January?
WITH updated_points_table AS(
SELECT s.customer_id, 
           product_name,
           price,
           order_date,
           join_date,
           CASE 
                WHEN product_name = 'sushi' THEN price*20 
                WHEN order_date BETWEEN join_date AND join_date+ INTERVAL'6 days' THEN price*20 
                ELSE price*10 END AS point
    FROM sales s
    JOIN menu m
    ON s.product_id = m.product_id
    JOIN members 
    ON members.customer_id = s.customer_id
    WHERE EXTRACT(MONTH FROM order_date) = 1
)
    
    SELECT customer_id, SUM(point) AS total_points
    FROM updated_points_table
    GROUP BY customer_id

Бонусные вопросы

1. Воссоздайте таблицу, в которой клиентам присваивается значение Y с момента присоединения к программе членства и значение N для обычных потребителей.

SELECT customer_id,
       order_date, 
       product_name, 
       price,
       CASE 
          WHEN order_date >= join_date THEN 'Y'
          ELSE 'N' END AS member
FROM sales
LEFT JOIN members
USING(customer_id)
LEFT JOIN menu
USING(product_id)
ORDER BY customer_id, order_date;

2. Повторите предыдущую таблицу с новым полем, которое ранжирует исключительно участников и возвращает значение null для нечленов или в периодах отсутствия членства.

WITH new_table AS (
 SELECT customer_id,
     order_date, 
     product_name, 
     price,
     CASE 
     WHEN order_date >= join_date THEN 'Y'
     ELSE 'N' END AS member 
 FROM sales
 LEFT JOIN members
 USING(customer_id)
 LEFT JOIN menu
 USING(product_id)
)

SELECT *, CASE WHEN member = 'Y' 
      THEN DENSE_RANK()OVER(PARTITION BY customer_id, member ORDER BY order_date)
      ELSE NULL END AS ranking
FROM new_table;

Идеи и рекомендации

На данный момент ресторан заработал больше денег на клиентах A и B.
Клиенты A и B стали участниками после того, как попробовали суши, было бы неплохо порекомендовать суши клиенту C.
Больше всего в меню продаётся рамен.
Клиентам В одинаково нравятся все блюда в меню, но клиенты А и С питают особую любовь к рамену.
Ресторан может использовать систему начисления баллов как способ предоставления скидок; например, если клиент наберёт 2000 баллов, он получит бесплатное питание или скидку. Это пойдёт на пользу бизнесу, привлекая больше клиентов и увеличивая продажи.

Заключение

Подводя итог, можно сказать, что освоение SQL имеет решающее значение для каждого специалиста по обработке данных, который хочет извлекать знания и решения из обширных и сложных наборов данных. В этой статье мы рассмотрели тематическое исследование и подходы SQL, которые использовались для извлечения и анализа данных, чтобы реагировать на сложные бизнес-задачи.

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

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