Tips for data statistics in MySQL

Tips for data statistics in MySQL

As a commonly used database, MySQL requires a lot of operations. It is very convenient for digital operations. This section will provide some statistical cases for your reference!

The order table, for example:

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 '0',
  `money` decimal(20,2) NOT NULL DEFAULT '0.00',
  `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` (`create_time`),
  KEY `updatetime` (`update_time`)
)ENGINE=InnoDB;

1. Count the number of orders received by day, date_format

SELECT DATE_FORMAT(t.`create_time`, '%Y-%m-%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`, '%Y-%m-%d');

2. Count incoming orders by 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. Comparison of order volume compared with yesterday, 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. Compared with the same hour last week, the order received, date in, order by

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`,'%Y-%m-%d') IN ('2018-05-03','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'); 

5. Count the returned values ​​in the remark field, group by remark like ...

SELECT DATE_FORMAT(t.`create_time`, '%Y-%m-%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`, '%Y-%m-%d'),SUBSTRING_INDEX(SUBSTRING_INDEX(t.`rsp_msg`, '{', -1), '}', 1);

6. Count the intervals of each amount every hour, sum if 1 0, count each

SELECT DATE_FORMAT(t.create_time,'%Y-%m-%d') t_date, SUM(IF(t.`amount`>0 AND t.`amount`<1000, 1, 0)) t_0_1000, SUM(IF(t.`amount`>1000 AND t.`amount`<5000, 1, 0)) t_1_5000,
  SUM(IF(t.`amount`>5000, 1, 0)) t_5000m FROM mobp2p.`yyd_order` t WHERE t.`create_time` > '2018-05-11' GROUP BY DATE_FORMAT(t.`create_time`, '%Y-%m-%d'); 

7. Count incoming orders by half an hour, floor h / 30, similarly 10 minutes, 20 minutes

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

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

SELECT * FROM 
 (SELECT DATE_FORMAT(t.`create_time`, '%Y-%m-%d') t_date,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`, '%Y-%m-%d')) t1
 RIGHT JOIN 
 (SELECT DATE_FORMAT(t.`create_time`, '%Y-%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`, '%Y-%m-%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. Back up the table, create table as select xxx where xxx

CREATE TABLE t_m AS SELECT * FROM t_order;

11. Simply modify the notes without locking the table, fast, all types are consistent

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.

You may also be interested in:
  • PHP+MySQL to achieve daily data statistics optimization operation example within a period of time
  • PHP advertising click statistics code (php+mysql)
  • Overview of MySQL Statistics
  • MySQL daily statistics report fills in 0 if there is no data on that day
  • Summary of MySQL time statistics methods

<<:  React Native reports "Cannot initialize a parameter of type'NSArray<id<RCTBridgeModule>>" error (solution)

>>:  Detailed explanation of how Vue components transfer values ​​to each other

Recommend

Implementing user registration function with js

This article example shares the specific code of ...

Tutorial on how to connect and use MySQL 8.0 in IDEA's Maven project

First, let's take a look at my basic developm...

CSS border half or partially visible implementation code

1. Use pseudo-classes to display half of the Bord...

Implementation of nginx proxy port 80 to port 443

The nginx.conf configuration file is as follows u...

Vue implements horizontal scrolling of marquee style text

This article shares the specific code for Vue to ...

Tutorial on upgrading, installing and configuring supervisor on centos6.5

Supervisor Introduction Supervisor is a client/se...

Markup Languages ​​- What to learn after learning HTML?

Click here to return to the 123WORDPRESS.COM HTML ...

JS implements array filtering from simple to multi-condition filtering

Table of contents Single condition single data fi...

How to configure Hexo and GitHub to bind a custom domain name under Windows 10

Hexo binds a custom domain name to GitHub under W...

Implementation of mysql configuration SSL certificate login

Table of contents Preface 1. MySQL enables SSL co...

Some common properties of CSS

CSS background: background:#00ffee; //Set the back...

How to implement Linux automatic shutdown when the battery is low

Preface The electricity in my residence has been ...

Raspberry Pi msmtp and mutt installation and configuration tutorial

1. Install mutt sudo apt-get install mutt 2. Inst...