SQL TJM公司3月31日的面试题

 一、面试题说明：

要求：

不限语言，实现后续数据自动化输出

题目：

如果左右侧空间的差小于40，做成左右侧空间小的那个数填入应该做的1（应该做的2填0）
如果连续的一组左右侧空间的差大于40，且这组对应的长度合计小于15，做成左右侧空间小的那个数填入应该做的1（应该做的2填0）
如果连续的一组左右侧空间的差大于40，且这组对应的长度合计大于等于15，做成一侧大、一侧小（应该做的1和2都不为0），一侧大的数为这组左侧或右侧空间里大的数中小的值，一侧小的数为这组左侧或右侧空间里小数中小的值

二、解题思路：

1、原题没有说明’左右侧空间的差等于40的情况，就采用模糊分类处理

2、因为结果实现还比较符合开窗函数特性，故用SQL实现

三、冲突：

1、关于用SQL计算筛出符合条件的组长度合计，也尝试求出组上限序号和下限序号用where筛表子查询出的结果没有成功，主要这道题的时间大量花费在读题和数据导入上，节约时间暂手动rank组了，为partition by做准备

2、将数据导入SQL时，csv-utf8也不支持，只好直翻字段名，数据字典如下：

四、代码实现：

(select datano,datalength,dataleft,dataright,
if(dataleft<dataright,dataleft,dataright) as done1,
0 as done2
from data1
where ranklen is null)
union
(select datano,datalength,dataleft,dataright,
min(if(dataleft<dataright,dataleft,dataright)) over(partition by ranklen order by datano asc rows between unbounded preceding and unbounded following) as done1,
sum(0) over() as done2
from
(select *
from(
select *,sum(datalength) over(partition by ranklen order by datano asc rows between unbounded preceding and unbounded following) as sumlen
from data1
where ranklen is not null) as t
where sumlen<15
) as t1)
union
(select datano,datalength,dataleft,dataright,
min(if(dataleft>=dataright,dataleft,dataright)) over(partition by ranklen order by datano asc rows between unbounded preceding and unbounded following) as done1,
min(if(dataleft<dataright,dataleft,dataright)) over(partition by ranklen order by datano asc rows between unbounded preceding and unbounded following) as done2
from
(select *
from(
select *,sum(datalength) over(partition by ranklen order by datano asc rows between unbounded preceding and unbounded following) as sumlen
from data1
where ranklen is not null) as t2
where sumlen>=15
) as t3)
order by datano asc;