Функции SQL, которые решают 80% проблем с данными

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

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

-- Базовая аналитика продаж
SELECT
region,
COUNT(*) as total_orders,
SUM(amount) as total_revenue,
AVG(amount) as avg_order_value,
SUM(amount) / COUNT(*) as calculated_avg
FROM sales
WHERE order_date >= '2024-01-01'
GROUP BY region;

-- Анализ клиентской базы
SELECT
COUNT(DISTINCT customer_id) as unique_customers,
COUNT(*) as total_transactions,
COUNT(*) / COUNT(DISTINCT customer_id) as transactions_per_customer
FROM transactions
WHERE date_part('year', transaction_date) = 2024;

В современных BI-платформах типа Glarus BI такие запросы часто автоматизированы через drag-and-drop интерфейс, но понимание SQL остается критически важным для кастомных вычислений.

-- Топ-3 продукта по продажам в каждой категории
SELECT *
FROM (
SELECT
category,
product_name,
total_sales,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_sales DESC) as rank
FROM product_sales
) ranked
WHERE rank <= 3;

-- Рост продаж месяц к месяцу
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) as prev_month_revenue,
revenue - LAG(revenue) OVER (ORDER BY month) as growth,
ROUND(
(revenue - LAG(revenue) OVER (ORDER BY month)) /
LAG(revenue) OVER (ORDER BY month) * 100, 2
) as growth_percent
FROM monthly_revenue
ORDER BY month;

-- Понимание разницы между RANK и DENSE_RANK
SELECT
student_name,
score,
RANK() OVER (ORDER BY score DESC) as rank_with_gaps,
DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank,
ROW_NUMBER() OVER (ORDER BY score DESC) as row_num
FROM exam_results;

-- Анализ трендов по неделям
SELECT
DATE_TRUNC('week', order_date) as week,
COUNT(*) as orders_count,
SUM(amount) as weekly_revenue
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '3 months'
GROUP BY DATE_TRUNC('week', order_date)
ORDER BY week;

-- Сезонный анализ продаж
SELECT
EXTRACT(month FROM order_date) as month,
EXTRACT(dow FROM order_date) as day_of_week, -- 0=Sunday
AVG(amount) as avg_order_value,
COUNT(*) as order_count
FROM orders
GROUP BY EXTRACT(month FROM order_date), EXTRACT(dow FROM order_date)
ORDER BY month, day_of_week;

-- Производительность по рабочим дням
SELECT
order_date,
CASE
WHEN EXTRACT(dow FROM order_date) IN (0, 6) THEN 'Weekend'
ELSE 'Weekday'
END as day_type,
COUNT(*) as orders
FROM orders
GROUP BY order_date, day_type;

-- Создание полных имен и очистка данных
SELECT
customer_id,
CONCAT(TRIM(first_name), ' ', TRIM(last_name)) as full_name,
UPPER(TRIM(email)) as cleaned_email,
LENGTH(phone) as phone_length,
CASE
WHEN LENGTH(TRIM(phone)) = 10 THEN 'Valid'
ELSE 'Invalid'
END as phone_status
FROM customers;

-- Извлечение доменов из email
SELECT
email,
SUBSTRING(email FROM POSITION('@' IN email) + 1) as domain,
CASE
WHEN email LIKE '%@gmail.com' THEN 'Gmail'
WHEN email LIKE '%@yahoo.com' THEN 'Yahoo'
ELSE 'Other'
END as email_provider
FROM customers
WHERE email IS NOT NULL;

-- Сегментация клиентов по объему покупок
SELECT
customer_id,
total_spent,
CASE
WHEN total_spent >= 10000 THEN 'VIP'
WHEN total_spent >= 5000 THEN 'Premium'
WHEN total_spent >= 1000 THEN 'Regular'
ELSE 'Basic'
END as customer_segment,
CASE
WHEN last_order_date >= CURRENT_DATE - INTERVAL '30 days' THEN 'Active'
WHEN last_order_date >= CURRENT_DATE - INTERVAL '90 days' THEN 'At Risk'
ELSE 'Inactive'
END as activity_status
FROM customer_summary;

-- Заполнение пропущенных значений
SELECT
product_id,
product_name,
COALESCE(description, 'No description available') as product_description,
COALESCE(weight, 0) as product_weight,
COALESCE(category, 'Uncategorized') as product_category
FROM products;

-- Анализ воронки продаж
WITH funnel_data AS (
SELECT
DATE_TRUNC('month', event_date) as month,
COUNT(CASE WHEN event_type = 'page_view' THEN 1 END) as page_views,
COUNT(CASE WHEN event_type = 'add_to_cart' THEN 1 END) as cart_adds,
COUNT(CASE WHEN event_type = 'purchase' THEN 1 END) as purchases
FROM user_events
WHERE event_date >= '2024-01-01'
GROUP BY DATE_TRUNC('month', event_date)
)
SELECT
month,
page_views,
cart_adds,
purchases,
ROUND(cart_adds::numeric / page_views * 100, 2) as cart_conversion_rate,
ROUND(purchases::numeric / cart_adds * 100, 2) as purchase_conversion_rate
FROM funnel_data
ORDER BY month;

-- Анализ метаданных пользователей
SELECT
user_id,
user_data->>'name' as user_name,
user_data->>'city' as city,
(user_data->>'age')::int as age,
jsonb_array_length(user_data->'interests') as interests_count
FROM users
WHERE user_data IS NOT NULL;

-- Анализ удержания клиентов
WITH first_orders AS (
SELECT
customer_id,
DATE_TRUNC('month', MIN(order_date)) as cohort_month
FROM orders
GROUP BY customer_id
),
order_periods AS (
SELECT
o.customer_id,
fo.cohort_month,
DATE_TRUNC('month', o.order_date) as order_month
FROM orders o
JOIN first_orders fo ON o.customer_id = fo.customer_id
)
SELECT
cohort_month,
COUNT(DISTINCT customer_id) as cohort_size,
COUNT(DISTINCT CASE WHEN order_month = cohort_month + INTERVAL '1 month'
THEN customer_id END) as month_1_retention
FROM order_periods
GROUP BY cohort_month
ORDER BY cohort_month;

-- Сегментация клиентов по RFM
WITH rfm_calc AS (
SELECT
customer_id,
MAX(order_date) as last_order_date,
COUNT(*) as frequency,
SUM(amount) as monetary_value,
NTILE(5) OVER (ORDER BY MAX(order_date) DESC) as recency_score,
NTILE(5) OVER (ORDER BY COUNT(*)) as frequency_score,
NTILE(5) OVER (ORDER BY SUM(amount)) as monetary_score
FROM orders
GROUP BY customer_id
)
SELECT
customer_id,
recency_score,
frequency_score,
monetary_score,
CASE
WHEN recency_score >= 4 AND frequency_score >= 4 AND monetary_score >= 4
THEN 'Champions'
WHEN recency_score >= 3 AND frequency_score >= 3 AND monetary_score >= 3
THEN 'Loyal Customers'
WHEN recency_score >= 3 AND frequency_score <= 2
THEN 'Potential Loyalists'
ELSE 'Other'
END as customer_segment
FROM rfm_calc;

-- Вместо медленного подзапроса
SELECT customer_id,
(SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) as order_count
FROM customers c;
-- Используйте JOIN
SELECT c.customer_id,
COALESCE(o.order_count, 0) as order_count
FROM customers c
LEFT JOIN (
SELECT customer_id, COUNT(*) as order_count
FROM orders
GROUP BY customer_id
) o ON c.customer_id = o.customer_id;

-- Используйте LIMIT для тестирования
SELECT *
FROM large_table
WHERE condition = 'test'
LIMIT 1000;
-- Добавляйте даты для партиционирования
SELECT *
FROM transactions
WHERE transaction_date >= '2024-01-01'
AND transaction_date < '2024-02-01';

Современные BI-платформы существенно упрощают работу с данными. К примеру, в Glarus BI многие из описанных паттернов можно реализовать через визуальный интерфейс:

Но знание SQL остается фундаментом — оно позволяет создавать кастомные метрики и решать нестандартные задачи.

Эти функции покрывают 80% ежедневных задач аналитика данных. Освоив их, вы сможете:

✅ Быстро исследовать новые датасеты

✅ Создавать сложные аналитические отчеты

✅ Оптимизировать производительность запросов

✅ Эффективно работать с любыми BI-инструментами

Следующие шаги:

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