大数据笔试之SQL必看篇（电商场景）

大家好，我是老兵。

本期继续为大家介绍大数据面试中常见的SQL真题，本期SQL系列难度为中等，可与第一期面试SQL文章对照观看。

原创好文！大数据笔试之SQL必看篇（初试牛刀）

文章旨在帮助大家抱团取暖，希望对小伙伴笔试会有所帮助。

1 统计出每个用户的月累积访问次数

题目介绍

页面用户行为日志中记录用户id、访问时间、访问次数字段，统计日志中每个用户的累积访问次数，数据示意如下:

原始数据

uid	visitDate	visitCount
001	2022-01-01	5
001	2022-01-03	8
002	2022-12-01	5
002	2022-10-02	4
002	2022-11-03	7

结果数据

uid	月份	小计	月累计
001	2022-01	11	11
001	2022-02	13	24
002	2022-01	10	11
001	2022-02	13	23

题目分析

月累计是企业实际生产场景中常见指标，一般结合多维度进行月、周统计分析，构建指标存于宽表。

通过分析可知累计访问次数包含用户每月份以及各月份累计，这里使用sum聚合函数 + 开窗函数来计算。

解题方法

1) 实现思路

• 计算每人单月访问量（group by uid/月份）

• 按月累计访问量（sum() over()）

• 统计每用户每月份访问和累计访问

2）SQL代码

select
    userId,
    mn,
    mn_count,
    sum(mn_count) over(partition by uid order by mn)
from 
(   select
        uid,
        mn,
        sum(visitCount) mn_count
    from
         (select
             uid,
             date_format(visitDate,'yyyy-MM') mn,
             visitCount
         from
             action)t1
group by uid,mn)t2;

2 统计每个店铺访问次数top3的访客信息

题目介绍

顾客访问日志中记录用户id、访问商铺shop字段，计算每个店铺的UV、每个店铺访问次数top3的访客信息（店铺名称、访客id、访问次数）。数据示意如下:

原始数据

uid	shop
001	a
001	b
002	a
002	b
003	a

结果数据

shop	uv
a	12
b	13

shop	uid	count
a	001	3
b	0011	4

题目分析

电商场景常见分析指标，通过统计店铺访问uv数和top信息，方便运营人员实时监控店铺访问详情。

其中uv为店铺访问去重用户、top信息需要进行开窗排序函数，对店铺访问次数排序。

解题方法

1）实现思路

• group by 分组统计每个店铺的UV

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

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

• 取每个店铺排名前3信息

2）SQL代码

## 店铺UV统计
select shop,count(distinct user_id) from visit group by shop;

## Top3商铺访问信息
select 
  shop,
  uid,
  ct
from
(select 
    shop,
    uid,
    ct,
    rank() over(partition by shop order by ct) rk
  from 
  (select 
      shop,
      uid,
      count(*) ct
   from visit
   group by 
     shop,
     uid)t1
)t2
where rk<=3;

3 计算月订单/用户/成交额及新客数

题目介绍

订单业务数据，有订单时间、订单id、用户id、成交额等字段，计算每月的订单数、用户数、总成交金额；12月的新客数，数据示意如下:

原始数据

date	order_id	uid	amount
2022-11-01	10029028	001	23.67
2022-12-02	10029027	001	45.23

结果数据

月份	订单总数	用户总数	成交额
2022-11	11	23	100
2022-12	12	24	200

12月新客
5

题目分析

电商场景常见统计指标之一，常见于宽表层月累计指标。其中月统计指标采用分组聚合函数；12月新客数表示指在12月才有第一笔订单.

解题方法

1）实现思路

• 根据月份聚合每个月的订单数、用户数、总成交金额

• 根据用户聚合用户，并过滤分组内最小月份为2022-12

2）SQL代码

## 统计每个月的订单数、用户数、总成交金额
select
   date_format(dt,'yyyy-MM'),
   count(order_id),
   count(distinct uid),
   sum(amount)
from
   order_table
where
   date_format(dt,'yyyy')='2017'
group by
   date_format(dt,'yyyy-MM');

## 统计12月新客数
select
   count(uid)
from
   order_table
group by
   uid
having
   date_format(min(dt),'yyyy-MM')='2022-12';

4 计算11月份首次购买商品的金额

题目介绍

用户商品交易信息表中记录uid、金额、支付时间、订单id字段，计算所有用户中在今年11月份第一次购买商品的信息，数据示意如下:

原始数据

uid	money	paymenttime	orderid
001	110	2022-11-01	01
001	119	2022-11-02	01

结果数据

uid	paymenttime	money
001	2022-11-01	122

题目分析

使用用户id分组聚合计算用户的最小购买时间，自关联订单表获取支付金额。

解题方法

1）实现思路

• 查询11月份用户最小购买时间

• 自关联获取用户11月份首次购买时间和金额

2）SQL代码

select
   t1.uid,
   t1.paymenttime,
   od.money
from
  (select
     uid,
     min(paymenttime) paymenttime
  from
     order_table
  where
     date_format(paymenttime,'yyyy-MM')='2017-10'
  group by
   uid
)t1
join
   order_table od
on
   t1.uid=od.uid
   and
   t1.paymenttime=od.paymenttime

5 计算退货率小于50%的商品信息

题目介绍

商品用户行为记录：uid、商品id、时间、是否点击、是否加购、是否支付、是否退货字段，计算2022年10月每个退货率小于50%的商品各项指标（商品点击率、加购率、成单率），数据示意如下:

原始数据

uid	p_id	event_time	if_click	if_cart	if_payment	if_refund
001	1001	2022-10-01 10:00:00	0	0	0	0
002	1001	2022-10-01 10:00:00	1	0	1	0
002	1001	2022-10-01 10:00:00	1	0	1	1

结果数据

p_id	ctr	cart_rate	payment_rate	refund_rate
1001	0.709	0.900	0.765	0.333
1002	0.801	0.678	0.675	0.122

题目分析

2022年10月每个有展示记录的退货率不大于0.5的商品各项指标,其中商品点展比=点击数÷展示数；加购率=加购数÷点击数；成单率=付款数÷加购数；退货率=退款数÷付款数，各项指标保留3位小数，并按商品ID升序排序

解题方法

1）实现思路

• 筛选时间窗内的记录并按商品ID分组

• 统计各种计数：展示数、点击数、加购数、付款数、退款数

• 计算各种指标率：点击率、加购率、付款率、退款率

• 结果round保留三位小数

2）SQL代码

select product_id, round(click_cnt/show_cnt, 3) as ctr,
    round(if(click_cnt>0, cart_cnt/click_cnt, 0), 3) as cart_rate,
    round(if(cart_cnt>0, payment_cnt/cart_cnt, 0), 3) as payment_rate,
    round(if(payment_cnt>0, refund_cnt/payment_cnt, 0), 3) as refund_rate
from (
    select p_id, COUNT(1) as show_cnt,
        sum(if_click) as click_cnt,
        sum(if_cart) as cart_cnt,
        sum(if_payment) as payment_cnt,
        sum(if_refund) as refund_cnt
    from tb_user_table
    where date_format(event_time, '%Y%m') = '202210'
    group by p_id
) t
where
  payment_cnt = 0 
  or refund_cnt/payment_cnt <= 0.5
order by p_id;