統計功能挖坑

declandragon發表於2019-10-10

前言

記錄自己在使用 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 ，效果並不好，這個功能暫時還沒做優化，歡迎各位評論~~~
統計查詢

相關文章