石榴姐yyds

在数据分析时，经常会遇到一些异常数据问题，比如某个商店近一周GMV突然下跌，某APP日活突然下降，此时就会被业务方质疑数据有问题。面对业务方质疑的时候我们如何快速找到问题原因，并给出解决方案呢？本文就为你提供一种指标异常时的完整解决方案。1数据准确性确认在面对异常信息的时候，首先要确认数据的准确性，也就是先要确认这个异常是否为真正的异常。1.1数据源的确认数据源是我们取数的基础，确保数据源的正确性是数据分析首要做的事情。1）确认数据有没有同步更新到最新2。

（1）确定主键id:确定对象，如学员表，对象为学员，根据学员id关联其他数据源，其粒度不变（2）确立对象的属性：将对象属性冗余进宽表。如学员id，将学员的相关信息进行冗余（3）确立对象与对象之间的关系：如学员与教练的关系，一个学员可以有多个教练，该教练的信息如何。（4）确立对象的行为指标：该对象做了什么，发生了什么？如：学员报了几门课程，一共上过几门课，还有多少没上，成绩如何。

高于业务视角就要数据开发人员深入了解业务的同时，要打破自身的壁垒，不仅仅只是处理几张报表、分析指标，而是要具备数据分析的思维，对数据敏感，通过横向、纵向对比找出业务的差异点并提供分析原因，同时能够将局部业务线串起来从全局的角度分析问题，并给出建议。因为业务看待问题往往都是局部化的，每个人只负责自己的模块，对自己所负责的业务流程很熟悉，但是从公司整体业务现状、业务来看数据的同学是具有优势的，可以从数据全局的视角分析问题，这就要求我们数据底层必须是打通的，而不是数据孤岛化的。

指标体系是我们数仓来搭建还是分析师来做，如何去推动？

一直以来percent_rank()函数作为分析函数中冷门函数，被好多人所不知，甚至成为知识盲区。包括很多官方文档，给出的定义并不明确，甚至理解起来也具有误导性，如下图所示，某官方产品给出的定义解释为百分比排名，咋一听就很晕乎，什么叫百分比排名？用在什么地方、场景并不清楚，该函数percentile()函数及cume_dist()函数有什么区分？相信很多同学还是一头雾水，本文就针对该函数的用法、使用场景及几个函数区分进行讲解

目录0 问题描述1 问题解决2 小结0 问题描述SQL 从不固定位置提取字符串的元素，你有一个字符串，其中包含一段连续的日志数据。你想解析该字符串，并从中提取出部分信息。不过，你需要的信息并不存在于字符串的固定位置。因此，你必须借助目标信息附近的某些字符来定位并提取所需的内容。例如，考虑下面的字符串。xxxxxabc[867]xxx[-]xxxx[5309]xxxxxxxxxxtime:[11271978]favnum:[4]id:[Joe]xxxxxcall:[F_GET_R

行列转换是数据开发者的基本技能，也是面试重点，在数据开发中处处可见，本文对常见的几种情况进行了总结，包括了多行转多列，多行转单列，多列转多行，单列转多行的几种情况，并给出了解决思路和方法，读者可根据本文给出的思路灵活应对业务中遇到的问题。

0 需求（1）需求描述按照一个人的订单日期从 1 开始排序，并计算下一笔订单距离上一笔订单间隔日期。如果间隔日期超过一个月，则消费次数需要重新排序，请用 HiveSQL 进行实现？原始数据表如下：order客人 订单日期 消费次数 距离上一笔间隔 A 2019/5/15 1 0 A 2019/5/16 2 1 A 2019/5/19 3 3 A 2019/6/20 4 32 A .

本文总结了关于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.

本题关键点是如何构建分组条件，在分组中求出所需要的目标，是之前SQL题中总结的重分组算法的典型应用。本题在多家面试中被作为笔试题，要求用spark算子、mr编程或HQL语言实现，spark算子或mr编程实现方法网上文章讲的比较多，而SQL语言实现基本没有，本文给出了一种HQL语言的实现方法，弥补了此类问题用SQL语言实现的空缺。

本文分析了一种采用str_to_map的方法进行行转列的方法，该方法结构优雅，从执行效率上来看性能更优，比通用的聚合函数加case when的方法效率更高。通过本文也可以利用str_to_map来拼接json串，不失为一种很好的技巧。......

分析函数在sql中非常重要，对于sqlboy来说是必须要掌握的 ，本文对Hive-Sql分析函数进行汇总和总结，Hive-Sql分析函数主要有以下五类： 聚合分析函数 排名分析函数 数学分析函数 跨行访问分析函数 多维分析函数其思维导图如下所示：

1 应用场景1.1 Hive中的分隔符Hive中默认使用单字节分隔符来加载文本数据，例如逗号、制表符、空格等等，默认的分隔符为\001。根据不同文件的不同分隔符，我们可以通过在创建表时使用 row format delimited fields terminated by ‘单字节分隔符’ 来指定文件中的分割符，确保正确将表中的每一列与文件中的每一列实现一一对应的关系。1.2 特殊数据在实际工作中，我们遇到的数据往往不是非常规范化的数据，例如我们会遇到以下的两种情况情况一：每一.

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...

本文通过实际需求中的案例，讲解了如何将分组内空值补充完整的技巧，通过开窗，min()/max() over(partition by 分组字段)来补充，注意点max()函数中根据实际情况写case when语句，或构造符合实际需求的条件，往往数据清晰中会用到这一技巧。

本文分了连续性增长这一指标的计算方法，在实际业务中经常会遇到，作为面试题也可以考察面试者的素养，因而掌握该类题的解法具有重要意义。本题要求解的是连续增长，那么条件就是连续的时间和销售金额的连续增长，求解如下。我们需要计算过去至少连续3天销售金额连续增长的商户。假设我们有一张订单表shop_order。

本文主要通过案例来分析ntile()函数的使用方法，ntile()函数主要用于对数据进行分片、分桶，其算法为如果数据能均匀分配的话就按照平均分配，不能均匀分配的话，优先将数据分配到编号较小的桶中。ntile()函数主要用来求某个数据的前或后百分比问题题，在统计中具有重要意义。

0 需 求（1）需求描述 col1是有序的，然后按照col2分块计数，每当col2发生变化，就重新开始计数，计数的结果当作col3返回。（2）效果如下2014,12015,12017,02018,02019,12020,12021,12022,02023,0=>2014,1,12015,1,22017,0,12018,0,22019,1,12020,1,22021,1,32022,0,12023,0,22 需求实现（1）数据准备2..

该问题的本质就是按照发生了某个事件，在该事件处重新进行分组的问题，怎么确定重新分组呢？换个思路为：统计截止到当前行发生事件的个数，那么每次从发生事件处个数发生变化，直到下一个事件发生前其个数是一致的，则相同个数的就是一组。该规律则提供了解决按照事件发生处进行重新分组的解决方法。

0 题目数据如下：date user_id age programid Playtime 20190421 u1 30 a 4 20190421 u1 31 b 10 20190421 u1 30 a 2 20190421 u2 27 c 1 20190422 u3 35 d 3 20190423 u2 27 a

业务场景统计物联网控制板上温度控制模块的使用次数（曾经为了实现这个需求，我写了Spark应用去分组按照时间排序，然后遍历数据集），其中温控模块的关闭状态为0，打开状态为1。统计使用次数，即是统计温控模块的状态从0-1的变化次数。数据格式：设备ID - 时间戳 -温控模块状态...

本文针对SQL统计同时在线人数问题进行了分析，利用累加器思想对该问题进行求解，最终划归为时序数据，进行时序数据分析（常用技巧：打标签，形成序列，多序列进行分析），最后利用sum() over()对标签进行累加求出当前在线人数。本题的思维方法值得借鉴，也可以与之前的文章统计断电次数及断电时长问题（https://blog.csdn.net/godlovedaniel/article/details/118027222）进行对比，形成此类问题统一的分析方法。本题最关键的点在于转换为时序数据及累加器的思想，望读者

目录0 需求1 数据准备2 数据分析3 小 结0 需求：求股票的波峰Crest 和 波谷trough波峰：当天的股票价格大于前一天和后一天波谷：当天的股票价格小于前一天和后一天数据准备： stock表有3个字段： id（股票id）、price（股票价格）、dt（日期）--原始数据 stock表-------id price dt 1 20 2021-07-01 1 21 2021-07-02 1 19 20

0 引言 SQL NULL（UNKNOW）是用来代表缺失值的术语，在表中的NULL值是显示为空白字段的值，用作不知道数据的具体值，或者不知道数据是否存在，或者数据不存在等情况。NULL值在SQL中普遍存在，想必大多数程序员对其有所了解，但是对于NULL值在SQL中贯穿体现及相关细节，是否会有人注意到呢？本文将对SQL中NULL值的一些使用细节进行研究，让你在使用过程中避开NULL值的坑，...

我想获取该json中的每一个key值，如何做？如下json_str。

本文给出一种不使用传统UNION ALL方法进行 行转列的方法,其中方法一采用了concat_ws+posexplode()方法，利用posexplode的位置索引实现key-value之间的一一对应，方法二采用explode()+case when的方法，利用case when 进行转换实现key-value之间的一一对应。

0 需求hive中怎么统计array中非零的个数【0，1,3，6,0】结果：非0的个数为31 实现（1）将array转换成字符串，采用concat_ws（）函数select concat_ws(',',array) from test_array返回：0,1,3,6,0注意区分concat函数和concat_ws函数concat函数在连接字符串的时候，只要其中一个是NULL，那么将返回NULL concat_ws函数在连接字符串的时候，只要有一个字符串不是NULL，

本文给出了一种利用HQL语言分离字符串中含有字符和数字的技巧和方法，主要使用regexp_replace()函数及translate()进行求解，求解的核心思想就是将不同的字符或数字替换成指定的特征字符，然后用regexp_replace()函数删除指定的特征字符，即可分离出字符及数字。

我们知道locate()函数可以判断某个字符在字符串中的位置，如果不在该字符串中返回0值，如果在返回字符对应的索引位置。（3）用regexp正则匹配。注意此时regxp()里面的正则为。由测试的结果看：locate()>regexp()>like。（1）用locate()函数判断【推荐比较高效】（2）用like正则匹配【不推荐，效率低】返回true值表示包含，存在要找的字符。返回值为1说明大于0，表示存在或包含。过滤出字符串中包含某字符的数据。（2）like模糊匹配测试。

本文给出了一种字符串展开的技巧和方法，充分利用了posexplodde()函数的使用技巧。其核心方法为利用步长值生成索引，再利用起始值加索引值获取展开值，该方法在顺序拓展数据中经常使用，务必掌握。

user1 语文 89user1 数学 92user1 英语 87user2 语文 96user2 数学 84user2 英语 991.将course, score多行转一列用户 课程 分数user1 语文，数学，英语 89，92，87user2 语文，数学，英语 89，92，87。

Hive中对于模糊匹配关联是不支持的，如or 连接，基于like的模糊匹配连接，对于此类问题往往需要找新的方案，对于or连接网上给出了解决方案如union的实现形式，本文借助于locate()+concat_ws()函数进行优雅的实现。......

本文给出了一种利用倍数小表优化数据倾斜的一种通用方法，该方法适用场景为数据倾斜时不能用mapjoin的时候，也就是集群资源不足时候，通过该方法能够有效缓解数据倾斜，但不能根除数据倾斜。处理数据倾斜最好的方法还是采用分治思想，利用mapjoin一分为二的处理，倾斜key单独走mapjoin，非倾斜key走reduce join，最终将数据union all起来，采用mapjoin的方法切断了shuffle过程，也就没有数据倾斜这一说，缺点耗资源，前提是集群资源足够条件下，且满足mapjoin的条件。

在日常数据分析中，经常会遇到数据产品经理或数据分析师提出这样的需求，比如按照某一给定的区间或数据范围对数据进行分类标签，而遇到这样的问题，好多同学感觉SQL做起来有点困难或无从下手，其实面对这样的问题笔者早在其他文章中已经进行总结，笔者将这类问题归结为分桶问题，而分桶问题又分为两类，一类是桶个数一定，但是桶尺寸不固定的分桶，一类是桶尺寸一定，但是桶个数不一定的分桶，针对这两种分桶的方式，我们分别给出如下解决方案。第一类：桶个数一定，桶尺寸不定，ntile函数中传的参数即为需要分桶的个数。

话题1：#什么是BI?话题2 ：#BI与大数据有什么关系？话题3：#BI与信息化、数字化之间的关系？话题4：#BI与报表之间的区别？

稀疏字段累计求和问题，如何构造连续日期？

本文总结了一种重新分组的方法，从不同的角度来认识该算法，该方法实际上在业务中经常被用到，之前我们讲到的分桶重排序算法等实际上和这个是类似的，只不过本文从不同的角度来解释该算法，让读者能够更深刻的认识该算法，从而更好的应用实践中，解决实际问题。

本题实际上是按事件发生后重分组算法的应用，给出了连续性问题的一种通用解法，如果按照等差数列的解法并能很好的适应此类问题的需求，如果问题变成间隔两天或三天等，等差数列的方式则不具备通用性，该思路巧妙地将定义的连续性数据分为一组，通过分组解决最终的问题值得推荐。

0 问题描述薪水表中是员工薪水的基本信息，包括雇员编号，部门编号和薪水 第1行表示雇员编号为10001的员工在1号部门，薪水为60117元; 第2行表示雇员编号为10002的员工在2号部门，薪水为92102元; ... 第10行表示雇员编号为10010的员工在1号部门，薪水为76884元问题：查询每个部门除去最高、最低薪水后的平均薪水，并保留整数。1 数据准备（1）数据基本数据 雇员编号 部门编号 薪水 10001 1 60117 10002 .

本文给出了一种按条件取有效值问题的思路和方法，其本质还是对上和对下重新分组的一种思路，即当前定义值是和上一个值成为一组，还是和下一个值成为一组，文中所述的NULL值即为定义值，利用sum() over(order by dt)来巧妙解决。