疫情用户行为轨迹分析【美团、阿里】

问题描述

已知用户-核酸站点表t1和用户-商场扫码表t2，统计用户每天的行动轨迹。注意：商场扫码表中存在重复扫码的情况，取最新的数据。

-- t1
user_id    in_time                 out_time             stat_id
001        2022-05-20 15:31:21     null                 001
001        null                    2022-05-20 16:01:05  002
001        2022-05-20 18:02:21     null                 003
001        null                    2022-05-20 18:22:17  001
001        2022-05-20 20:39:27     null                 004
001        null                    2022-05-20 21:55:33  005
-- t2
user_id    market_id    scan_time
001        1001         2022-05-20 16:11:41
001        1001         2022-05-20 16:11:51
001        1001         2022-05-20 16:11:58
001        1002         2022-05-20 17:01:28
001        1003         2022-05-20 18:31:28
001        1003         2022-05-20 18:31:58

分析

经过对需求的分析，可以简化为 求每个用户每天去过哪些地方。其实就是按照用户和日期进行分组，然后合并用户去过的所有地即可（可能会重复）。

答案

select
  user_id,
  to_date(trace_time) as dt,
  concat_ws('->', collect_list(trace_id)) as trace
from
  (
    select
      user_id,
      trace_time,
      trace_id
    from
      (
        select
          user_id,
          in_time as trace_time,
          stat_id as trace_id
        from
          t1
        union all
        select
          user_id,
          out_time as trace_time,
          stat_id as trace_id
        from
          t1
        union all
        select
          user_id,
          max(scan_time) as trace_time,
          market_id as trace_id
        from
          t2
        group by user_id, market_id
      ) t
    order by
      trace_time
) t
where trace_time is not null
group by user_id, to_date(trace_time)

模拟连续随机数

问题描述

生成1-100的连续整数

分析

posexplode爆炸函数，不仅可以炸裂出数值，而且还附带索引

答案

select 
    id_start + pos as id
from (
    select 1 as id_start, 100 as id_end
) t
lateral view posexplode(split(id_end - id_start), ' ')) t as pos, val

两两相互认识的组合数【快手】

问题描述

已知用户-网吧表t，字段：网吧id、用户id、上线时间、下线时间

• 规则1：如果有两个用户在一家网吧的前后上下线时间在10分钟以内，则两人可能认识
• 规则2：如果这两个用户在三家以上网吧出现【规则1】的情况，则两人一定认识

wid  uid       ontime                    offtime
1    110001    2020-01-01 11:10:00       2020-01-01 11:15:00
1    110002    2020-01-01 12:10:00       2020-01-01 13:15:00
2    110001    2020-01-02 12:10:00       2020-01-02 12:30:00
2    110002    2020-01-02 12:52:00       2020-01-02 12:55:00
3    110001    2020-01-03 12:10:00       2020-01-03 12:30:00

分析

经过对需求的分析，就是要求两两相识的组合数。对于这种两两组合的题目一般需要先进行自关联，然后根据规则1和规则2对组合进行筛选即可

答案

select sum(flag) as cnt
from(
    select uuid, if(count(wid) >= 3, 1, 0) as flag
    from(
        select t0.wid as wid, concat_ws('', t0.uid, t1.uid) as uuid
        from t as t0     
        join t as t1 
        where 
            t0.wid = t1.wid 
            and t0.uid != t1.uid 
            and (abs(unix_timestamp(t0.ontime,'yyyy-MM-dd HH:mm:ss')-unix_timestamp(t1.ontime,'yyyy-MM-dd HH:mm:ss'))<600 or abs(unix_timestamp(t0.offtime,'yyyy-MM-dd HH:mm:ss')-unix_timestamp(t1.offtime,'yyyy-MM-dd HH:mm:ss'))<600)
    ) t
    group by uuid
) m