Автор: Leonard Lobel, Multiplicative Aggregates with the PRODUCT Function in SQL Server 2025
В этой статье блога рассматривается новая функция PRODUCT в SQL Server 2025, которая вычисляет произведение набора числовых значений — аналогично тому, как SUM и AVG работают для сложения и усреднения, но для умножения.
До появления SQL Server 2025 в SQL Server не было встроенного способа вычисления произведения значений в наборе. Приходилось использовать обходные пути, такие как циклы или определяемые пользователем агрегаты. С PRODUCT это теперь простое однострочное выражение.
PRODUCT поддерживает как агрегатную, так и аналитическую (оконную) формы и работает как со значениями ALL (по умолчанию), так и с DISTINCT. Значения NULL игнорируются, и функция совместима со всеми числовыми типами, кроме bit.
Вычисление произведения цен для каждого продукта
Первый пример иллюстрирует, как использовать новую агрегатную функцию PRODUCT в SQL Server 2025 для вычисления накопительного произведения цен для каждого продукта по нескольким заказам. Также показано, как вычислить произведение, учитывая только уникальные значения цен.
CREATE TABLE OrderDetail (
OrderId int,
ProductId int,
Price decimal(10, 4)
)
INSERT INTO OrderDetail
(OrderId, ProductId, Price) VALUES
(1, 101, 136.87),
(1, 102, 29.57),
(1, 103, 396.85),
(2, 101, 136.87),
(2, 102, 29.57),
(3, 101, 136.87),
(3, 102, 29.57),
(4, 101, 149.22),
(4, 102, 29.57)
-- Compute product of all prices and distinct prices for each ProductId
SELECT
ProductId,
ProductOfPrices = PRODUCT(Price),
ProductOfDistinctPrices = PRODUCT(DISTINCT Price)
FROM
OrderDetail
GROUP BY
ProductId
Результат:
| ProductId | ProductOfPrices | ProductOfDistinctPrices |
|---|---|---|
| 101 | 382606053.829162 | 20423.741400 |
| 102 | 764548.953348 | 29.570000 |
| 103 | 396.850000 | 396.850000 |
Альтернатива с использованием OVER (PARTITION BY ...)
Этот вариант вычисляет произведение для каждой строки, используя оконную агрегацию (то есть с OVER вместо GROUP BY). Это позволяет сохранить детальные строки (которые были потеряны в предыдущем запросе с GROUP BY) и одновременно показать общее произведение по разделу.
SELECT
ProductId,
OrderId,
Price,
ProductOfPrices = PRODUCT(Price) OVER (PARTITION BY ProductId)
FROM
OrderDetail
ORDER BY
ProductId,
OrderId
Результат:
| ProductId | OrderId | Price | ProductOfPrices |
|---|---|---|---|
| 101 | 1 | 136.8700 | 382606053.829162 |
| 101 | 2 | 136.8700 | 382606053.829162 |
| 101 | 3 | 136.8700 | 382606053.829162 |
| 101 | 4 | 149.2200 | 382606053.829162 |
| 102 | 1 | 29.5700 | 764548.953348 |
| 102 | 2 | 29.5700 | 764548.953348 |
| 102 | 3 | 29.5700 | 764548.953348 |
| 102 | 4 | 29.5700 | 764548.953348 |
| 103 | 1 | 396.8500 | 396.850000 |
Совокупная доходность на основе периодических ставок
Следующий пример использует PRODUCT для вычисления совокупной доходности финансовых инструментов за несколько периодов времени.
CREATE TABLE Instrument (
InstrumentId varchar(10),
Period tinyint,
RateOfReturn decimal(10, 4)
)
INSERT INTO Instrument
(InstrumentId, Period, RateOfReturn) VALUES
('BOND1', 1, 0.035),
('BOND1', 2, 0.0275),
('BOND1', 3, 0.0325),
('ETF1', 1, 0.08),
('ETF1', 2, -0.045),
('ETF1', 3, 0.06),
('STOCK1', 1, 0.125),
('STOCK1', 2, 0.095),
('STOCK1', 3, 0.113)
-- Compute compounded return for each instrument
SELECT
InstrumentId,
CompoundedReturn = PRODUCT(1 + RateOfReturn) - 1,
CompoundedReturnPercentage = FORMAT((PRODUCT(1 + RateOfReturn) - 1) * 100, 'N1') || '%'
FROM
Instrument
GROUP BY
InstrumentId
Результат:
| InstrumentId | CompoundedReturn | CompoundedReturnPercentage |
|---|---|---|
| BOND1 | 0.098026 | 9.8% |
| ETF1 | 0.093284 | 9.3% |
| STOCK1 | 0.371077 | 37.1% |
Приведённый выше запрос вычисляет совокупную доходность для каждого инструмента, беря произведение (1 + RateOfReturn) за все периоды и затем вычитая 1, чтобы получить столбец CompoundedReturn. Столбец CompoundedReturnPercentage показывает то же значение, отформатированное для отображения в процентах с одним десятичным знаком.
На примере BOND1 расчёт выглядит так:
| (1 + 0.035) = 1.035 | × | Доходность за период 1 |
| (1 + 0.0275) = 1.0275 | × | Доходность за период 2 |
| (1 + 0.0325) = 1.0325 | × | Доходность за период 3 |
| 1.098026 | – 1 = | Множитель роста (включая первоначальный капитал $1) |
| 0.098026 | = | Совокупная доходность (т.е. процентный прирост) |
| 9.8% | Изолированный процент прибыли/убытка |
Альтернатива с использованием OVER (PARTITION BY ...)
Как и в первом примере, этот вариант использует оконную функцию с OVER для расчёта совокупной доходности для каждой отдельной строки.
SELECT
InstrumentId,
Period,
RateOfReturn,
CompoundedReturn = PRODUCT(1 + RateOfReturn) OVER (PARTITION BY InstrumentId) - 1,
CompoundedReturnPercentage = FORMAT((PRODUCT(1 + RateOfReturn) OVER (PARTITION BY InstrumentId) - 1) * 100,'N1') || '%'
FROM
Instrument
ORDER BY
InstrumentId,
Period
Результат:
| InstrumentId | Period | RateOfReturn | CompoundedReturn | CompoundedReturnPercentage |
|---|---|---|---|---|
| BOND1 | 1 | 0.0350 | 0.098026 | 9.8% |
| BOND1 | 2 | 0.0275 | 0.098026 | 9.8% |
| BOND1 | 3 | 0.0325 | 0.098026 | 9.8% |
| ETF1 | 1 | 0.0800 | 0.093284 | 9.3% |
| ETF1 | 2 | -0.0450 | 0.093284 | 9.3% |
| ETF1 | 3 | 0.0600 | 0.093284 | 9.3% |
| STOCK1 | 1 | 0.1250 | 0.371077 | 37.1% |
| STOCK1 | 2 | 0.0950 | 0.371077 | 37.1% |
| STOCK1 | 3 | 0.1130 | 0.371077 | 37.1% |
Резюме
Новая функция PRODUCT в SQL Server 2025 приносит в T-SQL нативную мультипликативную агрегацию, устраняя необходимость в обходных путях при вычислении произведения набора числовых значений. Она поддерживает стандартную агрегацию с GROUP BY, включая DISTINCT, а также аналитические вычисления с использованием OVER (PARTITION BY ...) для сохранения отдельных детальных строк. Как мы показали на примерах цен продуктов и совокупной доходности инвестиций, PRODUCT делает вычисления, зависящие от умножения значений в наборе, более простыми и выразительными.
Комментариев нет:
Отправить комментарий