英文:
Group by where category having max value
问题
这是你的表格：
+---------------+------------------------------+---------------+--+
| trading_year | sector | total_volume |
+---------------+------------------------------+---------------+--+
| 2010 | Consumer Discretionary | 119621915300 |
| 2012 | Consumer Discretionary | 93805031400 |
| 2014 | Consumer Discretionary | 80018436200 |
| 2016 | Consumer Discretionary | 81834656900 |
| 2010 | Consumer Staples | 50710518800 |
| 2012 | Consumer Staples | 39340784200 |
| 2014 | Consumer Staples | 33188087300 |
| 2016 | Consumer Staples | 37538472900 |
+---------------+------------------------------+---------------+--+
你想根据每个部门的总交易量的最大值和最小值对上面的数据进行标记，输出应该如下所示：
+---------------+------------------------------+---------------+----------------+
| trading_year | sector | total_volume | value |
+---------------+------------------------------+---------------+----------------+
| 2010 | Consumer Discretionary | 119621915300 | maxvalue |
| 2012 | Consumer Discretionary | 93805031400 | stable value |
| 2014 | Consumer Discretionary | 80018436200 | stable value |
| 2016 | Consumer Discretionary | 81834656900 | minvalue |
| 2010 | Consumer Staples | 50710518800 | maxvalue |
| 2012 | Consumer Staples | 39340784200 | stable value |
| 2014 | Consumer Staples | 33188087300 | minvalue |
| 2016 | Consumer Staples | 37538472900 | stable value |
+---------------+------------------------------+---------------+----------------+
你尝试的查询如下，但没有成功：
select *,
case when total_volume = max(total_volume) then 'maxvalue'
when total_volume = min(total_volume) then 'minvalue'
else 'stable value'
end value_type
from t
group by sector,trading_year
这个查询结果不正确，因为你的CASE语句在一个聚合查询中使用了MAX和MIN函数，这会导致不正确的结果。正确的查询应该如下所示：
SELECT t1.trading_year, t1.sector, t1.total_volume,
CASE
WHEN t1.total_volume = t2.max_volume THEN 'maxvalue'
WHEN t1.total_volume = t2.min_volume THEN 'minvalue'
ELSE 'stable value'
END AS value
FROM t t1
INNER JOIN (
SELECT sector, MAX(total_volume) AS max_volume, MIN(total_volume) AS min_volume
FROM t
GROUP BY sector
) t2 ON t1.sector = t2.sector;
这个查询将会给你正确的输出结果。
英文:
Here is my ttable
+---------------+------------------------------+---------------+--+
| trading_year | sector | total_volume |
+---------------+------------------------------+---------------+--+
| 2010 | Consumer Discretionary | 119621915300 |
| 2012 | Consumer Discretionary | 93805031400 |
| 2014 | Consumer Discretionary | 80018436200 |
| 2016 | Consumer Discretionary | 81834656900 |
| 2010 | Consumer Staples | 50710518800 |
| 2012 | Consumer Staples | 39340784200 |
| 2014 | Consumer Staples | 33188087300 |
| 2016 | Consumer Staples | 37538472900 |
i need to label the above data based on the max and min value of total_volume grouped by the sector
so my output should be like this
+---------------+------------------------------+---------------+----------------+
| trading_year | sector | total_volume | value
+---------------+------------------------------+---------------+--------------+
| 2010 | Consumer Discretionary | 119621915300 | maxvalue
| 2012 | Consumer Discretionary | 93805031400 | stable value
| 2014 | Consumer Discretionary | 80018436200 | stable value
| 2016 | Consumer Discretionary | 81834656900 | minvalue
| 2010 | Consumer Staples | 50710518800 | maxvalue
| 2012 | Consumer Staples | 39340784200 | stable value
| 2014 | Consumer Staples | 33188087300 | minvalue
| 2016 | Consumer Staples | 37538472900 | stable value
here is my query i tried but no luck
select *,
case when total_volume = max(total_volume) then 'maxvalue'
when total_volume = min(total_volume) then 'minvalue'
else 'stable value'
end value_type
from t
group by sector,trading_year
output from above query
+---------------+------------------------------+---------------+----------------+
| trading_year | sector | total_volume | value
+---------------+------------------------------+---------------+--------------+
| 2010 | Consumer Discretionary | 119621915300 | maxvalue
| 2012 | Consumer Discretionary | 93805031400 | maxvalue
| 2014 | Consumer Discretionary | 80018436200 | maxvalue
| 2016 | Consumer Discretionary | 81834656900 | maxvalue
| 2010 | Consumer Staples | 50710518800 | maxvalue
| 2012 | Consumer Staples | 39340784200 | maxvalue
| 2014 | Consumer Staples | 33188087300 | maxvalue
| 2016 | Consumer Staples | 37538472900 | maxvalue
答案1
得分: 2
你的SQL在这里不适用。不要使用 group by，因为你想要保留所有行。我们需要的是窗口和分析。
select
t.*,
case
when total_volume = last_value(total_volume) over w then 'maxvalue'
when total_volume = first_value(total_volume) over w then 'minvalue'
else 'stable value'
end as value
from t
window w as (partition by sector order by total_volume
rows between unbounded preceding and unbounded following)
祝你有一个愉快的一天 
更新：
另一种（更简单）的方法：
select
t.*,
case
when total_volume = max(total_volume) over w then 'maxvalue'
when total_volume = min(total_volume) over w then 'minvalue'
else 'stable value'
end as value
from t
window w as (partition by sector)
请注意，没有 order by，默认的窗口规范是 rows between unbounded preceding and unbounded following。而有了 order by，窗口规范默认为 rows between unbounded preceding and current row。
英文:
Your SQL won't work here. Don't use group by since you wanna preserve all the rows. What we need for this is windowing and analytics.
select
t.*,
case
when total_volume = last_value(total_volume) over w then 'maxvalue'
when total_volume = first_value(total_volume) over w then 'minvalue'
else 'stable value'
end as value
from t
window w as (partition by sector order by total_volume
rows between unbounded preceding and unbounded following)
Have a nice day
UPDATE:
Yet another (simpler) way:
select
t.*,
case
when total_volume = max(total_volume) over w then 'maxvalue'
when total_volume = min(total_volume) over w then 'minvalue'
else 'stable value'
end as value
from t
window w as (partition by sector)
Note, without order by, the default window specification is rows between unbounded proceeding and unbounded following. Whereas with order by, the window specification is defaulted to rows between unbounded proceeding and current row.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论