使用MYSQL8对 California Housing 加利福尼亚的房价数据集，进行统计分析

select housing_median_age,total_rooms,	total_bedrooms,	population,	households,	median_income  from housing limit 100;

先按街区的经度排个排名，并输出名次：

SELECT ROW_NUMBER() OVER (ORDER BY longitude) AS rankS, longitude
FROM (SELECT DISTINCT longitude FROM housing) AS t

-- 输出housing_median_age的区间分布，按照10年为一个区间

-- 输出housing_median_age的区间分布，按照10年为一个区间
SELECT CONCAT(FLOOR(housing_median_age / 10) * 10, '-', FLOOR(housing_median_age / 10) * 10 + 9) AS age_range, COUNT(*) AS frequency
FROM housing
GROUP BY age_range
ORDER BY age_range;

-- 使用一句SQL输出每个区间的百分比，保留两位小数

-- 使用一句SQL输出每个区间的百分比，保留两位小数
SELECT CONCAT(FLOOR(housing_median_age / 10) * 10, '-', FLOOR(housing_median_age / 10) * 10 + 9) AS age_range,
COUNT(*) AS frequency, ROUND(COUNT(*) / (SELECT COUNT(*) FROM housing) * 100, 2) AS percentage
FROM housing
GROUP BY age_range
ORDER BY age_range;

使用RANK()函数对每个地区的小区按照房价中位数进行排名，找出房价最高和最低的小区。

大致的思路如下：

- 首先，使用RANK()函数对每个地区的小区按照房价中位数进行排名，使用PARTITION BY子句按照地区进行分组，使用ORDER BY子句按照房价中位数进行排序，将排名结果作为一个新的字段添加到数据表中。
- 然后，使用HAVING子句筛选出每个地区的房价最高和最低的小区，即排名为1或等于该地区小区数量的小区。
- 最后，使用SELECT子句输出所需的字段，例如小区的地区、房价中位数、排名等。

SELECT longitude,latitude
    housing_median_age,
    total_rooms,
    total_bedrooms,
    population,
    households,
    median_income,
    median_house_value,
    RANK() OVER (PARTITION BY longitude,latitude ORDER BY median_house_value DESC) AS ranks
FROM
    housing
ORDER BY
    longitude, latitude,median_house_value DESC;

使用LAG()或LEAD()函数获取每个小区的前一个或后一个小区的房价中位数，计算房价的变化率或差异。

SELECT
    *,
    (median_house_value - LAG(median_house_value) OVER (ORDER BY longitude, latitude)) / LAG(median_house_value) OVER (ORDER BY longitude, latitude) AS price_change_rate
FROM
    housing
ORDER BY
    longitude, latitude;

使用ROW_NUMBER()函数对每个地区的小区按照人均收入进行排序，然后使用NTILE()函数将小区分为四个收入等级，分析不同收入等级的小区在房价上的差异。

SELECT
    longitude	,latitude,

    housing_median_age,
    total_rooms,
    total_bedrooms,
    population,
    households,
    median_income,
    median_house_value,
    NTILE(4) OVER (PARTITION BY longitude,	latitude ORDER BY median_income) AS income_level
FROM
    housing
ORDER BY
    longitude,latitude, median_income;

这个SQL语句将返回一个结果集，其中包含每个小区的信息，包括地区、小区名称、房屋年龄、总房间数、
总卧室数、人口数、家庭数、收入中位数、房价中位数，
以及按照人均收入进行分组的收入等级。使用ROW_NUMBER()函数对每个地区的小区按照人均收入进行排序，
然后使用NTILE()函数将小区分为四个收入等级。结果集按照地区和人均收入进行排序。

- 使用RANK()函数对每个地区的小区按照房价中位数进行排名，找出房价最高和最低的小区。
- 使用LAG()或LEAD()函数获取每个小区的前一个或后一个小区的房价中位数，计算房价的变化率或差异。
- 使用ROW_NUMBER()函数对每个地区的小区按照人均收入进行排序，然后使用NTILE()函数将小区分为四个收入等级，分析不同收入等级的小区在房价上的差异。
- 使用SUM()或AVG()函数计算每个地区的小区的总人口或平均人口，然后使用PARTITION BY子句按照地理位置进行分组，比较不同地理位置的人口情况。
- 使用FIRST_VALUE()或LAST_VALUE()函数获取每个地区的小区中房龄最大或最小的小区的房价中位数，分析房龄与房价的关系。
- 使用DENSE_RANK()函数对每个地区的小区按照房间数进行排名，然后使用PARTITION BY子句按照房龄进行分组，分析不同房龄下房间数与房价的关系。
- 使用COUNT()函数计算每个地区的小区的数量，然后使用PARTITION BY子句按照人均收入进行分组，分析不同收入水平下小区数量与房价的关系。
- 使用MAX()或MIN()函数获取每个地区的小区中房价中位数最高或最低的小区的特征值，然后使用PARTITION BY子句按照房间数进行分组，分析不同房间数下房价极值与特征值的关系。
- 使用PERCENT_RANK()函数对每个地区的小区按照人口密度进行排名，然后使用PARTITION BY子句按照地理位置进行分组，分析不同地理位置下人口密度与房价的关系。
- 使用CUME_DIST()函数计算每个小区在全州范围内的累积分布百分比，然后使用ORDER BY子句按照房价中位数进行排序，找出房价高于平均水平或低于平均水平的小区。

以上是我为您找到的一些使用MYSQL的窗口函数进行的统计分析案例。希望对您有所帮助。

参考文档：

• MySQL 8.0窗口函数介绍（一） 
• MySQL8.0概述及新特性
• MySQL 8.0 新特性梳理汇总 

By:xSky | 技术相关 |