CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
A Question Asker
A Question Asker

Reputation: 3311

SQL Server window functions: can you do a ROW BETWEEN type construct?

This is my version of SQL Server:

Microsoft SQL Server 2008 (SP2) - 10.0.4064.0 (X64) Feb 25 2011 13:56:11 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2)

I want to do something like this (which works in Sybase):

SELECT AVG(ct) OVER (PARTITION BY val1 
                         ORDER BY val2 ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
FROM table

Is there a way to implement something like this WITHOUT some nasty self join, or a subquery that uses row number etc? I suppose if I HAVE to do that I will, but I'd love to use the best most compact code.

EDIT:
Per the comments, here is what I'd like to do.

I have a time_id and a value. I want to take a moving average (1 week) of the value. Pretty simple.

Upvotes: 2

Views: 1549

Answers (2)

elvis
elvis

Reputation: 312

Try this:

SELECT T1.*, T2.myAvgValue from myTable T1
OUTER APPLY (SELECT Avg(T3.myValue) myAvgValue FROM myTable T3 WHERE T3.Date BETWEEN T1.Date-7 and T1.Date) T2

Hope this helps.

Upvotes: 1

Andriy M
Andriy M

Reputation: 77667

This is what I would probably do it like:

WITH ranked AS (
  SELECT
    val1,
    val2,
    ct,
    rank = ROW_NUMBER() OVER (PARTITION BY val1 ORDER BY val2)
  FROM table
)
SELECT
  r.val1,
  r.val2,
  avg_ct = AVG(r2.ct)
FROM ranked r
  INNER JOIN ranked r2 ON r2.val1 = r.val1
                      AND r2.rank BETWEEN r.rank - 6 AND r.rank
GROUP BY r.val1, r.val2

Definitely not as elegant and concise as the Sybase version, but, as for me, not too bad either.

Upvotes: 4

Related Questions