mysql按时间统计

来源：互联网发布：微众银行程序员工资编辑：程序博客网时间：2024/05/16 19:15

1.函数

DATE_FORMAT（now()，'格式'）格式化时间 %y-%m-%d---%Y-%m-%d

DATE_SUB(CURRENT_DATE , INTERVAL #{num_value} DAY) 当前日期减去num_value天

2.按月份统计

SELECT DATE_FORMAT(pubtime,'%Y-%m') MONTH,COUNT(1) COUNT FROM (SELECT url,fetch_time,domain,pubtime,title,TEXT,conllectionId,warningId,dailyId FROM (SELECT b.url ,b.fetch_time,GROUP_CONCAT(CASE WHEN b.CODE = 'domain' THEN text_value ELSE NULL END) AS domain,GROUP_CONCAT(CASE WHEN b.CODE = 'pubtime' THEN text_value ELSE NULL END) AS pubtime,GROUP_CONCAT(CASE WHEN b.CODE = 'title' THEN text_value ELSE NULL END) AS title,GROUP_CONCAT(CASE WHEN b.CODE = 'text' THEN text_value ELSE NULL END)AS TEXT,GROUP_CONCAT(CASE WHEN b.CODE = 'html' THEN text_value ELSE NULL END) AS html,a.crawl_data_id conllectionId,c.crawl_data_id warningId,d.crawl_data_id dailyIdFROM crawl_data bLEFT JOIN  conllection_info aON  b.url = a.crawl_data_idLEFT JOIN warning_info cON b.url = c.crawl_data_idLEFT JOIN  daily_info dON b.url = d.crawl_data_idGROUP BY b.url,b.fetch_time,a.crawl_data_id,c.crawl_data_id,d.crawl_data_id) crawl_dataWHERE 1 = 1 AND warningId IS NOT NULL AND url IS NOT NULL OR title IS NOT NULL OR TEXT IS NOT NULL OR pubtime IS NOT NULL) a GROUP BY DATE_FORMAT(pubtime,'%Y%m')

3.按日期统计

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d') DAY, COUNT(1) nowDayCount FROM (SELECT url,fetch_time,domain,pubtime,title,TEXT,conllectionId,warningId,dailyId FROM (SELECT b.url ,b.fetch_time,GROUP_CONCAT(CASE WHEN b.CODE = 'domain' THEN text_value ELSE NULL END) AS domain,GROUP_CONCAT(CASE WHEN b.CODE = 'pubtime' THEN text_value ELSE NULL END) AS pubtime,GROUP_CONCAT(CASE WHEN b.CODE = 'title' THEN text_value ELSE NULL END) AS title,GROUP_CONCAT(CASE WHEN b.CODE = 'text' THEN text_value ELSE NULL END)AS TEXT,GROUP_CONCAT(CASE WHEN b.CODE = 'html' THEN text_value ELSE NULL END) AS html,a.crawl_data_id conllectionId,c.crawl_data_id warningId,d.crawl_data_id dailyIdFROM crawl_data bLEFT JOIN  conllection_info aON  b.url = a.crawl_data_idLEFT JOIN warning_info cON b.url = c.crawl_data_idLEFT JOIN  daily_info dON b.url = d.crawl_data_idGROUP BY b.url,b.fetch_time,a.crawl_data_id,c.crawl_data_id,d.crawl_data_id) crawl_dataWHERE 1 = 1 <if test="num_value != null and num_value != ''" >       DATE_FORMAT(pubtime, '%Y-%m-%d' ) BETWEEN  DATE_SUB(CURRENT_DATE , INTERVAL #{num_value} DAY)  AND CURRENT_DATE</if> limit 0, 1

0 0