65*24=1560<2175 对数据的统计支撑决策假设 历史数据正确的情况下，去安排今后的任务

  

没有达到目标，原因不是时间投入不够，而是不用数据决策，不用数据调度定时脚本  

 

 

【数据源情况统计】---->
# 近30天，日生效coin数目
SELECT COUNT(DISTINCT coin) AS c,FROM_UNIXTIME(create_time,'%Y-%m-%d ') AS d FROM test_order WHERE unix_timestamp(now())-create_time<3600*24*30 GROUP BY d DESC ;
# 近30天，日均生效coin数目
SELECT AVG(c) FROM ( SELECT COUNT(DISTINCT coin) AS c,FROM_UNIXTIME(create_time,'%Y-%m-%d ') AS d FROM test_order WHERE unix_timestamp(now())-create_time<3600*24*30 GROUP BY d DESC) AS t ;
#2017年12月25日 10255

# 近30天，日小时生效coin数目
SELECT COUNT(DISTINCT coin) AS c,FROM_UNIXTIME(create_time,'%Y-%m-%d %H') AS h FROM test_order WHERE unix_timestamp(now())-create_time<3600*24*30 GROUP BY h DESC ;
# 近30天，日小时均生效coin数目
SELECT AVG(c) FROM (SELECT COUNT(DISTINCT coin) AS c,FROM_UNIXTIME(create_time,'%Y-%m-%d %H') AS h FROM test_order WHERE unix_timestamp(now())-create_time<3600*24*30 GROUP BY h DESC ) AS t ;
#2017年12月25日 743

# 历史日检测出异常coin数目
SELECT COUNT(1),FROM_UNIXTIME(update_time,'%Y-%m-%d ') AS d FROM test_error_temp GROUP BY d ORDER BY d DESC;
# 历史日均检测出异常coin数目
SELECT AVG(c) FROM (SELECT COUNT(1) AS c,FROM_UNIXTIME(update_time,'%Y-%m-%d ') AS d FROM test_error_temp GROUP BY d ORDER BY d DESC) AS t ;
#2017年12月25日 2175

 

 

 

#异常coin检测出的出错比例  (此数值，非客观数据)
#0.0063
SELECT
(SELECT COUNT(1) FROM test_error)
/(SELECT COUNT(1) FROM test_order WHERE
create_time
BETWEEN  (SELECT MIN(create_time) FROM test_error )  AND (SELECT  MAX(create_time) FROM test_error))
;
日应发现异常coin数目 （假设历史数据正确）
SELECT
(
 SELECT
 (SELECT COUNT(1) FROM test_error)
 /(SELECT COUNT(1) FROM test_order WHERE
 create_time
 BETWEEN  (SELECT MIN(create_time) FROM test_error )  AND (SELECT  MAX(create_time) FROM test_error))
)* (SELECT AVG(c) FROM (SELECT COUNT(DISTINCT coin) AS c,FROM_UNIXTIME(create_time,'%Y-%m-%d') AS d FROM test_order WHERE unix_timestamp(now())-create_time<3600*24*30 GROUP BY d DESC ) AS t )
; #0.0063*10289=65

# 65*24=1560<2175