阳性比例 mysql CASE UNION ALL

 

 

阳性比例 mysql CASE UNION ALL

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT t.*,t.type_0/all_ FROM (
SELECT
FROM_UNIXTIME(create_time,'%Y-%m-%d %H') AS p ,
SUM(CASE WHEN status=0 THEN 1 ELSE 0 END )AS type_0,
SUM(CASE WHEN status=13 THEN 1 ELSE 0 END ) AS type_13,
SUM(1) AS all_
FROM mytest  GROUP BY p ORDER BY p DESC
) AS t;
 
 
 
 
p   type_0  type_13 all_    t.type_0/all_
2015-05-10 22   759 1320    2079    0.3651
2015-05-10 21   1127    1082    2209    0.5102
2015-05-10 20   3280    4120    7400    0.4432
2015-05-10 19   2239    4599    6838    0.3274
2015-05-10 18   1841    3039    4880    0.3773
2015-05-10 16   3376    3666    7042    0.4794
2015-05-10 15   2917    4577    7494    0.3892
2015-05-10 14   871 1713    2584    0.3371

  

 

posted @   papering  阅读(266)  评论(0编辑  收藏  举报
努力加载评论中...
点击右上角即可分享
微信分享提示