常用统计分析

基础统计

-- 示例表：
drop table if exists test.test;
create table IF NOT EXISTS test.test(
    num int,
    text string
);
insert into test.test values
(0,null),
(1,'一'),
(2,'二'),
(3,'三'),
(3,'三'),
(3,'三'),
(4,'五'),
(4,'四'),
(5,'四'),
(5,'五');

  • 计数

    • count(*)

      • 返回检索到的总行数，包括 NULL

      • select count(*) from test.test;

      • 063

    • count(expr)

    • 返回提供的表达式 “非 NULL” 的行数

      • expr 如果为 “非 NULL” 的常量，等同于 count(*)
      • expr 如果是列名，返回该列 “非 NULL” 的行数
    • select count(NULL), count(0), count(1), count(num), count(text) from test.test;

    • 064

    • count(DISTINCT expr[, expr...])

    • 同上，但返回的是去重后的行数

    • select count(DISTINCT NULL), count(DISTINCT 0), count(DISTINCT 1) from test.test;
select count(DISTINCT num), count(DISTINCT text), count(DISTINCT num, text) from test.test;

    • DISTINCT 操作可通过 hive.optimize.distinct.rewrite 参数优化，参考

    • 065

  • 求和

    • sum(col)

      • 返回该列各行的和

    • sum(DISTINCT col)

      • 返回该列各行去重后的和
    • select sum(num), sum(DISTINCT num) from test.test;

    • 066

  • 平均数

    • avg(col)

      • 返回该列各行的平均数

    • avg(DISTINCT col)

      • 返回该列各行去重后的平均数
    • select avg(num), avg(DISTINCT num) from test.test;

    • 067

  • 最大、最小值

    • min(col)

      • 返回该列的最小值

    • max(col)

      • 返回该列的最大值
    • select min(num), max(num) from test.test;

    • 068

百分位数

-- 示例表：
drop table if exists test.test;
create table IF NOT EXISTS test.test(
    math_score int,
    en_score int,
    zh_score int,
    art_score float,
    name string
);
insert into test.test values
(0, 10, NULL, 55.55, '刘一'),
(20, 20, 20, 99.99, '陈二'),
(30, 30, 30, 66.66, '张三'),
(40, 40, 40, 88.88, '李四'),
(50, 50, 50, 90.05, '王五'),
(60, 60, 60, 77.77, '赵六'),
(70, 70, 70, 33.33, '孙七'),
(80, 80, 80, 66.66, '周八'),
(90, 90, 90, 70.52, '吴九'),
(100, 100, 100, 98.76, '郑十');

  • 百分位数，参考1，参考2

    • 069
    • 例如：
      • percentile(score, 0.5) ，P50 （中位数）表示 50% 的学生成绩大于等于这个分数，同时有 50% 的学生成绩小于等于这个分数
      • percentile(score, 1) ，表示 50% 最高分
      • percentile(score, 0) ，表示 50% 最低分

  • percentile(BIGINT col, p)

    • 从一个列中获取指定的百分位数

    • 半函数只能用于整数列，浮点数请使用下面的 percentile_approx 函数

    • p 必须介于 [0, 1] 之间

    • 不会把 NULL 纳入统计

    • select
    percentile(zh_score, 0),
    percentile(math_score, 0.5),
    percentile(en_score, 0.5),
    percentile(zh_score, 0.5)
from test.test;

    • 070

  • percentile(BIGINT col, array(p1 [, p2]...))

    • 同上，一次性获取多个百分位数

    • select
    percentile(math_score, array(0, 0.5, 1)),
    percentile(en_score, array(0, 0.5, 1)),
    percentile(zh_score, array(0, 0.5, 1))
from test.test;

    • 071

  • percentile_approx(DOUBLE col, p [, B]) 或 percentile_approx(DOUBLE col, array(p1 [, p2]...) [, B])

    • 同上

      • 区别 1 ：这个函数取的是近似结果，不是精确的结果

        • select
    percentile(en_score, 0.5),
    percentile_approx(en_score, 0.50),
    percentile_approx(en_score, 0.50, 5),
    percentile_approx(en_score, 0.50, 100000000)
from test.test;

        • 支持使用 B 参数来提高精度（以更大的内存为代价）：精度越高产生的近似值误差越小。缺省为10000。当 col 字段中的 distinct 值的个数小于 B 时，结果为准确的百分位数

        • 从上面的示例能看到：减少 B 值，会使结果偏离准确结果；但存在不明原因导致在 B 足够大时，得到的结果仍然是 50，而准确的应该是 55

        • 072

      • 区别 2 ：支持浮点数列

      • 区别 3 ：取值范围是：(0, 1) ，不能取 0.0 或 1.0

    • 参考 1 ，参考 2 ，参考 3

    • select
    percentile_approx(math_score, 0.5),
    percentile_approx(en_score, array(0.01, 0.5, 0.99)),
    percentile_approx(zh_score, array(0.01, 0.5, 0.99), 10),
    percentile_approx(art_score, array(0.01, 0.5, 0.99), 100000000)
from test.test;

    • 073

分组函数

  • ntile(INTEGER x)

    • 关于名称

      • 也称为 “分块函数” ，参考：Hive 分箱

      • 官方文档将其描述为

        • Divides an ordered partition into x groups called buckets and assigns a bucket number to each row in the partition.

        • 但这容易跟 “分桶表” 混淆

      • 所以，且称为 “分组函数”

    • 将数值列依序分为指定的 “x” 个组（等距分组后，按照数值所在的组进行划分，各组成员数不均匀），并为每个组分配一个组号。

    • 也可以用来获取三分位数、四分位数、十分位数、百分位数和其他常见的汇总统计数据。

    • select
    math_score,
    ntile(2) over(order by math_score) as group1,
    en_score,
    ntile(4) over(order by en_score) as group2,
    zh_score,
    ntile(5) over(order by zh_score) as group3,
    art_score,
    ntile(5) over(order by art_score) as group3
from test.test;

    • 075

直方图

  • histogram_numeric(col, b)

    • 将数值列依序分为指定的 “b” 个组（bins）（等距分组后，按照数值所在的组进行划分，各组成员数不均匀）

    • 并返回一个大小为 b 的双值 (x,y) 坐标数组，可以用来绘制直方图

      • x 的取值有点疑问

        • 官方文档说是：bin centers（大体是直方图代表的数值的中心的意思）

        • 但实测有时候并不是

        • 参考 1，参考 2，参考 3

        • select
    en_score,
    ntile(5) over(order by en_score),
    percentile(en_score, 0.5) over(partition by ntile(5) over(order by en_score)),
    avg(en_score) over(partition by ntile(5) over(order by en_score))
from test.test;
select histogram_numeric(en_score, 5) from test.test;

        • 076

      • y 的取值相当于该组成员数

    • 使用场景

      1. 直接使用，可以用来估计数值列在各个数值区段的分布频率

      2. 配合 case... when... 使用，使 x 的取值变得有意义，输出直观的直方图：

        • with tmp as(
    select
        art_score,
        case
            when art_score >= 0 and art_score < 60 then 0
            when art_score >= 60 and art_score < 70 then 1
            when art_score >= 70 and art_score < 80 then 2
            when art_score >= 80 and art_score < 90 then 3
            when art_score >= 90 and art_score <= 100 then 4
            else -1
        end as lv
    from test.test
)
select histogram_numeric(lv, 5) from tmp;

        • 077

        • 078