The json_group_structure() function in DuckDB is an aggregate function that inspects all JSON values within a group and returns a JSON representation of their structure. It essentially infers a “schema” for the JSON objects in that group. This can be useful for understanding the shape and consistency of your JSON data.
Tag: aggregate functions
Using DuckDB’s FSUM() Function for More Accurate Results
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.
A Quick Look at DuckDB’s WEIGHTED_AVG() 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.
Looking at DuckDB’s STRING_AGG() Function
DuckDB has a string_agg() function, which allows us to concatenate strings from a group of rows into a single string. This function can be useful when we need to aggregate text data in a meaningful way.
In this article, we’ll explore how the string_agg() function works, along with some simple examples to demonstrate its usage.
Looking at DuckDB’s PRODUCT() Function
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.
Examples of the BOOL_AND() Function in DuckDB
In DuckDB, bool_and() is an aggregate function that returns true if every input value is true, otherwise it returns false.
Here are some basic examples that demonstrate how it works.
Continue readingHow ARG_MAX_NULL() Works in DuckDB
In DuckDB, the arg_max_null() function works in a similar way to the arg_max() function, in that it finds the row with the maximum value in one column and returns the corresponding value from another column at that row.
But where it differs from arg_max() is in the way it deals with NULL values. Also, arg_max_null() only accepts two arguments, whereas arg_max() accepts an optional third argument. Additionally, there aren’t any aliases for arg_max_null() at the time of writing (arg_max() has a couple of aliases).
In this article we’ll look at how arg_max_null() works, and we’ll compare it with arg_max() to see how each function handles NULL values.
A Quick Overview of DuckDB’s LAST() Function
DuckDB is an in-process SQL OLAP database management system designed for analytical workloads. Among its many functions is one called last(). This function returns the last value in a column.
Let’s take a look at the last() function in DuckDB.
MAX_BY() Examples in DuckDB
DuckDB has a max_by() function that finds the row with the maximum value in one column and returns the corresponding value from another column at that row.
Below are some examples that demonstrate how it works.
Continue readingExploring the FIRST() Function in DuckDB
One of the aggregate functions available in DuckDB is the FIRST() function, which returns the first value from each group in a query.
Let’s take a look at some examples of using the FIRST() function in DuckDB