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

2024-07-24 12:50:12

字体：大中小

来源：转载

供稿：网友

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