This is probably a naughty use for sql... But here it goes....
这可能是sql的淘气用法...但是这里它......
I have a table:
我有一张桌子:
Banners
--------------
BannerID
request_t0
clicks_t0
request_t1
clicks_t1
...
request_t6
clicks_t6
Using the number of requests and clicks, I calculate a ctr (clicks to impressions ratio) for each set.... ctr_t0 = clicks_t0 / request_t0 * 100
使用请求和点击次数,我计算每个集合的ctr(点击次数).... ctr_t0 = clicks_t0 / request_t0 * 100
So now I have 6 separate CTRs in each row....
所以现在每行有6个单独的点击率....
For output, I would like the count of how often each CTR is the highest in it's row...
对于输出,我想计算每个CTR在其行中最高的频率......
So given the set:
所以给定集合:
ctr_t0 ctr_t1 ctr_t3
------ ------ ------
2.39% 1.24% 1.5%
1.4% 2.46% 2.2%
3.1% 2.45% 1.45%
I would like as my result:
我想结果如下:
ctr_t0_count ctr_t1_count ctr_t3_count
------------ ------------ ------------
2 1 0
Any ideas on how to do this w/o learning a programing language? :-)
关于如何学习编程语言的任何想法? :-)
1 个解决方案
#1
2
select
sum(case when greatest(ctr_t0,ctr_t1,ctr_t3) = ctr_t0 then 1 else 0 end) as ctr_t0_count,
sum(case when greatest(ctr_t0,ctr_t1,ctr_t3) = ctr_t1 then 1 else 0 end) as ctr_t1_count,
sum(case when greatest(ctr_t0,ctr_t1,ctr_t3) = ctr_t3 then 1 else 0 end) as ctr_t3_count
from (select .... ) as t
where within select there is your previous query.
在select中有你以前的查询。
#1
2
select
sum(case when greatest(ctr_t0,ctr_t1,ctr_t3) = ctr_t0 then 1 else 0 end) as ctr_t0_count,
sum(case when greatest(ctr_t0,ctr_t1,ctr_t3) = ctr_t1 then 1 else 0 end) as ctr_t1_count,
sum(case when greatest(ctr_t0,ctr_t1,ctr_t3) = ctr_t3 then 1 else 0 end) as ctr_t3_count
from (select .... ) as t
where within select there is your previous query.
在select中有你以前的查询。