В этом документе приведены примеры типичных запросов в сценариях 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;