97，【SQL递归后求中位数】SQL训练之，力扣，571. 给定数字的频率查询中位数

最新推荐文章于 2024-10-05 00:24:42 发布

凡梦_leo

最新推荐文章于 2024-10-05 00:24:42 发布

阅读量681

点赞数 12

分类专栏： SQL实战训练文章标签： sql leetcode 数据库 python pandas 算法 linux

本文链接：https://blog.csdn.net/qq_55006020/article/details/142501940

版权

SQL实战训练专栏收录该内容

120 篇文章 0 订阅

订阅专栏

学习：知识的初次邂逅
复习：知识的温故知新
练习：知识的实践应用

特殊的中位数解法

一，原题力扣链接

. - 力扣（LeetCode）

二，题干

Numbers 表：

+-------------+------+
| Column Name | Type |
+-------------+------+
| num         | int  |
| frequency   | int  |
+-------------+------+
num 是这张表的主键(具有唯一值的列)。
这张表的每一行表示某个数字在该数据库中的出现频率。

中位数是将数据样本中半数较高值和半数较低值分隔开的值。

编写解决方案，解压 Numbers 表，报告数据库中所有数字的 中位数 。结果四舍五入至 一位小数 。

返回结果如下例所示。

示例 1：

输入： 
Numbers 表：
+-----+-----------+
| num | frequency |
+-----+-----------+
| 0   | 7         |
| 1   | 1         |
| 2   | 3         |
| 3   | 1         |
+-----+-----------+
输出：
+--------+
| median |
+--------+
| 0.0    |
+--------+
解释：
如果解压这个 Numbers 表，可以得到 [0, 0, 0, 0, 0, 0, 0, 1, 2, 2, 2, 3] ，所以中位数是 (0 + 0) / 2 = 0 。

三，建表语句



Create table If Not Exists Numbers (num int, frequency int);
Truncate table Numbers;
insert into Numbers (num, frequency) values ('0', '7');
insert into Numbers (num, frequency) values ('1', '1');
insert into Numbers (num, frequency) values ('2', '3');
insert into Numbers (num, frequency) values ('3', '1');

四，分析

题解：

表：数值表：

字段：数值，数值出现的频率

求数值的中位数需要按照频率解压该表

第一步 CTE使用递归根据频率最大值求出一个序列

# 第一步递归生成一个低于最大值的序列
with recursive t1 as (
    select 1 as n  #递归的开始
    union
    select n+1    #递归的关系
    from t1
    where n<(select max(frequency) from numbers)  #递归的终止条件
)
    select * from t1;

第二步，建立笛卡尔乘积把num按照频率拉平

# 第一步递归生成一个低于最大值的序列
with recursive t1 as (
    select 1 as n  #递归的开始
    union
    select n+1    #递归的关系
    from t1
    where n<(select max(frequency) from numbers)  #递归的终止条件
)
#     select * from t1;
# 笛卡尔连接 开始拉平我们的频率表
,t2 as (
    select * from numbers n join t1 on t1.n<=n.frequency order by n.num
)
    select * from t2;

第三步，求num的中位数 sql计算中位数的准备工作：先按照数值大小排序，生成一个序列，以及求出一个总数备用

# 第一步递归生成一个低于最大值的序列
with recursive t1 as (
    select 1 as n  #递归的开始
    union
    select n+1    #递归的关系
    from t1
    where n<(select max(frequency) from numbers)  #递归的终止条件
)
#     select * from t1;
# 笛卡尔连接 开始拉平我们的频率表
,t2 as (
    select * from numbers n join t1 on t1.n<=n.frequency order by n.num
)
#     select * from t2;
#开始求中位数 准备2个列 一个时全部的行 一个是排序的列
,t3 as (
select num,
       row_number() over (order by num) rn,
       count(1)over() co
       from t2
)
select * from t3;

最后一步，用行数+1 或者行数+2 分别除2 如果排序在这个里面就是偶数或者奇数的中位数

对最后的结果avg 即可拿到中位数；最后按照要求 round 四舍五入保留1位小数


# 第一步递归生成一个低于最大值的序列
with recursive t1 as (
    select 1 as n  #递归的开始
    union
    select n+1    #递归的关系
    from t1
    where n<(select max(frequency) from numbers)  #递归的终止条件
)
#     select * from t1;
# 笛卡尔连接 开始拉平我们的频率表
,t2 as (
    select * from numbers n join t1 on t1.n<=n.frequency order by n.num
)
#     select * from t2;
#开始求中位数 准备2个列 一个时全部的行 一个是排序的列
,t3 as (
select num,
       row_number() over (order by num) rn,
       count(1)over() co
       from t2
)
# select * from t3;
# 中位数 +1 和+2 来确定偶数和奇数
select
    round(avg(num),1) as median
    from t3 where rn in (floor((co+1)/2),floor((co+2)/2));

五，SQL解答

# 第一步递归生成一个低于最大值的序列
with recursive t1 as (
    select 1 as n  #递归的开始
    union
    select n+1    #递归的关系
    from t1
    where n<(select max(frequency) from numbers)  #递归的终止条件
)
#     select * from t1;
# 笛卡尔连接 开始拉平我们的频率表
,t2 as (
    select * from numbers n join t1 on t1.n<=n.frequency order by n.num
)
#     select * from t2;
#开始求中位数 准备2个列 一个时全部的行 一个是排序的列
,t3 as (
select num,
       row_number() over (order by num) rn,
       count(1)over() co
       from t2
)
# select * from t3;
# 中位数 +1 和+2 来确定偶数和奇数
select
    round(avg(num),1) as median
    from t3 where rn in (floor((co+1)/2),floor((co+2)/2));