Looking at DuckDB’s PRODUCT() Function

Posted on by Ian

DuckDB is an in-process SQL OLAP database management system designed for analytical workloads. Among its various aggregate functions is the product() function, which calculates the product of a set of numeric values.

In this article, we’ll explore how the product() function works with some basic examples.

What is the product() Function?

DuckDB’s product() function is an aggregate function that computes the product of a set of numeric values. It multiplies all the values in a column or a set of rows and returns the result.

This function is particularly useful in scenarios where you need to calculate cumulative products, such as in financial calculations, growth rate analysis, or scientific computations.

Syntax

The syntax for the product() function is straightforward:

product(expression)
  • expression: This is typically a column name or a numeric expression whose values you want to multiply together.

The function returns the product of all non-null values in the specified column or expression.

Example: Using the product() Function

To demonstrate how the product() function works, let’s create a sample table, populate it with data, and then use the function to calculate the product of values in a column.

Step 1: Create a Table

First, we’ll create a table named sales that stores the sales data for different products. The table will have two columns: product_id and sales_amount.

CREATE TABLE sales (
    product_id INTEGER,
    sales_amount DOUBLE
);

Step 2: Populate the Table with Data

Next, we’ll insert some sample data into the sales table.

INSERT INTO sales (product_id, sales_amount) VALUES
(1, 100.0),
(2, 200.0),
(3, 300.0),
(4, 400.0),
(5, 500.0);

Step 3: Calculate the Product of Sales Amounts

Now that we have our table set up, we can use the product() function to calculate the product of all sales amounts.

SELECT 
    product(sales_amount) AS total_product
FROM sales;

Result:

+-----------------+
|  total_product  |
+-----------------+
| 1200000000000.0 |
+-----------------+

The query returned the product of all sales_amount values in the sales table.

This result is obtained by multiplying all the sales_amount values together: 100.0 * 200.0 * 300.0 * 400.0 * 500.0 = 12000000000.0.

Handling Null Values

The product() function ignores null values in the column. If all values in the column are null, the function returns null. Let’s check this by adding a row with a null sales_amount.

INSERT INTO sales (product_id, sales_amount) VALUES (6, NULL);

Now, if we run the product() function again:

SELECT 
    product(sales_amount) AS total_product
FROM sales;

Result:

+-----------------+
|  total_product  |
+-----------------+
| 1200000000000.0 |
+-----------------+

The result is still 12000000000.0 because the null value was ignored.

Filtering the Results

We can use a WHERE clause to calculate the product of a subset of the table.

For example:

SELECT 
    product(sales_amount) AS total_product
FROM sales
WHERE product_id IN (1, 2);

Result:

+---------------+
| total_product |
+---------------+
| 20000.0       |
+---------------+

This time, a lower number is returned, due to the fact that we only got the product of the values in the first two rows.

Using product() with GROUP BY

The product() function can also be used in conjunction with the GROUP BY clause to calculate the product of values within each group.

Add a Region Column and Populate

Let’s extend our example by adding a region column to the sales table and calculating the product of sales amounts for each region:

ALTER TABLE sales ADD COLUMN region VARCHAR;
UPDATE sales SET region = 'North' WHERE product_id IN (1, 2, 3);
UPDATE sales SET region = 'South' WHERE product_id IN (4, 5, 6);

Calculate the Product of Sales Amounts by Region

Now, we’ll use the product() function with the GROUP BY clause to calculate the product of sales amounts for each region.

SELECT 
    region, 
    product(sales_amount) AS region_product
FROM sales
GROUP BY region;

Result:

+--------+----------------+
| region | region_product |
+--------+----------------+
| North  | 6000000.0      |
| South  | 200000.0       |
+--------+----------------+

Non-Numeric Values

Trying to use product() on non-numeric values results in an error:

SELECT
    product(region)
FROM sales;

Result:

Binder Error: No function matches the given name and argument types 'product(VARCHAR)'. You might need to add explicit type casts.
	Candidate functions:
	product(DOUBLE) -> DOUBLE

LINE 2:     product(region)
            ^