校企成果

select COUNT(*) as '校企成果数'
from achievements a
where source = 4;


select COUNT(*) as '高校成果数'
from achievements a
where source = 4
  and tags = 1;

select COUNT(*) as '企业成果数'
from achievements a
where source = 4
  and tags = 2;

select count(*) as '校企点击数'
from clickers c
         inner join achievements a ON a.id = c.click_id
where c.click_type = 'Achievements'
  and a.source = '4';

/*
点击量（浏览量）:clickSum
关注量: watchSum
查询量：searchSum
收藏量: favoriteSum
下载量: downloadSum
*/
select a.clickSum, b.watchSum, c.searchSum, d.favoriteSum, e.downloadSum
        from (
                 (select count(1) as clickSum from clickers where click_type = 'Achievements' and click_id = #{id}) a,
                     (select count(1) as watchSum from watchers where watchable_type = 'Achievements' and watchable_id = #{id}) b,
                     (select count(1) as searchSum from searchers where search_type = 'Achievements' and search_id = #{id}) c,
                     (select count(1) as favoriteSum from favorites where favorite_type = 'Achievements' and favorite_id = #{id}) d,
                     (select count(1) as downloadSum from downloads where download_type = 'Achievements' and download_id = #{id}) e
                 )

/* 近7日的浏览量 */
select DATE_FORMAT(created_at, '%m-%d') as 'k', count(*) as 'v'
        from clickers
        where created_at >= #{s}
          and created_at <= #{e}
          and click_type = #{t}
          and click_id = #{id}
        group by DATE_FORMAT(created_at, '%m-%d')
        order by DATE_FORMAT(created_at, '%m-%d')
        
/* 近7日的浏览量 */
-- 1、获取所有行为汇总数据
 select a.clickSum,b.watchSum,c.searchSum,d.favoriteSum,e.downloadSum from (
            (select count(1) as clickSum from clickers where click_type='Achievements') a,
            (select count(1) as watchSum  from watchers  where watchable_type='Achievements') b,
            (select count(1) as searchSum from searchers where search_type='Achievements') c,
            (select count(1) as favoriteSum  from favorites  where favorite_type='Achievements' ) d,
            (select count(1) as downloadSum  from downloads  where download_type='Achievements' ) e
        );

--  2、从行为表中获取成果行为数据权限
 select behavior_code as "key" ,behavior_weight as "value"
        from behavior_image_weight
        where image_type = “Achievements”;
        
-- 3、根据成果id获取改成果对应的查询、浏览（点击）、收藏、下载、关注
select count(1) as searchSum from searchers where search_type = #{achievementsImage}  and search_id = #{id};

select count(1) as favoriteSum from favorites where favorite_type = #{achievementsImage}  and favorite_id = #{id};

select count(1) as watchSum from watchers where watchable_type = #{achievementsImage}  and watchable_id = #{id};

select count(1)  from downloads where download_type = #{achievementsImage}  and download_id = #{id};

select count(1) as clickSum from clickers where click_type = #{achievementsImage}  and click_id = #{id};

-- 4、计算单项行为的分数
--  clickResult = click * weight / clickSum ;
 
--  5、所有行为结果加起来，得到成果关注度