邮箱分组差比列
先上图
怎么根据邮箱类型(QQ/网易/新浪等)分组
计算出数量 比列 最后20分 求帮助
[解决办法]
create table tb(email nvarchar(20))
insert into tb select 'aaa@sina.com'
insert into tb select 'abb@sina.com'
insert into tb select 'acc@sina.com'
insert into tb select 'da@163.com'
insert into tb select '213435243@qq.com'
insert into tb select 'ddfasfa@hotmail.com'
insert into tb select '64545243@qq.com'
go
;with c1 as(
select substring(email,charindex('@',email)+1,len(email)-charindex('@',email))e from tb
),c2 as(
select e,count(*)ct from c1 group by e
),c3 as(
select count(*)ct from tb
)select a.e,convert(varchar,convert(decimal(10,2),100.*a.ct/b.ct))+'%' as r from c2 a,c3 b
/*
e r
-------------------- -------------------------------
163.com 14.29%
hotmail.com 14.29%
qq.com 28.57%
sina.com 42.86%
(4 行受影响)
*/
go
drop table tb
[解决办法]
select RIGHT(email,len(email)-charindex('@',email,0)),COUNT(RIGHT(email,len(email)-charindex('@',email,0))) from 表名
group by RIGHT(email,len(email)-charindex('@',email,0))
Declare @EMailtype table(name nvarchar(50),value nvarchar(50))
Insert into @EMailtype(name,value)
Select N'QQ',N'qq.com' Union all
Select N'网易',N'163.com' Union all
Select N'新浪'N'sina.com' Union
.......
Select N'雅虎',N'yahoo.com.cn'
Select
b.name As 邮箱类型,Count(1) As 数量,Convert(numeric(5,2),Count(1) * 1.0 / (Select Count(1) from EMailList) * 100) As 比例
from EMailList As a
Inner join @EMailtype As b On Stuff(a.EMail,1,patindex('%@%',a.EMail),'')=b.value
Declare @EMailtype table(name nvarchar(50),value nvarchar(50))
Insert into @EMailtype(name,value)
Select N'QQ',N'qq.com' Union all
Select N'网易',N'163.com' Union all
Select N'新浪'N'sina.com' Union
.......
Select N'雅虎',N'yahoo.com.cn'
Declare @EMail_cnt int
Select @EMail_cnt=Count(1) from EMailList
Select
b.name As 邮箱类型,Count(1) As 数量,ltrim(Convert(numeric(5,2),Count(1) * 1.0 / @EMail_cnt * 100))+'%' As 比例
from EMailList As a
Inner join @EMailtype As b On Stuff(a.EMail,1,patindex('%@%',a.EMail),'')=b.value
Group by b.name
--create table tb(email nvarchar(20))
--insert into tb select 'aaa@sina.com'
--insert into tb select 'abb@sina.com'
--insert into tb select 'acc@sina.com'
--insert into tb select 'da@163.com'
--insert into tb select '213435243@qq.com'
--insert into tb select 'ddfasfa@hotmail.com'
--insert into tb select '64545243@qq.com'
--go
SELECT REVERSE(SUBSTRING(REVERSE(Email), 1,
PATINDEX('%@%', REVERSE(Email)) - 1)) ,
SUBSTRING(CONVERT(VARCHAR(20), ( CONVERT(DECIMAL(4, 3), COUNT(SUBSTRING(REVERSE(Email),
1,
PATINDEX('%@%',
REVERSE(Email))
- 1)))
/ ( SELECT COUNT(1)
FROM TB
) ) * 100), 1, 5) + '%'
FROM TB
GROUP BY REVERSE(SUBSTRING(REVERSE(Email), 1,
PATINDEX('%@%', REVERSE(Email)) - 1))
/*
-------------------- -----------
163.com 14.28%
hotmail.com 14.28%
qq.com 28.57%
sina.com 42.85%
(4 行受影响)
*/
