精华内容
下载资源
问答
  • SQL 求中位数

    千次阅读 2019-03-15 15:05:34
    存在问题,修改后却无法解决 原文中的中间两个等值问题。 ps:对数据库的学习和认识并不深,如有错误 希望能得到指正,谢谢。 --------------------------------------------------------------------------...

     

    源于对该内容的一些修改https://blog.csdn.net/meaijojo/article/details/8101867

    原文中的方法:

    SELECT * from
    (
    SELECT a1.Name, a1.Sales, COUNT(a2.sales) Sales_Rank
    FROM Total_Sales a1, Total_Sales a2
    WHERE a1.Sales < a2.Sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
    GROUP BY a1.Name, a1.Sales
    ORDER BY a1.Sales DESC, a1.Name DESC
    ) as tab WHERE tab.sales_rank=(select (count(*)+1) div 2 from total_sales);


    --------------------- 
    作者:meaijojo 
    来源:CSDN 
    原文:https://blog.csdn.net/meaijojo/article/details/8101867 
    版权声明:本文为博主原创文章,转载请附上博文链接!

     

    个人对其作出一些修改,适用于更普遍的表:

    SELECT AVG(sales) 
    FROM(
        SELECT a1.Name, a1.Sales, COUNT(a2.sales) Sales_Rank
        FROM Total_Sales a1, Total_Sales a2
        WHERE a1.Sales < a2.Sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
        GROUP BY a1.Name, a1.Sales
        ORDER BY a1.Sales DESC, a1.Name DESC
    ) as tab 
    WHERE tab.sales_rank=(select FLOOR((count(*)+1)/2) from total_sales) 
          or tab.sales_rank=(select CEIL((count(*)+1)/2) from total_sales)

    存在问题,修改后却无法解决 原文中的中间两个数等值问题。

    ps:对数据库的学习和认识并不深,如有错误  希望能得到指正,谢谢。

    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    另有博主有更好的方法,个人认为方法很巧妙!很厉害!

    https://blog.csdn.net/NextAction/article/details/80421854

    select AVG(DISTINCT income)
    from (
    select T1.income from graduates T1,graduates T2
    group by T1.income
    having sum(case when T2.income >= T1.income then 1 else 0 end) >= count(*)/2
    and sum(case when T2.income <= T1.income then 1 else 0 end) >= count(*)/2
    ) tmp;
    

     

    展开全文
  • 利用SQL求中位数(已修复BUG)

    千次阅读 热门讨论 2019-09-18 16:48:36
    引言 看《SQL进阶教程》,看到用 HAVING 子句进行自连接:求中位数 这一节时对于给出的SQL不是很理解。因此花了一些时间分析了...那么如何利用SQL求中位数呢? 将集合的元素按照大小分为上半部分和下半部分两个子集...

    引言

    看《SQL进阶教程》,看到用 HAVING 子句进行自连接:求中位数 这一节时对于给出的SQL不是很理解。因此花了一些时间分析了一下。体会贴在此博文中。

    HAVING 子句进行自连接:求中位数

    中位数是指将集合中的元素按照升序排序后恰好位于正中间的元素。如果元素个数是偶数,则取中间两个元素的平均值作为中位数。

    那么如何利用SQL求中位数呢?
    将集合的元素按照大小分为上半部分和下半部分两个子集,同时让着两个子集共同拥有集合正中间的元素。这样共同部分的元素均值就是中位数:

    在这里插入图片描述

    对应的建表语句如下:

    CREATE TABLE Graduates
    (name   VARCHAR(16) PRIMARY KEY,
     income INTEGER NOT NULL);
    
    INSERT INTO Graduates VALUES('桑普森', 400000);
    INSERT INTO Graduates VALUES('迈克',     30000);
    INSERT INTO Graduates VALUES('怀特',   20000);
    INSERT INTO Graduates VALUES('阿诺德', 20000);
    INSERT INTO Graduates VALUES('史密斯',     20000);
    INSERT INTO Graduates VALUES('劳伦斯',   15000);
    INSERT INTO Graduates VALUES('哈德逊',   15000);
    INSERT INTO Graduates VALUES('肯特',     10000);
    INSERT INTO Graduates VALUES('贝克',   10000);
    INSERT INTO Graduates VALUES('斯科特',   10000);
    
    

    原文给出的求中位数的SQL为:

    SELECT AVG (DISTINCT income)
    FROM (SELECT t1.income FROM Graduates t1,Graduates t2
    	GROUP BY t1.income
    				--s1 的条件
    				HAVING SUM(CASE WHEN t2.income >= t1.income THEN 1 ELSE 0 END)
    										>= COUNT(*) / 2.0
    			  --s2的条件
    				AND SUM(CASE WHEN t2.income <= t1.income THEN 1 ELSE 0 END)
    										>= COUNT(*) / 2.0) TMP;
    

    这条SQL语句要注意的是>= COUNT(*) / 2.0里的等号,这么做的目的是让着两个字节拥有共同部分。如果去掉等号,当元素个数为偶数时,就没有共同的元素了。

    原文给出的解释就是上面这么一点。虽然SQL相对简短,但是很难理解。那么只能拆分开来一步一步分析了。

    注意在postgre中需要写成COUNT(*) / 2.0,感谢每天发芽的豆芽指出。

    为了分析方便,将数据修改一下,并且删掉一些记录。

    INSERT INTO "public"."graduates"("name", "income") VALUES ('桑普森', 40);
    INSERT INTO "public"."graduates"("name", "income") VALUES ('迈克', 3);
    INSERT INTO "public"."graduates"("name", "income") VALUES ('阿诺德', 2);
    INSERT INTO "public"."graduates"("name", "income") VALUES ('史密斯', 2);
    INSERT INTO "public"."graduates"("name", "income") VALUES ('劳伦斯', 1);
    
    

    <图片>

    先看下子查询

    (SELECT t1.income FROM Graduates t1,Graduates t2
    	GROUP BY t1.income
    				--s1 的条件
    				HAVING SUM(CASE WHEN t2.income >= t1.income THEN 1 ELSE 0 END)
    										>= COUNT(*) / 2.0
    			  --s2的条件
    				AND SUM(CASE WHEN t2.income <= t1.income THEN 1 ELSE 0 END)
    										>= COUNT(*) / 2.0)
    

    去掉 GROUP BY语句和HAVING得到一个全连接:

    SELECT t1.income income_t1,t2.income income_t2 FROM Graduates t1,Graduates t2;
    

    该全连接的结果为:

    在这里插入图片描述
    再将求集合s1的条件抽出来查询:

    SELECT t1.income,SUM(CASE WHEN t2.income >= t1.income THEN 1 ELSE 0 END),COUNT(*) FROM Graduates t1,Graduates t2 GROUP BY t1.income
    

    在这里插入图片描述

    那么这个结果是怎算的呢,其实就是一行中的两两比较(income_t2 >= income_t1),就拿t1.income=2来说:

    在这里插入图片描述

    因此对应t1.income=2满足条件(SUM(CASE WHEN t2.income >= t1.income THEN 1 ELSE 0 END) >= COUNT(*) / 2.0)的有8行。而全连接后共有10行。

    满足8 > = (10/2 = 5 )

    将上面的SQL进行一下修改:

    SELECT t1.income,SUM(CASE WHEN t2.income >= t1.income THEN 1 ELSE 0 END),COUNT(*)/2.0 cnt FROM Graduates t1,Graduates t2 GROUP BY t1.income
    

    在这里插入图片描述

    查询结果如上,满足条件的有{1,2}\{1,2\}

    求s2同理:

    SELECT t1.income,SUM(CASE WHEN t2.income <= t1.income THEN 1 ELSE 0 END),COUNT(*)/2.0 cnt FROM Graduates t1,Graduates t2
    GROUP BY t1.income
     --s2的条件
    HAVING SUM(CASE WHEN t2.income <= t1.income THEN 1 ELSE 0 END)
    						>= COUNT(*) / 2.0
    

    在这里插入图片描述

    得到集合{40,3,2}\{40,3,2\}

    注意它们求得的上下半的集合+1个,这里交集就是{2}\{2\}

    取平均数也是22

    SELECT AVG (DISTINCT income)
    FROM (SELECT t1.income FROM Graduates t1,Graduates t2
    								GROUP BY t1.income
    								--s1 的条件
    								HAVING SUM(CASE WHEN t2.income >= t1.income THEN 1 ELSE 0 END)
    														>= COUNT(*) / 2.0
    							  --s2的条件
    								AND SUM(CASE WHEN t2.income <= t1.income THEN 1 ELSE 0 END)
    														>= COUNT(*) / 2.0) TMP;
    

    结果:
    在这里插入图片描述

    展开全文
  • 最近看《SQL进阶教程》,提到一个求中位数的问题,开始很不理解。调整一下示例数据,豁然开朗。 #创建表 CREATE TABLE `Graduates` ( `name` varchar(16) COLLATE utf8mb4_general_ci NOT NULL, `income` int NOT ...

    最近看《SQL进阶教程》,提到一个求中位数的问题,开始很不理解。调整一下示例数据,豁然开朗。

    #创建表
    CREATE TABLE `Graduates` (
      name varchar(16) NOT NULL,
      income int NOT NULL
      );
    
    #数据
    INSERT INTO Graduates (name,income) VALUES 
    ('劳伦斯',1)
    ,('史密斯',2)
    ,('桑普森',5)
    ,('迈克',3)
    ,('阿诺德',4)
    ;
    
    #自联结
    select t1.income, count(*)
    from graduates t1, graduates t2
    group by t1.income
    
    #集合一
    select t1.income, count(*)
    from graduates t1, graduates t2
    group by t1.income
    HAVING sum(case when t2.income >= t1.income then 1 else 0 end) >= count(*) /2 
    
    #集合二
    select t1.income, count(*)
    from graduates t1, graduates t2
    group by t1.income
    HAVING sum(case when t2.income <= t1.income then 1 else 0 end) >= count(*) /2 
    

    重点要理解自连接后是一个笛卡尔积,按income分组后,每个组都是一个集合!
    仔细看一下:

    select t1.income, count(*), 
    	sum(case when t1.income >= t2.income then 1 else 0 end ) gt1
    from graduates t1, graduates t2
    group by t1.income
    order by 1
    

    在这里插入图片描述

    income = 1 的分组,有5个元素,这个5个元素中,小于 1 的

    展开全文
  • SQL求中位数(牛客)

    2021-04-13 11:47:14
    TM小哥和FH小妹在牛客大学若干年后成立了牛客SQL班,班的每个人的综合成绩用A,B,C,D,E表示,90分以上都是A,80~90分都是B,60~70分为C,50~60为D,E为50分以下 因为每个名次最多1个人,比如有2个A,那么必定有1个A...

    题目描述

    TM小哥和FH小妹在牛客大学若干年后成立了牛客SQL班,班的每个人的综合成绩用A,B,C,D,E表示,90分以上都是A,80~90分都是B,60~70分为C,50~60为D,E为50分以下

    因为每个名次最多1个人,比如有2个A,那么必定有1个A是第1名,有1个A是第2名(综合成绩同分也会按照某一门的成绩分先后)。

    每次SQL考试完之后,老师会将班级成绩表展示给同学看。

    现在有班级成绩表(class_grade)如下:

    第1行表示成绩为A的学生有2个

    .......

    最后1行表示成绩为D的学生有2个

    老师想知道学生们综合成绩的中位数是什么档位,请你写SQL帮忙查询一下,如果只有1个中位数,输出1个,如果有2个中位数,按grade升序输出,以上例子查询结果如下:

    解析:

    总体学生成绩排序如下:A, A, B, B, B, B, C, C, C, C, D, D,总共12个数,取中间的2个,取6,7为:B,C

    示例1

    输入

    复制

    drop table if exists class_grade;
    CREATE TABLE class_grade (
    grade varchar(32) NOT NULL,
    number int(4) NOT NULL
    );
    
    INSERT INTO class_grade VALUES
    ('A',2),
    ('C',4),
    ('B',4),
    ('D',2);

    输出

    复制

    B
    C
    --参考大佬解法
    select grade
    from
        (select grade,
        (select sum(number) from class_grade) as total,
        sum(number)over(order by grade) a, -- 求正序
        sum(number)over(order by grade desc) b  -- 求逆序
         from class_grade
        order by grade)t
    where a >= total/2 and b >= total/2  -- 正序逆序均大于整个数列数字个数的一半
    order by grade;

     

    展开全文
  • 请你写一个sql语句查询各个岗位分数的中位数位置上的所有grade信息,并且按id升序排序,结果如下: 第1行表示C++岗位的中位数位置上的为用户id为2,分数为10000,在C++岗位里面排名是第2 第2,3行表示Java岗位的中位...
  • SQL求中位数的逻辑并不简单,下面提供比较简单易记的两种思路:一、窗口函数法 —— 简单却巧妙的办法一般来说,如果序列的长度为奇数,中位数则为排序后最中间的一个数(如长度为5的序列,中位数是排序后第3个数)...
  • sql分组求中位数

    2020-09-30 13:42:34
    'C', '2645') insert into Employee (Id, Company, Salary) values ('16', 'C', '2652') insert into Employee (Id, Company, Salary) values ('17', 'C', '65') 请编写SQL查询来查找每个公司的薪水中位数。...
  • SQLSERVER 求中位数

    2020-08-14 15:49:48
    有id和数量两列,每个id的中位数,我写了一个,感觉不是很好似的,也没想起来又啥好写法,贴在这,看看大家有啥好写法没有 --测试数据 if not object_id(N'Tempdb..#T') is null drop table #T Go Create ...
  • SQL求中位数的逻辑并不简单,下面提供比较简单易记的两种思路:一、窗口函数法 —— 简单却巧妙的办法一般来说,如果序列的长度为奇数,中位数则为排序后最中间的一个数(如长度为5的序列,中位数是排序后第3个数)...
  • 文章目录1.员工薪水中位数(分组求中位数)2.给定数字的频率查询中位数3.至少有5名直接下属的经理4....此题,考察不用内置函数,利用SQL求中位数 窗口函数法 1 select Id,Company,Salary from ( select *, row_numbe
  • SQL 中位数求

    千次阅读 2019-04-29 15:16:47
    利用HAVING子句求中位数(收入的中位) select T1.income from graduates T1,graduates T2 group by T1.income having sum(case when T2.income >= T1.income then 1 else 0 end) >= count(*)/2 and sum(case...
  • 因为要做一个报表,需要统计中位数所以找了方法 类似下面的语句 select pid,avg(coadcladcon) coadcladcon from ( SELECT pid,coadcladcon,RowNum,Cnt FROM OrdersRN o WHERE RowNum IN((Cnt + 1) / 2,(Cnt + 2) / 2...
  • 首先用Scala写一个UDAF函数...import org.apache.spark.sql.expressions.MutableAggregationBuffer import org.apache.spark.sql.expressions.UserDefinedAggregateFunction import org.apache.spark.sql.Row import or
  • 实际上,使用 SQL 求中位数并不简单。问题描述我们先来看关于“中位数”的解释:中位数(Median)又称中值,统计学中的专有名词,是按顺序排列的一组数据中居于中间位置的数,代表一个样本、种群或概率分布中的一个...
  • 有些SQL的笔面试题中会出现一些要求众数、均值、中位数、四分位数等,我这里将我会的几种解法分享给大家,大家也可以在评论中继续补充!样例表(一)众数如员工工资的众数1、使用havingSELECT salary,COUNT(*) AS ...
  • SQL字段中的中位数,简单理解,简单方法 如果数据量为奇数,则是第 (n+1)/2 条,偶数则为 n/2 和 (n+2)/2条的和的平均值。SQL如下 select * from ( select * ,row_number() over( order by money ) top...
  • create table test( value int ) insert into test select 3 union all select 4 union all select 5 union all select 6 出峰度 偏度 和中位数 谢谢大神!

空空如也

空空如也

1 2 3 4 5 ... 12
收藏数 240
精华内容 96
关键字:

sql求中位数