我想使用前 5 名来捕获许多标准的前 5 名。
数据样式为月份日期、区域 1、子区域 1、子区域 2 等,类型,每行的唯一标识符。
我想做的是每个月和区域找到前 5 种类型。
示例数据:
Date Division Sub Division ID
31/05/2012 Sales 001
31/05/2012 Sales Call Centre 002
31/05/2012 Sales Call Centre 003
31/05/2012 Sales Store 004
31/05/2012 Marketing 005
31/05/2012 Marketing TV 006
30/04/2012 Sales 001
30/04/2012 Sales Call Centre 002
30/04/2012 Sales Call Centre 003
30/04/2012 Sales Store 004
30/04/2012 Marketing 005
30/04/2012 Marketing TV 006
等等。
我想要的输出是:每月分区级别前 5 名(不考虑分区)每月每个分区级别的前 5
名示例输出:
根据级别,相应区域的细分:
Date Top5Areas Volume
31/05/2012 Sales 100
31/05/2012 Marketing 90
31/05/2012 HR 50
30/04/2012 Sales 100
30/04/2012 Marketing 90
30/04/2012 HR 50
但每组 5 个。
Date Top5Areas Volume
31/05/2012 Call Centre 100
31/05/2012 Store 90
31/05/2012 HR 50
不应该如何做到这一点,想知道我是否需要通过许多查询来建立结果,或者是否有更好的方法。
提前感谢并感谢您的建议。
SELECT [TableA].[DisplayVar] AS DisplayVar, [TableB].[Date] AS MonthDate, TableC. [Divison] AS Divison, Volume
FROM (SELECT [TableA].[DisplayVar],
[TableB].[Date],
TableC.[Divison],
Volume
( SELECT COUNT() + 1
FROM ( SELECT [TableA].[DisplayVar], [TableB].[Date], TableC.[Divison], COUNT() AS Volume
FROM ([TableA] INNER JOIN [TableB] ON [TableA].[ID] = [TableB].[ID]) LEFT JOIN TableC ON [TableB].ID = TableC.Descriptor
GROUP BY [TableA].[DisplayVar], [TableB].[Date], TableC.[Divison]
) AS T
WHERE T.[TableB].[Date] = Data.[TableB].[Date]
AND T.Volume > Data.Volume
) AS Rank
FROM ( SELECT [TableA].[DisplayVar], [TableB].[Date], TableC.[Divison], COUNT(*) AS Volume
FROM ([TableA] INNER JOIN [TableB] ON [TableA].[ID] = [TableB].[ID]) LEFT JOIN TableC ON [TableB].ID = TableC.Descriptor
WHERE [TableB].[Date] BETWEEN Date() AND DateADD("m", -12, Date())
GROUP BY[TableA].[DisplayVar], [TableB].[Date], TableC.[Divison]
) AS DATA
) AS Data
WHERE (((Data.Rank)<=5))
ORDER BY [TableB].[Date] DESC , Volume DESC;
我能想到的在每个日期(我认为你所追求的)访问中做前 n 个的唯一方法是使用相关的子查询。我认为最简单的方法类似于在其他 DBMS 中使用 ROW_NUMBER()
函数,它为您的原始聚合查询添加一个额外的字段,为每个分区/日期提供一个排名,最外层的 where 子句将其限制为排名小于或等于 5 的行。
SELECT Date,
Division,
Volume
FROM ( SELECT Date,
Division,
Volume,
( SELECT COUNT(*) + 1
FROM ( SELECT Date, Division, COUNT(*) AS Volume
FROM YourTable
GROUP BY Date, Division
) AS T
WHERE T.Date = Data.Date
AND T.Volume > Data.Volume
) AS Rank
FROM ( SELECT Date, Division, COUNT(*) AS Volume
FROM YourTable
WHERE Date BETWEEN DATE() AND DATEADD("m", -12, DATE())
GROUP BY Date, Division
) AS DATA
) AS Data
WHERE Rank <= 5
这样做的缺点(或根据您的要求的好处)是它不处理平局,即如果您有 10 个具有相同卷的部门,则所有 10 个部门都将返回,而不仅仅是其中的 5 个。
我不确定这是否正是您所需要的,但希望至少会让您朝着正确的方向前进。