Most SQL developers are familiar with the min() function that allows us to get the minimum value from a data set. But if we want to get the minimum value from a list, passing the list to the min() function won’t quite cut it. But don’t despair! Finding the minimum value in a list is just as easy. Here are three ways to do it. And yes, we can even use the min() function if we want.
how to
4 Ways to Concatenate 3 or More Lists in DuckDB
If you’ve ever used list_concat() or any of its aliases to concatenate lists in DuckDB, you may have been disappointed to find out that it only concatenates two lists. Any more than two lists and it returns an error. At least that’s how it works at the time of this writing.
Fortunately, there are some alternatives we can use in order to concatenate three or more lists.
Here are four ways to concatenate three or more lists in DuckDB.
Calculating Time Differences in DuckDB
DuckDB has emerged as a powerful analytical database system designed for fast in-memory data processing. One common analytical task involves calculating time differences between events—whether measuring intervals between transactions, tracking service response times, or analyzing temporal patterns in time series data.
This article provides an exploration of calculating time differences in DuckDB, covering both basic and slightly more advanced techniques.
Subtract Days from a Date in DuckDB
DuckDB provides us with a couple of easy ways to perform additions and subtractions on dates. In particular, we can use the - operator to do the job, or the date_add() operator combined with the - operator.
Here are two options for subtracting days from a date in DuckDB.
Outputting Query Results as a TCL List in the DuckDB CLI
This article demonstrates how to output your DuckDB query results in TCL list format, which can be useful when integrating with TCL scripts or systems that expect TCL-formatted data.
Using Bracket Notation to Slice a List in DuckDB
DuckDB provides a few options for slicing lists, including the list_slice() function and its alias, array_slice(). We can also use bracket notation for a slightly more concise option. Bracket notation is where we append the slice details to the list.
Below are examples of using bracket notation to slice lists in DuckDB.
3 Ways to Get the Average From a List of Numbers in DuckDB
When presented with a list of numbers, there are many things we might want to do with it. One is to get the average value of all values in the list. Fortunately DuckDB provides us with several ways to do this.
Get the Last Day of the Month in DuckDB with LAST_DAY()
DuckDB provides us with a good selection of functions for working with date and timestamp values. One such function is the LAST_DAY() function, which returns the last day of the month of the given date.
The way it works is, we pass a date to the function, and it returns the date of the last day of that month.
Using LIST_RESIZE() to Resize a List in DuckDB
DuckDB provides us with a list_resize() function for those occasions where we need to resize a list. It also offers an alias called array_resize() that does exactly the same thing. When we resize a list using these functions, we specify how big we want the resulting list to be, and we can also specify a value to use for any extra elements that are added to the list.
Detect Infinite Values in DuckDB with the ISINF() Function
In DuckDB, we can use the ISINF() function to check whether a value is finite. DuckDB supports infinite values, and so this function allows us to check for that. This can be useful when working with floating-point data that might contain special values like NaN (Not a Number) or infinity. The function also works on date and timestamp values, as they can be infinite too.