MySQL中使用sum case when统计实战讲解
1、场景:
车险,分组织统计某一时间段内报价的车辆数、出单数、出单保费金额、A端出单数、B端出单数
2、涉及表:
-
报价订单表
-
组织机构表
-
车辆信息表
建表语句如下
-
报价订单表
create table y_quote_order
(
id bigint auto_increment comment 'id'
primary key,
batch_no varchar(150) default '' not null comment '报价批次号,若为批量报价,此字段存报价批次号',
order_no varchar(150) default '' not null comment '报价订单号',
total_premium decimal(18, 2) null comment '保费+服务包费',
auto_id bigint not null comment '车辆信息',
status tinyint(2) not null comment '状态',
create_time datetime default CURRENT_TIMESTAMP not null comment '创建时间',
update_time datetime default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间',
product_premium decimal(18, 2) null comment '商品金额',
total_insurance_premium decimal(18, 2) null comment '总车险保费',
non_auto_premium decimal(18, 2) null comment '非车险保费',
client_type int null comment '业务入口 1-C端,2-A端',
organization_id bigint default 0 not null comment '组织机构id'
)
comment '报价订单表';
create index idx_organization_id
on quote_order (organization_id)
comment '组织机构id';
- 组织机构表
create table y_organization
(
id bigint auto_increment comment 'id'
primary key,
name varchar(100) default '' not null comment '名称',
code varchar(45) default '' null comment '编码',
short_code varchar(20) null comment '顶级组织简称',
short_name varchar(45) default '' not null comment '简称',
level int null comment '层级',
parent_code varchar(45) default '0' not null comment '组织code',
operator varchar(50) default '' not null comment '操作人',
create_time datetime default CURRENT_TIMESTAMP not null comment '创建时间',
update_time datetime default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间',
constraint uniq_short_name
unique (short_name)
)
comment '组织机构表';
- 车辆信息表
create table y_auto
(
id bigint auto_increment comment 'id'
primary key,
license_plate_no varchar(45) null comment '车牌号',
owner varchar(45) not null comment '车主姓名',
vin_no varchar(45) not null comment '车架号/车辆识别代码'
)
comment '车辆信息表';
create index idx_vin_no
on auto (vin_no)
comment '车架号';
3、实际SQL
SELECT
SUM( CASE WHEN qo.status = 10 THEN qo.total_insurance_premium ELSE 0 END ) AS totalPremium,
SUM( CASE WHEN qo.status = 10 THEN qo.non_auto_premium ELSE 0 END) AS nonAutoPremium,
(SELECT LEFT ( m.code, 6 ) ) AS oneLevelCode,
SUM( CASE WHEN qo.client_type = 1 AND qo.status = 10 THEN 1 ELSE 0 END ) AS issueC,
SUM( CASE WHEN qo.client_type = 2 AND qo.status = 10 THEN 1 ELSE 0 END ) AS issueA,
SUM( CASE WHEN qo.status = 10 THEN 1 ELSE 0 END ) AS issueCount,
count(DISTINCT CASE WHEN qo.status != 1 THEN a.vin_no ELSE null END) AS quoteCarNumber
FROM y_quote_order qo
JOIN y_organization m ON m.id = qo.organization_id
JOIN y_auto a ON a.id = qo.auto_id
WHERE qo.create_time BETWEEN '2021-05-01' AND '2022-09-14'
AND m.level != 0
and substr( m.code, 1, 6) not in (select mm.code from y_organization mm where m.level=1 and m.short_code in ('cctest','cheche'))
GROUP BY ( SELECT LEFT ( m.code, 6 ) )
ORDER BY totalPremium DESC;
说明:
qo.status = 10 承保完成;
qo.status = 1 暂存;
m.code 一级组织code为6位;
client_type = 2 A端;
client_type = 1 C端。
4、语法说明
-
sum() 聚合求和函数;
-
count()聚合统计函数;
-
distinct 去重;
-
as 后面跟别名;
-
between … and … 范围;
-
join … on … 连表查询;
-
substr(字段名 ,1, 6)截取字符串前六位;
-
select left (字段名, 6) 截取字符串前六位;
-
in() 多参数;
-
group by 分组;
-
order by 排序 (DESC–降序, ASC-- 升序);
-
case when … then … else … end 当满足时 ,这样,不满足那样;
5、case when … then … else … end 说明
示例:统计A、C两端销售额:
SELECT SUM( CASE WHEN qo.client_type = 1 THEN 1 ELSE 0 END ) AS issueC, SUM( CASE WHEN qo.client_type = 2 THEN 1 ELSE 0 END ) AS issueA, from y_quote_order qo where qo.status = 10;
总结:
常用于同一纬度不同阶段数据的统计,条件不能放在where后面是,便可使用case when … then … else … end。