大家看看这个增长率的sql怎么写？

[charlesxu]

有个表domain，里面3个字段domain/count/date，分别代表domain名字和每天访问数量和日期。这个表每天都增加值

题目要求是：
Use this table to report the top 50 domains by count sorted by percentage growth of the last 30 days compared to the total.
翻译成中文就是：
前30天访问数量的增长率和总和的比，列出前50个domain。


我写的sql如下：
select domain, sum(count) from domain_count where DATEDIFF(curdate(),date)<30 group by
domain order by total desc limit 50

上面的sql算出来前30天内每个domain的访问量。请问在同一个sql里，我怎么算每个domian前30天的访问量和这个domain历史所有访问量的比值？

[一颗向往数据的心丶]

您这是mysql?limit

[一颗向往数据的心丶]

--嵌套查询一个不是可以了么
select domain,
       sum(count) num30,
       sum(count) /
       (select sum(count) from domain_count d where d.domain = t.domain) * 100 "rate30(%)"
  from domain_count t
 where "date" between sysdate - 30 and sysdate
 group by domain;
--后面自己order by查出前50个

[charlesxu]

引用 1 楼 z_shousi 的回复:

您这是mysql?limit

都一样，都是sql，用oracle写，我再转。 我自己写了一个不过感觉太繁琐

select t1.domain, ROUND((30d/total)*100,2) as percent from 
(select sum(count) as 30d, domain from domain_count 
where DATEDIFF(curdate(),date)<1 group by domain) t1,
(select sum(count) as total, domain from domain_count group by domain) t2 
where t1.domain = t2.domain
 order by percent desc limit 50

[CT_LXL]

引用 3 楼 charlesxu 的回复:

[quote=引用 1 楼 z_shousi 的回复:] 您这是mysql?limit

都一样，都是sql，用oracle写，我再转。 我自己写了一个不过感觉太繁琐

select t1.domain, ROUND((30d/total)*100,2) as percent from 
(select sum(count) as 30d, domain from domain_count 
where DATEDIFF(curdate(),date)<1 group by domain) t1,
(select sum(count) as total, domain from domain_count group by domain) t2 
where t1.domain = t2.domain
 order by percent desc limit 50

[/quote]

select domain,
       sum(count) /
       (select sum(count) from domain_count t2 where t1.domain = t2.domain)
  from domain_count t1
 where date > sysdate - 30
 group by domain