At work, I read a lot of customer-written SQL. This is a major part of my day job, and there are some underrated things that I regularly recommend. The ANY_VALUE() function is one of these things.
The ANY_VALUE() Function
The ANY_VALUE() function is part of standard SQL. I have actually mentioned it once before, in a MySQL blog about the funny thing MySQL does to make GROUP BY operations succeed when they should fail. Luckily most SQL platforms don’t provide that shortcut as a default.
The ANY_VALUE() function is an aggregate function. This means that it takes many values and returns only one value. The value that it returns isn’t determined by a calculation like many aggregate functions – instead it is non-deterministic. It basically just grabs the handiest value from the group. If the values in the group vary, this means that you might get a value fairly consistently, but when something changes in the data or behind the scenes in the table structure, you might get a different value. It should be thought of like a random choice, but without any guarantee of randomness.
While the ANY_VALUE() function is non-deterministic, it is one of the non-deterministic functions that has an explicit exception, and queries using it will be able to use the result-set cache.
How ANY_VALUE() Can Be Useful
As I’ve described it so far, you might wonder what use it could possibly be. The number one use I see for ANY_VALUE() is in reducing the width of a grouping key.
GROUP BY ALL
Look, I’m a fan of the ease of GROUP BY ALL, and use it fairly constantly myself. GROUP BY ALL is syntax that you use in place of an explicit GROUP BY clause, and it causes the grouping key to be made up of all columns in the select clause that do not involve an aggregate function. In many ways, this behavior is more like MySQL’s default.
The reason it is particularly useful for me is when I’m exploring data, often QUERY_HISTORY or its internal equivalent, and I want to explore the data by different dimensions before landing on the final way it makes sense to group data. Having one less clause to edit with each modification is nice. That said, I’m not sure I would use it on a truly large data set. The more that is at stake, the more likely I am to use an explicit column list in my GROUP BY clause.
While I’m diligent about GROUP BY ALL not making it into any reusable context, I frequently see it in customer queries.
Grouping Keys
Aggregates often turn up as expensive operator nodes in the query profile. When investigating an expensive aggregate, I’m often thinking about how to make it cheaper. While pumping less data through an aggregate is generally my first choice, that’s not always possible. If I can’t reduce the data in volume, another option is to reduce the width, or number of columns, in the the grouping key.
The vast majority of SQL dialects, including Snowflake’s, require that each column that is in the select clause without an aggregate function is also in the grouping key. This means that people often include a column in the grouping key just to get a value for it, not because it is actually required to uniquely identify a group. GROUP BY ALL makes this inclusion all that much easier to achieve, sometimes without understanding the implications.
Grouping Key Example
For example, let’s say you want to get a sum of orders for a customer. There is often a primary or surrogate key – a customer id – that actually uniquely identifies a customer and serves as a join key between an orders table and a customers table. Often, in addition to that customer id, you want know basic information about the customer in the same query such as the customer name or some contact method. The same customer id never has different names, but in order to get the name, often it is added to the grouping key. In the SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000 data set, this might look something like this:
USE snowflake_sample_data.tpch_sf1000;
ALTER SESSION SET use_cached_result=FALSE;
SELECT c.c_custkey
, c.c_name
, c.c_phone
, MAX(O_ORDERDATE) as last_order_date
, SUM(O_TOTALPRICE) as all_orders_sum
FROM customer AS c
JOIN orders AS o on c.c_custkey=o.o_custkey
GROUP BY ALL;
This does, indeed give the desired result – a summary of order amounts and the date of the last order by customer, along with a bit of basic information about the customer:
This method, in my small-scale testing(run 4 times across different days), runs for about 3 minutes and 40 seconds.
If we instead use the ANY_VALUE() function for the columns where we want a value, but we know they do not contribute to identifying a group, that might be written this way:
SELECT c.c_custkey
, ANY_VALUE(c.c_name) AS c_name
, ANY_VALUE(c.c_phone) AS c_phone
, MAX(O_ORDERDATE) as last_order_date
, SUM(O_TOTALPRICE) as all_orders_sum
FROM customer AS c
JOIN orders AS o on c.c_custkey=o.o_custkey
GROUP BY ALL;
Not surprisingly, this returns very similar-looking results:
They don’t look identical here, because we did not specify an order. Using HASH_AGG(), I verified that the results are the same (HASH_AGG value of -5509791463467788115).
This second format of the query, with a narrower grouping key, returned in about 2 minutes and 50 seconds (again average across 4 executions on two different days). I know of customers who would absolutely love a 22% improvement in query time with a relatively simple syntax change. To others, this difference may not seem that huge. The difference in the grouping key is on the small side (1 column vs 3 columns), and the data size is relatively small compared to many real-world data sets. The effect of a narrower grouping key often is magnified with scale in either direction, particularly if remote spilling is required to achieve the aggregation.
Summary
It is hard to quantify the impact using ANY_VALUE() might have. Testing is the only real way to understand the impact. There is a small amount of complexity introduced by using ANY_VALUE(), such as knowing which columns it makes sense for, but this is something that in my opinion, most query writers should know or discover about their data anyway. It is certainly worth testing and understanding the differences when we encounter expensive aggregates.
