DuckDB has a fsum() function that can be used instead of the regular sum() function in order to get more accurate results. fsum() calculates the sum using a floating point summation method known as Kahan summation (or compensated summation).
This method helps reduce the accumulation of rounding errors that can occur when summing many floating point numbers when using the regular sum() function.
In analytical SQL workloads, expressing weighted averages can sometimes involve verbose expressions such as combining the sum() function with other operators. DuckDB streamlines this with its native weighted_avg() aggregate function, allowing us to compute weighted averages directly and efficiently. The weighted_avg() function enhances both clarity and speed when dealing with data where values contribute unequally — such as population-adjusted metrics or revenue-weighted scores.
This article explores the weighted_avg() in DuckDB, along with examples to demonstrate its usage.
DuckDB offers a variety of utility functions to help us better understand the data that we need to work with. Among these functions, typeof() serves as a useful tool for type inspection and validation. In this article, we’ll explore how this function works, along with examples that demonstrate its usage.
DuckDB’s list_aggregate() function is a handy tool for performing grouped aggregations over lists. It allows us to apply any aggregate function (like sum, avg, min, max, count, etc.) to a list column as if each list item were a row.
The way it works is that we pass the list as the first argument, followed by the name of an aggregate function we want to apply to that list. The function will return its result as if it were the named aggregate function.
Most SQL databases provide a cast() function and DuckDB is no exception. The purpose of the cast() function is to convert a value from one data type to another. This can also be referred to as casting the value as the other data type.
DuckDB has a list_slice() function that enables us to select multiple list items from a list, based on their index range within the list. For example, we can select all list items between position 5 and 10 in a list.
DuckDB’s epoch() function is a useful time conversion tool that allows us to transform timestamp values into Unix epoch time – the number of seconds that have elapsed since January 1, 1970 (UTC).
The function is particularly useful when working with time-series data and when we need to perform mathematical operations on timestamp values. By converting timestamps to integer representations, we can easily calculate time differences, group time-based data, or integrate with systems that use epoch time.
DuckDB provides us with the list_has_any() function that enables us to find any overlapping elements across two lists. The way it works is that we pass two lists, and it returns true or false, depending on whether any element exists in both lists.
DuckDB has a list_zip() function that zips one or more lists to a new list with a length of the longest list provided. The new list contains unnamed structs of the elements from the original lists. We pass the lists to the function when we call it. We can also pass a Boolean value to specify whether or not to truncate all lists to the smallest list.
Below are some basic examples that demonstrate how the list_zip() function works.
Among DuckDB’s many tools for handling list data is the list_grade_up() function. This function works similarly to a sort operation, but instead of returning the sorted values themselves, it returns the indexes that represent the positions of those values in the original list.
Let’s take a quick look.