HiveSQL题——array_contains函数

目次
一、原创文章被引用次数
0 问题描述
1 数据准备
2 数据分析
​编辑
3 小结
二、学生退费人数
0 问题描述
1 数据准备
2 数据分析
3 小结

一、原创文章被引用次数

0 问题描述

   求原创文章被引用的次数，注意本题不能用关联的形式求解。
1 数据准备

   id表现文章id,oid表现引用的其他文章id，当oid为0时表现当前文章为原创文章。

1. create table if not exists  table18
2. (
3.     id    int comment '文章id',
4.     oid   int comment '引用的其他文章id'
5. ) comment '文章信息表';
7. insert overwrite table table18 values
8. (1,0),
9. (2,0),
10. (3,1),
11. (4,1),
12. (5,2),
13. (6,0),
14. (7,3);

复制代码

2 数据分析

    标题要求的是原创文章被引用的次数，其中原创文章为oid即是0的文章，即求解文章id为【1,2,6】被引用的次数。常见的思绪是用关联方式求解，详细SQL如下图所示：
思绪一：用左连接 left join 

2. --思路一：用左连接 left join
3. select
4.     t1.id,
5.     count(t2.oid) as cnt
6. from (select * from table18 where oid = 0) t1
7.          left join
8.          (select * from table18 where oid <> 0) t2
9.          on t1.id = t2.oid
10. group by t1.id
11. order by t1.id;

复制代码

 输出效果为：

 题意要求不能使用join等关联形式求解，其实该题本质是存在性计数问题。
思绪二：借助array_contains(array,element) 函数

1. select
2.     new_id,
3.     sum(flag)as cnt
4. from (
5.          select
6.              id,
7.              oid,
8.              contains,
9.              -- 第二步：利用array_contains()函数判断引用的oid是否在原创文章id集合中，ture则记为1，false则记为0
10.              if(array_contains(contains, oid), 1, 0)    flag,
11.              -- 第三步：清洗数据，补充完整的原创文章
12.              if(array_contains(contains, oid), oid, if(oid = 0, id, null)) new_id
13.          from ( -- 第一步：构建原创文章id集合，作为辅助列
14.                   select
15.                       id,
16.                       oid,
17.                       collect_set(if(oid = 0, id, null)) over () contains
18.                   from table18
19.               ) tmp1
20.      ) tmp2
21. where new_id is not null
22. group by new_id;

复制代码

    上述代码解析：通过array_contains(array,column) 函数举行存在性检测，如果array中包含column 则记为1，不存在记为0，关键公式： sum(if(array_contains(array,column),1,0))

上述代码解析：
第一步：构建原创文章id集合contains，将contains作为辅助列。

1.   select
2.         id,
3.         oid,
4.         collect_set(if(oid = 0, id, null)) over () contains
5. from table18;

复制代码

第二步：利用array_contains()函数，判断非原创的oid是否在原创文章id集合中，存在则计数为1，否则计数为0。

1. select
2.         id,
3.         oid,
4.         contains,
5.         if(array_contains(contains, oid), 1, 0) as flag
6. from (
7.          select
8.                id,
9.                oid,
10.                collect_set(if(oid = 0, id, null)) over () contains
11.           from table18
12.       ) tmp1;

复制代码

第三步：清洗数据，对原创文章id补充完整

1. select
2.         id,
3.         oid,
4.         contains,
5.         if(array_contains(contains, oid), 1, 0)   flag,
6.       --清洗数据，对原创文章id补充完整
7.         if(array_contains(contains, oid), oid, if(oid = 0, id, null)) new_id
8. from (
9.           select
10.                id,
11.                oid,
12.                collect_set(if(oid = 0, id, null)) over () contains
13.           from table18
14.        ) tmp1;

复制代码

 

   ps: 此处须要对原创文章id补充完整，否则会丢失记载。详细是：通过array_contains(contains,oid)去判断，代码为 if(array_contains(contains, oid), oid, if(oid = 0, id, null)) as  new_id   -->  代表的意思是 ：如果oid存在于原创文章id构建的集合中，就取得该oid,如果不存在，再判断oid是否为0，如果是0，则取得id,否则记为null。
第四步：将new_id 为null的数据滤掉，并对new_id分组，求出各原创文章被引用的次数sum(flag)as cnt

1. select
2.     new_id,
3.     sum(flag)as cnt
4. from (
5.          select
6.              id,
7.              oid,
8.              contains,
9.              -- 第二步：利用array_contains()函数判断引用的oid是否在原创文章id集合中，ture则记为1，false则记为0
10.              if(array_contains(contains, oid), 1, 0)    flag,
11.              -- 第三步：清洗数据，补充完整的原创文章id
12.              if(array_contains(contains, oid), oid, if(oid = 0, id, null)) new_id
13.          from ( -- 第一步：构建原创文章id集合，作为辅助列
14.                   select
15.                       id,
16.                       oid,
17.                       collect_set(if(oid = 0, id, null)) over () contains
18.                   from table18
19.               ) tmp1
20.      ) tmp2
21.   -- 第四步：将为null的数值过滤掉，并对new_id分组，求出各原创文章被引用的次数sum(flag)as cnt
22. where new_id is not null
23. group by new_id;

复制代码

3 小结

 上述例子中利用array_contains(array,column)举行存在性检测，如果存在则记为1，不存在则记为0，核心计算公式为 sum(if(array_contains(array,value),1,0))
二、学生退费人数

0 问题描述

求截止当前月的学生退费总人数【当月的学生退费人数：上月存在，这月不存在的学生个数】。
1 数据准备

1. create table if not exists test19( dt string comment '日期',
2. stu_id string comment '学生id');
4. insert overwrite table test19
5. values ('2020-01-02','1001'),
6.        ('2020-01-02','1002'),
7.        ('2020-02-02','1001'),
8.        ('2020-02-02','1002'),
9.        ('2020-02-02','1003'),
10.        ('2020-02-02','1004'),
11.        ('2020-03-02','1001'),
12.        ('2020-03-02','1002'),
13.        ('2020-04-02','1005'),
14.        ('2020-05-02','1006');

复制代码

2 数据分析

完整的代码如下：

1. select month,
2.       sum(month_cnt) over(order by month) as result
3. from(
4.     select month,
5.            lag(next_month_cnt,1,0) over(order by month) as month_cnt
6.     from(
7.         select distinct
8.                t0.month as month,
9.                sum(if(!array_contains(t1.lead_stu_id_arr,t0.stu_id),1,0)) over(partition by t0.month) as next_month_cnt
10.         from
11.             (select
12.                   date_format(dt,'yyyy-MM') as month,
13.                   stu_id
14.             from test19) t0
15.         left join
16.         (
17.             select month,
18.                    lead(stu_id_arr,1) over(order by month) as lead_stu_id_arr
19.             from(
20.                  select date_format(dt,'yyyy-MM') as month,
21.                         collect_list(stu_id) as stu_id_arr
22.                  from test19
23.                  group by date_format(dt,'yyyy-MM')
24.                 ) tmp1
25.         ) t1
26.         on t0.month = t1.month
27.     ) tmp2
28. ) tmp3;

复制代码

第一步：聚合每个月的stu_id，利用collect_list()函数（不去重）合并，详细sql如下:

1. select date_format(dt,'yyyy-MM') as month,
2.        collect_list(stu_id) as stu_id_arr
3. from test19
4. group by date_format(dt,'yyyy-MM')

复制代码

计算效果如下：

1. 2020-01        [1001,1002]
2. 2020-02        [1001,1002,1003,1004]
3. 2020-03        [1001,1002]
4. 2020-04        [1005]
5. 2020-05        [1006]

复制代码

第二步：按照月份排序，获取下一月合并之后的值，sql如下：

1. select month,
2.         stu_id_arr,
3.         lead(stu_id_arr,1) over(order by month) as lead_stu_id_arr
4. from(
5.        select
6.                 date_format(dt,'yyyy-MM') as month,
7.                 collect_list(stu_id) as stu_id_arr
8.        from test19
9.        group by date_format(dt,'yyyy-MM')
10.      ) tmp1;

复制代码

计算效果如下：

1. 2020-01        [1001,1002]        [1001,1002,1003,1004]
2. 2020-02        [1001,1002,1003,1004]        [1001,1002]
3. 2020-03        [1001,1002]        [1005]
4. 2020-04        [1005]        [1006]
5. 2020-05        [1006]        NULL

复制代码

     ps:总体思绪是利用数组差集函数求出差值集合后，再利用size()求出详细的个数，末了sum聚合即可。hive中的数组函数array_contains可以实现这个需求，该函数表现在数组中查询某个元素是否存在。在该标题中，借助此函数判断 当月某个学生id是否在下月（数据集合 -->数组）中存在，如果存在就为0，不存在标志为1。
 第三步：利用步调2的效果与原表举行关联，获取当前学生id

1. select
2.     t0.*,
3.     t1.*
4. from (select
5.           date_format(dt, 'yyyy-MM') as month,
6.           stu_id
7.       from test19) t0
8. left join ( select
9.                    month,
10.                    lead(stu_id_arr, 1) over (order by month) as lead_stu_id_arr
11.              from ( select
12.                           date_format(dt, 'yyyy-MM') as month,
13.                           collect_list(stu_id)       as stu_id_arr
14.                   from test19
15.                   group by date_format(dt, 'yyyy-MM')
16.                  ) tmp1
17.              ) t1
18. on t0.month = t1.month;

复制代码

效果如下：

1. 2020-01        1001        2020-01        [1001,1002,1003,1004]
2. 2020-01        1002        2020-01        [1001,1002,1003,1004]
3. 2020-02        1001        2020-02        [1001,1002]
4. 2020-02        1002        2020-02        [1001,1002]
5. 2020-02        1003        2020-02        [1001,1002]
6. 2020-02        1004        2020-02        [1001,1002]
7. 2020-03        1001        2020-03        [1005]
8. 2020-03        1002        2020-03        [1005]
9. 2020-04        1005        2020-04        [1006]
10. 2020-05        1006        2020-05        NULL

复制代码

第四步：利用array_contains()函数判断当月的stu_id是否在下个月array数组中，如果存在标志0，不存在标志1。详细sql如下：

1.         select t0.month,
2.                t0.stu_id,
3.               if(!array_contains(t1.lead_stu_id_arr,t0.stu_id),1,0) as flag
4.         from
5.             (select
6.                    date_format(dt,'yyyy-MM') as month,
7.                    stu_id
8.             from test19) t0
9.         left join
10.         (
11.             select month,
12.                    lead(stu_id_arr,1) over(order by month) as lead_stu_id_arr
13.             from(
14.                  select date_format(dt,'yyyy-MM') as month,
15.                         collect_list(stu_id) as stu_id_arr
16.                  from test19
17.                  group by date_format(dt,'yyyy-MM')
18.                 ) tmp1
19.         ) t1
20.         on t0.month = t1.month

复制代码

效果如下：

1. 2020-01        1001        0
2. 2020-01        1002        0
3. 2020-02        1001        0
4. 2020-02        1002        0
5. 2020-02        1003        1
6. 2020-02        1004        1
7. 2020-03        1001        1
8. 2020-03        1002        1
9. 2020-04        1005        1
10. 2020-05        1006        1

复制代码

第五步：基于步调四的效果，按照月份分组，对flag求和，得到下个月的学生退费人数

1. select  distinct t0.month,
2.        -- 求解下个月的退费人数
3.         sum(if(!array_contains(t1.lead_stu_id_arr,t0.stu_id),1,0)) over(partition by t0.month) as next_month_cnt
4. from  (select
5.              date_format(dt,'yyyy-MM') as month,
6.              stu_id
7.        from test19) t0
8. left join
9.         ( select month,
10.                  lead(stu_id_arr,1) over(order by month) as lead_stu_id_arr
11.           from( select
12.                         date_format(dt,'yyyy-MM') as month,
13.                         collect_list(stu_id) as stu_id_arr
14.                  from test19
15.                  group by date_format(dt,'yyyy-MM')
16.                 ) tmp1
17.         ) t1
18. on t0.month = t1.month;

复制代码

计算效果如下：
注意：第二列求是下个月的退费人数。

1. 2020-01        0
2. 2020-02        2
3. 2020-03        2
4. 2020-04        1

复制代码

第六步：计算当前月的退费人数
    步调五计算的是下一个月的学生退费人数，再利用 lag(next_month_cnt,1,0) over(order by month) 向上偏移一行，就得到当前月的退费人数。

sql代码如下：

1. select month,
2.       --基于下月的退费人数month_cnt字段，向上偏移一行，就得到当前月的退费人数
3.        lag(next_month_cnt,1,0) over(order by month) as month_cnt
4. from(
5.         select distinct t0.month as month,
6.                sum(if(!array_contains(t1.lead_stu_id_arr,t0.stu_id),1,0)) over(partition by t0.month) as next_month_cnt
7.         from
8.             (select
9.                   date_format(dt,'yyyy-MM') as month,
10.                   stu_id
11.             from test19) t0
12.         left join
13.         (
14.             select month,
15.                    lead(stu_id_arr,1) over(order by month) as lead_stu_id_arr
16.             from(
17.                  select date_format(dt,'yyyy-MM') as month,
18.                         collect_list(stu_id) as stu_id_arr
19.                  from test19
20.                  group by date_format(dt,'yyyy-MM')
21.                 ) tmp1
22.         ) t1
23.         on t0.month = t1.month
24.     ) tmp2;

复制代码

计算效果如下：

1. 2020-01        0
2. 2020-02        0
3. 2020-03        2
4. 2020-04        2
5. 2020-05        1

复制代码

计算截止到当前月的退费人数，sql代码如下：

1. select month,
2.        -- sum() over(order by ..) 窗口计算范围：上无边界(起始行)到当前行
3.        sum(month_cnt) over(order by month) as result
4. from(
5.     select month,
6.           lag(next_month_cnt,1,0) over(order by month) as month_cnt
7.     from(
8.         select distinct t0.month as month,
9.                sum(if(!array_contains(t1.lead_stu_id_arr,t0.stu_id),1,0)) over(partition by t0.month) as next_month_cnt
10.         from
11.             (select
12.                   date_format(dt,'yyyy-MM') as month,
13.                   stu_id
14.             from test19) t0
15.         left join
16.         (
17.             select month,
18.                    lead(stu_id_arr,1) over(order by month) as lead_stu_id_arr
19.             from(
20.                  select date_format(dt,'yyyy-MM') as month,
21.                         collect_list(stu_id) as stu_id_arr
22.                  from test19
23.                  group by date_format(dt,'yyyy-MM')
24.                 ) tmp1
25.         ) t1
26.         on t0.month = t1.month
27.     ) tmp2
28. ) tmp3;

复制代码

计算效果为:

1. 2020-01        0
2. 2020-02        0
3. 2020-03        2
4. 2020-04        4
5. 2020-05        5

复制代码

3 小结

   针对存在性问题，一般的求解思绪是：1.利用collect_set()或者 collect_list()函数举行聚合，将数据集转换成数据组。2.再利用array_contains()等函数判断集合（数组）中是否存在某元素，针对效果打上标签。3.再根据标签举行之后的分组聚合计算等。
ps:以上文章参考：
https://blog.csdn.net/godlovedaniel/article/details/119388498?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522167921970316800184142859%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=167921970316800184142859&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~sobaiduend~default-1-119388498-null-null.142^v74^control_1,201^v4^add_ask,239^v2^insert_chatgpt&utm_term=%E5%AD%98%E5%9C%A8%E6%80%A7%E9%97%AE%E9%A2%98&spm=1018.2226.3001.4187文章欣赏阅读741次。本文对存在性问题举行了探究和研究，此类问题通常须要对不同的记载做对比分析，我们可以先将符合条件的数据域按照collect_set（）或collect_list（）函数举行聚合转换成数组，然后获取历史的数据域放入当前行，末了利用hive中数组的相关处理手段举行对比分析。常用的hive数组处理函数如expode()、size()、array()、array_contains()等函数，本题就借助于hive ,array_contains()函数举行存在性问题分析。_sql 求截止当前月退费总人数

https://blog.csdn.net/godlovedaniel/article/details/119388498?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522167921970316800184142859%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=167921970316800184142859&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~sobaiduend~default-1-119388498-null-null.142%5Ev74%5Econtrol_1,201%5Ev4%5Eadd_ask,239%5Ev2%5Einsert_chatgpt&utm_term=%E5%AD%98%E5%9C%A8%E6%80%A7%E9%97%AE%E9%A2%98&spm=1018.2226.3001.4187

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。