Tag: T-SQL

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I’ve been writing about window functions, which I find very handy. In this post, I want to build a running total using the framing of the window. This continues from my last post on aggregates.

I’ve been looking at baseball data. Imagine that someone wants to know how many total home runs a player had at each stage of his career. In this case, if a player hit 1, then 4, then 5 home runs, we’d expect a running total to show:

• Year 1: 1
• Year 2: 5
• Year 3: 10

This is a cumbersome query without window functions, and inefficient, as I really need a subtotal query for each of the main rows. It’s difficult to write, read, and it’s slow. With a window function, however, I can use this query. You can see the framing with the ROWS section in the OVER() clause.

SELECT
          yearid
        , hr
        , SUM (b.hr) OVER (ORDER BY b.yearID 
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS TotalHR

FROM     dbo.batting AS b

WHERE    b.playerID = 'griffke02'

ORDER BY b.yearID;

This gives me results that look like this:

Simple and easy to see and read.

This works as the window is scanned and totaled using the OVER() clause. In this case, the partition is the entire window, meaning all rows for this player. I’ve set the ordering to be the year, so as we move through the years, the SUM() is calculated using the part of the window that goes from the beginning, with the UNBOUNDED PRECEDING marker, until the current row.

You can think of this as we scan from year 1989 first. In this case, the entire preceding section is nothing, and the current row is 1989. Therefore the sum is 16.

Next we look at year 1990. There is a preceding row (1989) and this current row. We sum those to get 16+22=38. We repeat this with each row, always going back to the first row.

For the ROWS clause, we can use the between to determine the start and end portion of the partition that we scan. This means we can use:

• unbounded preceding
• unbounded following
• current row
• an integer

We can combine these 4 choices to get what we need. If we were looking only for a best 3 year time frame of home runs, we could get a sum like this:

SELECT
          yearid
        , hr
        , SUM (b.hr) OVER (ORDER BY b.yearID
                           ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS TotalHR

FROM     dbo.batting AS b

WHERE    b.playerID = 'griffke02'

ORDER BY b.yearID;

This gives me a grouping of the SUM for the current 1, as well as 1 before and after, for each row.  My results are shown below. For the first year, there is no preceding row, so we sum the current and next row, 16+22 for 38. For the second row, we have 16 preceding, 22 current, and 22 next, which sum to 60. You can check the math for others.

There’s more to this, but for now, a quick running total is using SUM() and then the ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW clause.

SQLNewBlogger

This was a quick 15 minutes to write. I took part of what I’d done in the last post, changed the query quickly, and then started to explain part of a clause. I’ll keep this around and use it to expand on some other places where the framing can be useful and affect how I work with data.

A good chance for you to also show how you might build a running total, or even running count, with your own data.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

In the last post on Window functions, I looked at ROW_NUMBER, and how I can use this to order rows. In this one, I want to look at one of the advantages of Window functions in trying to combine data and aggregates together.

A Scenario

In the last post, I examined the career of Ken Griffey, Jr., showing his home runs with some ordering. That wasn’t a very realistic case of using data, so let’s look at another one. Suppose I want to know the percentage of his career home runs he hit during each one of his seasons. That’s an interesting question, showing some idea of how much he improved or declined. If I try a to start combining a “normal” aggregate with other data, I can’t do it without a GROUP BY.

Not a huge big deal, as I can put the sum in a CTE and use it later. Here’s the code:

WITH cteHR (PlayerID, TotalHR)
AS (   SELECT
                 b.playerID
               , SUM (hr)
        FROM     dbo.batting AS b
        WHERE    b.playerID = 'griffke02'
        GROUP BY b.playerID)
SELECT
                b.yearID
              , b.teamID
              , hr
              , TotalHR
              , ROUND((hr * 1.0) / TotalHR * 100, 2) AS percentofCareer
FROM
                dbo.batting AS b
     INNER JOIN cteHR
         ON cteHR.PlayerID = b.playerID
WHERE          b.playerID = 'griffke02'
ORDER BY       b.yearID;

And the results.

That’s OK, but the code is complex, and if I wanted to break the home runs by team or some other group, it would get more complex quickly.

Window Functions Simplify Things

Here’s a simpler query. I’ve just added the SUM with an OVER() clause, without any order. I just want all rows summed. I added this to the query to see the value.

SELECT
          b.yearID
        , TeamID
        , HR
        , SUM (b.hr) OVER (ORDER BY (SELECT NULL)) AS TotalHR
        , ROUND ((b.HR * 1.0) / SUM (b.hr) OVER (ORDER BY (SELECT NULL))  * 100, 2) AS TeamPercentofCareer
FROM     dbo.batting AS b
WHERE    b.playerID = 'griffke02'
ORDER BY b.yearID;

The results are the same as the other query, but it’s easy to see.

What if I wanted to change this and order this by the highest percentage years of his career. In other words, when was he the most productive. I can easily add an ORDER BY to both queries to see this, but I lose some context.

Look at these results. How do I know if 1997 was closed to the beginning or end of his career?

I want to add some context with the span of his career. I can do that easily with a few more Window functions. Here’s the result I want, with the years showing his career first. I moved some of the other data to the end.

Without window functions, this would be complex, as the MIN() and MAX() would be from different columns, so I’d need another CTE. Here, I can use this code:

SELECT
        CAST(MIN (b.yearID) OVER (ORDER BY (SELECT NULL)) AS CHAR(4)) 
        + '-'
        + CAST(MAX (b.yearID) OVER (ORDER BY (SELECT NULL)) AS CHAR(4)) AS CareerSpan
        , b.yearID
        , ROUND ((b.HR * 1.0) / SUM (b.hr) OVER (ORDER BY (SELECT NULL))  * 100, 2) AS TeamPercentofCareer
        , TeamID
        , HR
        , SUM (b.hr) OVER (ORDER BY (SELECT NULL)) AS TotalHR
FROM     dbo.batting AS b
WHERE    b.playerID = 'griffke02'
ORDER BY TeamPercentofCareer desc;

If I wanted to add some math, like how many years into his career was he, I could easily do that. Here I’ve added the year number to his career, which comes from ROW_NUMBER().

The code? I just add the aggregates I need, which in this case are ones containing the entire set. I mix MIN(), MAX(), COUNT() and ROW_NUMBER(), and use a partition of the entire data set.

SELECT
        CAST(MIN (b.yearID) OVER (ORDER BY (SELECT NULL)) AS CHAR(4)) 
        + '-'
        + CAST(MAX (b.yearID) OVER (ORDER BY (SELECT NULL)) AS CHAR(4)) AS CareerSpan
        , b.yearID
        , ROUND ((b.HR * 1.0) / SUM (b.hr) OVER (ORDER BY (SELECT NULL))  * 100, 2) AS TeamPercentofCareer
        , RTRIM(CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS CHAR(2)) )
        + ' of ' 
        + CAST( COUNT(yearid) OVER(ORDER BY (SELECT NULL)) AS CHAR(2))
        AS YearInCareer
        , TeamID
        , HR
        , SUM (b.hr) OVER (ORDER BY (SELECT NULL)) AS TotalHR
FROM     dbo.batting AS b
WHERE    b.playerID = 'griffke02'
ORDER BY TeamPercentofCareer desc;

Try doing that without window functions. It’s a nightmare to write in T-SQL.

SQLNewBlogger

This was pretty easy to write. The hard part was thinking of the questions I might ask of this data set and setting up the queries. Duplicating this without window functions was fun, and took more time. But it was good practice for me, and helped me to better understand why I like window functions.

This took me about 30 minutes, and it’s a good showcase of learning a new technique and applying it. You should do this if writing reports and aggregates is part of your job and you might want to showcase this to your next potential employer.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

In a recent post, I started looking at some basics for window functions. This post continues with a look at one of the most commonly used ones: row_number().

Rows in a table aren’t in any particular order. They can be physically stored in order by the clustered index, but in a SELECT, there is no guarantee of any particular order unless you have an ORDER BY clause. However, even when you get a set or rows, there isn’t any number for the rows that is given.

Many of us would like to have some number that allows us to know this is row 1, row 2, etc.

We can do that with ROW_NUMBER(), which is a window function that assigns sequential numbers to rows. In the previous post, I used some baseball data, so I’ll continue with that today, but I’ll use another amazing batter, Ken Griffey Jr.

If I just get the list of batting records for Ken, I see this results (abbreviated) below. Note that there is no ordering I can count on here. The row number to the right is added by SSMS, but isn’t in the result set:

If I want to ensure every client has a row number, I can use that function with an OVER() clause. Note that I need to include something in the OVER() clause.

Let’s fix that. I’ll order by year and then ensure I show the SSMS number added by the GUI. I see the numbers seem to correspond to the years. What if I order the entire query by team?

This appear to have reversed the numbers. However, note that rows 11 and 12 are the 22 and 23. The window function applied the numbers based on the ordering of years for the entire set, then the rows were re-ordered for the query based on the ORDER BY. We see this more clearly with an ORDER BY using the HR column.

What about just an ordering for the results? I do need an ORDER BY I can use this trick.

SELECT   TOP 100
          ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Rowsetnumber
          , teamid
          , yearID
          , HR

FROM     batting

WHERE    playerID = 'griffke02'

ORDER BY hr

This allows me to just apply the ROW_NUMBER to whatever the query is doing. Here’s the result, ordered by HR.

If I go back to my ordering by team, I see this:

I can also add a PARTITION BY, and get numbering inside the partition or group. Here I’ll partition by team. I’ll go back to ordering by year, since that makes sense. I’ll use this query.

SELECT   TOP 100
          ROW_NUMBER() OVER(PARTITION BY teamID ORDER BY (SELECT NULL)) AS Rowsetnumber
          , teamid
          , yearID
          , HR

FROM     batting

WHERE    playerID = 'griffke02'

ORDER BY yearID

The results are then shown with the numbering restarting with each team.

The one strange thing to note here is that since Ken went back to Seattle late in his career, the numbering for his final two years show a continuation of the numbers from earlier with SEA.

The ROW_NUMBER() function is very powerful and useful when you need some ranking and ordering to show to the client for the rows. As with all data, you need to ensure you understand the data set and be aware of how your partition (grouping) and ordering in the OVER() clause apply to the data, but the final results are dependent on the query’s ORDER BY. This can cause some confusion, so be sure you understand the difference and inform your clients.

SQLNewBlogger

This was a quick 10 minute post. I’ve done a lot of work with Window functions and presented on them, so this was a portion of a presentation I’d given, where I took part if a demo and wrote it up.

However, you can experiment in 15-20 minutes and then spend 10-15 minutes structuring a post on this topic. How have you used ROW_NUMBER(), or if you’ve just learned it, what does it mean to you. Come up with some examples, ensure you understand them, and then explain them back. Might be an easy interview question to answer at some point if they find it on your blog.