亂馬客

前言

在上SQL效能調教課程時，有個作業是，請列出近3年前10大的客戶(2001~2003)，包括年度、客戶代碼、名稱、總訂單金額，依年度、金額排序。

原本以為是這3年中前10大，結果SKY老師說，要每年的前10大，所以共有30筆資料哦(資料庫是AdventureWorks)!

實作

Step:先找出各年度前10大的客戶(由Sales.SalesOrderHeader找出)，再Join [Sales].[Store] 列出客戶名稱。

作法1.使用UNION來將各年的前10大加起來，SQL如下，

SELECT Z.*
FROM (
SELECT TOP 10 A.OrderYear, A.CustomerID, A.Total
FROM (
SELECT YEAR(GETDATE()) - 10 AS OrderYear, SOH.CustomerID, SUM(SOH.TotalDue) Total
FROM Sales.SalesOrderHeader SOH (NOLOCK)
WHERE SOH.OrderDate BETWEEN CAST(STR(YEAR(GETDATE()) - 10) + '/1/1'  AS DATETIME) AND CAST(STR(YEAR(GETDATE()) - 10) + '/12/31'  AS DATETIME) 
GROUP BY SOH.CustomerID 
) A
ORDER BY  A.Total DESC) Z
UNION 
SELECT Z.*
FROM (
SELECT TOP 10 A.OrderYear, A.CustomerID, A.Total
FROM (
SELECT YEAR(GETDATE()) - 9 AS OrderYear, SOH.CustomerID, SUM(SOH.TotalDue) Total
FROM Sales.SalesOrderHeader SOH (NOLOCK)
WHERE SOH.OrderDate BETWEEN CAST(STR(YEAR(GETDATE()) - 9) + '/1/1'  AS DATETIME) AND CAST(STR(YEAR(GETDATE()) - 9) + '/12/31'  AS DATETIME) 
GROUP BY SOH.CustomerID 
) A
ORDER BY  A.Total DESC) Z
UNION 
SELECT Z.*
FROM (
SELECT TOP 10 *
FROM (
SELECT YEAR(GETDATE()) - 8 AS OrderYear, SOH.CustomerID, SUM(SOH.TotalDue) Total
FROM Sales.SalesOrderHeader SOH (NOLOCK)
WHERE SOH.OrderDate BETWEEN CAST(STR(YEAR(GETDATE()) - 8) + '/1/1'  AS DATETIME) 
AND CAST(STR(YEAR(GETDATE()) - 8) + '/12/31'  AS DATETIME) 
GROUP BY SOH.CustomerID 
) A
ORDER BY  A.Total DESC) Z

作法2.使用Temp Table + While把各年前10大的資料暫存到Temp Table之中，SQL如下，

SELECT YEAR(SOH.OrderDate) AS OrderYear, SOH.CustomerID,  SOH.TotalDue AS Total
INTO #TOP_ORDER
FROM Sales.SalesOrderHeader SOH (NOLOCK)
WHERE 1 = 0

DECLARE @BEGIN_YEAR INT, @TOP_N INT, @IDX INT
SET @BEGIN_YEAR = YEAR(GETDATE()) - 10
SET @TOP_N = 3
SET @IDX = 0
WHILE @IDX < @TOP_N
BEGIN
    INSERT INTO #TOP_ORDER(OrderYear, CustomerID, Total)
    SELECT TOP 10 A.OrderYear, A.CustomerID, A.Total
    FROM (
    SELECT @BEGIN_YEAR + @IDX AS OrderYear, SOH.CustomerID, SUM(SOH.TotalDue) Total
    FROM Sales.SalesOrderHeader SOH (NOLOCK)
    WHERE SOH.OrderDate BETWEEN CAST(STR(@BEGIN_YEAR + @IDX) + '/1/1'  AS DATETIME) 
AND CAST(STR(@BEGIN_YEAR + @IDX) + '/12/31'  AS DATETIME) 
    GROUP BY SOH.CustomerID 
    ) A
    ORDER BY  A.Total DESC
    
    SET @IDX = @IDX + 1
END

SELECT * FROM #TOP_ORDER

SELECT Z.OrderYear, S.CustomerID, S.Name, Z.Total   
FROM #TOP_ORDER  Z INNER JOIN [Sales].[Customer] C (NOLOCK) ON (Z.CustomerID = C.CustomerID)
INNER JOIN [Sales].[Store] S (NOLOCK) ON (C.CustomerID = S.CustomerID)

DROP TABLE #TOP_ORDER

作法3.使用ROW_NUMBER()，依各年排列序號，然後取出序號為1~10的，SQL如下，

SELECT Z.OrderYear, S.CustomerID, S.Name, Z.Total   
FROM 
(
 SELECT *
 FROM (
    SELECT YEAR(SOH.OrderDate) AS OrderYear, SOH.CustomerID, SUM(SOH.TotalDue) Total
, row_number() OVER( PARTITION BY YEAR(SOH.OrderDate) ORDER BY SUM(SOH.TotalDue) DESC) 
AS GROUPNUM
    FROM Sales.SalesOrderHeader SOH (NOLOCK)
    WHERE SOH.OrderDate BETWEEN CAST(STR(YEAR(GETDATE()) - 10) + '/1/1'  AS DATETIME) 
AND CAST(STR(YEAR(GETDATE()) - 8) + '/12/31'  AS DATETIME) 
    GROUP BY YEAR(SOH.OrderDate), SOH.CustomerID ) A
WHERE GROUPNUM BETWEEN 1 AND 10
) Z INNER JOIN [Sales].[Store] S (NOLOCK) ON (Z.CustomerID = S.CustomerID)

如果要某個群組列出序號，可以在ROW_NUMBER() 的OVER 中加入PARTITION BY設定。

使用作法3，SQL看起來比較精簡一點。

今天看了其他組，也可也可以使用RANK函數，只是最後要再加入Order By，如下，

SELECT Z.OrderYear, S.CustomerID, S.Name, Z.Total   
FROM 
(
 SELECT *
 FROM (
	SELECT YEAR(SOH.OrderDate) AS OrderYear, SOH.CustomerID, SUM(SOH.TotalDue) Total
	, RANK() OVER( PARTITION BY YEAR(SOH.OrderDate) ORDER BY SUM(SOH.TotalDue) DESC) AS GROUPNUM
	FROM Sales.SalesOrderHeader SOH (NOLOCK)
	WHERE SOH.OrderDate BETWEEN CAST(STR(YEAR(GETDATE()) - 10) + '/1/1'  AS DATETIME) AND CAST(STR(YEAR(GETDATE()) - 8) + '/12/31'  AS DATETIME) 
	GROUP BY YEAR(SOH.OrderDate), SOH.CustomerID ) A
WHERE GROUPNUM BETWEEN 1 AND 10
) Z INNER JOIN [Sales].[Customer] C (NOLOCK) ON (Z.CustomerID = C.CustomerID)
INNER JOIN [Sales].[Store] S (NOLOCK) ON (Z.CustomerID = S.CustomerID)
ORDER BY OrderYear, Total DESC

 

Hi, 

亂馬客Blog已移到了 「亂馬客​ : Re:從零開始的軟體開發生活」

請大家繼續支持 ^_^