MySQL查询统计，统计唯一值并分组

做个笔记

SQLyog客户端访问MySQL服务器

统计数据：次数总数， 次数成功率，对象(obj)总数，对象(obj)成功率

要求：按时间排序和分组

 

sql语句如下：

 1 SELECT 
 2 a.date AS `日期`,
 3 a.total AS `总次数`,
 4 a.objs AS `总对象数`,
 5 ROUND(a.failed / a.total * 100, 2) AS `失败次数比率`,
 6 ROUND(a.obj_failed/ a.objs * 100, 2) AS `对象失败率`
 7 FROM
 8 (SELECT 
 9 COUNT(1) AS `total`,
10 COUNT(DISTINCT(`obj_id`)) AS `objs`,
11 COUNT(IF(`description` LIKE '%object failed%', 1, NULL)) AS `failed`,
12 COUNT(DISTINCT(IF(`description` LIKE '%object failed%', `obj_id`, NULL))) AS `obj_failed`,
13 DATE(`created_at`) AS `date`
14 FROM `mydb.my_table` WHERE `created_at` > '2016-03-07 0'
15 GROUP BY `date`
16 ORDER BY `date` DESC)
17 AS a

 

posted @ 2016-03-07 14:50  Jojodru  阅读(5124)  评论(0)  编辑  收藏  举报