统计每个url访问的topN用户以及访问次数(sql)

一、题目如下

有一张表t1，假设有以下三个字段,代表某个userid在某一天访问了这个url：

                               userid   url  event_day

q1:20210725这一天每个url的pv／uv

q2: 20210725这一天每个url访问的top10客户以及访问次数

输出：url, userid,pv，排名

q3: 20210725这一天的7日留存

表如下：

1.对于问题1

pv：每个url的总访问量   uv:每个url的访问用户量

pv：

SELECT COUNT(*), url
FROM user_url
where event_day = '2021-07-25'
GROUP BY url

uv:

SELECT COUNT(DISTINCT userid) as userCount, url
FROM user_url
where event_day = '2021-07-25'
GROUP BY url

2.对于问题2

难点在于两处：

1.如果弄出每个ur的每个客户访问次数

2.提取出前10访问量

对于难点一：

我们采用Group By A,B两个字段一起分组。我们对用户和url一起分组，然后计算条数就知道每个用户的每个url访问次数

SELECT count(*) as countUrl, userid, url 
FROM `user_url`
where event_day = '2021-07-25'
GROUP BY userid, url

对于难点二：

怎么提取出每个url的客户方面量的前10。 因为例子有限，所以提取出前1。

SELECT * from (
SELECT count(*) as countUrl, userid, url 
FROM `user_url`
where event_day = '2021-07-25'
GROUP BY userid, url
) as t1
where 1 >(
SELECT COUNT(*) FROM(
SELECT count(*) as countUrl, userid, url 
FROM `user_url`
where event_day = '2021-07-25'
GROUP BY userid, url
)as t2
where t1.url = t2.url  
AND t1.countUrl < t2.countUrl
)

解释下：

 其中两个表已经用了t1.url = t2.url连接，因此用C来举例。

一定要明显那个是主表，那个是副表。t1是主表，t2是副表。因此t1的每个数据去和t2的所有数据对比。才会有下图的解释。 当t1取不同值的时候，去与t2比较，看满足条件的条数有多少，就可以找到排序前n的问题(不用OrderBy找排序)

所以就出来了。 把每组的前n的提取出来。 主要思路是采用主表与副表的比较，看比较出来的条数，就可以确定排名。

还有一个更简单的方法，利用Parition By 与窗口函数结合。

SELECT r2.userid, r2.url, r2.countUrl
FROM
(
SELECT r1.userid, r1.url,r1.countUrl,
DENSE_RANK() OVER (PARTITION BY url ORDER BY countUrl DESC) rankURL
FROM(
SELECT count(*) as countUrl, userid, url 
FROM `user_url`
where event_day = '2021-07-25'
GROUP BY userid, url
) as r1
)as r2
WHERE r2.rankURL < 2

可能一大段下来有些不好消耗，我们一段一段来，默认我们每个url的每个用户访问量都已知哈

先来这段：

SELECT r1.userid, r1.url,r1.countUrl,
DENSE_RANK() OVER (PARTITION BY url ORDER BY countUrl DESC) rankURL
FROM(
SELECT count(*) as countUrl, userid, url 
FROM `user_url`
where event_day = '2021-07-25'
GROUP BY userid, url
) as r1

其中的PARITION BY URL 非常有用，对每组进行分开(可以保留下每组的数据) ，group by 只能提取出聚合后的数据(只有一条)。

PARITION BY 与RANK()系列的函数函数堪称绝配！！！直接在分组里进行统计前几名，然后在通过这个排序的序号进行筛选。

窗口函数有如下三种，分别排序形式：

RANK(): 1,1,1，4

DENSE_RANK():1，1，1，2

ROW_NUMBER():1，2，3，4

这里就根据自己的情况来选择排序序号了

我题目中选择的第二种，因为对于不同URL没有存在相同的，因此没有显示出效果。

然后把最大的晒出来， rankURL<2的

SELECT r2.userid, r2.url, r2.countUrl
FROM
(
SELECT r1.userid, r1.url,r1.countUrl,
DENSE_RANK() OVER (PARTITION BY url ORDER BY countUrl DESC) rankURL
FROM(
SELECT count(*) as countUrl, userid, url 
FROM `user_url`
where event_day = '2021-07-25'
GROUP BY userid, url
) as r1
)as r2
WHERE r2.rankURL < 2

 

这样就把排名最前的筛选出来了。

3.对于问题3

select DISTINCT userid 
from user_url
where event_day >= '2021-07-18'
AND event_day <= '2021-07-25'