Shulou(Shulou.com)05/31 Report--

This article shows you how to achieve data statistics in mysql, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

CREATE TABLE `yyd_ order` (`id`bigint (20) unsigned NOT NULL AUTO_INCREMENT, `user_ id` int (11) NOT NULL, `order_ nid` varchar (50) NOT NULL, `status` varchar (50) NOT NULL DEFAULT '0mm, `money`decimal (20L2) NOT NULL DEFAULT' 0.005, `create_ time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `update_ time`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `userid` (`user_ id`) KEY `createtime` (`createtime`), KEY `updatetime` (`update_ time`) ENGINE=InnoDB

1. Daily purchase order, date_format

SELECT DATE_FORMAT (t.`create _ time`,'% Ymuri% mmi% d') t_date, COUNT (1) t_count FROM t_order t WHERE t.`create _ time` > '2018-05-11' GROUP BY DATE_FORMAT (t.`create _ time`,'% Ymury% mmi% d')

two。 Statistics of purchase orders by the hour

SELECT DATE_FORMAT (t.`create _ time`,'% Y-%m-%d% H') t_hour, COUNT (1) t_count FROM t_order t WHERE t.`create _ time` > '2018-05-11' GROUP BY DATE_FORMAT (t.`create _ time`,'% Y-%m-%d% H')

3. Compared with yesterday's purchase order, order by h, date

SELECT DATE_FORMAT (t.`create _ time`,'% Y-%m-%d% H') t_date, COUNT (1) t_count FROM yyd_order t WHERE t.`create _ time` > '2018-05-11' GROUP BY DATE_FORMAT (t.`create _ time`,'% Y-%m-%d% H') ORDER BY DATE_FORMAT (t.`create _ time`,'% H'), DATE_FORMAT (t.`create _ time`,'% Y-%m-%d% H')

4. Date in, order by in the same hour last week.

SELECT DATE_FORMAT (t.`create _ time`,'% Y-%m-%d% H') t_date, COUNT (1) t_count FROM yyd_order t WHERE DATE_FORMAT (t.`create _ time`,'% Ymuri% mmure% d') IN ('2018-05-03) GROUP BY DATE_FORMAT (t.`create _ time`,'% Y-%m-%d% H') ORDER BY DATE_FORMAT (t.`create _ time`,'% H') DATE_FORMAT (t.`create _ time`,'% Y-%m-%d% H')

5. According to the return value in the remark field, group by remark like...

SELECT DATE_FORMAT (t.`create _ time`,'% Ymuri% mmi% d') t_date, COUNT (1) t_count, SUBSTRING_INDEX (SUBSTRING_INDEX (t.`msg`,'{',-1),'}', 1) t_rsp_msg FROM cmoo_tab t WHERE t.`create _ time` > '2018-05-17' AND t.`rsp _ msg` LIKE'% nextProcessCode%C9000%' GROUP BY DATE_FORMAT (t.`create _ time`,'% Ylam% mmi% d') SUBSTRING_INDEX (SUBSTRING_INDEX (t.`rsp _ msg`,'{',-1),'}', 1)

6. Statistics interval statistics of each amount per hour, sum if 1 0, respective statistics

SELECT DATE_FORMAT (t.createtimekeeper% Ymee% mmee% d') t_date, SUM (IF (t.`amount t` > 0 AND t.`sum t`1000 AND t.`amount `5000, 1, 0)) 5000m FROM mobp2p.`yd _ order`t WHERE t.`create _ time` > '2018-05-11' GROUP BY DATE_FORMAT (t.`create _ time`,'% Ymi% m% d'))

7. The input quantity is counted by half hour, floor h / 30, similarly 10 minutes, 20 minutes.

SELECT CONCAT (DATE_FORMAT (create_time,'% Y-%m-%d% Hcreate_time'), IF (FLOOR (DATE_FORMAT (create_time,'% i') / 30) = 0,'00 million FROM yyd_order WHERE create_time 30') AS time_scope, COUNT (*) FROM yyd_order WHERE create_time > '2018-05-11' GROUP BY time_scope ORDER BY DATE_FORMAT (create_time,'% Hcreate_time% i'), DATE_FORMAT (create_time) '% Ymuri% mmi% d') DESC

8. Success rate, failure rate, temporary table join on hour

SELECT * FROM (SELECT DATE_FORMAT (t.`create _ time`,'% Ymuri% mmae% d') tsuccesses count (1) 'number of successes' FROM yyd_order t WHERE t.`create _ time` > '2018-05-17' AND t.`status` =' repay_yes' GROUP BY DATE_FORMAT (t.`create _ time`,'% Ymi% mmi% d')) T1 RIGHT JOIN (SELECT DATE_FORMAT (t.`create _ time`,'% Yfay% m% d') t_date COUNT (1) 'total' FROM yyd_order t WHERE t.`create _ time` > '2018-05-11' GROUP BY DATE_FORMAT (t.`create _ time`,'% Ymure% mmure% d')) T2 ON t1.t_date=t2.t_date

9. Update the last log status value in the log table to the status in the information table, update a join b on xx set a.status=b.status where tmp group by userid tmp2, pay attention to the index

UPDATE t_order t0 LEFT JOIN (SELECT * FROM (SELECT * FROM t_order_log t WHERE t.create_time > '2018-05-11' ORDER BY id DESC) T1 GROUP BY t1.user_id) ON t.user_id=t2.user_id SET t0.`status` = t2.status WHERE t0.`create _ time` > '2018-05-11' AND t0.`status` = 10

10. Backup table, create table as select xxx where xxx

CREATE TABLE tweem AS SELECT * FROM tweeder; the above is how to implement data statistics in mysql. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.