mysql 作為常用資料庫,操作賊六是必須的,對于數字操作相關的東西,那是相當友善,本節就來拎幾個統計案例出來供參考!
order訂單表,樣例如下:
CREATE TABLE `t_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. 按天統計進單量,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. 按小時統計進單量
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. 同比昨天進單量對比,order by h, date
SELECT DATE_FORMAT(t.`create_time`, '%Y-%m-%d %H') 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 %H')
ORDER BY DATE_FORMAT(t.`create_time`, '%H'),DATE_FORMAT(t.`create_time`, '%Y-%m-%d %H');
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIyZuBnL3MDN1QjNzETNtITO1UTN3QTNxAzM1ADOxAjMtEzM3AzM48CX1ADOxAjMvwVMzcDMzgzLcd2bsJ2Lc12bj5ycn9Gbi52YugTMwIzcldWYtl2Lc9CX6MHc0RHaiojIsJye.png)
4. 環比上周同小時進單,date in ,order by
SELECT DATE_FORMAT(t.`create_time`, '%Y-%m-%d %H') t_date, COUNT(1) t_count FROM t_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. 按照remark字段中的傳回值進行統計,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. 統計每小時的各金額的區間數統計,sum if 1 0,各自統計
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 t_order t WHERE t.`create_time` > '2018-05-11' GROUP BY DATE_FORMAT(t.`create_time`, '%Y-%m-%d');
7. 按半小時統計進單量,floor h / 30,同理10分鐘,20分鐘
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 t_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. 成功率,失敗率,臨時表 join on hour
SELECT * FROM
(SELECT DATE_FORMAT(t.`create_time`, '%Y-%m-%d') t_date,COUNT(1) '成功數' FROM t_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) '總數' FROM t_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 a join b on xx set a.status=b.status where tmp group by userid tmp2,注意索引
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. 備份表,create table as select xxx where xxx
CREATE TABLE t_m AS SELECT * FROM t_order;
11. 純改備注不鎖表,快,類型全一緻
12. 動态查詢環比上周資料
SELECT DATE_FORMAT(t.create_time, '%Y-%m-%d %H') t_hour, COUNT(1) FROM `t_order` t WHERE t.`create_time` > CURDATE()
OR (t.`create_time` > DATE_SUB(CURDATE(), INTERVAL 8 DAY) AND t.`create_time` < DATE_SUB(CURDATE(), INTERVAL 7 DAY))
GROUP BY DATE_FORMAT(t.create_time, '%H'), DATE_FORMAT(t.create_time, '%Y-%m-%d');
結果如之前環比,隻是不用每次變換日期以迎合不同的時間查詢,同理可能同比昨天的資料問題!
不要害怕今日的苦,你要相信明天,更苦!