mysql - 如何在多个组和表上使用 WHERE 子句获得前 3 位平均值?
问题描述
我正在使用一个法国房地产数据库,其中记录了每学期在法国的所有销售额。它有3个表:
- Localite,具有城市(公社)和州(部门)数据,
- Bien,具有房屋数据(m²、地址等),仅在此查询中使用,因为表的链接方式,以及
- 具有价格(ValeurFonciere)数据的突变。
使用以下方法创建的:
CREATE TABLE Localite (
Commune_ID SMALLINT UNSIGNED NOT NULL,
Commune VARCHAR(50) NOT NULL,
Departement VARCHAR(3) NOT NULL,
PRIMARY KEY (Commune_ID)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE Bien (
Bien_ID SMALLINT UNSIGNED NOT NULL,
SurCar1 FLOAT NOT NULL,
TypeLoc VARCHAR(15) NOT NULL,
NoPP SMALLINT NOT NULL,
NoVoie VARCHAR(5) NOT NULL,
TypeVoie VARCHAR(10) NOT NULL,
NomVoie VARCHAR(50) NOT NULL,
CodePostal VARCHAR(5) NOT NULL,
Commune_ID SMALLINT UNSIGNED NOT NULL,
PRIMARY KEY (Bien_ID)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE Mutation (
Mutation_ID SMALLINT UNSIGNED NOT NULL,
DateMutation DATETIME NOT NULL,
ValeurFonciere INT UNSIGNED NOT NULL,
Bien_ID SMALLINT UNSIGNED NOT NULL,
PRIMARY KEY (Mutation_ID)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
我需要找到平均售价最高的 3 个城市,但只针对少数几个州。
当我使用这个时:
SELECT Departement, Commune, avg(ValeurFonciere) as Prix_Moy
FROM localite, bien, mutation
where mutation.Bien_ID = bien.Bien_ID
And bien.Bien_ID = localite.Commune_ID
AND Departement in (6, 13, 33, 59, 69)
GROUP BY Commune
ORDER BY Prix_Moy DESC
limit 3;
我在所有选定的 5 个部门(即 6、13、33、59、69)中获得了前 3 名。在法国,每个部门(州)都有自己的识别号码,所以这些号码是我感兴趣的部门。
但是我如何获得每个部门的前 3 名(所以,总共 15 个公社)?
我在这里查看并找到了几个使用带有分区的 rank() 函数的解决方案,但我不知道这是否是正确的解决方案,因为我不能告诉它只查看我需要的 5 个部门。
任何帮助将不胜感激。
解决方案
在朋友的帮助下花了我一些时间,但这是我们想出的,它给出了所需的结果:
WITH VF_par_Commune AS
(SELECT Departement, Commune, avg(ValeurFonciere) as AVF
FROM localite, bien, mutation
where mutation.Bien_ID = bien.Bien_ID
And bien.Commune_ID = localite.Commune_ID
AND Departement IN (6,13,33,59,69)
GROUP BY Departement, Commune
)
SELECT Departement, Commune, round(AVF,2) AS Prix_Moy
FROM (
SELECT Departement, Commune, AVF,
rank() OVER (PARTITION BY Departement ORDER BY AVF DESC) AS rang FROM VF_par_Commune) AS result
WHERE rang <= 3;
推荐阅读
- java - 在 Jar 文件中运行 exe 文件
- aws-msk - AWS MSK - 通过修改 CF 更新存储卷大小
- azure-devops - Azure DevOps Terraform Task 可以使用存储帐户 SAS 令牌来存储远程状态文件吗?
- webpack - 无法使用 webpack-pug 应用程序实现脚本
- python - 如何在 Python 中追加列表而不覆盖?
- spring-boot - Spring数据elasticsearchQuery等价于HasChildQuery
- javascript - 如何让webpack打包结果使用模块语法加载chunk
- c# - 尝试在 Mac 上使用单声道打开 c# 文件不起作用
- android - 访问被拒绝查找属性“vendor.camera.aux.packagelist” Flutter
- angular - Ag 网格组件过度滚动问题