oracle 城市的平均流行[重复]

z4bn682m 于 2022-12-03 发布在 Oracle

关注(0)|答案(2)|浏览(128)

此问题在此处已有答案：

Fetch the rows which have the Max value for a column for each distinct value of another column（35个答案）
2天前关闭。
我想为每个州找到一个平均人口最多的城市？我有三个表：州、城市和人口。提供的表经过简化，只有2个州
“state”代码是我们的关键字，对每个状态都是唯一的
| 姓名|代码|
| - -|- -|
| 俄亥俄州|羟基|
| 威斯康星州|工作区|
“城市”
指两个州之间边界的两州代码
| 代码|姓名|
| - -|- -|
| 羟基|采夫兰|
| 羟基|代顿|
| 羟基|托莱多|
| 工作区|麦迪逊|
| 工作区|拉辛|
“都市流行音乐”
| 代码|姓名|年份|爆响|
| - -|- -|- -|- -|
| 羟基|采夫兰|一九九八年|万|
| 羟基|采夫兰|二○ ○ ○年|千|
| 羟基|代顿|一九九八年|六千|
| 羟基|托莱多|小行星7787|八千|
| 工作区|麦迪逊|一九九九年|二○ ○ ○年|
| 工作区|麦迪逊|二○ ○ ○年|二万|
| 工作区|拉辛|二○ ○ ○年|五千|
预期结果：Cevland未选择，因为选择了avgpop、麦迪逊和Toledo
| 城市|平均流行|
| - -|- -|
| 托莱多|八千|
| 麦迪逊|小行星11000|
到目前为止我所做的查询。

Select c.name, avg(cp.length)
from city c
Inner Join citypop cp
On c.name = cp.city
Group by c.name

我的想法是我想选择的名称和avg，但不确定如何获得下一步只有一个城市为每个国家。
“编辑”之所以选择麦迪逊是因为我们将2000、20000相加，然后除以平均值。因此，麦迪逊平均值是11000，而拉辛（只有一个数据点值）是5000。我们希望得到最大平均值，因此选择麦迪逊。

oracle

来源：https://stackoverflow.com/questions/74632510/avg-pop-of-a-city

2条答案

按热度按时间

zf9nrax11#

也许这个能解决问题：

--  S a m p l e    D a t a
WITH
    states AS
        (
            Select 'Ohio'       "STATE",    'OH' "CODE" From Dual Union All
            Select 'Wisconsin'  "STATE",    'WI' "CODE" From Dual 
        ),
    cities AS
        (
            Select 'OH' "CODE", 'Clevland' "CITY" From Dual Union All
            Select 'OH' "CODE", 'Dayton'   "CITY" From Dual Union All
            Select 'OH' "CODE", 'Toledo'   "CITY" From Dual Union All
            Select 'WI' "CODE", 'Madison'  "CITY" From Dual Union All
            Select 'WI' "CODE", 'Racine'   "CITY" From Dual 
        ),
    citypops AS
        (
            Select 'OH' "CODE", 'Clevland' "CITY", 1998 "YR", 10000 "POP" From Dual Union All 
            Select 'OH' "CODE", 'Clevland' "CITY", 2000 "YR",  1000 "POP" From Dual Union All
            Select 'OH' "CODE", 'Dayton'   "CITY", 1998 "YR",  6000 "POP" From Dual Union All
            Select 'OH' "CODE", 'Toledo'   "CITY", 1978 "YR",  8000 "POP" From Dual Union All
            Select 'WI' "CODE", 'Madison'  "CITY", 1999 "YR",  2000 "POP" From Dual Union All
            Select 'WI' "CODE", 'Madison'  "CITY", 2000 "YR", 20000 "POP" From Dual Union All
            Select 'WI' "CODE", 'Racine'   "CITY", 2000 "YR",  5000 "POP" From Dual
        ),

使用平均值创建CTE...

avgs AS
        (
            Select
                s.CODE "CODE",
                c.CITY "CITY",
                AVG(p.POP) "AVG_POP"
            From
                states s
            Inner Join
                cities c ON(c.CODE = s.CODE)
            Inner Join
                citypops p ON(p.CODE = c.CODE And p.CITY = c.CITY)
            Group By
                s.CODE, c.CITY        
        )

...使用平均CTE选择数据并使用内部连接进行过滤

Select
    a.CITY,
    a.AVG_POP "MAX_AVG_POP"
From
    avgs a
Inner Join
    (
        Select
          CODE,
          CITY,
          MAX(AVG_POP) OVER(PARTITION BY CODE) "MAX_AVG_POP" 
        FROM
          avgs
    ) m ON(a.CODE = m.CODE And m.CITY = a.CITY And a.AVG_POP = m.MAX_AVG_POP)

结果：
| 城市名称|最大平均POP|
| - -|- -|
| 托莱多|八千|
| 麦迪逊|小行星11000|

注：

如果在同一个州内有两个城市具有相同的（最大）平均弹出次数-这两个城市都将被选中...

赞(0）回复(0）举报 2022-12-03

wgmfuz8q2#

使用CTE和具有ROW_NUMBER的简单Grouping函数，可以实现
这将为每个州只找到一个城市，正如您所希望的，如果有两个城市具有相同的平均值，它将随机选择，或者您添加第二个顺序，通过它选择所需的城市

WITH CITY_POP AS (
SELECT
 "code", "name", AVG("pop") avgpop
  ,ROW_NUMBER() OVER(PARTITION BY "code" ORDER BY  AVG("pop") DESC) rn
FROM citypop
GROUP BY "code", "name")
SELECT "name", avgpop FROM CITY_POP WHERE rn = 1

| 姓名|平均POP|
| - -|- -|
| 托莱多|八千|
| 麦迪逊|小行星11000|
fiddle

赞(0）回复(0）举报 2022-12-03

我来回答

oracle 城市的平均流行[重复]

2条答案

相关问题

热门标签

最新问答