sq面试题04

-- 已知一个表 STG.ORDER ，有如下字段 :Date ， Order_id ， User_id ， amount 。

-- 数据样例 :2017-01-01,10029028,1000003251,33.57 。

-- 请给出 sql 进行统计 :

--(1) 给出 2017 年每个月的订单数、用户数、总成交金额。

--(2) 给出 2017 年 11 月的新客数 ( 指在 11 月才有第一笔订单 )

数据准备：

CREATE TABLE shopping2 (
dt varchar(30),
order_id varchar(30),
user_id varchar(30),
amount int )
;

insert into shopping2 values
('2017-01-01','10029028','1000003251',33.57)
,('2017-01-01','10029029','1000003251',33.57)
,('2017-01-01','100290288','1000003252',33.57)
,('2017-02-02','10029088','1000003251',33.57)
,('2017-02-02','100290281','1000003251',33.57)
,('2017-02-02','100290282','1000003253',33.57)
,('2017-11-02','10290282','100003253',234)
,('2017-11-01','102902810','100003253',234)
,('2018-11-02','10290284','100003243',234)

这道题实际上是两个问题，第一个问题：

--(1)给出 2017年每个月的订单数、用户数、总成交金额。

分别站在订单数、用户数、金额三个维度对数据经行聚合（通过时间），但是要注意对用户进行聚合时要对重复的用户进行去重。

sql实现：

select substr(dt,1,7)
      ,count(order_id)
      ,count(distinct(user_id))
      ,sum(amount) 
from shopping2 where substr(dt,1,4)='2017' group by 1;

实现效果：

 02.第二个问题--给出2017年11月的新客数(指在11月才有第一笔订单)

两点需要注意的，一是2017年11月出现的新客，第二点是第一笔订单。

方法一通过子查询来实现。实现sql:

select * from shopping2 where substr(dt,1,7) = '2017-11' and user_id not in (
select user_id from shopping2 where substr(dt,1,7)<'2017-11'
) order by dt limit 1

实现效果：

方法二：通过开窗函数来实现

sql:

select t1.dt,t1.order_id,t1.user_id,t1.amount from (
select dt,order_id,user_id,amount,row_number() over(partition by user_id order by dt) rn from  shopping2  where substr(dt,1,7) = '2017-11'
) t1 where t1.rn = 1

 用开窗也要嵌套一层子查询，通过过滤子查询中的rn=1,来获取每个客户201711的第一个订单。实现效果和方法一。