Hopefully you had a chance to read our previous top 10 posts. As promised, we continue the series with a deeper dive into another of the Top 10 Cool Features from Snowflake: ANSI SQL.
#5 ANSI compliant SQL with Analytic Functions
At Snowflake, we believe that it should be easy to access, query, and derive insights from your data. To support that, we provide our users with the ability to query all their data using ANSI compliant SQL . (Hard to call yourself a relational database otherwise, right?).
However, Snowflake goes beyond basic SQL, delivering sophisticated analytic and windowing functions as part of our data warehouse service. Functions like:
- CUME_DIST
- DENSE_RANK
- FIRST_VALUE
- LAG
- LAST_VALUE
- LEAD
- NTILE
- PERCENT_RANK
- RANK
- ROW_NUMBER
select Nation, Customer, Total
from (select n.n_name Nation,
c.c_name Customer,
sum(o.o_totalprice) Total,
rank() over (partition by n.n_name
order by sum(o.o_totalprice) desc)
customer_rank
from orders o,
customer c,
nation n
where o.o_custkey = c.c_custkey
and c.c_nationkey = n.n_nationkey
group by 1, 2)
where customer_rank <= 3
order by 1, customer_rank;
As you see in the example, we support not only analytic windowing functions, but all the other features you would expect in SQL. This includes but is not limited to general aggregation functions (e.g. sum), nested virtual tables, sub queries, order by, and group by.
In additional to general aggregation functions, we also have:
- Bitwise aggregation functions
- Linear regressions functions and
- Cardinality estimation functions (i.e., HyperLogLog)
So, if your existing queries are written with standard SQL, they will run in Snowflake. And, as we noted in the previous blog on JSON, you can apply all these functions to your semi-structured data natively using Snowflake.
Another reason to love the Snowflake Elastic Data Warehouse.
As always, keep an eye on this blog site (or better – sign up for the RSS feed), and our Snowflake Twitter feeds (@SnowflakeDB), (@kentgraziano), and (@cloudsommelier) for more Top 10 Cool Things About Snowflake and for updates on all the action and activities here at Snowflake Computing.