---同一个mid 同一条weibo 被repo comm like 之和
select
mid,
action_type,
count(action_type) as intract
from tianchi_weibo.weibo_action_data_train
group by mid,action_type
order by intract desc limit 200000
--uid 各个月份 blog 总数--
select
c.uid,
sum(c.month1num) as month1sum,
sum(c.month2num) as month2sum,
sum(c.month3num) as month3sum,
sum(c.month4num) as month4sum,
sum(c.month5num) as month5sum
from
(
select
b.uid,
b.month,
case when b.month=1 then b.blogsum else 0 end as month1num,
case when b.month=2 then b.blogsum else 0 end as month2num,
case when b.month=3 then b.blogsum else 0 end as month3num,
case when b.month=4 then b.blogsum else 0 end as month4num,
case when b.month=5 then b.blogsum else 0 end as month5num
from
(
select
a.uid,
a.month,
count(a.month) as blogsum
from
(
select *,
case
when substr(blog_time,1,10)<"2014-12-01" and substr(blog_time,1,10)>="2014-11-01" then 1
when substr(blog_time,1,10)<"2015-01-01" and substr(blog_time,1,10)>="2014-12-01" then 2
when substr(blog_time,1,10)<"2015-02-01" and substr(blog_time,1,10)>="2015-01-01" then 3
when substr(blog_time,1,10)<"2015-03-01" and substr(blog_time,1,10)>="2015-02-01" then 4
when substr(blog_time,1,10)<"2015-04-01" and substr(blog_time,1,10)>="2015-03-01" then 5
end as month
from tianchi_weibo.weibo_blog_data_train
)a
group by a.uid,a.month
)b
group by b.uid,b.month,b.blogsum
)c
group by c.uid
order by month5sum limit 20000;--从大到小
```sql
```sql
--blogsum*weight
--uid 各个月份 blog 总数--
select
c.uid,
sum(c.month1num) as month1sum,
sum(c.month2num) as month2sum,
sum(c.month3num) as month3sum,
sum(c.month4num) as month4sum,
sum(c.month5num) as month5sum
from
(
select
b.uid,
b.month,
case when b.month=1 then 1 else 0 end as month1num,
case when b.month=2 then 1 else 0 end as month2num,
case when b.month=3 then 1 else 0 end as month3num,
case when b.month=4 then 1 else 0 end as month4num,
case when b.month=5 then 1 else 0 end as month5num
from
(
select *,
case
when substr(blog_time,1,10)<"2014-12-01" and substr(blog_time,1,10)>="2014-11-01" then 1
when substr(blog_time,1,10)<"2015-01-01" and substr(blog_time,1,10)>="2014-12-01" then 2
when substr(blog_time,1,10)<"2015-02-01" and substr(blog_time,1,10)>="2015-01-01" then 3
when substr(blog_time,1,10)<"2015-03-01" and substr(blog_time,1,10)>="2015-02-01" then 4
when substr(blog_time,1,10)<"2015-04-01" and substr(blog_time,1,10)>="2015-03-01" then 5
end as month
from tianchi_weibo.weibo_blog_data_train
)b
group by b.uid,b.month
)c
group by c.uid
order by month5sum desc limit 20000;--从大到小
```sql
