首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >PostgreSQL -每个地区找到最昂贵和最便宜的葡萄酒

问PostgreSQL -每个地区找到最昂贵和最便宜的葡萄酒
EN

Stack Overflow用户
提问于 2022-04-05 13:41:25
回答 3查看 120关注 0票数 0

最贵最便宜的葡萄酒

我试图从Stratascratch解决这个问题,在平台上给出提示。

找出每个地区最便宜和最贵的品种。输出区域与相应的最昂贵和最便宜的品种。

请帮助审查我的答案,并希望知道更好的方法来解决这个问题。

代码语言:javascript
复制
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
EN

回答 3

Stack Overflow用户

发布于 2022-04-05 14:07:51

就像这样,每个区域的最小值和最大值:

代码语言:javascript
复制
SELECT region
, MIN(price) AS cheapest
, MAX(price) AS most_expensive
FROM table_name
GROUP BY region;
票数 1
EN

Stack Overflow用户

发布于 2022-04-05 14:17:20

您可以在同一个子查询中找到这两个。

代码语言:javascript
复制
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;
``
票数 0
EN

Stack Overflow用户

发布于 2022-04-05 14:30:16

您可以使用窗口函数或子查询来获取每个区域的最高和最低价格。然后得到这些价格的所有行和每个区域的总和。

例如:

代码语言:javascript
复制
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.
  • Why DISTINCT设置的无序数据?没有要删除的副本。

,

  • ,您不处理领带。例如,如果在一个地区有两种价格相同的顶级葡萄酒,你可以任意挑选一种。--
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/71752817

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档