sql
文章平均质量分 75
莫叫石榴姐
云淡风轻一轮江月明
展开
-
SQL如何利用Bitmap思想优化array_contains()函数
本文主要阐述了如何利用位图思想优化array_contains()函数的方法,在具体业务中得到了较好的性能提升,当表数据量比较大,且利用array_contains()函数比较多时候,性能提升明显,利用计算机底层位移运算减少了开销。原创 2024-04-30 17:11:25 · 1078 阅读 · 1 评论 -
HiveSQL一天一个小技巧:如何统计当前时间点状态情况【辅助变量+累计变换思路】
本文总结了一种当前时间点状态统计的思路和方法,对于此类问题主要采用构造辅助计数变量及累加变换思路进行求解。常见的场景有:直播同时在线人数、服务器实时并发数、公家车当前时间段人数、某个仓库的货物积压数量,某一段时间内的同时处于服务过程中的最大订单量等原创 2023-04-04 17:14:51 · 2301 阅读 · 3 评论 -
HiveSQL一天一个小技巧:如何用倍数小表优化数据倾斜?
本文给出了一种利用倍数小表优化数据倾斜的一种通用方法,该方法适用场景为数据倾斜时不能用mapjoin的时候,也就是集群资源不足时候,通过该方法能够有效缓解数据倾斜,但不能根除数据倾斜。处理数据倾斜最好的方法还是采用分治思想,利用mapjoin一分为二的处理,倾斜key单独走mapjoin,非倾斜key走reduce join,最终将数据union all起来,采用mapjoin的方法切断了shuffle过程,也就没有数据倾斜这一说,缺点耗资源,前提是集群资源足够条件下,且满足mapjoin的条件。原创 2023-03-05 20:25:47 · 726 阅读 · 2 评论 -
HiveSQL一天一个小技巧:清洗数据如何将同一组内稀疏字段用有值的数据填充完整?
本文通过实际需求中的案例,讲解了如何将分组内空值补充完整的技巧,通过开窗,min()/max() over(partition by 分组字段)来补充,注意点max()函数中根据实际情况写case when语句,或构造符合实际需求的条件,往往数据清晰中会用到这一技巧。原创 2023-03-01 15:49:46 · 1639 阅读 · 0 评论 -
HiveSQL一天一个小技巧:如何设计一张1-180天注册、活跃留存表?
本文给出了一种1-180天注册活跃留存表计算模型,主要采用笛卡尔集的形式求解,这也是数据报表经常采用的方法,需要掌握。原创 2023-02-04 21:08:39 · 1689 阅读 · 3 评论 -
HiveSQL高级进阶技巧,掌握这10个技巧,你的SQL水平将有一个质的提升!
HiveSQL高级进阶技巧,掌握这10个技巧,你的SQL水平将有一个质的提升!原创 2023-01-30 15:35:26 · 1040 阅读 · 1 评论 -
SQL面试题之区间合并问题
本题为区间合并问题,问题比较经典,判断的核心思路是构造条件:当前行的起始时间原创 2022-09-02 18:10:26 · 2262 阅读 · 8 评论 -
SQL避坑:当in,not in遇上null这种坑你避过吗?
用in的时候一般问题不大,使用not in的时候需要特别主要,结合本文恰当使用,之所以造成如此大的差异原因是对NULL的判断上,NULL的判断本质上is null或is not null,in或not in的判断是等于(=),所以对NULL判断是无效的,需要谨记。.........原创 2022-06-23 16:59:53 · 6500 阅读 · 2 评论 -
Hive中Left Join基于or形式匹配连接的一种优雅实现方式
Hive中对于模糊匹配关联是不支持的,如or 连接,基于like的模糊匹配连接,对于此类问题往往需要找新的方案,对于or连接网上给出了解决方案如union的实现形式,本文借助于locate()+concat_ws()函数进行优雅的实现。......原创 2022-06-04 22:21:19 · 3994 阅读 · 5 评论 -
HiveSql常用的时间维度计算方法(月初、月末、周几)及时间维度 表生成
本文总结了关于Hive中时间函数的使用及时间维度表的生成方法,时间维度表及时间函数在数据开发中经常被用到,这块需要切实掌目前格式支持:MM(月)YYYY(年)Hive3.0后支持Q表示季度select trunc(current_date,'MM') --月初select trunc(current_date,'YY') --年初last_day()last_day(string date) — 返回该月最后一天的日期select last_day(current_d.原创 2022-05-30 10:32:27 · 20659 阅读 · 2 评论 -
HiveSql一天一个小技巧:如何不使用union all 进行列转行
本文给出一种不使用传统UNION ALL方法进行 行转列的方法,其中方法一采用了concat_ws+posexplode()方法,利用posexplode的位置索引实现key-value之间的一一对应,方法二采用explode()+case when的方法,利用case when 进行转换实现key-value之间的一一对应。原创 2022-05-28 18:13:37 · 1089 阅读 · 1 评论 -
HiveSql面试题56:即时订单比例问题
HiveSql面试题56:即时订单比例问题原创 2022-05-22 19:19:00 · 966 阅读 · 2 评论 -
HQL面试题55:阿里最新面试题之地铁站用户行为轨迹分析
HQL面试题55:阿里最新面试题之地铁站用户行为轨迹分析原创 2022-05-21 17:22:15 · 1059 阅读 · 2 评论 -
HiveSql一天一个小技巧:高阶多维聚合函数应用
本文详细分析了Hive中高阶多维聚合函数的使用,由以上分析可以看出,高阶聚合函数实质上是对多维统计采用UNION ALL实现的一种优化,其性能也比UNION ALL实现性能要高,往往作为一种优化手段使用。......原创 2022-05-05 18:36:16 · 2247 阅读 · 1 评论 -
HiveSql一天一个小技巧:如何进行多容器的列转行
HiveSql一天一个小技巧:如何进行多容器的列转行2 小结欢迎关注石榴姐公众号"我的SQL呀",关注我不迷路0 需求表名:t表字段及内容:a b c001 A/B 1/3/5002 B/C/D 4/5问题:转多行输出结果如下所示:a d e001 type_b A001 type_b B001 type_c 1001 type_c 3001 type_c 5002 type_b B002 type_b C002 type...原创 2022-05-04 19:21:27 · 861 阅读 · 2 评论 -
HiveSql一天一个小技巧:如何成对提取字符串内容
本文给出了一种如何成对提取字符串内容技巧和方法,主要利用posexplode()函数进行求解,利用索引值相等提取出对应的字段内容。原创 2022-05-04 18:51:35 · 1567 阅读 · 1 评论 -
HiveSql面试题:连续签到领金币问题【百度-困难题-通用解法】
本文给出了一种连续签到领取金币的一种通用解法,通过窗口函数生成标签值进行辅助计算原创 2022-05-04 00:55:13 · 2421 阅读 · 5 评论 -
HIveSQL面试题52:近一个月发布的视频中热度最高的top3视频【抖音面试题,不得不去吐槽的一个题目】
这道题源自于牛客网,题目本身没什么难度,但在牛客中通过率极低,原因是题目本身但表达很模糊,很多定义没有直接给出,靠面试者猜,造成了误解,如果面试遇到这种题目90%挂,因为你不能准确理解题意,需要反复和面试官确认,即使做出来了,最终也是失败,而这题竟然出自抖音,面试出题也太随意了,无力吐槽,看来面试有时候真的靠缘分,哈哈哈。。。。原创 2022-04-17 13:15:03 · 1389 阅读 · 0 评论 -
HiveSql一天一个小技巧:如何在表的特定位置添加字段
hive表在某个特定的位置添加一个字段,在操作上是先在表的最后添加一个字段然后再将该字段移动到那个特定的位置第一步:先在最后一个位置增加字段alter table table_name add columns now_time string comment '当前时间';第二步:通过移动位置的指令移动到指定位置alter table table_name change now_time now_time string after id;Hive整体更改指令ALTER TAB.原创 2022-04-15 15:02:48 · 5410 阅读 · 1 评论 -
HiveSql一天一个小技巧:如何不借助其他任何外表,产生连续数值
0 需求描述输出结果如下所示:12345...1001 问题分析方法一:起始值(start)+步长(diff)=结束值(end)select split(space(5), ' ')+----------------------+| _c0 |+----------------------+| ["","","","","",""] |+----------------------+select posexplode(spl原创 2022-04-12 22:35:04 · 1504 阅读 · 0 评论 -
HiveSql一天一个小技巧:如何获取稀疏表字段中最新的值所对应的其他字段值
获取某最新日期所对应的其他值,此类问题往往利用first_value()函数来求解。如果表是稀疏的,各个字段存在NULL值,且NULL值分布也不均匀,此时要想一并取出,窗口中的排序需要加限定条件才可以,此类窗口为带有条件限制的动态窗口,动态窗口的控制往往是通过order by来限定的。另外需要注意:first_value()函数获取的是窗口内截止当前行所指定的字段第一条非null值的值,如果该字段中所有值都为null,那么只能返回NULL,也就是说他可以过滤NULL值,因而在获取字段值的时候也就没必要加ca原创 2022-04-10 18:37:23 · 1162 阅读 · 0 评论 -
HiveSql一天一个小技巧:一种字符串展开的技巧和方法
本文给出了一种字符串展开的技巧和方法,充分利用了posexplodde()函数的使用技巧。其核心方法为利用步长值生成索引,再利用起始值加索引值获取展开值,该方法在顺序拓展数据中经常使用,务必掌握。原创 2022-04-09 23:33:49 · 834 阅读 · 3 评论 -
HiveSql一天一个小技巧:数据清洗如何用距离空值记录最近的一条记录补全空值【稀疏表补全方法】
用户一个session中发生浏览点击事件时候,url往往在浏览事件时候给出而点击事件中往往没有给出,这种表格往往是稀疏的,如果此时想知道用户点击了某个按钮后当前页面是哪个,那么我们往往就需要利用这种方法补全数据来获取当前点击事件所对应的页面原创 2022-04-09 19:44:50 · 1104 阅读 · 0 评论 -
HiveSql一天一个小技巧:多指标累计去重问题
目录0 需求1 数据准备2 数据分析3 小结0 需求假设表A 为事件流水表,客户当天有一条记录则视为当天活跃。表A:time_id user_id2018-01-01 10:00:00 0012018-01-01 11:03:00 0022018-01-01 13:18:00 0012018-01-02 08:34:00 0042018-01-02 10:08:00 0022018-01-02 10:40:00 003...原创 2022-04-09 19:01:19 · 2931 阅读 · 8 评论 -
HiveSql一天一个小技巧:如何遍历字符串及获取索引位置
本文给出了一种采用SQL语言遍历字符串的方法及技巧,主要采用split()函数及posexplode()函数进行解析字符串,并利用表生成函数与原表进行关联,这一技巧在处理字符串时候经常使用忘读者掌握。原创 2022-04-09 17:57:06 · 1912 阅读 · 0 评论 -
HiveSql面试题50:京东面试题之有效值问题
本文给出了一种按条件取有效值问题的思路和方法,其本质还是对上和对下重新分组的一种思路,即当前定义值是和上一个值成为一组,还是和下一个值成为一组,文中所述的NULL值即为定义值,利用sum() over(order by dt)来巧妙解决。原创 2022-04-06 16:07:35 · 2841 阅读 · 8 评论 -
HiveSql一天一个小技巧:如何构造连续日期【稀疏字段累计求和问题】
稀疏字段累计求和问题,如何构造连续日期?原创 2022-04-05 19:46:47 · 2371 阅读 · 1 评论 -
HiveSQL一天一个小技巧:借助于聚合函数与case when进行行转列
0 需求目标结果表1 数据准备创建原始数据表,加载数据 --切换数据库 use db_function; --建表create table row2col1( col1 string, col2 string, col3 int) row format delimited fields terminated by '\t'; --加载数据到表中load data local inpath '/export/data/r...原创 2022-03-20 22:19:12 · 1311 阅读 · 1 评论 -
HiveSQL一天一个小技巧:如何借助于str_to_map进行行转列
本文分析了一种采用str_to_map的方法进行行转列的方法,该方法结构优雅,从执行效率上来看性能更优,比通用的聚合函数加case when的方法效率更高。通过本文也可以利用str_to_map来拼接json串,不失为一种很好的技巧。......原创 2022-03-20 22:11:28 · 1873 阅读 · 0 评论 -
HiveSQL一天一个小技巧:借助于join进行行转列
SQL如下:select tmp1.id as id,tmp1.value as height,tmp2.value as weight,tmp3.value as age from (select id,label,value from tmp2 where label = 'heit') as tmp1joinon tmp1.id = tmp2.id(select id,label,value from tmp2 where label = 'weit') as tmp2joino原创 2022-03-20 22:08:04 · 448 阅读 · 0 评论 -
HiveSQL一天一个小技巧:如何进行字符串收缩变换【京东面试题】
本案例详解了对字符串进行收缩变换(合并操作)的技巧和方法,主要使用函数concat_ws(',',collect_list())来对数据进行合并,合并时要注意合并的分组条件。本案例需要掌握知识点: (1) 数据收缩变换的技巧和方法(2)断点分组的技巧和方法问题思考:(1)collect_list()由于内部底层实现原因往往合并值是乱序的,如何保证合并后内容的有序性?(2)如何保证group by分组后输出的顺序性?原创 2022-03-20 22:00:10 · 4266 阅读 · 4 评论 -
水位线思想在解决SQL复杂场景问题中的应用与研究
SQL语言在数据处理、数据分析领域具有重要意义,往往要求开发者具有结构化的思维方式,能将二维表进行各种方式的转换,以达到输出最终结果的目的。这就要求数据开发者必须掌握一些核心的变换操作,如关联、和UNION操作,其中关联的意义在于联合查询,获取更多的行数据信息及逻辑判断的特征信息(让行粒度的数据变得更丰富),UNION的意义是为了分治处理,将不同特征的数据分开处理并进行合并,这两种操作是处理数据变化的基础操作和基本思维。而窗口函数的出现,让SQL应对复杂场景的问题变得简单,他以数据集(窗口)的形式对数据进行原创 2022-02-27 00:26:16 · 1555 阅读 · 7 评论 -
HQL一天一个小技巧:coalesce和nvl函数区别
coalesce 函数COALESCE函数语法为COALESCE(表达式1,表达式2,...,表达式n),n>=2,此表达式的功能为返回第一个不为空的表达式,如果都为空则返回空值。注意:所有表达式必须为同一类型或者能转换成同一类型。CREATE OR REPLACE VIEW v ASSELECT NULL AS C1, NULL AS C2, 1 AS C3, NULL AS C4, 2 AS C5, NULL AS C6 FROM tmpUNION ALLSELECT NUL.原创 2022-01-04 14:19:07 · 5695 阅读 · 0 评论 -
SQL一天一个小技巧:如何通过HQL解析JSON
在Hive中会有很多数据是用Json格式来存储的,如开发人员对APP上的页面进行埋点时,会将多个字段存放在一个json数组中,因此数据平台调用数据时,要对埋点数据进行解析。接下来就聊聊Hive中是如何解析json数据的。Hive自带的json解析函数1. get_json_object 语法:get_json_object(json_string, '$.key') 说明:解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效,那么返回NUL转载 2021-09-02 18:42:33 · 2801 阅读 · 0 评论 -
你真的了解HiveSql吗?真实的HiveSql执行顺序是长这样的
通过上面的研究我们发现在hivesql中其执行顺序大致如下:from->where(谓词下推)->join->on->select(select中的字段与group by只要不一致就会有)->group by->select(为having准备数据,因而having中可以使用select别名)->having->select(过滤后的结果集)->distinct->select->order by ->select->limit实际上上述需要注意的是select在group by前和group by原创 2021-09-01 19:31:24 · 2002 阅读 · 1 评论 -
HQL一天一个小技巧:如何解决NULL值引发的数据倾斜
目录0 需求描述1 问题解决2 小结0 需求描述实际业务中有些大量的null值或者一些无意义的数据参与到计算作业中,表中有大量的null值,如果表之间进行join操作,就会有shuffle产生,这样所有的null值都会被分配到一个reduce中,必然产生数据倾斜。1 问题解决(1)问题剖析疑问:NULL值和需要匹配的字段根本就匹配不上,为什么会进入到同一个reduce?数据放到同一个reduce中的原因不是因为字段能不能join上,而是因为shuffle阶段的数据分发操作原创 2021-08-31 11:32:50 · 1326 阅读 · 4 评论 -
SQL之一天一个小技巧:如何判断某个字符是否在字符串中
目录0 需求1 实验2 小结0 需求过滤出字符串中包含某字符的数据1 实验(1)用locate()函数判断【推荐比较高效】我们知道locate()函数可以判断某个字符在字符串中的位置,如果不在该字符串中返回0值,如果在返回字符对应的索引位置。select locate('1','10002,21002,11001,11001')0: jdbc:hive2://10.9.1.212:10000> select locate('1','10002,21002,1原创 2021-08-27 17:36:50 · 6322 阅读 · 0 评论 -
SQL之一天一个小技巧:一种中位数的分析技巧及思路
目录0 需求1 分析2 小结0 需求字段:店铺id(shop_id),销量(sale),商品id(commodity_id)求每个店铺商品销量排名的中位数1 分析(1)数据准备create table shop_test( shop_id string, commodity_id string, sale int)row format delimitedfields terminated by '\t';insert into shop_test原创 2021-08-26 23:29:47 · 1429 阅读 · 0 评论 -
SQL 向用户推荐好友喜欢的音乐--HQL面试题49【腾讯】
0 问题描述问题描述:向用户 user_id = 1 推荐其关注的人喜欢的音乐有如下三个表:用户关注表 follow 用户喜欢的音乐 music_likes 音乐名字表 music1 数据分析create table follow( user_id int, follower_id int);insert into follow values (1,2);insert into follow values (1,4);insert into follo..原创 2021-08-23 22:00:32 · 1311 阅读 · 0 评论 -
SQL 之共同使用ip用户检测问题【自关联问题】-HQL面试题48【拼多多面试题】
0 问题描述create table log( uid char(10), ip char(15), time timestamp);insert into log valuesinsert into log values('a', '124', '2019-08-07 12:0:0'),('a', '124', '2019-08-07 13:0:0'),('b', '124', '2019-08-08 12:0:0'),('c', '124', '2019-0.原创 2021-08-23 18:46:20 · 893 阅读 · 0 评论