b2b 网站方案/媒体资源

1.最近7天连续3天登陆用户，字段，id，date（已去重）

思路：

1. lag对时间开窗（注意时间得转换为时间戳（int类型才可以添加后续条件），跳行为2（连续3天），前2行没有值的，默认为0

2. 转化时间字段为时间戳减去它上2条的那条数据的时间戳，得出是否为连续登陆3天的差值（第三天减去第一天的时间戳为(2 * 24 * 60 * 60）

3. Where 过滤出最近7天的数据

4. 对id进行分组，过滤差值为2 * 24 * 60 * 60的数据

5. 答案：

select id
from (select id, date, (unix_timestamp(date) - lag(unix_timestamp(date), 2, 0) over (partition by id order by date)) lofrom toms) t1
where date between date_sub(current_date(), interval 7 day) and current_date()
group by id
having min(lo) = 2 * 24 * 60 * 60;

2.字段，id，send_time（datetime类型），soc，需求：soc大于100并且持续5s会被告警，求出所有数据（告警和不告警），输出字段，id，send_time，soc，持续时间，告警状态（0：不告警，1：告警）

思路：

• 判断soc是否大于100，如果是的给它一个自增项（用于对告警的数据进行分组），否则取0，得到字段flag

• 对flag字段进行求出截止到当前的最大值，得到字段flag2，用于得到一整段分组的告警组

• 对flag2字段进行开窗分组求充电时间最小值，得到字段over_time

• 时间相减判断，如果大于5状态置为1

答案：

select charge_id,soc,daq_time,timestampdiff(second, over_time, daq_time)                stay_length,if(timestampdiff(second, over_time, daq_time) >= 5, 1, 0) status
from (select *, min(daq_time) over (partition by flag2 order by daq_time) over_timefrom (select charge_id,soc,daq_time,flag,max(flag)over (partition by charge_id order by daq_time rows between unbounded preceding and current row ) flag2from (select charge_id,soc,daq_time,if(abs(soc) > 100, 0, row_number() over (partition by charge_id order by daq_time )) flagfrom demo) t1) t2) t3;