SQL Server: how to calculate median (group by)? [d

Possible Duplicate:
Function to Calculate Median in Sql Server

I have a table like this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[cars](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [sp] [int] NOT NULL,
    [dst] [int] NOT NULL,
    [type] [varchar](10) NULL,
 CONSTRAINT [PK_id] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[cars] ON
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (1, 4, 2, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (2, 4, 10, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (3, 7, 4, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (4, 7, 22, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (5, 8, 16, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (6, 9, 10, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (7, 10, 18, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (8, 10, 26, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (9, 10, 34, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (10, 11, 17, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (11, 11, 28, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (12, 12, 14, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (13, 12, 20, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (14, 12, 24, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (15, 12, 28, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (16, 13, 26, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (17, 13, 34, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (18, 13, 34, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (19, 13, 46, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (20, 14, 26, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (21, 14, 36, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (22, 14, 60, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (23, 14, 80, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (24, 15, 20, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (25, 15, 26, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (26, 15, 54, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (27, 16, 32, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (28, 16, 40, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (29, 17, 32, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (30, 17, 40, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (31, 17, 50, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (32, 18, 42, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (33, 18, 56, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (34, 18, 76, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (35, 18, 84, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (36, 19, 36, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (37, 19, 46, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (38, 19, 68, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (39, 20, 32, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (40, 20, 48, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (41, 20, 52, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (42, 20, 56, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (43, 20, 64, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (44, 22, 66, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (45, 23, 54, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (46, 24, 70, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (47, 24, 92, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (48, 24, 93, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (49, 24, 120, NULL)
INSERT [dbo].[cars] ([id], [sp], [dst], [type]) VALUES (50, 25, 85, NULL)
SET IDENTITY_INSERT [dbo].[cars] OFF

I can calculate agerage using group by clause

SELECT dst, AVG(sp) AS average
FROM dbo.cars
GROUP BY dbo.cars.dst

but I'm not able to find MEDIAN function in SQLServer. How can I calculate median like this?

SELECT dst, MEDIAN(sp) AS median
FROM dbo.cars
GROUP BY dbo.cars.dst

回答1:

You need to change your code to use decimals as follows

 SELECT
   dst,
   AVG(sp)
FROM
(
   SELECT
      dst,
      cast(sp as decimal(5,2)) sp,
      ROW_NUMBER() OVER (
         PARTITION BY dst 
         ORDER BY sp ASC, id ASC) AS RowAsc,
      ROW_NUMBER() OVER (
         PARTITION BY dst 
         ORDER BY sp DESC, id DESC) AS RowDesc
   FROM dbo.cars SOH
) x
WHERE 
   RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
GROUP BY dst
ORDER BY dst;

Currently, the AVG command is performed on an int, therefore the result is an int

回答2:

I think I found the solution, but this does not seem to produce even numbers (like 10.5)

SELECT
   dst,
   AVG(sp)
FROM
(
   SELECT
      dst,
      sp,
      ROW_NUMBER() OVER (
         PARTITION BY dst 
         ORDER BY sp ASC, id ASC) AS RowAsc,
      ROW_NUMBER() OVER (
         PARTITION BY dst 
         ORDER BY sp DESC, id DESC) AS RowDesc
   FROM dbo.cars SOH
) x
WHERE 
   RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
GROUP BY dst
ORDER BY dst;

回答3:

You can use partition by clause and ranking functions