2021年7月份加入了当前项目组，以一个原汁原味的Java开发工程师的身份进来的，来了没多久，项目组唯一一名大数据开发工程师要离职了，一时间一大堆的数据需求急需人来接手，此刻又招不来新的数据开发。没辙，我和同组的另一位Java开发同事算是临危受命，接下了大数据方面的工作，开启了Java工程师从0到1搞大数据的漫长旅途，开始的磕磕碰碰叫苦不堪到如今的还算得心应手，已经整整16个月了，16个月期间双向支持着数据分析和后端开发的工作，两者时而穿插时而并行处理，大数据工作占得比重之多，有时让我怀疑我还是不是一名纯粹的Java开发工作者，当我看见假期值班表中我的角色填写一项变成“B端后端/数据”时，我就知道我已经不纯粹了。

1.Sql -- 大数据分析的灵魂

​       搞大数据究竟每天在做些什么？坦白讲，情况和我想象的不太一样，因为做大数据开发时最最主要的工作居然写Sql，曾经我还以为它是有一套刁钻困难冷门的牛逼技术，将海量数据玩弄于股掌之中。现在看来，我是每天和各种各样的大数据表打交道，在大数据平台用sql提取出业务方想要的信息，有时会出各式各样的数据报表，有时是为C端项目服务，提供底层海量数据计算的支持，有时是为各种数据看板服务，提供他们想要的销量排行了、人群覆盖情况了诸类。工作前两年纯粹写java时也是对sql有所研究的，毕竟数据持久层的交互离不开sql，然后搞了大数据才明白，之前写的sql都是小儿科，现在一条sql写上百行那都是常有的事，而且最开始解读大sql时总是慢半拍，好久才能搞明白前辈留下的交接文档表达的是什么，现在不一样了，看见那些sql都亲切很多，很多需求提出来总能迅速想到sql解决的方案，下面呢，我就开始分享一些我在写大sql时经常会使用的一些语法，这些语法可能针对于只做Java的人并不会经常性的熟练使用。

1.1with.. as..

with temp1 as (
select * from ... where ..
),
temp2 as (
select * from ... where...
),
...
tempn as (
select * from ... inner join ... where
)
select a.*,b.*,c.* from temp1 a inner join temp2 b on a.id = b.id left join ..tempn c on a.iid = c.iid where ...

模板中的temp1,temp2,tempn都可以看做这个sql执行过程中的临时表，存在周期仅限于执行这条sql期间，sql执行完毕临时表也销毁，并且和其他的sql是相互隔离的，下面的sql都可以使用之前的产生的临时表(temp2就可以使用temp1的结果)，使用with时最后一定跟的select语句，当然，跟的是insert into table ...... select * from也是可以的。

使用with..as..语法大大提高了长Sql的解读性。

之前一直以为这个HiveSql特有的语法，后来才发现在mysql中也可以使用，只不过是mysql8.0以后的版本可以使用，之前的版本是没有这个语法的。

1.2开窗函数：row_number() over(partition by file order by file2 desc/asc)

select  row_number() over(partition by userid order by pay_time desc) as rn,userid,name,order_cd,goods_name,pay_time
from  db_dw.table _order 
having rn = 1

这个sql的作用就是找出每个用户的最新付款的那笔订单的订单信息。

实现思路就是利用开窗函数按照用户id分组，再按照付款时间倒叙排序，给每组的数据加上一个rn的编号，每组的第一条rn 都等于 1 ,第二条rn = 2，以此类推，再通过having函数将结果中rn = 1的数据全取出来，这样就能通过单条sql完成取每一个用户最新一条订单的数据需求。

1.3开窗函数lag(field, num, defaultvalue) over(partition by ..order by ..) 与 lead() over()

select  lag(pay_time,1,NULL) over(partition by userid order by pay_time asc) as last_pay_time,userid,name,order_cd,goods_name,pay_time
from  db_dw.table _order 

select  lead(pay_time,1,NULL) over(partition by userid order by pay_time asc) as next_pay_time,userid,name,order_cd,goods_name,pay_time
from  db_dw.table _order 

• lag(field, num, defaultvalue)，其中fied是要查询的字段，num是向前取几行，defaultvalue是取不到值时的默认值。向上面案例中那样，假设按照userid分组后又按照pay_time排序了，第一个查出来的用户刚好也有多条不同pay_time的数据，那么查询结果应该是第一行数据为last_pay_time为NULL，pay_time为该用户的最小的时间，第二行数据的last_pay_time等于第一行数据的pay_time值，而pay_time为第二小的时间。
• lead(field, num, defaultvalue)，其中fied是要查询的字段，num是向后取几行，defaultvalue是取不到值时的默认值。向上面案例中那样，假设按照userid分组后又按照pay_time排序了，第一个查出来的用户刚好也有多条不同pay_time的数据，那么查询结果应该是第一行数据为next_pay_time为第二行的pay_time，pay_time为该用户的最小的时间，第二行数据的next_pay_time等于第三行数据的pay_time值，而pay_time为第二小的时间，而该用户的最后一行的next_pay_time则为NULL。

1.4case when <条件1> then <结果1> when <条件2> then <结果2> else <剩余数据的结果> end as 字段名

-- 将用户年龄按照18岁及以下，18岁至65岁，65岁以上分类
select  case when age<=18 then '未成年' when age>18 and age <=65 then '青中年' else '老年' end as age_group,name,age,sex
from user

case when 语法其实就是java语言的if...else if ...else if...else，当满足条件时就进入该分支，不满足的话就一直进入下面的分支，最后所有条件都不满足则进入else分支，通常在Sql中我们使用case when then进行一些归纳分类，譬如我们的电商涉及到的商品种类众多，可能需要按照某些规则进行分类，就免不了使用该语法。

标签：数据,开发,java,分析,sql,工作,函数,语言 来源：

本站声明： 1. iCode9 技术分享网（下文简称本站）提供的所有内容，仅供技术学习、探讨和分享； 2. 关于本站的所有留言、评论、转载及引用，纯属内容发起人的个人观点，与本站观点和立场无关； 3. 关于本站的所有言论和文字，纯属内容发起人的个人观点，与本站观点和立场无关； 4. 本站文章均是网友提供，不完全保证技术分享内容的完整性、准确性、时效性、风险性和版权归属；如您发现该文章侵犯了您的权益，可联系我们第一时间进行删除； 5. 本站为非盈利性的个人网站，所有内容不会用来进行牟利，也不会利用任何形式的广告来间接获益，纯粹是为了广大技术爱好者提供技术内容和技术思想的分享性交流网站。