欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql中数据统计的技巧备忘录

程序员文章站 2023-12-02 13:21:52
mysql 作为常用数据库,操作贼六是必须的,对于数字操作相关的东西,那是相当方便,本节就来拎几个统计案例出来供参考! order订单表,样例如下: creat...

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

order订单表,样例如下:

create table `yyd_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 yyd_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');

mysql中数据统计的技巧备忘录

4. 环比上周同小时进单,date in ,order by

select date_format(t.`create_time`, '%y-%m-%d %h') t_date, count(1) t_count from yyd_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');

mysql中数据统计的技巧备忘录

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);

mysql中数据统计的技巧备忘录

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 mobp2p.`yyd_order` t where t.`create_time` > '2018-05-11' group by date_format(t.`create_time`, '%y-%m-%d');

mysql中数据统计的技巧备忘录

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 yyd_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 ;

mysql中数据统计的技巧备忘录

8. 成功率,失败率,临时表 join on hour

select * from 
 (select date_format(t.`create_time`, '%y-%m-%d') t_date,count(1) '成功数' from yyd_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 yyd_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;

mysql中数据统计的技巧备忘录

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. 纯改备注不锁表,快,类型全一致

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对的支持。