Get median value of a time period in SQL Server 2005, 2008 server

Question

I have a table where I count the used licenses every minute. A cron task checks a session panel and counts the number of session every minute. It fills a session_counts table.

Session_counts:

CREATE TABLE [dbo].[session_counts](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [license_count] [int] NOT NULL,
    [created_at] [datetime] NOT NULL,
    [updated_at] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [id] ASC
)

Example values (I have 525600 rows for a year in my real data table):

id  license_count   created_at      updated_at
1   879     2014-12-30 15:58:42.000 2014-12-30 16:10:50.000
2   55      2014-05-28 12:20:00.000 2014-05-28 12:26:45.000
3   40      2014-03-15 15:51:26.000 2014-03-15 16:02:29.000
4   979     2014-04-25 09:09:49.000 2014-04-25 09:24:03.000
5   614     2014-12-27 11:17:53.000 2014-12-27 11:19:36.000
6   721     2014-09-02 15:20:40.000 2014-09-02 15:32:47.000
7   625     2014-02-27 11:17:42.000 2014-02-27 11:23:26.000
8   1576        2014-12-30 17:18:32.000 2014-12-30 17:36:55.000
9   893     2014-07-26 17:32:47.000 2014-07-26 17:34:22.000
10  897     2014-07-19 11:26:32.000 2014-07-19 11:42:26.000

Now I want to create statistics between a time range like this:

SELECT 
    MIN(license_count) min_lic
  , MAX(license_count) max_lic
  , AVG(license_count) avg_lic
  , DATEPART(month, created_at) monat
FROM session_counts
WHERE created_at BETWEEN '01.01.2014' AND '31.12.2014'
GROUP BY DATEPART(month, created_at)
ORDER BY monat DESC

This gives me MIN, AVG, MAX usages.

Instead of AVG I want to get the median value of the license usage.

My intention is to cut off the min/max peaks in a year to get a more real usage statistic. If necessary I can setup a min/max gap value, if that helps.

I checked this link Function to Calculate Median in Sql Server before, but I wasn't able to transform this to my need.

I need to make this run on SQL Server 2005, 2008 and 2012, so I can't use PERCENTILE_CONT() from 2012.

If someone want to create a table and fill with lots of data I can provide this small TSQL to fill data:

DECLARE @numRows int,@i int, @date datetime
SET @numRows = 100000
SET @i=1

WHILE @i<@numRows
BEGIN
  SET @date = DATEADD(second, RAND()*36000, DATEADD(day, RAND()*365, '2014-01-01 08:00:00'))
    INSERT session_counts (license_count, created_at, updated_at)
  SELECT CAST(RAND()*1000 AS int) license_count
   , @date created_at
   , DATEADD(second, RAND()*1440, @date) updated_at
    SET @i=@i+1
END

Hope anybody has a proper solution for my problem.

GriGrim · Accepted Answer · 2014-03-24T12:11:03.180

1

Just tried to implement method described here: http://www.sqlperformance.com/2012/08/t-sql-queries/median

SELECT MIN(license_count) min_lic
    , MAX(license_count) max_lic
    , AVG(license_count) avg_lic
    , SUM(CASE WHEN ra BETWEEN rd - 1 AND rd + 1 THEN license_count ELSE 0 END) * 1.0
    / SUM(CASE WHEN ra BETWEEN rd - 1 AND rd + 1 THEN 1 ELSE 0 END) as median_lic
    , DATEPART(month, created_at) monat
FROM (
    SELECT created_at
        , license_count
        , ROW_NUMBER() OVER (PARTITION BY DATEPART(month, created_at) ORDER BY license_count, created_at) as ra
        , ROW_NUMBER() OVER (PARTITION BY DATEPART(month, created_at) ORDER BY license_count DESC, created_at DESC) as rd
    FROM session_counts
    WHERE created_at BETWEEN '01.01.2014' AND '31.12.2014'
) T
GROUP BY DATEPART(month, created_at)
ORDER BY monat DESC

edited Mar 24 '14 at 12:11

answered Mar 24 '14 at 09:52

GriGrim

2,891
1
19
33

I get and error devision by 0 if I execute this statement. I added ´WHERE license_count>0´ but no change. – YvesR Mar 24 '14 at 11:26
Sorry. Updated my answer. Now it seems to be correct. – GriGrim Mar 24 '14 at 12:11
Getting results now, thank you. Works also when I change to different time durations DATEPART (month, week, day). Very nice! – YvesR Mar 24 '14 at 12:24
It may work incorrect if there would be different years in dataset. In this case you should add `YEAR(created_at)` in `PARTITION BY` and `GROUP BY` clauses. – GriGrim Mar 24 '14 at 12:27

Get median value of a time period in SQL Server 2005, 2008 server

1 Answers1