一个sql请教

zrjjokin
  • 66

问一个查询场景的sql应该怎么写
背景:
会有一张表记录每个用户浏览过一些内容(带id)
需求
现在要计算出每篇文章被看哪些人看过,然后这些人又看了其他的哪些文章,浏览次数取top
现在要计算那些看过同一篇内容的人中浏览次数最多的几篇内容,大概sql是这样

select * from
(
select contentId,count(*) as pv
from t 
where uid in (
select uid
from t 
where t.contentid = ?
)
group by contentId
)order by pv desc
limit n

上面的sql只能计算某一条内容的数据
我现在需要计算每篇文章,应该怎么弄呢

可能我表达的不是很明确
我的表结构假设是【contentID | uID】我以一个内容举例,一个内容会对应一个用户集合,代表这些用户浏览过该内容,我先是查找这个用户集合,然后这些用户中每个用户又浏览了其他的内容(也是利用那张初始表),我想查询出来的这些内容做一个计算,计算出出现次数最高的那几条内容,代表和最初那条内容关联度最高的内容列表。上面的逻辑过程是对一个单条内容的计算,我现在想对每条内容计算同样的数据,应该如何处理

逻辑流程图差不多如下图
image.png

回复
阅读 500
3 个回答
✓ 已被采纳
  • 这种需求其实还是用每个界面ID定时异步任务去统计比较好,如果非要用sql一把梭,量大了数据
    库扛不住,得上大数据方案,不能用线上mysql。
  • 如果非要这么做,酱紫。(我没测过)

    -- 页面访问日志表
    CREATE TABLE `read_log` (
    `content_id` int,
    `uid` int
    )                          
  
-- 每个页面的访问用户访问过的页面在这些用户下的访问次数统计
 select
        l.content_id as l_content_id, -- 每个需要统计的页面ID,
           --      l.uid_list    l.uid_list as '访问过 每个需要统计的页面ID 的用户组',
           t.content_id as t_content_id, -- 访问过 每个需要统计的页面ID 的用户组 仿问过的需要统计pv的页面ID
                                  count(t.uid) as pv
                           from read_log t
                                    join (
                               select content_id, group_concat(distinct uid, '') as uid_list
                               from read_log
                               group by content_id
                           ) l -- 找出每个页面的用户列表
                                        on find_in_set(t.uid, l.uid_list)
                           group by l.content_id, t.content_id
                           order by l.content_id, pv desc;
                           

如果排名后只取前3条数据,就外面还需要套一层,比较复杂


select
l_content_id,
       t_content_id,
       pv
from (
                  select data.*,
#        data.l_content_id = @last_data_id,
                         case when data.l_content_id = @last_data_id then @rank := @rank + 1 else @rank := 1 end,
                         @rank as r,
                         @last_data_id := data.l_content_id
                  from (
                           select l.content_id as l_content_id,
                                  --   l.uid_list
                                  t.content_id as t_content_id,
                                  count(t.uid) as pv
                           from read_log t
                                    join (
                               select content_id, group_concat(distinct uid, '') as uid_list
                               from read_log
                               group by content_id
                           ) l on find_in_set(t.uid, l.uid_list)
                           group by l.content_id, t.content_id
                           order by l.content_id, pv desc
                       ) data,
                       (select @last_data_id := null, @rank := 0) tmp
              )a where r<4;

我反正没看出来你那个select uid 是啥骚操作
你直接用uid和contentid 分组不就得了?

image.png

原始数据,只有用户1看了1,
文章2,2看了两次,2看了两次,1看了一次,文章3,1和2各看了一次,
你这按照这两个分组,就统计出来了 - -
image.png

select * from (select contentId, uid,count(t.uid) as pv from t group by t.contentId
union all
select contentId,uid,count(contentId) as pv from t group by t.uid) as jk order by jk.pv desc

你要的是这种效果?不知道我理解对你的需求没有

image.png

你知道吗?

宣传栏