最贵最便宜的葡萄酒
我试图从Stratascratch解决这个问题,在平台上给出提示。
找出每个地区最便宜和最贵的品种。输出区域与相应的最昂贵和最便宜的品种。
请帮助审查我的答案,并希望知道更好的方法来解决这个问题。
SELECT EX.region_1, EX.expensive_variety, CH.cheap_variety
FROM
(SELECT A.region_1, A.expensive_variety
FROM
(SELECT distinct region_1, variety AS expensive_variety, price,
ROW_NUMBER() OVER (PARTITION BY region_1 ORDER BY price desc) as
most_expensive
FROM winemag_p1
ORDER BY region_1 asc) A
WHERE A.most_expensive = 1) EX
INNER JOIN
(SELECT B.region_1, B.cheap_variety
FROM
(SELECT distinct region_1, variety as cheap_variety, price,
ROW_NUMBER() OVER (PARTITION BY region_1 ORDER BY price ASC) as cheapest
FROM winemag_p1
ORDER BY region_1 asc) B
WHERE B.cheapest = 1) CH
ON EX.region_1 = CH.region_1发布于 2022-04-05 14:07:51
就像这样,每个区域的最小值和最大值:
SELECT region
, MIN(price) AS cheapest
, MAX(price) AS most_expensive
FROM table_name
GROUP BY region;发布于 2022-04-05 14:17:20
您可以在同一个子查询中找到这两个。
SELECT
B.region_1,
MAX(CASE WHEN cheapest = 1 then variety else '' end) cheapest_variety,
MAX(CASE WHEN cheapest = 1 then price else 0 end) cheapest_price,
MAX(CASE WHEN expensive = 1 then variety else '' end) expensive_variety,
MAX(CASE WHEN expensive = 1 then price else 0 end) expensive_price
FROM
(SELECT distinct region_1, variety as cheap_variety, price,
ROW_NUMBER() OVER (PARTITION BY region_1 ORDER BY price ASC) as cheapest,
ROW_NUMBER() OVER (PARTITION BY region_1 ORDER BY price DESC) as expensive
FROM winemag_p1
) B
WHERE cheapest = 1 OR expensive = 1
GROUP BY region_1
ORDER BY region_1;
``发布于 2022-04-05 14:30:16
您可以使用窗口函数或子查询来获取每个区域的最高和最低价格。然后得到这些价格的所有行和每个区域的总和。
例如:
select
region_1,
min(price) as low_price,
string_agg(variety, ', ') filter (where price = min_price) as low_price_varieties,
max(price) as high_price,
string_agg(variety, ', ') filter (where price = max_price) as high_price_varieties
from
(
select
region_1, variety, price,
min(price) over (partition by region_1) as min_price,
max(price) over (partition by region_1) as max_price
from winemag_p1
) with_min_and_max
where price in (min_price, max_price)
group by region_1
order by region_1;至于您自己的查询:这是一个好的查询。以下是我的意见:
子查询中的
ORDER BY仅在限制行(使用FETCH FIRST ROWS)时才有意义,因为查询结果是由definition.DISTINCT设置的无序数据?没有要删除的副本。,
https://stackoverflow.com/questions/71752817
复制相似问题