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

状况说明：

表一：FTU_Sim

FTUID      SIMNO   SIMNumber

87654321     1     13755479620

87654321     2     13984752145

表二：MOS

id     mobile            updata         mosvalue 

 

3137554796202008-1-2 0:00:001.205137554796202008-1-2 0:00:002.507137554796202008-1-2 0:00:003.308137554796202008-1-2 0:00:004.209137554796202008-1-2 0:00:001.6010137554796202008-1-2 0:00:001.2012137554796202008-1-2 0:00:002.8013137554796202008-1-2 0:00:002.2014137554796202008-1-3 0:00:001.6015137554796202008-1-2 0:00:004.0416137554796202008-1-5 0:00:003.8817137554796202008-1-2 0:00:002.2318137554796202008-1-3 0:00:001.4320139847521452008-1-2 0:00:001.2321139847521452008-1-3 0:00:002.2323139847521452008-1-3 0:00:004.5024139847521452008-1-2 0:00:003.7525139847521452008-1-3 0:00:004.8526139847521452008-1-5 0:00:002.2327139847521452008-1-6 0:00:003.5828139847521452008-1-2 0:00:001.9829139847521452008-1-2 0:00:000.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   >