New set and analytic function MUL

Detail name	Value
Changelog Number	9205
Type	New Feature
Status	Resolved
Fix Versions	Exasol 7.0.0
Resolution Date	2020-09-11

Background
Currently, there is no easy way in Exasol to compute the product of a column or a window. Workarounds that use logarithms and exponentiation exists, but are slow and error-prone.

Feature description
We introduce the new set and analytic function MUL. This function computes the product of a column or window. All multiplications are performed using 64 bit floating point arithmetic and the result type is DOUBLE PRECISION. The argument must be castable to a numeric type.

Examples:

CREATE OR REPLACE TABLE inflation_rates (relevant_year DECIMAL(4, 0), annual_inflation DOUBLE);
INSERT INTO inflation_rates VALUES (2019, 1.4451);
INSERT INTO inflation_rates VALUES (2018, 1.7647);
INSERT INTO inflation_rates VALUES (2017, 1.4925);
INSERT INTO inflation_rates VALUES (2016, 0.5000);
INSERT INTO inflation_rates VALUES (2015, 0.5025);
INSERT INTO inflation_rates VALUES (2014, 1.0152);
INSERT INTO inflation_rates VALUES (2013, 1.4418);
INSERT INTO inflation_rates VALUES (2012, 1.9958);
INSERT INTO inflation_rates VALUES (2011, 2.1459);
INSERT INTO inflation_rates VALUES (2010, 1.0846);
INSERT INTO inflation_rates VALUES (2009, 0.3264);

-- Set function 
SELECT MUL(annual_inflation) total_inflation_rate FROM inflation_rates;

TOTAL_INFLATION_RATE
2.1222281724238696 


-- Analytic function
SELECT relevant_year, MUL(annual_inflation) OVER (ORDER BY relevant_year ASC ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) inflation_last_five_years FROM inflation_rates;

RELEVANT_YEAR	INFLATION_LAST_FIVE_YEARS
2019	0.9562890809252812
2018	0.6718044944677499
2017	0.5488795376685
2016	0.73397238276636
2015	3.150062672356664
2014	6.799120347140375
2013	2.1860055962437137
2012	1.516164236540237
2011	0.7596774408960001
2010	0.35401344
2009	0.3264

Benefits
This feature makes it easy to efficiently compute products.

Restrictions
All computations are performed using 64 bit floating point arithmetic. This means that results can be inexact and overflows can happen if many numbers are multiplied.