sql order by 降序_SQL面试题（二）

这些面试题使用了很多SQL窗口函数，对SQL窗口函数不太了解的，可以移步去看我之前更新的文章，那些例子可以让大家通俗易懂的学会SQL窗口函数。

https://zhuanlan.zhihu.com/p/150812199​zhuanlan.zhihu.com

（一）商品订单数据

现有一个订单表，含有以下几个字段：order_info(order_id,user_id,pay_amount,pay_time,goods_level_1,goods_level_2)，分别为订单ID、用户ID、成交金额、成交时间、一级类目，二级类目。

1、求最近7天内每一个类目下成交总金额排名前三的二级类目

解题思路：

• 先计算每一个类目下的成交总金额；
• 再在外层计算每个类目下的成交总金额的排名；
• 提取出排名前三的数据。

SELECT b.goods_level_1 AS '商品一级类目',
       b.goods_level_2 AS '商品二级类目',
       b.pay_amount AS '成交总金额',             
FROM
	(SELECT a.goods_level_1,
		a.goods_level_2,
		a.pay_amount,                        -- 根据成交总金额进行降序排序
		ROW_NUMBER() OVER(PARTITION BY a.goods_level_1 ORDER BY a.pay_amount DESC) AS 'rank'
	FROM
		(SELECT goods_level_1,
			goods_level_2,
			SUM(pay_amount) AS pay_amount
		FROM order_info
		WHERE DATEDIFF(NOW(),pay_time)<=7 -- 由于不知道pay_time是时间格式还是日期格式，这里按照时间格式来处理
		GROUP BY goods_level_1,goods_level_2) AS a -- 先计算每个类目下的金额总和
		) AS b
WHERE b.rank<=3;   

2、提取2019年8月1日-8月10日每天消费金额排名在101-195的用户ID

解题思路：

• 先获取8月1日-8月10日每天的消费总金额；
• 根据日期对消费总金额进行降序排序；
• 在where条件中获取排名在101和195之间的数据。

SELECT b.pay_date AS '订单日期',
       b.user_id,
       b.pay_amount AS '消费金额'
FROM
	(SELECT a.pay_date,
		a.user_id,
		a.pay_amount,
		ROW_NUMBER() OVER(PARTITION BY a.pay_date ORDER BY a.pay_amount DESC) AS 'rank'
	FROM
		(SELECT SUBSTR(pay_time,'%Y-%m-%d') as pay_date,
			user_id,
			SUM(pay_amount) AS pay_amount
		FROM order_info
		WHERE SUBSTR(pay_time,'%Y-%m-%d') BETWEEN '2019-08-01' AND '2019-08-10'
		GROUP BY SUBSTR(pay_time,'%Y-%m-%d')
，user_id) AS a
	) AS b
WHERE b.rank>=101 AND b.rank<=195;

（二）活动运营数据

有订单详情表和活动报名表

• 订单详情表order_info(user_id,pay_amount,pay_time）
• 活动报名表act_apply(act_id,user_id,act_time)

1、统计每个活动对应所有用户在报名后产生的总订单金额、总订单数

解题思路：

• 先将两个表连接起来，这里使用订单详情表为左表；
• 题目强调是报名后产生的订单数和金额，所以在外层的where需要限定条件；
• 统计每个活动，所以需要根据活动ID进行分组。

SELECT b.act_id AS '活动编号',
       SUM(b.pay_amount) AS '总订单金额',
       COUNT(a.*) AS '总订单数'
FROM
      (SELECT * FROM order_info) AS a
 LEFT JOIN
      (SELECT * FROM act_apply) AS b ON a.user_id=b.user_id
WHERE a.pay_time>b.act_time
GROUP BY b.act_id;

2、统计每个活动从开始到当天（考试日）平均每天产生的订单数，活动开始时间定义为用户报名时间

解题思路：

• 活动开始时间定义为用户的报名时间，需要找到活动中用户最早报名的时间，这里可以使用min() over()；
• 计算最早用户到活动当天的时间间隔，用datediff()函数

SELECT b.act_id,
       COUNT(*)/DATEDIFF(NOW(),b.start_time) AS '平均订单数'
FROM
	(SELECT * FROM order_info) AS a
LEFT JOIN
	(SELECT *,
	MIN(act_time) OVER(PARTITION BY user_id ORDER BY act_time) AS 'start_time'
	FROM act_apply) AS b ON a.user_id=b.user_id
WHERE a.pay_time>b.start_time
GROUP BY b.act_id;

（三）用户行为路径

有用户行为表tracking_log（user_id,operate_id,log_time），其中字段的含义分别为用户ID、操作ID和操作时间。

1、统计每天符合以下条件的用户数：A操作之后是B操作，AB操作必须相邻

解题思路：

• 先根据用户ID和日期，用LEAD()窗口函数向后获取下一步的步骤；
• AB必须相邻，则表明当前的步骤为A，而下一个步骤为B，即A向下移的步骤是B；
• “每天”，即根据日期进行分组。

SELECT a.log_date,
       COUNT(DISTINCT a.user_id)
FROM
       (SELECT user_id,
		 operate_id,
		 DATE_FORMAT(log_time,'%Y-%m-%d') AS log_date,
		 LEAD(operate_id,1,NULL) OVER(PARTITION BY user_id,DATE_FORMAT(log_time,'%Y-%m-%d') ORDER BY log_time) AS 'next_operate'
	FROM tracking_log) AS a			 
WHERE a.operate_id=A AND b.next_operate=B
GROUP BY a.log_date;

2、统计用户行为序列为A-B-D的用户数

其中A-B之间可以有任何其他浏览记录（如C、E等），B-D之间除了C记录可以有任何其他记录（如A、E等）

关于常见字符串函数，如concat()、group_concat()和like()函数可以见我之前更新的文章。

https://zhuanlan.zhihu.com/p/148515639​zhuanlan.zhihu.com

解题思路：

• 这里可以使用group_concat()函数将查询结果进行连接；
• 根据题目的意思，A-B之间可以有任何记录，可以使用LIKE和通配符%来匹配，A-B之间不可以出现C，则使用NOT LIKE 和通配符来得到。

SELECT COUNT(DISTINCT a.user_id)
FROM
	(SELECT user_id,
		GROUP_CONCAT(operate_id ORDER BY log_time) AS 'user_behavior'
	 GROUP BY user_id) AS a
WHERE a.user_behavior LIKE (A%B%D%) AND a.a.user_behavior NOT LIKE (A%B%C%D%);