hive 和 spark-sql 如何写SQL求出中位数平均数和众数（count 之外的方法)

1、概念介绍

       平均数、中位数、众数都是度量一组数据集中趋势的统计量。所谓集中趋势是指一组数据向某一中心值靠拢的倾向，测度集中趋势就是寻找数据一般水平的代表值或中心值。而这三个特征数又各有特点，能够从不同的角度提供信息。

平均数

特点：计算用到所有的数据，它能够充分利用数据提供的信息，它具有优秀的数学性质，因此在实际应用中较为广泛。但它受极端值的影响较大。
应用场合：没有极端值的情况下数据集中趋势的刻画。
如：小明五次测试的成绩为87、88、89、93、94你认为小明这五次测试成绩怎样？
分析：

中位数

特点：中位数是一组数据中间位置的代表值。计算简单，不受极端值的影响，但不能充分利用每个数据所提供的信息。
应用场合：有极端值，且无某数据重复出现多次的情况下集中趋势的刻画。
如：某公司员工月工资如下：
这个公司员工的月工资有一般水平是多少？
员工         经理 副经理 员工a  员工b  员工c   员工d   员工e   员工f    杂工
月工资/元 6000 4000   1700  1300   1200    1100    1100    1100     500
分析：这组数据的平均数是2000,而高于这一水平的只有2人,不具有代表性。其中位数是1200，处于其相当水平的有5人，占大多数。较好的反映了一般水平。

众数

特点：众数是一组数据中出现次数最多的数据。不受极端值的影响，当一组数据中某些数据多次重复出现时，众数往往是人们最关心的一个量。但它不能象平均数那样充分利用数据提供信息。
应用场合：有极端值，有某些数据多次重复出现时。
如：一家鞋店在一段时间内销售了某种女鞋30双，各种尺码鞋的销量如下：
你能为这家鞋店提供进货建议吗？
尺码/厘米   22   22.5   23    23.5    24    24.5    25
销售量/双    1     1        2       5      15      5        1
分析：由于进货最关心的是哪种尺码的鞋最多，而这里很明显24码的要占相当大的量15双。较好的得到所需信息。

2、示例demo

2.1、需求描述

一道SQL题：如何SQL求出中位数、平均数和众数（count 之外的方法）

2.2、创建样例数据

Spark context Web UI available at http://xxxx.hadooppdt.srv:4040
Spark context available as 'sc' (master = local[*], app id = local-1637718802299).
Spark session available as 'spark'.
Welcome to
      ____              __
     / __/__  ___ _____/ /__
    _\ \/ _ \/ _ `/ __/  '_/
   /___/ .__/\_,_/_/ /_/\_\   version 2.3.4-mdh1.0.0-SNAPSHOT
      /_/
         
Using Scala version 2.11.8 (OpenJDK 64-Bit Server VM, Java 1.8.0_202)
Type in expressions to have them evaluated.
Type :help for more information.

scala>spark.sql("drop  table test.test_avg_medium_freq")


scala>spark.sql("CREATE TABLE if not exists test.test_avg_medium_freq(name string,income int)")


scala> spark.sql("INSERT into test.test_avg_medium_freq VALUES ('桑普森', '400000'),('迈克', '30000'),('怀特', '20000'),('阿诺德', '20000'),('史密斯', '20000'),('劳伦斯', '15000'),('哈德逊', '15000'),('肯特', '10000'),('贝克', '10000'),('斯科特', '10000')")


scala> spark.sql("select * from test.test_avg_medium_freq").show()
+----+------+                                                                   
|name|income|
+----+------+
| 桑普森|400000|
|  迈克| 30000|
|  怀特| 20000|
| 阿诺德| 20000|
| 史密斯| 20000|
| 劳伦斯| 15000|
| 哈德逊| 15000|
|  肯特| 10000|
|  贝克| 10000|
| 斯科特| 10000|
+----+------+

2.3、求均值(人均薪资)

#1.avg() 因为存在一个员工，多条薪资记录的情况，所以需要先分组统计

spark.sql(""" SELECT AVG(a.income) FROM (
           SELECT SUM(income) AS income FROM test.test_avg_medium_freq
           GROUP BY name
           ) AS a """).show()

+-----------+                                                                   
|avg(income)|
+-----------+
|    55000.0|
+-----------+


# 2.sum/人数
spark.sql(""" SELECT SUM(income)/COUNT(DISTINCT name) AS avg_income
FROM test.test_avg_medium_freq """).show()

+-----------+                                                                   
|avg(income)|
+-----------+
|    55000.0|
+-----------+

2.4、求中位数

A.用笛卡尔积，然后判断哪条元素位于中间位置，取中间位置元素均值

spark.sql("set spark.sql.crossJoin.enabled=true")

spark.sql(""" select avg(tmp.income) from (  --如果是奇数直接取，若是偶数取平均
--先做笛卡尔积，计算每条数据对应的上半部分(大于该条)、下半部分(小于该条)两个子集，求其交集(即中间位置元素)，这时若聚合数据的数目是奇数，最后得一条，偶数得两条）
             select t1.income    
             from test.test_avg_medium_freq t1,test.test_avg_medium_freq t2
             group by t1.income
             having sum(case when t1.income>=t2.income then 1 else 0 end)>=count(*)/2
                and sum(case when t2.income>=t1.income then 1 else 0 end)>=count(*)/2
           ) as tmp
           """).show()


+-----------+                                                                   
|avg(income)|
+-----------+
|    17500.0|
+-----------+

B.用percentage函数

参考：https://www.jianshu.com/p/57129421ee85

参考：https://blog.csdn.net/qq_33637730/article/details/109066665

在hive环境中，可以使用percentile(BIGINT col, p)来查找中位数，但该函数中的列只能使用整型，我们也可以使用percentile_approx()来近似中位数

percentile_approx还有一种形式percentile_approx(col, p，B)，参数B控制内存消耗的近似精度，B越大，结果的精度越高。默认值为10000。当col字段中的distinct值的个数小于B时，结果就为准确的百分位数  

spark.sql(""" select percentile(income,0.5) from test.test_avg_medium_freq""").show()
+------------------------------------------+                                    
|percentile(income, CAST(0.5 AS DOUBLE), 1)|
+------------------------------------------+
|                                   17500.0|
+------------------------------------------+

C.用row_numberA

spark.sql(""" select avg(income) from ( 
             select income,
             row_number() over( order by income ) num,
             count(*) over( ) cnt
             from test.test_avg_medium_freq 
            ) as tmp
            --如果是奇数，取排序中间的，如果是偶数，取两个中间的均值
         where if(cnt%2=0,num in(cnt/2,cnt/2+1),num=(cnt+1)/2)""").show()

          --数据量非常大时，这里或许可以直接使用num=ceil(cnt/2)
+-----------+
|avg(income)|
+-----------+
|    17500.0|
+-----------+

2.5、求众数

参考：https://www.cnblogs.com/tgzhu/p/9946628.html

 用having +count(max)
#HIve没有all，any的用法,只能用max来定位出现次数最多的了
#https://issues.apache.org/jira/browse/HIVE-15229
#1. 'LIKE ANY' operator return true if a text(column value) matches to any pattern.
#2. 'LIKE ALL' operator return true if a text(column value) matches to all patterns.
#3. 'LIKE ANY' and 'LIKE ALL' returns NULL not only if the expression on the left hand side is NULL, but also if one of the pattern in the list is NULL.

spark.sql(""" SELECT income from test.test_avg_medium_freq 
          group by income
          having count(*)>=(select max(num) from (select count(*) as num from test.test_avg_medium_freq group by income)) """).show()


+------+
|income|
+------+
| 10000|
| 20000|
+------+