统计功能挖坑

前言

记录自己在使用 MySQL 过程中踩过的坑、SQL 优化方案等。

有收获的话请 点个赞，没有收获的话可以 反对 没有帮助 举报 三连。

需求

实现一个统计功能，在转账记录表中，统计每个用户的充值次数-pt、充值数额-pa，提现次数-wt，提现数额-wa；支持时间范围查询，四个统计字段范围查询，分页查询等。

表结构

CREATE TABLE `table_name` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `uid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '用户',
  `admin` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '管理员',
  `txid` char(120) NOT NULL DEFAULT '' COMMENT '交易号',
  `confirm` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '确认数',
  `number` decimal(20,8) unsigned NOT NULL DEFAULT '0.00000000' COMMENT '数量',
  `pay` decimal(20,8) unsigned NOT NULL DEFAULT '0.00000000' COMMENT '实际数量',
  `opt_type` enum('in','out') NOT NULL DEFAULT 'in' COMMENT '类型',
  `status` tinyint(2) unsigned NOT NULL DEFAULT '0' COMMENT '状态',
  `msg` char(255) NOT NULL DEFAULT '' COMMENT '消息',
  `created` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
  `createip` char(15) NOT NULL DEFAULT '0.0.0.0' COMMENT '创建ip',
  `updated` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '修改时间',
  `updateip` char(15) NOT NULL DEFAULT '0.0.0.0' COMMENT '修改ip',
  PRIMARY KEY (`id`),
  KEY `uid` (`uid`),
  KEY `txid` (`txid`)
) ENGINE=InnoDB AUTO_INCREMENT DEFAULT CHARSET=utf8 COMMENT='转账记录表';

思路梳理

• 方案1：分两次查询，先查充值或提现，再用得到的 uid 进行 in 查询
• 方案2：写个子查询 sql，根据查询条件组装，然后 join 查询

实现（方案2）

话不多说直接上 sql

//子查询 查询条件
$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 ( @field@ ) a
LEFT JOIN ${table} b ON a.uid = b.uid AND b.opt_type = '%s' 
GROUP BY uid LIMIT ${firstRow},${page_size};";

根据查询条件可以组装四种sql出来，下面就放出来其中一种，最终执行的 sql ↓

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;

分析

用 EXPLAIN 去分析该 sql ，效果并不好，这个功能暂时还没做优化，欢迎各位评论~~~

本作品采用《CC 协议》，转载必须注明作者和本文链接