SQL Server:如何计算中位数(分组依据)? [英] SQL Server: how to calculate median (group by)?
本文介绍了SQL Server:如何计算中位数(分组依据)?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
可能重复:
在Sql Server中计算中位数的功能
Possible Duplicate:
Function to Calculate Median in Sql Server
我有一个这样的表:
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
我可以使用group by子句来计算年龄
I can calculate agerage using group by clause
SELECT dst, AVG(sp) AS average
FROM dbo.cars
GROUP BY dbo.cars.dst
,但是我无法在SQLServer中找到MEDIAN
函数.我该如何计算中位数?
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
推荐答案
您需要更改代码以使用小数,如下所示:
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;
当前,AVG命令是在int上执行的,因此结果是int
Currently, the AVG command is performed on an int, therefore the result is an int
这篇关于SQL Server:如何计算中位数(分组依据)?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文