sql 如何计算总数，比值等问题

状况说明：

表一：FTU_Sim

FTUID      SIMNO   SIMNumber

87654321     1     13755479620

87654321     2     13984752145

表二：MOS

id     mobile            updata         mosvalue 

 

3	13755479620	2008-1-2 0:00:00	1.20
5	13755479620	2008-1-2 0:00:00	2.50
7	13755479620	2008-1-2 0:00:00	3.30
8	13755479620	2008-1-2 0:00:00	4.20
9	13755479620	2008-1-2 0:00:00	1.60
10	13755479620	2008-1-2 0:00:00	1.20
12	13755479620	2008-1-2 0:00:00	2.80
13	13755479620	2008-1-2 0:00:00	2.20
14	13755479620	2008-1-3 0:00:00	1.60
15	13755479620	2008-1-2 0:00:00	4.04
16	13755479620	2008-1-5 0:00:00	3.88
17	13755479620	2008-1-2 0:00:00	2.23
18	13755479620	2008-1-3 0:00:00	1.43
20	13984752145	2008-1-2 0:00:00	1.23
21	13984752145	2008-1-3 0:00:00	2.23
23	13984752145	2008-1-3 0:00:00	4.50
24	13984752145	2008-1-2 0:00:00	3.75
25	13984752145	2008-1-3 0:00:00	4.85
26	13984752145	2008-1-5 0:00:00	2.23
27	13984752145	2008-1-6 0:00:00	3.58
28	13984752145	2008-1-2 0:00:00	1.98
29	13984752145	2008-1-2 0:00:00	0.80

   

 

要求：以FTUID做为条件，按照号码分组，得到每个号码的mosvalue在一个范围类出现的次数，出现的总次数，两者的比率，和mosvalue的平均值

 

解决方案：

1：分析，由于我们要获取每个号码产生mosvalue在一个范围类出现的次数所以需要记录在该范围的所有项，同时需要记录该卡的所有项，这是考虑把得到每个卡出现的次数放到顶层，而在次一层来得到范围类的值。同时由于要计算比率，所有需要把比率放在计算范围类出现次数的上一层。

 

2：解决过程

sql1:得到总的出现次数

with temp_table as

(

select mobile,count(mosdate) as allcounts from mos where mobile in
(
select simnumber from ftu_sim where ftuid = '87654920' and simno in (1,2)
)
group by mobile

)

 

sql2:得到范围的次数以及平均值

select
a.mobile,
count(mosvalue)  as counts,
allcounts,
cast(avg(mosvalue)as numeric(5,2)) as averagevalue
from mos a, temp_table
where a.mobile = temp_table.mobile
and mosvalue between 1 and 1.5
group by a.mobile,allcounts

这张表暂且称为t

 

sql3:得到最终想要的结果

select '1
<1.5?, /> rate = case when allcounts <> 0 then cast(cast(counts as numeric(5,2)) * 100 / allcounts as numeric(5,2)) else 0 end,
averagevalue
from t

 

sql总：最后组合后的结果是

--得到总的次数

with temp_table as
(
select mobile,count(mosdate) as allcounts from mos where mobile in
(
select simnumber from ftu_sim where ftuid = '87654920' and simno in (1,2)
)
group by mobile
)

--得到最后想要的结果
select '1
<1.5?, /> rate = case when allcounts <> 0 then cast(cast(counts as numeric(5,2)) * 100 / allcounts as numeric(5,2)) else 0 end,
averagevalue
from
(

--得到范围内的次数和平均值
select
a.mobile,
count(mosvalue)  as counts,
allcounts,
cast(avg(mosvalue)as numeric(5,2)) as averagevalue
from mos a, temp_table
where a.mobile = temp_table.mobile
and mosvalue between 1 and 1.5
and mosdate between '2007-12-30' and '2008-1-10'
group by a.mobile,allcounts
)t

 

执行结果

      无列名         mobile    counts   allcounts    rate   averagevalue

1
<1.5  /> 1< 11.11      9       1          <1.5  13984752145   >