7.统计UV、分组TopN

百里守约学编程

于 2022-12-17 19:32:22 发布

阅读量731

点赞数 1

CC 4.0 BY-SA版权

分类专栏： # HSQL练习文章标签： uv sql 数据库

本文链接：https://blog.youkuaiyun.com/YouMing_Li/article/details/128356297

HSQL练习专栏收录该内容

10 篇文章

订阅专栏

该博客介绍了如何使用SQL对京东店铺的访问日志数据进行分析，包括统计每个店铺的唯一访客数（UV）以及每个店铺访问次数最多的前3名访客信息。首先，通过`COUNT(DISTINCT user_id)`计算每个店铺的UV。然后，利用`WITH`子句和`ROW_NUMBER()`函数找出每个店铺访问次数最多的前三名访客，展示店铺名称、访客ID及访问次数。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

有50w个京东店铺，每个顾客访问任何一个店铺的任何一个商品时都会产生一条访问日志，访问日志存储的表名为Visit，访客的用户id为user_id，被访问的店铺名称为shop，数据如下：

在这里插入图片描述

请统计：

(1) 每个店铺的UV（访客数）

(2) 每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数

实现

(1) 每个店铺的UV统计，最简单的分组统计即可

select shop,
       count(distinct user_id) as uv
from test2 group by shop;

在这里插入图片描述

(2) 每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数

第一步：先统计每个店铺每个访客的访问次数

select user_id,shop,
       count(*) as cnt
from test2
group by user_id,shop
order by shop,user_id

在这里插入图片描述

接下来，我们对访客的访问次数进行倒序排序，并筛选访问次数top3的访客信息，嵌套子查询比较多，可读性不好,因此使用with as

with t1 as (
    select user_id,
           shop,
           count(*) as cnt
    from test2
    group by user_id, shop
    order by shop, user_id
),
     t2 as (
         select shop,
                user_id,
                cnt,
                row_number() over (partition by shop order by cnt desc) rn
         from t1
     )
select *from t2 where rn<=3 order by shop ,cnt desc;

在这里插入图片描述