分享、总结

🌿挑战100天不停更，刷爆 hive sql🧲详情请点击🔗我的专栏🖲,共同学习,一起进步~NUM: 第35天 - 数字频率的中位数我们昨天求的是薪水的中位数,今天再补充一种求中位数的方式,昨天可以通过三种方式,具体可以参考,昨天的链接: 挑战100天不停更hive sql第34天 - 薪水求中位数先看看表字段:number :数字frequency :数字的频率(注意:这里是频率,并不是数字, 所以不能根据这个字段排序)问题:求得number的中位数(需要通过frequency 字段

🌿挑战100天不停更，刷爆 hive sql🧲详情请点击🔗我的专栏🖲,共同学习,一起进步~文章目录🌿挑战100天不停更，刷爆 hive sql🧲NUM: 第34天 - 薪水求中位数🧨那就不废话了, 开始今天的刷题~~🧨🎈表结构🎉建表并插入数据👓问题:查询每个公司的薪水中位数查询结果🧨SQL关于sql的规范问题NUM: 第34天 - 薪水求中位数今天的sql是求中位数, 当然,我们第一眼看到这个问题的时候我们首先想到的是开窗函数row_number()根据公司名称分区,再将薪水排序,那么中位数怎么.

🌿挑战100天不停更，刷爆 hive sql🧲详情请点击🔗我的专栏🖲,共同学习,一起进步~NUM: 第33天 -游戏玩家-首日连续登陆占比我们之前写过关于访问的统计,活跃用户的统计,以及连续出现的值等先把链接贴出来挑战100天不停更第30天 -求连续出现的值 (lead()求的偏移量,再join即可)挑战100天不停更hive sql第27天 -活跃用户统计(row_number()打标求差值,然后分组)挑战100天不停更，刷爆 hive sql第26天 - 场景题-访问统计(累计访问 .

🌿挑战100天不停更，刷爆 hive sql🧲详情请点击🔗我的专栏🖲,共同学习,一起进步~NUM: 第32天 -行程和用戶：查询乘客取消率(包含数值运算函数总结和in 、exits、 left join的效率对比)今天的sql是通过两张表,用户表,行程表, 计算行程的取消率, 注意:这里的取消率包含乘客和司机的取消率所用到的函数包括: sum() round() if()等今天主要复习两个问题:(由于内容比较多, 我放在文章后面~)一 关于数值的函数(25个)二 使用in() exit.

🌿挑战100天不停更，刷爆 hive sql🧲详情请点击🔗我的专栏🖲,共同学习,一起进步~NUM: 第31天 -查找比前一天温度高的所有日期的ID查找前一天的数据一般都需要用到开窗函数lag(),取窗口的前一天数据,对开窗函数lag()不清楚的可以参考之前写过的sql,还有些取值相关的挑战100天不停更hive sql第20天时间序列-补全数据(取值相关函数有详细解释)这里再详细说一下关于lag()函数的传参问题lag(col,n,DEFAULT):用于统计窗口内往上第n行值默认的DEFA.

🌿挑战100天不停更，刷爆 hive sql🧲详情请点击🔗我的专栏🖲,共同学习,一起进步~NUM: 第30天 -求连续出现的值最近一直在找题, 要么题重复了(连续登陆天数,累计求值什么的,这些都比较常见的), 都是以前总结的, 要么就是要收费的,这里给大家推荐一个网站,里面的题还是不错的, 但是需要会员才可以看答案,需要进阶的可以看下http://sqlintern.com/,大家有好的学习网站或者场景题也可以在下面留言哈~ 感谢~我自己思来想去, 觉得基础还是很重要的, 更要学会举一反.

🌿挑战100天不停更，刷爆 hive sql🧲详情请点击🔗我的专栏🖲,共同学习,一起进步~NUM: 第29天 -统计充值日志-使用with as今天的日志统计要用到子查询 , 在实际工作中, 为了提高sql的课阅读性, 和运行效率, 我们经常会使用with as来将一个子查询引用,今天着重介绍下 with as的使用方法** ****什么是with as ?**公用表表达式（CTE）是从WITH子句中指定的简单查询派生的临时结果集（会把查询的表数据放到内存中，供其他查询随时使用）**怎么使用呢?.

🌿挑战100天不停更，刷爆 hive sql🧲详情请点击🔗我的专栏🖲,共同学习,一起进步~NUM: 第28天 -图书馆需求现有图书管理数据库的三个数据模型如下：图书（数据表名：BOOK）读者（数据表名：READER)借阅记录（数据表名：BORROW LOG）需求:（1） 创建图书管理库的图书、读者和借阅三个基本表的表结构。请写出建表语句。（2） 找出姓李的读者姓名（NAME）和所在单位（COMPANY）。（3） 查找“高等教育出版社”的所有图书名称（BOOK_NAME）及单价（P.

🌿挑战100天不停更，刷爆 hive sql🧲详情请点击🔗我的专栏🖲,共同学习,一起进步~NUM: 第27天 -活跃用户统计今天是关于活跃用户的统计问题, 所有用户及连续登陆两天的活跃用户,所有用户的活跃度比较简单,那连续两天活跃的用户要怎么求呢 ?这道题的核心在于: 日期减去行号 -->在分组-->判断是否>=2,那么就是活跃的用户,这个思想特别重要,之前写过的还有关于求绝对值去算最接近的, 这个: 挑战100天不停更hive sql第23天 - 非等值连接-最近匹配,大.

🌿挑战100天不停更，刷爆 hive sql🧲详情请点击🔗我的专栏🖲,共同学习,一起进步~NUM: 第26天 -访问统计先说几个关键函数date_format()regexp_replace()sum() over()如果对这几个函数都比较熟悉的话,下面内容可以不用看了,嵌套个子查询就能查出来如果考虑到访问记录的去重问题:,可以参考另外一篇:挑战100天不停更hive sql第24天 -累计去重🧨刷题~~🧨🎈表结构userid:用户visitdate:日期visitcou.

🌿挑战100天不停更，刷爆 hive sql🧲详情请点击🔗我的专栏🖲,共同学习,一起进步~NUM: 第25天 -场景题一 学生课程最近两天天下班一直都在整理这套场景题,一共是50题,因为后面的比较简单,二十八后面的我就省略了(最后面我把问题贴出来), 表结构和数据我都写好了,可以直接复制, 由于篇幅比较长,本来我是想在前面贴导航的,但是无奈我是`markdown`,所以大家只能看边上的导航去筛选了先简单说一下吧:一共四张表, 学生 -成绩- 课程 - 老师 ,主要是用一些join,分组,排序.

🌿挑战100天不停更，刷爆 hive sql🧲详情请点击🔗我的专栏🖲,共同学习,一起进步~NUM: 第24天 -累计去重去重? 我们一般会用到哪几个函数呢?dinstinct ``group by ``row_number()等等…那具体有什么区别呢?在实际的场景中又怎么选择呢??在数据量特别大的时候,又该如何调优呢 ?先刷题 , sql后面会细细来总结~🧨刷题~~🧨🎈表结构_1, __t24_为事件流水表，客户当天有一条记录则视为当天活跃🎉建表create .

🌿挑战100天不停更，刷爆 hive sql🧲详情请点击🔗我的专栏🖲,共同学习,一起进步~NUM: 第23天 - 非等值连接-最近匹配先说几个关键的函数,同学可以先思考下~rank() ?abs() ?思考五分钟~ 然后再接着往下看吧, 或许你已经有一个大概的思路了!!最近有个小小的总结: 看sql 不如写sql,当你面对一个自己完全没有思路,然后又用尽几乎所有办法时,才会发现每一个函数都有他的妙用之处! 才能真正的学会一个函数,也许sql看似简单, 但是,当你写sql的时候才会真正关.

🌿挑战100天不停更，刷爆 hive sql🧲详情请点击🔗我的专栏🖲,共同学习,一起进步~NUM: 第22天 - 非等值连接-范围匹配表 f 是事实表，表 d 是匹配表，在 hive 中如何将匹配表中的值关联到事实表中？ 表 d 相当于拉链过的变化维，但日期范围可能是不全的。那我们要怎么匹配呢??🧨不废话，刷题~~🧨🎈表结构f:d:🎉建表-- 建表并插入数据-- 事实表create table f( date_id string, p_id st.

🌿挑战100天不停更，刷爆 hive sql🧲详情请点击🔗我的专栏🖲,共同学习,一起进步~NUM: 第21天 - 时间序列-取最新完成状态的前一个状态当我们看到这道题目的时候首先会想到取最新的时间,然后用lead()开窗函数向下取一行,也许还有不同的解法,虽然sql并不长,但是我觉的有点绕,自己也写了很久,这道题也是非常考察对开窗取值函数的理解,特别是开窗中嵌套开窗, 就会有点绕,思路会断开🧨不废话，刷题~~🧨🎈表结构B为完整状态,A为未完成状态🎉建表-- 建表并插入数据cr.

🌿挑战100天不停更，刷爆 hive sql🧲详情请点击🔗我的专栏🖲,共同学习,一起进步~NUM: 第20天 - 时间序列-补全数据怎么理解用最新的值来进行填充呢?打个比方, 有一张账单表,2017年是收入100块钱,2018年没收入,2019年收入200块钱,那么2018年的收入就为null,但其实真实的业务可能会出现2018年的收入其实和2017年收入一样,但是默认没有入库,那么我们需要手动去填充(类似的补全操作都是一样的)那具体怎么填充呢?下面的实现通过 开窗函数count()+fi.

🌿挑战100天不停更，刷爆 hive sql🧲详情请点击🔗我的专栏🖲,共同学习,一起进步~NUM: 第19天 -求最新值实际工作中,随着时间的不断推移,有些字段都在不断的变化和更新,不同的字段有不同的变化,并且时间不连续,那我们具体怎么求呢?请往下看🔽🧨不废话，刷题~~🧨🎈表结构🎉建表create table t19( date_id string, a string, b string, c string.

🌿挑战100天不停更，刷爆 hive sql🧲NUM: 第18天 -构造累计时间为什么会构造累计时间呢? 因为有时候表的时间字段并不是连续的,又要根据时间对其他字段进行操作,这个时候就需要手动构造时间来解决问题,思路也很简单,用炸裂函数求出连续时间,并和原来的表进行关联后,再进行开窗即可🧨不废话，刷题~~🧨🎈表结构🎉建表create table t18( a string, b string, c int);insert into t18 (a, b.

🌿挑战100天不停更，刷爆 hive sql🧲NUM: 第17天 -时间进行累计时间的问题需要对时间函数掌握的很全面，并且对时间的获取和转换流程很清楚，才能游刃有余解决问题，可以根据总的需求一步步的向下推，不断的寻求每个时间的解法🧨不废话，刷题~~🧨🎈表结构🎉建表-- 时间序列,构造累计时间--建表并插入数据create table t17( date_id string);insert into t17 (date_id)values ('2017-08-0.

🌿挑战100天不停更，刷爆 hive sql🧲NUM: 第16天 -构造时间马上12点了，抓住今天的小尾巴，把今天的sql更新了，今天突然通知居家办公，早上就去公司搬电脑，耽误了不少时间，但是该做的事情还是要做，把握好每一天，今天的sql主要是以时间为主，下面将每条sql进行拆解，生僻的时间函数都添加了注释和详解，希望能帮助看博客的人更好的理解~~🧨不废话，刷题~~🧨🎈先给出要求得的表结构-- 创建日期表create table if not exists dim_date( .

🌿挑战100天不停更，刷爆 hive sql🧲NUM: 第15天 - 时间序列,进度及剩余🧨不废话，刷题~~🧨🎈表结构🎉建表create table t15( date_id string, is_work string);insert into t15 (date_id, is_work)values ('2017-07-30', '0'), ('2017-07-31', '1'), ('2017-08-01', '1'), .

🌿挑战100天不停更，刷爆 hive sql🧲NUM: 第14天 -时效问题🧨不废话，刷题~~🧨🎈表结构工作日：周一至周五 09:30-18:30日期表：客户申请表：🎉建表-- 日期表create table d_date( date_id string, is_work string);-- 客户申请表：t14create table t14( a string, b string, c string);insert in.

🎈表结构🎉建表create table t13( a string, b string);insert into t13values ('2014', '1'), ('2015', '1'), ('2016', '1'), ('2017', '0'), ('2018', '0'), ('2019', '-1'), ('2020', '-1'), ('2021', '-1'),.

🎈表结构🎉建表create table t12( a string, b string, c string);insert into t12values ('001', 'A/B', '1/3/5'), ('002', 'B/C/D', '4/5');👓将b和c拆分开并转多行👙输出结果🎨思路需要将两列拆分开，b、c都是多个数字，那么要先用split切分为数组通过炸裂函数explode()将b、c一行转为多行，因为是两个字段，需要.

表结构建表create table t11( a string, b string);insert into t11values ('A/B', '1/3'), ('B/C/D', '4/5/2');1）成对提取数字并一一对应思路lateral view posexplode()展开多列输出结果SQLselect a_val, b_valfrom (select a, .

表结构建表create table t10( a string);insert into t10values ('AB,CA,BAD'), ('BD,EA');1）反转逗号分隔的数据：改变顺序，内容不变思路reverse()输出结果SQLselect a, concat_ws(',',collect_list(reverse(str)))from(select a, strfrom t10 la.

表结构建表create table t7( a string, b string);insert into t7values ('2014', 'A'), ('2014', 'B'), ('2015', 'B'), ('2015', 'D');1）合并思路concat_ws()SQL-- 合并select a, concat_ws(',', collect_set(b))from t7group.

表结构建表create table t9( a string, b string, c string, d string);insert into t9values ('2014', '2016', '2014', 'A'), ('2014', '2015', '2015', 'B');1）不使用 distinct 或 group by 去重输出结果思路– 1,先将多个显示年份的列转为1列，用_union all_– 2,使用.

表结构建表create table t8( a string);insert into t8values ('1011'), ('0101');1）取出字符串中’1’的位置输出结果思路0100字符串首先要切割，获取字符串的数组炸裂函数posexplode()可以取出当前索引，然后+1再用concat_ws()拼接到一起SQLselect a, concat_ws(',', collect_list(cast(index as str

表结构1）数据的扩充（a字段不变，b字段根据a字段递减）思路通过split(space(a的最大值), ' ')，计算出对应的数组将数组通过侧视图lateral view + explode炸裂函数一行转为多列通过开窗函数row_number()取出行号将原表和行号进行左外关联，条件是1=1，取出所有字段的值，并判断a字段的值大于且等于行号，就可以求出a的递减了，因为行号是从1开始的根据a分组 ，collect_set收集行号并用 concat_ws()拼接即可SQLSELE.

执行结果方法一思路根据space()获取字符串的长度，space(int i) 返回i长度的字符串，再通过split()根据字符串切割为想要的数组通过lateral view侧视图+posexplode()将行转为列，再取出对应的索引值+1即可问题：lateral view侧视图?(参考)Lateral View和**UDTF**类功能函数一起使用，表中的每一行和UDTF函数输出的每一行进行连接，生成一张新的虚拟表，可以对UDTF产生的记录设置字段名称，新加的字段可以使用在so.

表结构建表INSERT into t4 values('2014', 'A', 3),('2014' ,'B' ,1 ),('2014' ,'C' ,2 ),('2015' ,'A' ,4),('2015', 'D' ,3);SELECT * FROM t4;1）按a分组按照b字段排序，求c的前一行后后一行的和思路LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值 第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时.

表结构建表CREATE table t3 like t1;INSERT into t3 values('2014', 'A', 3),('2014' ,'B' ,1 ),('2014' ,'C' ,2 ),('2015' ,'A' ,4),('2015', 'D' ,3);SELECT * FROM t3;1）按a分组，对b排序，对c进行累加思路sum(c) 开窗 partitiona 然后orderby bSQLSELECT a, b, c, .

建表-- 表名:t2CREATE TABLE t2(a STRING,b string,c INT);-- 表结构INSERT INTO t2 values('2014','A',3),('2014','B',1),('2014','C',2),('2014','A',4),('2015','D',3);DESCRIBE t2;![image.png](https://img-blog.csdnimg.cn/img_convert/89be8af476b253ad963551fe892c3.

– # a – 年份 b – 部门 c – 绩效得分-- 建表CREATE TABLE t1 ( a string,b string,c DOUBLE);INSERT INTO t1 VALUES ('2014','B',9), ('2015','A',8), ('2014','A',10), ('2015','B',7);1,多行转多列思路：年份分组，取出不同部门的绩效 年份（分组） - 部门A - 部门BSELECT a, max(CASE .