When working with lists in DuckDB, sometimes you might want to return just the first value, rather than the whole list. Fortunately, DuckDB provides us with several ways to do this.
Continue readingTag: lists
3 Easy Ways to Calculate the Median Value from a List in DuckDB
While we might typically use DuckDB’s median() function to get the median value from a column, when it comes to getting the median value from a list, we need to modify this slightly. A list can contain many values, and so we need a way to calculate the median of all of those values, rather than a single value like we’d normally be doing when calculating the median value from a column.
Fortunately, DuckDB provides us with several easy options for achieving this. Below are three options for getting the median value from a list in DuckDB.
Continue reading3 DuckDB Functions that Prepend a Value to a List
DuckDB provides us with a few functions that prepend a value to a list. Actually, these functions are synonymous, so they all do the same thing, with the same/similar basic syntax.
Continue readingUsing LIST_INTERSECT() to Extract Overlapping Elements from Two Lists in DuckDB
The list_intersect() can be quite a handy function when working with lists in DuckDB. It accepts two lists as arguments, and it returns a list of elements that are common to both lists. It essentially performs a set intersection operation while preserving the list data type.
Duplicate values are ignored, so only one value is returned even if there are multiple occurrences in one or both lists.
Continue readingAn Important Consideration When Choosing Between LIST_CONCAT() and || For Concatenating Lists in DuckDB
When it comes to concatenating lists in DuckDB, we can use the list_concat() function (and its aliases), or the list concatenation operator (||). Often when we have a choice like this, we can use either option interchangeably. But that isn’t quite true in this case.
There’s an important difference between list_concat() and the || operator in DuckDB, and it all comes down to how it handles NULL values.
3 Ways to Count the Number of Elements in a List in DuckDB
When working with lists in DuckDB, it’s often useful to know how many elements you’re working with. Fortunately DuckDB provides us with several functions that help us determine this. Below are three options for counting up the elements in a list when using DuckDB.
Continue readingFix “list dimensions must be equal” in DuckDB
If you’re getting an error in DuckDB that includes “list dimensions must be equal“, it appears that you’re performing a list operation that requires the lists to be the same dimension, but the lists are of different dimensions.
To fix this error, be sure to use lists of equal dimensions when performing the operation.
Continue readingA Quick Look at the LIST_AGGREGATE() Function in DuckDB
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.
Continue reading3 Functions that Return the Maximum Value from a List in DuckDB
Sometimes when working with DuckDB, we need to find the maximum value from a given list. This can often be the case when working with lists that contain numeric data. Fortunately DuckDB provides a few options for us for doing this.
Continue reading2 Ways to Unnest the Results of GENERATE_SERIES() in DuckDB
Whenever we use DuckDB’s generate_series() function, the results are returned in a list. But sometimes we might want the results to be returned in a table instead of a list, so that each value in the series is presented as a separate row in a table (as opposed to being an item in a list). In such cases we would need to “unnest” the results.