mysql计算同比和环比的区别_mysql中数据统计的技巧备忘录

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

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

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 mobp2p.`yyd_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 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 ;

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;

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

总结

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值