SQL 中的中位值计算

1. 在Northwind数据库上建立一个视图 VOrders

SET NOCOUNT ON;
USE Northwind;
GO

IF OBJECT_ID('dbo.VOrders') IS NOT NULL
  DROP VIEW dbo.VOrders;
GO
CREATE VIEW dbo.VOrders
AS

SELECT O.OrderID, O.OrderDate, O.CustomerID, O.EmployeeID, O.ShipVia,
  SUM(OD.Quantity) AS Qty,
  CAST(SUM(OD.Quantity * UnitPrice * (1 - Discount)) AS DECIMAL(12, 2)) AS Value 
FROM dbo.Orders AS O
  JOIN [Order Details] AS OD
    ON O.OrderID = OD.OrderID
GROUP BY O.OrderID, O.OrderDate, O.CustomerID, O.EmployeeID, O.ShipVia
GO

 
2. 计算整个表中数据的中位值 （MSSQL 2000的做法）

SELECT
(
 (SELECT MAX(Value) FROM
   (SELECT TOP 50 PERCENT Value FROM dbo.VOrders ORDER BY Value) AS H1)
 +
 (SELECT MIN(Value) FROM
   (SELECT TOP 50 PERCENT Value FROM dbo.VOrders ORDER BY Value DESC) AS H2)
) / 2 AS Median;

 

3. 用子查询来查询每个员工的中位值（MSSQL 2000的做法）

SELECT EmployeeID,
(
 (SELECT MAX(Value) FROM
   (SELECT TOP 50 PERCENT Value FROM dbo.VOrders AS O1
    WHERE O1.EmployeeID = E.EmployeeID
    ORDER BY Value) AS H1)
 +
 (SELECT MIN(Value) FROM
   (SELECT TOP 50 PERCENT Value FROM dbo.VOrders AS O2
    WHERE O2.EmployeeID = E.EmployeeID
    ORDER BY Value DESC) AS H2)
) / 2 AS Median
FROM dbo.Employees AS E;

 

4.1 SQL 2005的做法，首先我们看看用来计算中位值的行的数据，这里用到了通用表（CTE）和Row_Number函数

WITH OrdersRN AS
(
  SELECT EmployeeID, Value,
    ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY Value) AS RowNum,
    COUNT(*) OVER(PARTITION BY EmployeeID) AS Cnt
  FROM dbo.VOrders
)
SELECT EmployeeID, Value, RowNum, Cnt
FROM OrdersRN
WHERE RowNum IN((Cnt + 1) / 2, (Cnt + 2) / 2);

 

4.2 SQL 2005中，用通用表和Row_Number函数来计算中位值

 

WITH OrdersRN AS
(
  SELECT EmployeeID, Value,
    ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY Value) AS RowNum,
    COUNT(*) OVER(PARTITION BY EmployeeID) AS Cnt
  FROM dbo.VOrders
)
SELECT EmployeeID, AVG(Value) AS Median
FROM OrdersRN
WHERE RowNum IN((Cnt + 1) / 2, (Cnt + 2) / 2)
GROUP BY EmployeeID;

 

 

关键词：中位值，中位值计算，Median，CTE，Row_Number