SQL的日常用法及常用面试

1.找出股票曲线图的所有波峰和波谷

思路：自连接两次，关联条件为时间点和时间点加一及时间点减一关联，取金额大于前一时刻且大于后一时刻的作为波峰

2.拉链表的设计

思路：增加主键分组排序序号列，根据序号和序号加一自关联，关联不上的闭链

3.行转列，数据拉平

思路：按主键分组，指标列用case when做聚合

4.列转行，数据拉直

思路：把待拉直的几个字段组合成一个数组，再用lateral view explode拉直

SQL的日常用法及常用面试

5.上海区域下有100家门店，找出多少家门店可以构成100家门店的总销售额的百分之九十

思路：取到100家门店总销售额的百分之九十的销售额的值；取两个排序，1根据销售额的row_number排序2区域分组门店销售额排序用sum(销售额） over(partition by 区域 order by 销售额)排序，取结果中该字段超过九折销售额的排序的最小序号值

6.某区域包含n家门店，每家没点销售的商品不同，如何找出每样商品销售数量均大于30的门店

思路:一家门店的所有产品的销售量的最小值大于30

select 门店

from 表

group by 门店

having min销售数大于30

7.count(*),count(1)和count(column)区别

前两者是返回数据表的行数，后者是返回改字段中不为null值的数据行数

8.排序的区别

row_number()顺序排序(不允许并列)，即使中间数值相同也会顺序向下排序，序号不断;

rank()跳档排序，中间有数值相同会跳档排序，有两个第三名时，此种排序不出现第四名，顺位第五向下

dense_rank()顺序排序(允许并列情况)中间数值相同的情况允许并列，可以有并列第三名，且下一个顺位是排为第四名

一个表中只有a,b,c,d4支球队，求所有可能的对战情况

思路：笛卡尔积的1/2

select a.name, b.name

from team a, team b

where a.name < b.name

10.几个高效的窗口函数

查询客户上次和下次的购买时间

Lag() over ;lead() over

select name,orderdate,cost, lag(orderdate,1,'无上条订单') over(partition by name order by orderdate) prevdate,

lead(orderdate,1,'无下条订单') over(partition by name order by orderdate) behinddate

from business

SQL的日常用法及常用面试

查询前20%时间的订单信息

ntile() over

select name,orderdate,cost, ntile(5) over(order by orderdate) sorted from business

SQL的日常用法及常用面试

irst_value(col)

select name,orderdate,cost,first_value(col) over(partition by name,month(orderdate) order by orderdate )

from business

--根据name，month(orderdate)分组，orderdate排序取最早的orderdate那条数据对应的col字段的值