当前位置:网站首页>SQL日常练习3-简单记录9问

SQL日常练习3-简单记录9问

2022-09-23 08:20:10我爱肉肉

1 统计每个用户累计访问

需求:
在这里插入图片描述
目标:

在这里插入图片描述
SQL:

select *,
	sum(sum1) over (partition by userId order by visitMonth) as num2 
(
select 
	userId, substr(visitDate,0,6) as visitMonth, sum(visitCount) as sum1
from 
	test1
group by
	userId, substr(visitDate,0,6)
) 
order by
	userId, visitDate;

备注partition by和group by用法区别问题:

partition by是开窗函数内使用sum等函数时,不会改变结果行数,如sum是逐次累加显示
而group by则改变了返回结果行数,如sum id,则每个id只有一个sum结果,不是逐次累加

2 统计店铺UV

在这里插入图片描述
问题1:
在这里插入图片描述
UV SQL:
方式2是用于对方式1 distinct的优化处理

在这里插入图片描述
问题2:
在这里插入图片描述
所有涉及TOP N问题,都可以用row_number函数排序取值来解决
在这里插入图片描述
方法2:用with as优化多层嵌套问题

在这里插入图片描述

3 指定条件下的数据筛选

在这里插入图片描述
问题:给出2017年2月新客数量(指2月之前没出现过的user_id数量)

思路:先求出每个用户user_id的第一次出现的dt时间(也就是group by后dt最小的),然后选择dt为2月的就是需要的结果

在这里插入图片描述

4 根据年龄段对观影次数进行排序

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
期望结果

在这里插入图片描述

思路:先查询log表计算每个用户的url次数,然后age表划分每个用户的年龄段,最后join并根据年龄段sum

SQL:

with ti as (
	select user_id,
			  count(url) as cnt,
	from log
	group by user_id
),
	t2 as (
		select user_id,
				  age,
				  case when age >= 0 and age <= 10 then "0-10"
				  		  when age >= 10 and age <= 20 then "10-20"
				  		  when age >= 20 and age <= 30 then "20-30"
				  end age_phase
		from age
),
	t3 as (
		select age_phase,
				  sum(cnt) sum1
		from t1 join t2 on t1.user_id = t2.user_id
		group by t2.age_phase
)

备注优化:大量的case when可以改成 concat( floor(age/10)*10,'-',floor(age/10+1)*10 ) as x

5 所有用户的活跃用户总数和平均年龄

在这里插入图片描述
期望结果:

在这里插入图片描述
思路:

1 所有用户的总数和平均年龄

在这里插入图片描述

2 活跃用户的总数和平均年龄 (活跃用户指连续两天访问的用户)

先打上rn标签如下图

在这里插入图片描述
在这里插入图片描述

打上标签rn

在这里插入图片描述

标签时间相减

在这里插入图片描述

相减为1则为连续的天数,连续>=2则为活跃用户

在这里插入图片描述

最后查询

在这里插入图片描述

6 所有用户十月份第一次购买商品金额

在这里插入图片描述

7 访问接口top10的IP地址

问题

在这里插入图片描述
SQl:

在这里插入图片描述

8 查询每个区组下充值额最大的账号

问题:

在这里插入图片描述
SQL:

在这里插入图片描述

9 查询各自区组money排名前十的账号

问题:

在这里插入图片描述
SQL:

在这里插入图片描述

原网站

版权声明
本文为[我爱肉肉]所创,转载请带上原文链接,感谢
https://blog.csdn.net/weixin_40503364/article/details/126532350

随机推荐