mysql中數(shù)據(jù)統(tǒng)計(jì)的技巧備忘錄

2024-07-24 12:50:12

字體：大中小

供稿：網(wǎng)友

mysql 作為常用數(shù)據(jù)庫(kù)，操作賊六是必須的，對(duì)于數(shù)字操作相關(guān)的東西，那是相當(dāng)方便，本節(jié)就來(lái)拎幾個(gè)統(tǒng)計(jì)案例出來(lái)供參考！

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. 按天統(tǒng)計(jì)進(jìn)單量，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. 按小時(shí)統(tǒng)計(jì)進(jìn)單量

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. 同比昨天進(jìn)單量對(duì)比，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. 環(huán)比上周同小時(shí)進(jìn)單，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');