6 простых способов использовать передовые методы SQL - UPROGER

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

1. Оконные функции для сложных вычислений

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

Текущий итог

SELECT 
  category,
  SUM(revenue) OVER (PARTITION BY category ORDER BY date) AS running_total
FROM sales_data;

В этом примере функция SUM используется для расчёта общей выручки по каждой товарной категории. Предложение OVER используется для определения окна, в пределах которого будет вычисляться функция. Предложение PARTITION BY используется для группировки данных по категориям продуктов, а предложение ORDER BY используется для сортировки данных по дате. Результатом является таблица, в которой показан текущий общий доход по каждой товарной категории.

Скользящая средняя

SELECT
  date,
  AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg
FROM
  sales
ORDER BY
  date;

Здесь функция AVG используется с предложением OVER для вычисления скользящего среднего. Предложение ORDER BY указывает, что данные должны быть упорядочены по дате, а предложение ROWS BETWEEN 6 PREVIOUS AND CURRENT ROW указывает, что среднее значение должно быть рассчитано для текущей строки и шести предыдущих строк.

Верхние n строк

SELECT
  date,
  region,
  amount,
  DENSE_RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank
FROM
  sales
WHERE
  rank <= 3;

Здесь функция DENSE_RANK используется с предложением OVER для присвоения ранга каждой строке на основе объёма продаж в порядке убывания. В предложении PARTITION BY указано, что расчёт должен выполняться отдельно для каждого региона, а в предложении ORDER BY указано, что расчёт должен выполняться в порядке увеличения объема продаж.

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

Процентили

SELECT
  student_id,
  score,
  PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY score) OVER (PARTITION BY student_id) AS percentile_90
FROM
  scores;

Здесь функция PERCENTILE_CONT используется с предложением OVER для вычисления 90-го процентильного балла для каждого учащегося. PARTITION BY указывает, что расчёт должен выполняться отдельно для каждого учащегося, а пункт ORDER BY указывает, что расчёт должен выполняться в порядке получения баллов. Предложение WITHIN GROUP указывает, что вычисление процентиля должно выполняться внутри группы баллов для каждого учащегося.

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

2. Оконные функции для сравнения строк

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

Получить предыдущее или следующее значение

SELECT
  order_date,
  order_total,
  LAG(order_total) OVER (ORDER BY order_date) AS previous_order_total,
  order_total - LAG(order_total) OVER (ORDER BY order_date) AS order_total_diff
FROM
  orders;

Этот запрос вычисляет разницу между общим объемом текущего заказа и общим объёмом предыдущего заказа для каждого заказа, используя функцию LAG и предложение ORDER BY.

Получить первое или последнее значение

SELECT
  customer_id,
  order_date,
  order_total,
  FIRST_VALUE(order_total) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_order_total
  LAST_VALUE(order_total) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_order_total
FROM
  orders;

Этот запрос получает общую сумму первого и последнего заказов для каждого клиента, используя функции FIRST_VALUE и LAST_VALUE соответственно, с предложениями PARTITION BY и ORDER BY для определения окна.

Сегменты гистограмм

SELECT
  product_id,
  price,
  WIDTH_BUCKET(price, 0, 100, 4) AS bucket_number
FROM
  products;

Этот запрос группирует товары в четыре блока гистограммы на основе их цены, используя функцию WIDTH_BUCKET и минимальное значение, максимальное значение и количество блоков, указанных в аргументах функции. Результирующий набор включает идентификатор продукта, цену и присвоенный номер корзины.

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

3. Общие табличные выражения и подзапросы

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

“Крепкий орешек” – это рождественский фильм? Скорее всего, нет. Я всё ещё смотрю его каждый праздничный сезон? Да, смотрю! Вам не нужно выбирать между CTE и подзапросами, просто когда их использовать, чтобы ваши запросы выполнялись как у Джона Макклейна, эффективно и неостановимо (или, по крайней мере, не застревали в вентиляционной шахте).

CTEs

WITH revenue_by_category AS (
  SELECT category, SUM(revenue) AS total_revenue
  FROM sales_data
  GROUP BY category
)
SELECT 
  category,
  total_revenue,
  total_revenue / (SELECT SUM(total_revenue) FROM revenue_by_category) AS revenue_share
FROM revenue_by_category

CTE работают в пределах ограниченной области и обеспечивают большую читаемость и возможность повторного использования, что может облегчить понимание запросов и их обслуживание.

Подзапросы

SELECT *
FROM customers
WHERE customer_id IN (
  SELECT customer_id
  FROM orders
  GROUP BY customer_id
  ORDER BY COUNT(*) DESC
  LIMIT 10
)

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

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

4. Рекурсивные общие табличные выражения (CTEs)

Петли? В SQL? Еще бы! С помощью рекурсивных общих табличных выражений (CTEs) вы можете создавать циклы, которые позволяют исследовать иерархические структуры данных, такие как деревья и графики, мощным и гибким способом. Вызывая запрос внутри самого себя, вы можете разбить сложные задачи анализа данных на более мелкие, более управляемые части и повторять их до тех пор, пока не получите нужную информацию.

Вот пример рекурсивного CTE, который вычисляет общую стоимость продукта и всех его компонентов:

WITH RECURSIVE product_cost AS (
  SELECT id, cost, id AS component_id
  FROM products
  WHERE id = 1
  UNION ALL
  SELECT products.id, products.cost, product_cost.component_id
  FROM products
  JOIN product_components ON products.id = product_components.product_id
  JOIN product_cost ON product_components.component_id = product_cost.id
)
SELECT component_id, SUM(cost) AS total_cost
FROM product_cost
GROUP BY component_id

В этом примере рекурсивный CTE с именем product_cost используется для вычисления общей стоимости продукта и всех его компонентов. Базовый вариант определяется в первой части CTE, где выбирается стоимость исходного продукта. Рекурсивный случай определён во второй части CTE, где выбирается стоимость компонентов продукта и объединяется с предыдущими результатами. Рекурсия продолжается до тех пор, пока не будут обработаны все компоненты. Наконец, основной запрос вычисляет общую стоимость каждого компонента путем группировки по столбцу component_id.

5. Самостоятельные соединения

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

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

SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;

В этом примере мы присоединяем таблицу “employees” к самой себе, используя столбец “manager_id” и присоединяясь к столбцу “employee_id”. Результатом этого запроса будет таблица с двумя столбцами: “employee_name” и “manager_name”.

6. PIVOT

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

Если вы работаете с диалектом SQL, который не поддерживает PIVOT, не волнуйтесь! Вы всё ещё можете достичь аналогичных результатов, используя операторы CASE.

Вот пример того, как вы можете сводить данные с помощью PIVOT:

SELECT *
FROM (
   SELECT category, year, revenue
   FROM sales
) sales
PIVOT (
   SUM(revenue)
   FOR year IN ([2018], [2019], [2020])
) AS sales_pivot;

И вот как вы можете достичь этих результатов, используя операторы CASE:

SELECT
   category,
   SUM(CASE WHEN year = '2018' THEN revenue ELSE 0 END) AS `2018`,
   SUM(CASE WHEN year = '2019' THEN revenue ELSE 0 END) AS `2019`,
   SUM(CASE WHEN year = '2020' THEN revenue ELSE 0 END) AS `2020`
FROM sales
GROUP BY category;

В обоих примерах SQL-запрос выбирает данные из таблицы “sales” и группирует их по столбцу “category”. Затем мы используем функции SUM с операторами PIVOT или CASE для создания трёх новых столбцов, содержащих выручку по каждой категории за каждый из 2018, 2019 и 2020 годов.

Надеюсь, что данная статья оказалась полезной для вас!