Common SQL Window Functions: Positional Functions

Window Functions, analytics, SQL for advanced, aggregate functions, SQL clauses, positional functions

Positional SQL window functions deal with data’s location in the set. In this post, we explain LEAD, LAG, and other positional functions. SQL window functions allow us to aggregate data while still using individual row values. We’ve already dealt with ranking functions and the use of partitions. In this post, we’ll examine positional window functions, which are extremely helpful in reporting and summarizing data. Specifically, we’ll look at LAG, LEAD, FIRST_VALUE and LAST_VALUE. It is worthwhile mentioning that LEAD mirrors

Continue Reading

Common SQL Window Functions: Using Partitions with Ranking Functions

You’ve started your mastery of SQL window functions by learning RANK, NTILE, and other basic functions. In this article, we will explain how to use partitions with ranking functions. Mastering SQL window (or analytical) functions is a bumpy road, but it helps to break the journey into logical stages that build on each other. In the previous Common SQL Functions article, you learned about the various ranking functions, which are the most basic form of window functions. In this article, we

Continue Reading

Common SQL Window Functions: Ranking Functions

Want to learn how to use SQL window functions?  Ranking functions are a good place to start! Learning about SQL window functions usually comes after you’ve built a foundation in the language, but these powerful functions take your skills up a level. As you master them, you’ll find better ways to solve query problems. When used for business intelligence applications, SQL queries combine data retrieval and advanced computations. These operations are more complex than those used in OLTP systems. In

Continue Reading

High Performance Statistical Queries in SQL: Part 3 – Measuring the Spread of a Distribution

Besides knowing the centers of a distribution in your data, you need to know how varied the observations are. In this article, we’ll explain how to find the spread of a distribution. Are you dealing with a very uniform or a very spread population? To really understand what the numbers are saying, you must know the answer to this question. In the second part of this series, we discussed how to calculate centers of distribution. Like the center, the spread can

Continue Reading