Statistical function digging

Time:2021-9-14

preface

Record the pits and SQL optimization schemes you stepped on in the process of using mysql.

If you have any harvest, pleaseLike it, if there is no harvest, you canObjection does not help reportThree company.

demand

Realize a statistical function. In the transfer record table, count the recharge times – Pt, recharge amount – PA, withdrawal times – WT and withdrawal amount – WA of each user; Support time range query, four statistical field range query, paging query, etc.

Table structure

CREATE TABLE `table_name` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `Uid ` int (11) unsigned not null default '0' comment 'user',
  `Admin ` int (11) unsigned not null default '0' comment 'administrator',
  `Txid ` char (120) not null default '' comment 'transaction number',
  `Confirm ` int (11) unsigned not null default '0' comment 'number of confirmations',
  `Number ` decimal (20,8) unsigned not null default '0.00000000' comment 'number',
  `Pay ` decimal (20,8) unsigned not null default '0.00000000' comment 'actual quantity',
  `opt_ Type ` enum ('In ',' out ') not null default' in 'comment',
  `Status ` tinyint (2) unsigned not null default '0' comment 'status',
  `MSG ` char (255) not null default '' comment 'message',
  `Created ` int (11) unsigned not null default '0' comment 'creation time',
  `Createip ` char (15) not null default '0.0.0.0' comment 'create IP',
  `Updated ` int (11) unsigned not null default '0' comment 'modified time',
  `Updateip ` char (15) not null default '0.0.0.0' comment 'modify IP',
  PRIMARY KEY (`id`),
  KEY `uid` (`uid`),
  KEY `txid` (`txid`)
) ENGINE=InnoDB AUTO_ Increment default charset = utf8 comment = 'transfer record table';

Train of thought combing

  • Scheme 1: query twice, first check the recharge or withdrawal, and then the obtaineduidconductinquery
  • Scheme 2: write sub querysql, assemble according to the query criteria, and thenjoinquery

Implementation (option 2)

Don’t say much, just go straightsql

//Subquery query criteria
$field = "SELECT uid,COUNT(uid) AS %s,SUM(number) AS %s  FROM ${table} 
    WHERE opt_type = '%s'  AND created BETWEEN %s AND %s 
    GROUP BY uid HAVING %s BETWEEN %s AND %s ";

$sql = "SELECT a.*, COUNT(b.uid) AS %s, SUM(b.number) AS %s FROM ( @[email protected] ) a
LEFT JOIN ${table} b ON a.uid = b.uid AND b.opt_type = '%s' 
GROUP BY uid LIMIT ${firstRow},${page_size};";

Four kinds of SQL can be assembled according to the query conditions, and one of them will be released below for final executionsql

SELECT
    a.*, COUNT(b.uid) AS pt,
    SUM(b.number) AS pa
FROM
    (
        SELECT
            uid,
            COUNT(uid) AS wt,
            SUM(number) AS wa
        FROM
            table_name
        WHERE opt_type = 'out'
        AND created BETWEEN 1472918400 AND 1571673600
        GROUP BY uid
        HAVING `wt` BETWEEN 0 AND 1
    ) a
LEFT JOIN table_name b ON a.uid = b.uid
AND b.opt_type = 'in'
GROUP BY uid
LIMIT 0,10;

analysis

useEXPLAINTo analyze thesql, the effect is not good. This function has not been optimized yet. You are welcome to comment~~~
Statistical function digging

This work adoptsCC agreement, reprint must indicate the author and the link to this article

Recommended Today

Supervisor

Supervisor [note] Supervisor – H view supervisor command help Supervisorctl – H view supervisorctl command help Supervisorctl help view the action command of supervisorctl Supervisorctl help any action to view the use of this action 1. Introduction Supervisor is a process control system. Generally speaking, it can monitor your process. If the process exits abnormally, […]