大厂sql题(一)：

实现方式sparkSql

 

一、求用户的一个留存数

留存数 =（登录日-注册日 = 流存数）

涉及到求时间的一个差值：

datadiff(from_unixtime(unix_timestamp(login_data,"yyMMdd"),"yy-MM-dd"),from_unixtime(unix_time(register_data,"yyMMdd"),"yy-MM-dd") )

一、unix_timestamp函数用法 1、unix_timestamp() 得到当前时间戳 2、如果参数date满足yyyy-MM-dd HH:mm:ss形式，则可以直接unix_timestamp(string date) 得到参数对应的时间戳 3、如果参数date不满足yyyy-MM-dd HH:mm:ss形式，则我们需要指定date的形式，在进行转换

select unix_timestamp('2009-03-20') --1237507200select unix_timestamp('2009-03-20 00:00:00', 'yyyy-MM-dd HH:mm:ss') --1237507200select unix_timestamp('2009-03-20 00:00:01', 'yyyy-MM-dd HH:mm:ss') --1237507201

二、from_unixtime函数用法 语法：from_unixtime(t1,’yyyy-MM-dd HH:mm:ss’) 其中t1是10位的时间戳值，即1970-1-1至今的秒，而13位的所谓毫秒的是不可以的。 对于13位时间戳，需要截取，然后转换成bigint类型，因为from_unixtime类第一个参数只接受bigint类型。例如：

select from_unixtime(1237507201,'yyyy-MM-dd HH:mm:ss') -- 2009-03-20 00:00:01select from_unixtime(1237507200,'yyyy-MM-dd HH:mm:ss') -- 2009-03-20 00:00:00

常用的插入时间为当前系统时间 转换成距离1970的时间戳，再转换成当前时间

select FROM_UNIXTIME(UNIX_TIMESTAMP() ,'yyyy-MM-dd HH:mm:ss') AS W_INSERT_DT

二、行列变换

方式一：

经典case when item= 'A' then price else 0 end as A(拿一行举例)

然后结果还需要group by分组再做sum聚合或者是其它的聚合

sum(case when item= 'A' then price else 0 end) as A

可能还需要用到cast(xxx，as 转换的类型)

cast(sum(case when item = 'A' then price else 0 end as A)as int)

方式二：

用hive函数来完成

concat(字段，":", 字段)：拼接字段

collect_list（）: （不去重）

collect_set（）：（去重）

concat_ws("分隔符"，str):

str_to_map（str, "," , ":"）:

nvl（）：判断是否为null，为null，默认给一个值

select usernaem, str_to_map(concat_ws("," , collect_lsit(concat(item,":",price))) , "," , ":") as mp from 某张表 group by username

select username, nul(mp["A"] ,0) as A, nvl( mp["B"] ,0) as B,nvl(mp["C"],0) as C ,nvl(mp["D"],0) as D from 上边的表，————实现了行转列

三、列转行

方式一：

如果是mysql中的话可以用union

select username ，"A" as item , A as price from 某张表 union

select username ，"B" as item , B as price from 某张表 union

select username ，"C" as item , C as price from 某张表 union

select username ，"D" as item , D as price from 某张表

如果是在大数据中的话

方式二：

map(): 往map中添加数据，可以是map("A",A,"B",B) ,按照key：value区分A：A的value

explode（）：一行变多行，例如username 对应一个map的一个映射

select username ,exploda(map("A",A,"B",B,"C",C,"D",D)) as (item ,price)from 某张表

四、每个小时在线用户数

方式一：

拿两条数据举例，在线的时间有4，5，6，7，8这些小时，然后explode出在线小时的对应的数据，然后分组count就能求出具体某小时在线的用户个数。

1.首先把数据补全，用到开窗函数，

select username, time ,type row_number () over (partition by username order by time ) as rank from 某张表

2.然后做表的错位自连接，用full join，顺便做一下日期的转换

select a.username,replace(repalce(a.time,"T"," 空格"),"00Z","")as a.time,a.type.a.rank,b.username,b.time,.b.type,b.rank from temp1 a full join temp1 b where a.rank = b.rank -1 ;做一个表的错位连接

3.补全数据，前面为空的是login类型，时间是当天的 00：00：00,如果是后一天的数据，类型就是logout，时间是当天的23：59：59。

select

nvl(a.username,b.username) as username1,

nvl(a.time,concat(split(b.time," ")[0],"00:00:00") as time1 #切分完的是一个数组 ,

nvl(a.type,"login" )as type1,

a.rank,

nvl(b.username,a.username) as username2,

nvl(b.time,concat(split(a.time)[0],"23:59:"59) as time2,

nvl(b.type,"logout")as type,

b.rank

from temp1 a full join temp1 b where a.rank = b.rank -1 ;

4.我们要的数据： 前面是login的后面是logot的数据

所以在上面的基础上再加上where条件的过滤，order by username1，time1 asc

5.实现一个udf函数，然后返回一个list（相差的时间点的集合），然后explode形成新的记录。

函数具体是（login的时间，时间差多少个小时） #相差的时间用hour函数

myudf(time1,(hour(time2)-hour(time1)))

// 这里用的scala语言

session.udf.register("myudf",( dt:String , diff:Int )=>{ # 注册用户自定义函数

val returnlist = new ListBuffer()

// 定义需要的类日期格式

val sdf1 = new SimpleDataFormat("yyyy-MM-dd HH:mm:ss")

val sdf2 = new SimpleDataFormat("yyyy-MM-dd_HH")

retunrlsit.append(sdf2.format(sdf1.parse(dt)))

// 循环让日期的天数加1 存到要返回的集合中区

val calendar:Calendar = Calendar.getInstance()

calendar.setTime(sdf1.parse(dt))

for(i <- 0 until diff){

calendar.add(Calendar.HOUR,1) //每循环一次，小时加1

returnlist.append(sdf2.format(calender.getTime))

}

//返回最后的日期集合（实现一对多）

returnlist

})

最后4和5的综合sql：

select username1,time1,type1,username2,time2,type2,explode(myudf(time1,(hour(time2)-hour(time1)))) as rt from temp3 where type1 = 'login' and type2 = 'logout' order by username,time1;

6.最后按照time1分组count即可球的每小时的在线用户个数

select username1 ,count(distinct(username1)) form 某张临时表 group by rt如果数据量很大的话，可以把distinct换成group by username1

1.计算一天里各个用户在线的总时长

用后面的日期减去前面的日期，sum求和即可

2.在线次数

count（*） 一次登入登出算一次在线

3.最大在线时长

登出时间减去登入时间再max（）就能求出最大值

五、大数据面试流程及重点需要复习的东西：

首先一定先从项目问起，但是java的一些东西，jvm，GC也会问到

大数据项目中涉及到的几块：

1.ETL的操作

用什么工具抽取的数据？sqoop，flume，DataX，kettle，maxwell等，

2.数据仓库

构建数据仓库用到的，Kafka，HDFS，Hive，知道数据仓库的分层，分多少主题域，做多少指标。还有要知道数据仓库的建模理论

当然数仓中还涉及到一些技术，需要掌握Kylin，Presto，clickhouse，impala等

3.应用的开发（app）

Spark集合，core，sql，Streaming Flink 它们的流式计算，批计算

4.可视化展示

SpringBoot、SSM。小工具superset，sugar等一些BI工具

5.资源调度

yarn，CM平台

6.任务调度

oozie、Azkaban、airflow等

7.数据治理

元数据管理-atlas，数据质量校验，数据安全-Ranger

总结：

数据仓库是重中之重，结合项目去理解去说，其他的每块都需要掌握一些会随着项目一点一点问到，像hdfs，yarn，hive，spark，flink这些都需要知道底层的原理，比如hdfs的读写，mr和yarn的工作流程，hive常用函数，基本语法hsql的使用，以及自定义函数的编写和使用，sparkcore的算子，rdd，以及划分stage等一系列的知识，sparkSql的书写，SparkStreaming批处理的使用等，flink的状态一致性，checkpoint，等原理的东西。其它第三方的工具得明白说得出来它的一个使用，工作机制等。机器学习在大数据的领域一般是做数据分析，这个看自己的能力，说出来肯定是加分项。做过项目的应该都知道离线和实时的区别，所以用到的一些工具，组件与组件之间的联系也要明白