MySQL分区间进行数据统计

蔚落 2022-11-18 01:29 217阅读 0赞

假设当前数据库mc\_userdb，其中有customer\_login(用户登陆表)

CREATE DATABASE `mc_userdb`;
    USE `mc_userdb`;
    
    DROP TABLE IF EXISTS `customer_login`;
    CREATE TABLE `customer_login` (
      `customer_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户ID',
      `login_name` VARCHAR(20) NOT NULL COMMENT '用户登陆名',
      `password` CHAR(32) NOT NULL COMMENT 'md5加密的密码',
      `user_stats` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '用户状态',
      `modified_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
      PRIMARY KEY (`customer_id`)
    ) ENGINE=INNODB AUTO_INCREMENT=10011 DEFAULT CHARSET=utf8 COMMENT='用户登陆表';

另外有数据库mc\_orderdb，其中有order\_master(订单主表)

CREATE DATABASE `mc_orderdb`;
    USE `mc_orderdb`;
    
    DROP TABLE IF EXISTS `order_master`;
    CREATE TABLE `order_master` (
      `order_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '订单ID',
      `order_sn` BIGINT(20) UNSIGNED NOT NULL COMMENT '订单编号 yyyymmddnnnnnnnn',
      `customer_id` INT(10) UNSIGNED NOT NULL COMMENT '下单人ID',
      `shipping_user` VARCHAR(10) NOT NULL COMMENT '收货人姓名',
      `province` SMALLINT(6) NOT NULL COMMENT '收货人所在省',
      `city` SMALLINT(6) NOT NULL COMMENT '收货人所在市',
      `district` SMALLINT(6) NOT NULL COMMENT '收货人所在区',
      `address` VARCHAR(100) NOT NULL COMMENT '收货人详细地址',
      `payment_method` TINYINT(4) NOT NULL COMMENT '支付方式:1现金,2余额,3网银,4支付宝,5微信',
      `order_money` DECIMAL(8,2) NOT NULL COMMENT '订单金额',
      `district_money` DECIMAL(8,2) NOT NULL DEFAULT '0.00' COMMENT '优惠金额',
      `shipping_money` DECIMAL(8,2) NOT NULL DEFAULT '0.00' COMMENT '运费金额',
      `payment_money` DECIMAL(8,2) NOT NULL DEFAULT '0.00' COMMENT '支付金额',
      `shipping_comp_name` VARCHAR(10) DEFAULT NULL COMMENT '快递公司名称',
      `shipping_sn` VARCHAR(50) DEFAULT NULL COMMENT '快递单号',
      `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
      `shipping_time` DATETIME DEFAULT NULL COMMENT '发货时间',
      `pay_time` DATETIME DEFAULT NULL COMMENT '支付时间',
      `receive_time` DATETIME DEFAULT NULL COMMENT '收货时间',
      `order_status` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '订单状态',
      `order_point` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '订单积分',
      `invoice_title` VARCHAR(100) DEFAULT NULL COMMENT '发票抬头',
      `modified_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
      PRIMARY KEY (`order_id`),
      UNIQUE KEY `ux_ordersn` (`order_sn`)
    ) ENGINE=INNODB AUTO_INCREMENT=10011 DEFAULT CHARSET=utf8 COMMENT='订单主表';

业务场景：统计消费总金额大于1000元的，800到1000元的，500到800元的，以及500元以下的人数

第一步： 根据customer\_id字段进行分组，计算出每位用户的消费总和

SELECT customer_id,SUM(order_money) AS total_money
    FROM mc_orderdb.`order_master`
    GROUP BY customer_id

第二步：将customer\_login与order\_master进行左连接，对左连接的结果集使用count函数计算出各个区间段的数据统计

SELECT COUNT(CASE WHEN IFNULL(total_money,0) >=1000 THEN a.customer_id END) AS '>1000',
          COUNT(CASE WHEN IFNULL(total_money,0) >=800 AND IFNULL(total_money,0) <1000 THEN a.customer_id END) AS '800~1000',
          COUNT(CASE WHEN IFNULL(total_money,0) >=500 AND IFNULL(total_money,0) <800 THEN a.customer_id END) AS '500~800',
          COUNT(CASE WHEN IFNULL(total_money,0) <500  THEN a.customer_id END) AS '<500'
    FROM mc_userdb.`customer_login` a
    LEFT JOIN
    ( SELECT customer_id,SUM(order_money) AS total_money
      FROM mc_orderdb.`order_master` GROUP BY customer_id) b
    ON a.`customer_id`=b.`customer_id`