SQL面试题>面试题——奔驰SQL面试题>面试题
我们的表大致如下
CREATE TABLE signal_log(
vin STRING COMMENT'vehicle frame id',
signal_name STRING COMMENT'function name',
signal_value STRING COMMENT 'signal value' ,
ts BIGINT COMMENT'event timestamp',
dt STRING COMMENT'format yyyy-mm-dd'
)
下面给出了一些样例数据
说明
- 信号power_mode(驾驶模式: SPORT/NORMAL/ECO/COMFORT)2)
- 信号odometer_value (里程表示数: km)
- power mode上报的同一个ts，会有odometer value记录上报
- 信号记录有重复上报的情况
- ts 单位为秒
问题
- 对于每个车辆，统计每天行驶的距离及总共使用过的驾驶模式 (power_mode)个数
- 统计每个车辆，在不同驾驶模式下的总行驶的时间 (单位:秒)
- 计算每个车辆的驾驶偏好 (驾驶时间越长则表示越偏好;展示第一、第二偏好)
分析
- 这里给出的都是样例数据，ts 和我们平常看到的都不一样
- signal_value 存储的是不同信号上报上来的值，具体是数值还是字符串取决于signal_name，也就是说所有的信号值都放在一起了，我们处理的时候可能需要针对不同信号值单独处理
问题一 对于每个车辆，统计每天行驶的距离及总共使用过的驾驶模式
这个问题就很简单了
- 驾驶模式的信号是power_mode，我们过滤一下，直接统计即可
selectvin,dt,count(distinct signal_value) power_mode_cnt
fromsignal_log
wheresignal_name='power_mode'
group byvin,dt
- 每天行驶的距离我们只需要用当天最大的里程数减去最小的里程数即可
selectvin,dt,max(signal_value)-min(signal_value)
fromsignal_log
wheresignal_name='odometer_value '
group byvin,dt
这两个放在一起的话，我们可以这样写
selectvin,dt,count(distinct if(signal_name='power_mode',signal_value,"")) -1 as power_mode_cnt,max(if(signal_name='odo_value',signal_value,NULL))-min(if(signal_name='odo_value',signal_value,NULL))
fromlog
group byvin,dt
问题二 统计每个车辆，在不同驾驶模式下的总行驶的时间 (单位:秒)
我们上报上来的数据包含当前的驾驶模式和当前时间戳，我们可以把下次上报上来的时间减去当前时间的差作为这个驾驶模式的形式时间，很多人可能会说要不要判断下次上来的驾驶模式是不是和当前的相当呀
我们举个例子
SPORT TS1
SPORT TS2
SPORT TS3
NORMAL TS4
NORMAL TS5
SPORT TS6
如果相等作为条件，SPORT 的驾驶时长就是TS3-TS1，如果没有这个条件SPORT 的驾驶时长就是TS4-TS1
这里我们可以当作没有这个条件进行处理，因为传感器信号其实是很密集的，所以误差很小
那这个问题就很简单了，我们获取下一次上报的时间和当前时间做差，当成当前驾驶模式的形式时间
selectvin,power_mode,sum(time_diff) as total_seconds
from(selectvin,dt,ts as current_time,signal_value as power_modelead(ts,1,unix_timestamp()) over(partition by vin order by ts) next_time-ts as time_difffromsignal_logwheresignal_name='power_mode'
)tmp
group by vin,power_mode
问题三 计算每个车辆的驾驶偏好 (驾驶时间越长则表示越偏好;展示第一、第二偏好)
前面我们已经有了每种驾驶模式的驾驶时间，我们只需要按照驾驶时间排序即可
with tmp as
selectvin,power_mode,sum(time_diff) as total_seconds
from(selectvin,dt,ts as current_time,signal_value as power_modelead(ts,1,unix_timestamp()) over(partition by vin order by ts) next_time-ts as time_difffromlogwheresignal_name='power_mode'
)tmp
group by vin,power_mode
唯一需要注意的是，由于是按照每辆车每天的标准去计算，所以相当于分组内排序
select*
from(
selectvin,power_mode,total_secondsrow_number()over(partition by vin order by total_seconds desc) as rn
fromtmp
)t
wherern<=2
