Snowflake Aggregates, Window Functions, Ranking and Casting

1.Aggregating with GROUP BY versus OVER

Most work with a dataset starts with summarising it. In Snowflake, SUM, COUNT and AVG each come in two forms. The function name is the same in both, and the OVER clause tells them apart. With GROUP BY, each group of rows collapses to one output row. That is the right shape for a report of totals per category. With an OVER clause, the function still computes over a group of rows, but every input row stays in the result and carries the computed value next to it.

SELECT menu_category,
    AVG(menu_cogs_usd) avg_cogs
  FROM menu_items
  GROUP BY 1
  ORDER BY menu_category;

That query returns four rows: Beverage 0.60, Dessert 1.79, Main 6.11 and Snack 3.10. When the same AVG is used as a window function, every row in the table is returned. Each Beverage row shows 0.60000, and each Dessert row shows 1.79166. This gives you a simple way to validate an aggregate. The two forms must agree for each group, and the window form lets you check each detail row against its group value, such as the category average, in the same result.

SELECT menu_category,
    AVG(menu_cogs_usd) OVER( ?  BY menu_category) avg_cogs
  FROM menu_items
  ORDER BY menu_category
  LIMIT 15;

Validation also means knowing how aggregates treat NULLs. AVG and SUM work on the non-NULL records only. COUNT has two behaviours: given specific columns it counts the non-NULL records, and otherwise it counts all records. Comparing COUNT(*) with COUNT(column) therefore shows how many NULLs a column holds. COUNT_IF counts the records that satisfy a condition. For large inputs, APPROX_COUNT_DISTINCT (alias HLL) estimates COUNT(DISTINCT ...) using HyperLogLog. It is deterministic for the same input, but the two results do not always match exactly: in the documentation example, 1024 distinct values were estimated as 1007. As a window function it does not support an ORDER BY or an explicit window frame.

GROUPING SETS extends GROUP BY to compute several groupings in one statement. Grouping by (a) and (b) as separate sets is equivalent to a UNION ALL of two GROUP BY queries. The output contains NULLs in the columns that a given grouping does not use, and a NULL can also come from NULL data, so read those rows with care.

Sources1234

2.Ordering, window frames and ranking

With a partition on its own, every row in a partition gets the same value. Adding ORDER BY and a window frame inside OVER turns the calculation into a rolling one. The query below computes a moving average over the current row and the two rows that follow it in the same category. The query has a second ORDER BY at the end, which sorts the output. The two ORDER BY clauses are independent.

SELECT menu_category, menu_price_usd, menu_cogs_usd,
    AVG(menu_cogs_usd) OVER(PARTITION BY menu_category
      ORDER BY menu_price_usd, menu_cogs_usd ROWS BETWEEN CURRENT ROW and 2 FOLLOWING) avg_cogs
  FROM menu_items
  ORDER BY menu_category, menu_price_usd, menu_cogs_usd
  LIMIT 15;

menu_cogs_usd appears in the window's ORDER BY as a tiebreaker. Several items share the same price, and without a tiebreaker the 'following' rows could change from one run to the next. Two kinds of window function require ORDER BY: functions with an explicit frame, such as running totals and moving averages, and ranking functions such as CUME_DIST, RANK and DENSE_RANK. For example, ranking stores by monthly profit in descending order gives the most profitable store rank 1. Some functions treat an ORDER BY as an implied frame, so Snowflake recommends declaring the frame explicitly.

So when an ORDER BY appears inside OVER, always name the columns. The ordinal shorthand that works in a query-level ORDER BY or in GROUP BY 1 has a different meaning inside OVER.

Sources1

3.Pre-math: randomization, ranking and grouping

Pre-math calculations prepare values that later analysis uses: a rank for each row, a group label, or a random number for assigning rows to test cohorts. Ranking comes from the window functions above, and grouping comes from GROUP BY or PARTITION BY. Ranking functions need an ORDER BY inside OVER. DENSE_RANK returns the rank of a value within a group of values without gaps in the ranks, and ROW_NUMBER is the function to use when you need a gap-free numbering of rows. For random values, Snowflake provides two related functions. RANDOM returns raw pseudo-random 64-bit integers. UNIFORM converts a random source into a value within a range you specify.

SELECT UNIFORM(1, 10, RANDOM()) FROM TABLE(GENERATOR(ROWCOUNT => 5));

Sources526

4.Casting values to a consistent type

Values usually need to be converted to one data type before they display consistently, for example strings that hold dates or numbers. Converting a data type is called casting. An explicit cast uses the CAST function, the :: operator, or a TO_ function such as TO_DOUBLE or TO_DATE. Snowflake also converts some values automatically, which is called implicit casting or coercion. For example, an INTEGER is coerced to VARCHAR so that 17 || '76' gives '1776'. Not all contexts support coercion, so cast explicitly when the type matters.

SELECT CAST('2022-04-01' AS DATE); SELECT '2022-04-01'::DATE; SELECT TO_DATE('2022-04-01');

The semantics of CAST are the same as those of the matching TO_ function, and the :: operator is alternative syntax for CAST. When you give a target type a precision and scale, the target controls the display: if the scale is too small the value is rounded, so '9.8765' cast to NUMBER(5,2) becomes 9.88. If the precision is too small, or a value exceeds a VARCHAR length such as VARCHAR(4), the cast raises an error. The date and time TO_ functions also accept an optional format argument, with elements such as YYYY, MM, DD and HH24, to describe how a string is parsed or produced.

CAST and the :: operator raise an error if a value can't be converted. On a messy column, one bad value is enough to make the whole query fail. TRY_CAST performs the same conversion but returns NULL for values it can't convert. It has two limits: its input must be a string expression, and the target type must be VARCHAR, NUMBER, DOUBLE, BOOLEAN, DATE, an interval variation, TIME or one of the TIMESTAMP types. The TRY_TO_ functions, such as TRY_TO_DATE, are the error-handling versions of the TO_ functions. They are optimised for data with few conversion errors and can be much slower when many conversions fail.

SELECT TRY_CAST('ABCD' AS CHAR(2));

'ABCD' doesn't fit in CHAR(2), so the result is NULL. TRY_CAST('ABCD' AS VARCHAR(10)) returns ABCD. In the same way, '05-Mar-2016' converts to a TIMESTAMP, but '05/16' returns NULL.

SELECT  ? ('05/16' AS TIMESTAMP);

Sources789

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.OVER (PARTITION BY 1 ORDER BY 2) orders the window by the second column of the SELECT list.Why is that wrong?

   Inside a window's ORDER BY, 2 is treated as the constant 2. Name the columns explicitly.

   Covered in Ordering, window frames and ranking

2. 2.TRY_CAST can safely replace CAST for any input type, including numeric columns.Why is that wrong?

   TRY_CAST accepts only string expressions, and only some target types. For other conversions, use CAST.

   Covered in Casting values to a consistent type