Issue with results from window function tutorial

Hello.  The aggregations are breaking down in certain rows of the window function from the tutorial.  For example, the 'running_total', 'running_count' and 'running_avg' values are duplicated.  Here is the query:

SELECT start_terminal,
       duration_seconds,
       SUM(duration_seconds) OVER
         (PARTITION BY start_terminal ORDER BY start_time)
         AS running_total,
       COUNT(duration_seconds) OVER
         (PARTITION BY start_terminal ORDER BY start_time)
         AS running_count,
       AVG(duration_seconds) OVER
         (PARTITION BY start_terminal ORDER BY start_time)
         AS running_avg
  FROM tutorial.dc_bikeshare_q1_2012
 WHERE start_time < '2012-01-08'

Here is a link to the run that I'm referring to.  I've also attached a screenshot highlighting the problem in the results.   

I'd love to understand why this is happening.  Thanks! 

1reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi James Clare - Good catch. This is a result of that dataset containing multiple records that share a 'start_terminal' and 'start_time'. Since the 'start_time' is the same for those records, they essentially "tie" when the ORDER BY clause of the window function is evaluated.

    To avoid this, you can add 'id' after 'start_time' to the ORDER BY clause . Since every row has a unique 'id', this will force those records to evaluate distinctly. Here's an example:

    SELECT start_terminal,
           duration_seconds,
           SUM(duration_seconds) OVER
             (PARTITION BY start_terminal ORDER BY start_time, id)
             AS running_total,
           COUNT(duration_seconds) OVER
             (PARTITION BY start_terminal ORDER BY start_time, id)
             AS running_count,
           AVG(duration_seconds) OVER
             (PARTITION BY start_terminal ORDER BY start_time, id)
             AS running_avg
      FROM tutorial.dc_bikeshare_q1_2012
     WHERE start_time < '2012-01-08'
    Reply Like
reply to topic
Like Follow
  • 3 mths agoLast active
  • 1Replies
  • 140Views
  • 2 Following

Create A New Discussion

Share your thoughts