SQL生产使用实践

1.应用场景：查询近三年的投产数量，如何得到当前月的总数为前几个月数的加和？

思路：使用自连接，date_add()函数，date_parse()函数。

SELECT
   a.online_year,
   a.online_month,
   sum( b.success) as success
FROM
 (
SELECT
     SUBSTRING (dedate, 1, 4) AS online_year,
     SUBSTRING (dedate, 1, 7) AS online_month,
     COUNT (IF (status = '上线成功',TRUE,NULL)) AS success
    FROM
        model_test
    WHERE
        date_parse (SUBSTRING (dedate, 1, 4), '%Y') > date_add ('year' ,-3, now())
    GROUP BY
        SUBSTRING (dedate, 1, 4),
        SUBSTRING (dedate, 1, 7)
 )as a 
LEFT JOIN
(
SELECT
     SUBSTRING (dedate, 1, 4) AS online_year,
     SUBSTRING (dedate, 1, 7) AS online_month,
     COUNT (IF  (status = '上线成功',TRUE,NULL)) AS success
    FROM
        model_test
    WHERE
        date_parse (SUBSTRING (dedate, 1, 4), '%Y') > date_add ('year' ,-3, now())
    GROUP BY
        SUBSTRING (dedate, 1, 4),
        SUBSTRING (dedate, 1, 7)
)as b 
ON a.online_year= b.online_year AND a.online_month >= b.online_month
GROUP BY 
    a.online_year,
    a.online_month
ORDER BY 
    a.online_month

2、应用场景：如何查询当前年或月的数据？

思路：当前时间和数据中的时间处理后做判断。

SELECT *  FROM model_test  WHERE online_month = date_format(now(),'%Y-%m') 
SELECT *  FROM model_test  WHERE online_year = date_format(now(),'%Y')

3、应用场景：如何查询一周内的数据？

思路：使用date_diff()函数。

 select 
   * 
 from 
  model_test 
 where 
  date_diff ('day', create_time, date_parse(date_format(now(),'%Y-%m-%d'),'%Y-%m-%d')) <= 7

4、应用场景：近一个月测试用例执行数占总的用例数及比率问题？

思路：近一个月使用date_add()函数，比率，求百分比。

SELECT
	SUM (i.test_num) AS test_count,
	100 * CAST (j.test_num_zx AS DOUBLE) / SUM (i.test_num) AS exec_rate
FROM
	model_test i,
WHERE
	i.create_time > date_add('day',-30,now())
GROUP BY
	j.exec_num