Быстрый старт
Развертывание
Моделирование данных
Подключение
Запись данных
Миграция
Запросы
Операции и обслуживание
Типовое обслуживание
Секционирование
Резервное копирование и восстановление
Масштабирование
Зеркалирование
Управление ресурсами
Безопасность
Мониторинг
Настройка производительности
Устранение неполадок
Справочник
Руководство по инструментам
Типы данных
Хранилище данных
Выполняющая система
Потоковая передача
Восстановление после сбоев
Конфигурация
Индексы
Расширения
Справочник по SQL
Часто задаваемые вопросы
В этом документе приведены примеры типичных запросов в сценариях OLAP и OLTP. Поскольку большинство реальных шаблонов запросов носят гибридный характер, классификация здесь не является строго исключающей.
Примечание!
Понимание требований к запросам в конкретном сценарии — один из ключевых этапов построения точной модели данных.
OLAP означает онлайн-аналитическую обработку (Online Analytical Processing). Обычно это называют сценарием хранилища данных, который в основном используется для поддержки принятия решений. OLAP позволяет выполнять сложный анализ данных, предоставляя специалистам по данным, бизнес-аналитикам и другим сотрудникам возможность исследовать данные с различных точек зрения. Он поддерживает приложения бизнес-аналитики (BI), интеллектуального анализа данных и другие системы поддержки решений.
Пример:
=# SELECT *,
row_number() OVER (ORDER BY c1 DESC),
rank() OVER (ORDER BY c1 DESC),
dense_rank() OVER (ORDER BY c1 DESC)
FROM t1;Ключевые моменты:
row_number(), rank() и dense_rank() для вычисления различных типов ранжирования:row_number() присваивает каждой строке уникальный последовательный ранг на основе убывающего порядка столбца c1.rank() присваивает ранг каждому уникальному значению в c1, упорядоченному по c1 в порядке убывания. Одинаковые значения получают одинаковый ранг, а следующие ранги пропускаются соответствующим образом.dense_rank() присваивает ранг каждому уникальному значению в c1, упорядоченному по c1 в порядке убывания. Одинаковые значения получают одинаковый ранг, но после этого пропусков в нумерации рангов не происходит.Пример:
=# SELECT *,
first_value(c1) OVER (ORDER BY c1 DESC),
last_value(c1) OVER (ORDER BY c1 DESC)
FROM t1;Пример:
=# SELECT *,
SUM(c1) OVER (ORDER BY c1 DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS sum_c1 -- Frame declaration includes all rows from first to last
FROM t1;Определение окон также может контролировать область агрегированных вычислений. Например, расчёт месячной выручки и её среднего значения за текущий месяц и два месяца до и после:
=# SELECT month,
revenue,
avg(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
FROM t;Ключевые моменты:
Получение записей лиц, доход которых попадает в диапазон от 40% до 70% (включительно) в каждом городе:
=# SELECT id,name,salary,city
FROM (
SELECT *,
ntile(10) OVER (
PARTITION BY city
ORDER BY salary DESC
) AS bucket
FROM city_salary
) AS t
WHERE t.bucket >= 4 AND t.bucket <= 7;Ключевые моменты:
ntile() для разделения записей в таблице city_salary на 10 равных по размеру групп (корзин). Затем фильтруются и возвращаются только записи из групп с 4 по 7.Расчёт процентного изменения месячной выручки по сравнению с предыдущим месяцем:
=# WITH detail(m, r, l) AS (
SELECT month,
revenue,
lag(revenue, 1) OVER (ORDER BY month) AS last_month_revenue
FROM sales
)
SELECT m, ((r-l)::float8/l * 100)::decimal(18,2) || '%'
FROM detail;Ключевые моменты:
(r-l)::float8/l * 100)::decimal(18,2) || '%': Рассчитывает темп роста путём вычитания выручки прошлого месяца из выручки текущего месяца и деления на выручку прошлого месяца. Приведение типов преобразует результат в процент с двумя знаками после запятой.Расчёт доли месячной выручки от общей выручки за квартал:
=# SELECT *,
revenue::real / quarter_revenue * 100 AS percent_of_quarter
FROM (
SELECT quarter,
month,
revenue,
sum(revenue) OVER (PARTITION BY quarter) AS quarter_revenue
FROM sales2
) AS t;Определение записей, в которых значения отличаются от предыдущих. Такой тип запросов полезен для выявления изменений в иначе стабильных наборах данных:
=# SELECT time, read FROM (
SELECT time,
read,
read - lag(read) OVER (ORDER BY TIME) AS diff
FROM disk
WHERE time BETWEEN '2021-04-10 21:00:00'::timestamp AND '2021-04-10 21:01:00'::timestamp
AND tag_id = 1 ) ht
WHERE diff IS NULL OR diff != 0
ORDER BY time;Ключевые моменты:
lag() для вычисления разницы между текущим и предыдущим значением read.WHERE diff IS NULL OR diff != 0 сохраняет только те записи, где разница составляет NULL или не равна нулю.Вычисление 20-го перцентиля скорости чтения и 30-го перцентиля скорости записи, упорядоченных по tag_id:
=# SELECT tag_id,
percentile_cont(0.2) WITHIN GROUP (ORDER BY read) AS read,
percentile_cont(0.3) WITHIN GROUP (ORDER BY write) AS write
FROM disk
GROUP BY tag_id
ORDER BY tag_id;Если параметр функции равен 0.5, вычисляется медиана.
Например, использование соединений нескольких таблиц и агрегаций для сравнения показателей розничных магазинов в праздничные дни:
=# WITH holiday_rst AS (
SELECT year,holiday,date,row_number() OVER(PARTITION BY year,holiday ORDER BY date)
AS the_day_of_the_holiday
FROM dim_holiday_info
)
SELECT t.year,t.holiday,t.date,t.the_day_of_the_holiday,b.date AS corresponding_date_of_previous_year
FROM holiday_rst t
LEFT JOIN holiday rst b
ON t.year = b.year + 1
AND t.holiday = b.holiday
AND t.the_day_of_the_holiday = b.the_day_of_the_holiday;Ключевые моменты:
the_day_of_the_holiday) каждому дню в пределах праздничного периода каждого года.В области бизнес-аналитики (BI) многомерный анализ позволяет пользователям исследовать наборы данных по различным измерениям. С помощью инструментов визуализации данных скрытые закономерности в больших объёмах данных представляются руководителям в виде диаграмм, графиков и дашбордов, что обеспечивает интерактивное исследование и прогнозирование.
Выполнение агрегаций по нескольким измерениям и совместное представление результатов:
=# SELECT city,
season,
category,
sum(value)
FROM bi_sales
GROUP BY GROUPING SETS (city, season, category)
ORDER by (city);
city | season | category | sum
----------+--------+-----------+------
Beijing | | | 1098
Shanghai | | | 1089
Guangzhou| | | 1080
| Q3 | | 1989
| | Groceries | 1089
| Q1 | | 189
| | Electrics | 999
| | Books | 1179
| Q2 | | 1089
(9 rows)Агрегированные результаты показывают:
value для каждого города.value для каждого сезона.value для каждой категории товаров.OLTP означает онлайн-транзакционную обработку (Online Transaction Processing). Это транзакционные сценарии, обычно используемые для повседневных операций. Системы OLTP обеспечивают быструю и точную обработку транзакций, таких как снятие средств в банкомате, интернет-банкинг, покупки в торговых точках, заказы в электронной коммерции, бронирование отелей или авиабилетов. Они поддерживают выполнение в реальном времени множества операций с базой данных, включая вставку, обновление, удаление и выборку данных.
Специальные (ad hoc) запросы позволяют пользователям динамически задавать условия фильтрации в соответствии с текущими потребностями. Система формирует отчёты или результаты по требованию. В отличие от заранее определённых запросов приложений, ad hoc запросы определяются пользователем и являются гибкими.
=# SELECT * FROM sales WHERE customer_id=100;