TSQL 选择最小和分组时的最大行数 [英] TSQL Select Min & Max row when grouping
问题描述
假设我有一个包含许多行的表,如下所示:
Lets say I have a table containing many many rows like this:
ID Range Range_begining Profit
----------------------------------------------------
1 (100-150) 100 -20
2 (200-250) 200 40.2
3 (100-150) 100 100
4 (450-500) 450 -90
...
我正在做一个这样的简单查询:
I'm doing a simple query like this:
SELECT max([Range]) AS 'Range'
, count(ID) AS 'Count'
, round(avg([Profit]), 2) AS 'AVG Profit'
FROM
Orders
GROUP BY
Range_begining
运行此查询后,我得到如下结果:
After this query is run I get results like this:
Range Count AVG Profit
------------------------------------
(100-150) 2 40
(200-250) 1 40.2
(450-500) 1 -90
...
很简单:)
我现在需要做的是选择具有最小和最大利润的行,其中计数大于 10(这是一个参数)
What I need to do now is to select row with minimum and maximum profit where count is bigger than 10 (this is a parameter)
我能够通过这个获得最小值:
I was able to get minimum value with this:
SELECT TOP 1 [Range], [AVG Profit] FROM (
SELECT max([Range]) AS 'Range'
, count(ID) AS 'Count'
, round(avg([Profit]), 2) AS 'AVG Profit'
FROM
Orders
GROUP BY
Range_begining) X
WHERE
[Count]>10
ORDER BY
[AVG Profit] ASC --or DESC if I want max profit
我正在考虑使用 ORDER BY DESC 为上述查询执行 UNION
,但这不是最佳解决方案.
I was thinking of doing an UNION
for above query with ORDER BY DESC, but it isn't the best solution.
我需要做的:
选择 2 行:按范围分组时,第一行具有最小值,第二行具有最大 AVG Profit.
如果我像这样在主数据表中添加 2 个移动列:
If I add 2 move columns to my main data table like this:
ID Range Range_begining Profit OrderDate Company
---------------------------------------------------------------------------------
1 (100-150) 100 -20 2012-01-02 1
2 (200-250) 200 40.2 2012-03-22 0
3 (100-150) 100 100 2012-02-05 0
4 (450-500) 450 -90 2012-05-12 1
...
然后尝试添加另外 2 个这样的条件:
And then try to add 2 more conditions like this:
; with ordering as (
SELECT max([Range]) AS 'Range'
, count(ID) AS 'Count'
, round(avg([Profit]), 2) AS 'AVG Profit'
, row_number() over (order by avg([Profit])) rn_min
, row_number() over (order by avg([Profit]) desc) rn_max
FROM
Orders
GROUP BY
Range_begining
HAVING COUNT(ID) > 10
AND [Company]=@company
AND (@from= '' OR [OrderDate]>=@from)
AND (@to= '' OR [OrderDate]<=@to)
)
select [range], [count], [avg profit]
from ordering
where (rn_max = 1 or rn_min = 1)
我收到一个错误,因为 [Company] 和 [OrderDate]
I get an error because [Company] and [OrderDate]
在 HAVING 子句中无效,因为它不包含在任何一个聚合函数或 GROUP BY 子句.
is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
我该如何解决这个问题?
How can I fix this?
EDIT2成功了!
; with ordering as (
SELECT max([Range]) AS 'Range'
, count(ID) AS 'Count'
, round(avg([Profit]), 2) AS 'AVG Profit'
, row_number() over (order by avg([Profit])) rn_min
, row_number() over (order by avg([Profit]) desc) rn_max
FROM
Orders
WHERE
[Company]=@company
AND (@from= '' OR [OrderDate]>=@from)
AND (@to= '' OR [OrderDate]<=@to)
GROUP BY
Range_begining
HAVING COUNT(ID) > 10
)
select [range], [count], [avg profit]
from ordering
where (rn_max = 1 or rn_min = 1)
编辑 3我可以返回带有这样描述的另一列吗:
EDIT 3 Can I return another column with description like this:
Range AVG Profit Description
-------------------------------------------------
(200-250) 40.2 Max profit here
(450-500) -90 Min profit, well done
编辑 4快速回答(基于@Nikola Markovinović 的回答):
EDIT 4 Fast answer (based on @Nikola Markovinović answer):
; with ordering as (
SELECT max([Range]) AS 'Range'
, count(ID) AS 'Count'
, round(avg([Profit]), 2) AS 'AVG Profit'
, row_number() over (order by avg([Profit])) rn_min
, row_number() over (order by avg([Profit]) desc) rn_max
FROM
Orders
WHERE
[Company]=@company
AND (@from= '' OR [OrderDate]>=@from)
AND (@to= '' OR [OrderDate]<=@to)
GROUP BY
Range_begining
HAVING COUNT(ID) > 10
)
SELECT
CASE WHEN rn_max=1 THEN 'This is max' ELSE 'Min' END AS 'Description'
,[range]
,[count]
,[avg profit]
FROM ordering
WHERE (rn_max = 1 or rn_min = 1)
推荐答案
您可以使用 立即完成窗口函数:
; with ordering as (
SELECT max([Range]) AS 'Range'
, count(ID) AS 'Count'
, round(avg([Profit]), 2) AS 'AVG Profit'
, row_number() over (order by avg([Profit])) rn_min
, row_number() over (order by avg([Profit]) desc) rn_max
FROM
Orders
GROUP BY
Range_begining
HAVING COUNT(ID) > 10
)
select [range], [count], [avg profit],
case when rn_max = 1
then 'Max profit'
else 'Min profit'
end Description
from ordering
where (rn_max = 1 or rn_min = 1)
这篇关于TSQL 选择最小和分组时的最大行数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!