原表:select * from pagereferrer;
如下:
如下:
![图片](https://i-blog.csdnimg.cn/blog_migrate/170f09632db7452e124dad70dc892a5c.gif)
如何在这张表中,查询出按 URL分类的信息 ,例如:211.95.60.43:8080算一类信息,并按百分比显示。
预期结果如下: precent字段显示百分比,urlCount字段统计该url的数量。
预期结果如下: precent字段显示百分比,urlCount字段统计该url的数量。
![图片](https://i-blog.csdnimg.cn/blog_migrate/065cf81de23e27e3e01cf5ecb3707643.gif)
会遇到两个主要问题:
1:如何只截取到红色标注的部分。
1:如何只截取到红色标注的部分。
![图片](https://i-blog.csdnimg.cn/blog_migrate/ae176d007e4727db66bf57c301621279.gif)
2:截取前8位字符之后,URL为空如何以显示“ / ”
![图片](https://i-blog.csdnimg.cn/blog_migrate/337fdd7bb70f7f34b9c893feb47be751.gif)
3:如何统计不同类型URL所占百分比。
===================================================================================
下面步骤分解 :
第一步:
先按URL分组查询
select * from pagereferrer GROUP BY url;
第一步:
先按URL分组查询
select * from pagereferrer GROUP BY url;
![图片](https://i-blog.csdnimg.cn/blog_migrate/e1712d3ae126d1165fc46a9bdc76995a.gif)
第二步:
截取掉前8位字符“http://”
语句:
select substr(url,8) from pagereferrer GROUP BY url;
截取掉前8位字符“http://”
语句:
select substr(url,8) from pagereferrer GROUP BY url;
![图片](https://i-blog.csdnimg.cn/blog_migrate/56b13a2548ce9b5a6f0dcb3010dc7246.gif)
第三步:
使用instr函数找到 截取掉前8位字符“http://”之后的第一个"/"斜杠位置在第几个字符。
语句:
select substr(url,8),INSTR(substr(url,8),'/') from pagereferrer GROUP BY url;
使用instr函数找到 截取掉前8位字符“http://”之后的第一个"/"斜杠位置在第几个字符。
语句:
select substr(url,8),INSTR(substr(url,8),'/') from pagereferrer GROUP BY url;
![图片](https://i-blog.csdnimg.cn/blog_migrate/0c91c328c341603a130a489abc8fc46b.gif)
第四步:
配合LEFT函数,查询到第一个" / "之前的所有字符串 ,包含" / "。并且给该列命名一个别名 url
语句:select left(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/'))as url from pagereferrer GROUP BY url;
配合LEFT函数,查询到第一个" / "之前的所有字符串 ,包含" / "。并且给该列命名一个别名 url
语句:select left(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/'))as url from pagereferrer GROUP BY url;
![图片](https://i-blog.csdnimg.cn/blog_migrate/d04cf69104206c317464fe4338cc9dc0.gif)
第五步:
把最后一个字符取出在left函数中加一个参数-1,并且按该列分组。
语句:select left(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1)as url
from pagereferrer
GROUP BY left(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1);
把最后一个字符取出在left函数中加一个参数-1,并且按该列分组。
语句:select left(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1)as url
from pagereferrer
GROUP BY left(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1);
![图片](https://i-blog.csdnimg.cn/blog_migrate/c57f0ad82717dfaf40ad01feb1f9b23f.gif)
好了到这里基本上大功完成!只剩下考虑如何把为空的URL显示 " / "的问题,之后要在SQL中加一个判断。
第六步:
加一个判断,使得URL显示为" / "
语句:
select case when LENGTH(left(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1))=0
then '/'
else left(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1)
end as url
from pagereferrer
GROUP BY left(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1);
第六步:
加一个判断,使得URL显示为" / "
语句:
select case when LENGTH(left(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1))=0
then '/'
else left(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1)
end as url
from pagereferrer
GROUP BY left(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1);
![图片](https://i-blog.csdnimg.cn/blog_migrate/107aef2a3fb6f6b74513fa80c43d49c5.gif)
第7步:
完成统计不同类型URL数量和百分比显示,加一个count(1) as urlCount,统计第一列的数量。
语句:
select case when LENGTH(left(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1))=0
then '/'
else left(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1)
end as url ,count(1) as urlCount
from pagereferrer
GROUP BY left(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1);
完成统计不同类型URL数量和百分比显示,加一个count(1) as urlCount,统计第一列的数量。
语句:
select case when LENGTH(left(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1))=0
then '/'
else left(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1)
end as url ,count(1) as urlCount
from pagereferrer
GROUP BY left(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1);
![图片](https://i-blog.csdnimg.cn/blog_migrate/928e6aaaad090114d871f552b597b9c0.gif)
第8步 :
统计百分比, 这里比较繁琐需要准备两个临时表,一个用来统计总数,一个用来统计每个分类的url数量。
先准备显示统计Url总数表:
语句:
select sum(x.urlCount) as sumUrlCount from(
select case when LENGTH(LEFT(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1))=0 then '/' else LEFT(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1) end as url,
count(1) as urlCount
from pagereferrer
group by LEFT(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1))x
统计百分比, 这里比较繁琐需要准备两个临时表,一个用来统计总数,一个用来统计每个分类的url数量。
先准备显示统计Url总数表:
语句:
select sum(x.urlCount) as sumUrlCount from(
select case when LENGTH(LEFT(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1))=0 then '/' else LEFT(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1) end as url,
count(1) as urlCount
from pagereferrer
group by LEFT(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1))x
![图片](https://i-blog.csdnimg.cn/blog_migrate/86c2fb9d9e01325acfa4a3a92afed674.gif)
接着准备统计每个分类的URL数量
语句:
select case when LENGTH(LEFT(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1))=0 then '/' else LEFT(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1) end as url,
count(1) as urlCount
from pagereferrer
group by LEFT(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1)
ORDER BY urlCount desc
语句:
select case when LENGTH(LEFT(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1))=0 then '/' else LEFT(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1) end as url,
count(1) as urlCount
from pagereferrer
group by LEFT(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1)
ORDER BY urlCount desc
![图片](https://i-blog.csdnimg.cn/blog_migrate/9807f0ea7334679659c7e254ba0d532d.gif)
最后 select (t1.urlCount/t2.sumUrlCount)*100 as precent 把该列显示百分比,t2表作为分母的临时表,t2表作为分子
语句:
select (t1.urlCount/t2.sumUrlCount)*100 as precent,t1.url,t1.urlCount from
(select case when LENGTH(LEFT(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1))=0 then '/' else LEFT(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1) end as url, count(1) as urlCount from pagereferrer group by LEFT(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1) ORDER BY urlCount desc)t1,
(select sum(x.urlCount) as sumUrlCount from( select case when LENGTH(LEFT(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1))=0 then '/' else LEFT(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1) end as url, count(1) as urlCount from pagereferrer group by LEFT(SUBSTR(url,8),INSTR(SUBSTR(url,8),'/')-1))x)t2
![图片](https://i-blog.csdnimg.cn/blog_migrate/5a0b756e511a320042fb7beee038dd68.gif)
好了到这里终于把要求的结果查询出来了。。。。
由于IE浏览器升级禁用了alt+x快捷键,请用alt+q快捷键来快速进入写说说入口
正在加载中...
显示评论签名