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

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

Оконные функции подобны швейцарскому армейскому ножу 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 указывает, что среднее значение должно быть рассчитано для текущей строки и шести предыдущих строк.

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.

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

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 и минимальное значение, максимальное значение и количество блоков, указанных в аргументах функции. Результирующий набор включает идентификатор продукта, цену и присвоенный номер корзины.

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

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

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-запросы.

Петли? В 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.

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

Чтобы выполнить самостоятельное соединение, вам нужно присвоить таблице другое псевдонимное имя для каждого экземпляра соединения. Это позволяет вам проводить различие между двумя экземплярами одной и той же таблицы при обращении к столбцам в вашей инструкции 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”.

Данные могут быть неразберихой, но с помощью функции 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 годов.

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

Статья была взята из этого источника: