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

Notes on MySQL case sensitivity

Table of contents MySQL case sensitivity is contr...

Complete steps to enable gzip compression in nginx

Table of contents Preface 1. Configure gzip compr...

How to use skeleton screen in vue project

Nowadays, application development is basically se...

SSM VUE Axios Detailed Explanation

Table of contents How to display SQL log? ? Descr...

How to quickly build an FTP file service using FileZilla

In order to facilitate the storage and access of ...

Node.js+postman to simulate HTTP server and client interaction

Table of contents 1. Node builds HTTP server 2. H...

Example code for implementing an Upload component using Vue3

Table of contents General upload component develo...

Next.js Getting Started Tutorial

Table of contents Introduction Create a Next.js p...

Tutorial on installing and using virtualenv in Deepin

virtualenv is a tool for creating isolated Python...

jQuery plugin to achieve seamless carousel

Seamless carousel is a very common effect, and it...

Detailed explanation of several error handling when Nginx fails to start

When using Nginx as a Web server, I encountered t...

The process of installing Docker in Linux system

In this blog, I will walk you through the process...

MySQL case when usage example analysis

First we create the database table: CREATE TABLE ...