mysql数据统计技巧备忘录

mysql 作为常用数据库,操作贼六是必须的,对于数字操作相关的东西,那是相当方便,本节就来拎几个统计案例出来供参考!

attachments-2020-11-ynZ1jD3L5fb8778c86c68.png

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
MAT(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');
  1. 按小时统计进单量
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');
  1. 同比昨天进单量对比,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');

attachments-2020-11-MdchpILn5fb8779c8548e.jpg

  1. 环比上周同小时进单,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');

attachments-2020-11-sC3rRPki5fb877a8a62de.jpg

  1. 按照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);

attachments-2020-11-ic9btIfF5fb877b4a5fcc.jpg

  1. 统计每小时的各金额的区间数统计,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');

attachments-2020-11-VXDtzVKL5fb877bf85d01.jpg

  1. 按半小时统计进单量,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 ;

attachments-2020-11-YJX4eMVa5fb877c9e0c13.jpg

  1. 成功率,失败率,临时表 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;

attachments-2020-11-CCuxuycS5fb877d504c00.jpg

  1. 更新日志表中最后条一条日志状态值到信息表中状态,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;
  1. 备份表,create table as select xxx where xxx
CREATE TABLE t_m AS SELECT * FROM t_order;
  1. 纯改备注不锁表,快,类型全一致
  2. 动态查询环比上周数据
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');

结果如之前环比,只是不用每次变换日期以迎合不同的时间查询,同理可能同比昨天的数据问题!


attachments-2020-11-OWsyPeow5fb877e6d082d.jpg原文:https://www.cnblogs.com/yougewe/p/9103591.html

  • 发表于 2020-11-21 10:12
  • 阅读 ( 15 )
  • 分类:数据库

0 条评论

请先 登录 后评论
阿梓
阿梓

965 篇文章

作家榜 »

  1. 阿梓 965 文章
  2. 吉洪叶 6 文章
  3. 百日慕晴 2 文章
  4. baiyang 2 文章
  5. li 1 文章
  6. yaya6x 1 文章
  7. 阿杰和飞机 1 文章
  8. 刘新超 1 文章