ANSI SQL with Analytic Functions

April 12, 2016 Kent Graziano

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:

#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.

The post ANSI SQL with Analytic Functions appeared first on Snowflake.

Previous Article
Making Data Warehousing Easy
Making Data Warehousing Easy

Legacy Problems Organizations with legacy on-premise data warehouses spend a lot of time and money managing...

Next Article
Enterprise Cloud Acceleration and the SaaS Data Analytics Explosion
Enterprise Cloud Acceleration and the SaaS Data Analytics Explosion

When we launched Snowflake back in 2014, I remember how exciting it was to see signs that we were coming in...

×

Subscribe to email updates from the Snowflake Blog

You're subscribed!
Error - something went wrong!