大数据面试系列之手写HQL

大数据真有意思 2020-06-08

333

点击关注上方“知了小巷”，

设为“置顶或星标”，第一时间送达干货。

大数据面试系列之手写HQL

</> 找出所有科目成绩都大于某一学科平均成绩的学生

我们现在有某班级学生的成绩表（学生ID，学科ID，分数）

表结构：uid, subject_id, score

需求：找出所有科目成绩都大于某一学科平均成绩的学生

uid	学生ID
subject_id	学科ID
score	分数

数据集如下

10010190

10010290

10010390

10020185

10020285

10020370

10030170

10030270

10030385

思路：

比如01的平均成绩(90 + 85 + 70) 3；02...

然后学生ID1001的90与01的平均成绩进行比较，依次类推...

比较的结果都大于平均值的学生即为所求。

1.建表语句

create table score (
    uid string,
    subject_id string,
    score int
) row format delimited fields terminated by '\t';

2.求出每个学科平均成绩

select
    uid,
    score,
    avg(score) over(partition by subject_id) avg_score
from score; -- t1

3.根据是否大于平均成绩记录flag，大于则记为0否则记为1

select
    uid,
    if(score > avg_score, 0, 1) flag
from t1; -- t2

4.根据学生id进行分组统计flag的和，和为0则是所有学科都大于平均成绩

select
    uid
from t2
group by uid having sum(flag) = 0;

5.最终SQL

select
    uid
from (
  select
      uid,
      if(score > avg_score, 0, 1) flag
  from (
    select
        uid,
        score,
        avg(score) over(partition by subject_id) avg_score
    from score
  ) t1
) t2
group by uid having sum(flag) = 0;

</> 统计每个用户的按月累积访问次数

我们现有如下的用户访问数据（用户ID，访问日期，访问次数）

表结构：user_id, visit_date, visit_count

需求：统计出每个用户的按月累积访问次数，结果如下表所示：

用户id 月份小计累积

u01 2020-01 11 11

u01 2020-02 12 23

u02 2020-01 12 12

u03 2020-01 8 8

u04 2020-01 3 3

user_id	用户ID
visit_date	访问日期
visit_count	访问次数

数据集如下

u01 2020/1/21 5

u02 2020/1/23 6

u03 2020/1/22 8

u04 2020/1/20 3

u01 2020/1/23 6

u01 2020/2/21 8

u02 2020/1/23 6

u01 2020/2/22 4

1.创建表

create table action (
    user_id string,
    visit_date string,
    visit_count int
) row format delimited fields terminated by "\t";

2.修改数据格式

select
     user_id,
     date_format(regexp_replace(visit_date, '/', '-'), 'yyyy-MM') mn,
     visit_count
from action; -- t1

3.计算每人单月访问量

select
    user_id,
    mn,
    sum(visit_count) mn_count
from t1 group by user_id, mn; -- t2

4.按月累计访问量

select
    user_id,
    mn,
    mn_count,
    sum(mn_count) over(partition by user_id order by mn)
from t2;

5.最终SQL

select
    user_id,
    mn,
    mn_count,
    sum(mn_count) over(partition by user_id order by mn)
from (
    select
        user_id,
        mn,
        sum(visit_count) mn_count
    from (
         select
             user_id,
             date_format(regexp_replace(visit_date, '/', '-'), 'yyyy-MM') mn,
             visit_count
         from action
    ) t1
    group by user_id, mn
) t2;

</> 统计店铺UV和店铺访问次数Top3的访客信息

我们现在有50W个店铺，每个访客访问任何一个店铺的任何一个商品时都会产生一条访问日志，访问日志存储的表名为visit，访客的用户id为user_id，被访问的店铺名称为shop。

表结构：user_id, shop

需求：统计

1.每个店铺的UV（访客数）

2.每个店铺访问次数Top3的访客信息。输出店铺名称、访客id、访问次数。

user_id	用户id
shop	店铺名称

数据集如下

u1a

u2b

u1b

u1a

u3c

u4b

u1a

u2c

u5b

u4b

u6c

u2c

u1b

u2a

u3a

u5a

1.建表

create table visit (
    user_id string,
    shop string
) row format delimited fields terminated by '\t';

2.每个店铺的UV（访客数）

select shop, count(distinct user_id) from visit group by shop;

3.每个店铺访问次数Top3的访客信息。输出店铺名称、访客id、访问次数。

查询每个店铺被每个用户访问次数

select 
    shop,
    user_id,
    count(*) ct
from visit
group by shop, user_id; -- t1

计算每个店铺被用户访问次数排名

select 
    shop, 
    user_id, 
    ct, 
    rank() over(partition by shop order by ct) rk
from t1; -- t2

取每个店铺排名前3的

select 
    shop,
    user_id,
    ct
from t2
where rk <= 3;

最终SQL

select 
  shop,
  user_id,
  ct
from (
  select 
    shop,
    user_id,
    ct,
    rank() over(partition by shop order by ct) rk
  from (
    select 
      shop,
      user_id,
      count(*) ct
    from visit
    group by shop, user_id
  ) t1
) t2
where rk <= 3;

</> 统计每月订单数、用户数和总成交金额；某月份新增客户数

我们现在有一个订单表

表结构：date, order_id, user_id, amount

需求是统计：

1.给出2020年每个月的订单数、用户数、总成交金额。

2.给出2020年1月的新客数(指在1月才有第一笔订单)

数据样例: 2020-01-01, 10029028, 1000003251, 33.57

date	下单日期
order_id	订单ID
user_id	用户ID
amount	订单金额

0.建表

create table order_tab (
    dt string,
    order_id string,
    user_id string,
    amount decimal(10,2)
) row format delimited fields terminated by '\t';

1.给出 2020年每个月的订单数、用户数、总成交金额。

select
   date_format(dt,'yyyy-MM'),
   count(order_id),
   count(distinct user_id),
   sum(amount)
from order_tab
where date_format(dt, 'yyyy') = '2020'
group by date_format(dt, 'yyyy-MM');

2.给出2020年1月的新客数(指在1月才有第一笔订单)

select
   count(user_id)
from order_tab
group by user_id
having date_format(min(dt), 'yyyy-MM') = '2020-01';

</> 统计所有用户和活跃用户的总数及平均年龄

我们现在有如下日志

表结构：dt, user_id, age

需求：所有用户和活跃用户的总数及平均年龄

（活跃用户指连续两天都有访问记录的用户）

dt	日期
user_id	用户ID
age	年龄

数据集

2019-02-11,test_1,23

2019-02-11,test_2,19

2019-02-11,test_3,39

2019-02-11,test_1,23

2019-02-11,test_3,39

2019-02-11,test_1,23

2019-02-12,test_2,19

2019-02-13,test_1,23

2019-02-15,test_2,19

2019-02-16,test_2,19

1.建表

create table user_age (
    dt string,
    user_id string,
    age int
) row format delimited fields terminated by ',';

2.按照日期以及用户分组，按照日期排序并给出排名

select
   dt,
   user_id,
   min(age) age,
   rank() over(partition by user_id order by dt) rk
from user_age
group by dt, user_id; -- t1

3.计算日期及排名的差值

select
   user_id,
   age,
   date_sub(dt,rk) flag
from t1; -- t2

4.过滤出差值大于等于2的，即为连续两天活跃的用户

select
   user_id,
   min(age) age
from t2
group by user_id, flag
having count(*) >= 2; -- t3

5.对数据进行去重处理（一个用户可以在两个不同的时间点连续登录），例如：a用户在1月10号1月11号以及1月20号和1月21号4天登录。

select
   user_id,
   min(age) age
from t3
group by user_id; -- t4

6.计算活跃用户（两天连续有访问）的人数以及平均年龄

select
   count(*) ct,
   cast(sum(age)  count(*) as decimal(10, 2))
from t4;

7.对全量数据集进行按照用户去重

select
   user_id,
   min(age) age 
from user_age 
group by user_id; -- t5

8.计算所有用户的数量以及平均年龄

select
   count(*) user_count,
   cast((sum(age)/count(*)) as decimal(10, 1)) 
from t5;

9.将第5步以及第7步两个数据集进行union all操作

select
   0 user_total_count,
   0 user_total_avg_age,
   count(*) twice_count,
   cast(sum(age)  count(*) as decimal(10, 2)) twice_count_avg_age
from (
  select
    user_id,
       min(age) age
  from (
    select
         user_id,
         min(age) age
    from (
        select
           user_id,
           age,
           date_sub(dt,rk) flag
      from (
          select
               dt,
               user_id,
               min(age) age,
               rank() over(partition by user_id order by dt) rk
          from user_age
          group by dt, user_id
        ) t1
      ) t2
    group by user_id, flag
    having count(*) >= 2
  ) t3
  group by user_id 
) t4
union all
select
   count(*) user_total_count,
   cast((sum(age)  count(*)) as decimal(10, 1)),
   0 twice_count,
   0 twice_count_avg_age
from (
  select
    user_id,
    min(age) age 
  from user_age 
  group by user_id
) t5; -- t6

</> 统计用户在某年某月份第一次购买商品的金额

我们现在有一份商品购买记录数据

表结构：user_id, money, payment_time, order_id

需求：统计今年1月份第一次购买商品的金额

user_id	购买用户的用户ID
money	购买金额
payment_time	购买时间
order_id	订单ID

1.建表

create table order_table (
    user_id string,
    money int,
    payment_time string,
    order_id string)
row format delimited fields terminated by '\t';

2.查询出1月份购买用户的最早购买商品的时间

select
   user_id,
   min(payment_time) payment_time
from order_table
where date_format(payment_time, 'yyyy-MM') = '2020-01'
group by user_id; -- t1

3.与原表进行关联查询，查询出具体金额

select
   t1.user_id,
   t1.payment_time,
   od.money
from t1 join order_table od on t1.user_id = od.user_id and t1.payment_time = od.payment_time;

4.最终SQL

select
  t1.user_id,
  t1.payment_time,
  od.money
from (
  select
    user_id,
    min(payment_time) payment_time
  from order_table
  where date_format(payment_time, 'yyyy-MM') = '2020-01'
  group by user_id
) t1 join order_table od on t1.user_id = od.user_id and t1.payment_time = od.payment_time;

【END】《剑指大数据面试》视频资源

识别下面二维码，回复001获取资源链接

面试大数据

文章转载自大数据真有意思，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

大数据面试系列之手写HQL

评论