Skip to content
  • AT SNOWFLAKE
  • Industry solutions
  • Partner & Customer Value
  • Product & Technology
  • Strategy & Insights
Languages
  • Deutsch
  • Français
  • Português
  • Español
  • English
  • Italiano
  • 日本語
  • 한국어
  • Deutsch
  • Français
  • Português
  • Español
  • English
  • Italiano
  • 日本語
  • 한국어
  • AT SNOWFLAKE
  • Industry solutions
  • Partner & Customer Value
  • Product & Technology
  • Strategy & Insights
  • Deutsch
  • Français
  • Português
  • Español
  • English
  • Italiano
  • 日本語
  • 한국어
  • 개요
    • Why Snowflake
    • 고객 사례
    • 파트너 네트워크
    • 서비스
  • 데이터 클라우드
    • 데이터 클라우드
    • 플랫폼 개요
    • SNOWFLAKE 데이터 마켓플레이스
    • Powered by Snowflake
    • 라이브 데모
  • WORKLOADS
    • 협업
    • 데이터 사이언스&머신러닝
    • 사이버 보안
    • 애플리케이션
    • 데이터 웨어하우스
    • 데이터 레이크
    • 데이터 엔지니어링
    • 유니스토어
  • PRICING
    • Pricing Options
  • 산업별 솔루션
    • 광고, 미디어 및 엔터테인먼트
    • 금융 서비스
    • 의료 및 생명 과학
    • 제조
    • 공공 부문
    • 소매 / CPG
    • 테크놀로지
  • 리소스
    • 리소스
    • Documentation
    • 핸즈온 랩
    • 트레이닝
  • CONNECT
    • Snowflake 블로그
    • 커뮤니티
    • 이벤트
    • 웨비나
    • 팟캐스트
  • 개요
    • 회사 소개
    • 투자정보
    • 리더십 및 이사회
    • 채용
Share
Subscribe
2016년 04월 12일

ANSI SQL with Analytic Functions

  • 제품 및 기술
    • 데이터 엔지니어링
    • 데이터 사이언스
ANSI SQL with Analytic Functions

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.

Additional Links

  • SQL Definition
  • How to Easily Load SQL and XML (Part 1)
Share

Queries in SQL

Explore SQL queries using Snowflake. Enhance your SQL skills and leverage Snowflake's capabilities for efficient data querying. Unlock the power of data today.

Full Details
Read More

Genomic Data

Genomic data is the DNA data of organisms. In the biology and computer science subdiscipline of bioinformatics, genomic data is collected, stored, and processed.

More
Read More

Omnichannel Retail Analytics for Revenue Growth

Omnichannel retail analytics combines customer and product data to unlock insights, enabling more accurate forecasting and effective future event planning.

Full Details
Read More

Data Cloud Glossary

SQL, or Structured Query Language is by far the most common language for data communication. It’s a standardized format for...

Expand your knowledge
Read More
Snowflake Inc.
  • 플랫폼 개요
    • 아키텍처
    • 데이터 애플리케이션
  • 데이터 마켓플레이스
  • Snowflake 파트너 네트워크
  • 지원 및 서비스
  • 회사
    • 문의하기

Sign up for Snowflake Communications

Thanks for signing up!

  • Privacy Notice
  • Site Terms
  • Cookie Settings

© 2023 Snowflake Inc. All Rights Reserved