how to

3 Easy Ways to Calculate the Median Value from a List in DuckDB

by

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.

Read more

RPAD() Alternative: Applying Right Padding in SQL Server

by

The SQL rpad() function has been widely implemented across many major RDBMSs, including MySQL, Oracle, PostgreSQL, and MariaDB, to name just a few. But when it comes to SQL Server, we have a problem. SQL Server doesn’t currently provide us with an rpad() function.

But that’s not to say we can’t apply right padding in SQL Server. SQL Server still provides us with enough tools to get the job done. With a bit of work, we can get a similar result to what we might be able to achieve with rpad(). It may not be as elegant as a simple rpad() function, but at least it’s an option.

Read more

Using LIST_INTERSECT() to Extract Overlapping Elements from Two Lists in DuckDB

by

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.

Read more

5 Functions that Return the Year from a Date in DuckDB

by

When working with dates in DuckDB, some common tasks we might need to perform include extracting date parts from a date or timestamp value. For example we might want to extract the year from a date. Fortunately, DuckDB provides us with an abundance of options for doing that.

In this article, we’ll look at five different functions extract the year from a date in DuckDB.

Read more

How to Get a Reproducible Result Set When Using the SAMPLE Clause in DuckDB

by

When working with large datasets in DuckDB, the SAMPLE clause offers an efficient way to query a subset of your data. However, unless you specifically construct your query to get repeatable results, this sampling will return a different set of results each time the query is run.

But we can change that. We can write our query to return the same random result set every time we run it.

This article explores how to achieve consistent, reproducible result sets when using the SAMPLE clause in DuckDB.

Read more

Fix “list dimensions must be equal” in DuckDB

by

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.

Read more

Add Minutes to a Date/Time Value in DuckDB

by

Date/time arithmetic, such as adding one or more minutes to a date/time value, is an operation we often need to perform when using SQL databases such as DuckDB. As with most RDBMSs, DuckDB makes this easy for us to achieve.

Here are two options for adding one or more minutes to a date, timestamp, or time value.

Read more