mysql解析sql（含计算）的使用（个人记录）

SELECT DATE(create_date) as 'playDay', 
count(*) as 'playPv', 
count(DISTINCT open_id) as 'playUv', 
count(CASE WHEN play_percentage>=75 THEN open_id END) as 'finishPv', 
count(DISTINCT CASE WHEN play_percentage>=75 THEN open_id END) as 'finishUv',
 round((count(CASE WHEN play_percentage>=75 THEN open_id END)/count(*))*100,2) AS 'finishprv' 

FROM play_info a WHERE a.del_flag = '0' AND 
a.play_duration > 2 AND date_format(a.create_date,'%Y-%m-%d') BETWEEN '2021-02-24' AND '2021-03-02' 
GROUP BY DATE(create_date) limit 20

mysql解析sql（含计算）的使用如图计算结果：

页面展示如图：

数据表的数据如图：

数据表结构：

DROP TABLE IF EXISTS play_info;
-- 播放记录
CREATE TABLE play_info(
  id varchar(64) BINARY NOT NULL comment 'id',
  video_id VARCHAR(64) NOT NULL COMMENT '外键，对应短视频表主键',
  open_id varchar(64)BINARY comment '用户id',
  play_percentage int(10) DEFAULT NULL COMMENT '播放百分比，取值0~100之间',
  duration int(10) DEFAULT NULL COMMENT '总时长',
  play_duration int(10) DEFAULT NULL COMMENT '播放时长',
  invoke_from varchar(64)BINARY comment '来源(播放属性)',
  media_id VARCHAR(64) COMMENT '号主id',
  create_by varchar(64) NOT NULL COMMENT '创建者' ,
  create_date datetime NOT NULL COMMENT '创建时间' ,
  update_by varchar(64) NOT NULL COMMENT '更新者' ,
  update_date datetime NOT NULL COMMENT '更新时间' ,
  remarks varchar(255) DEFAULT NULL COMMENT '备注信息' ,
  del_flag char(1) NOT NULL DEFAULT '0' COMMENT '删除标记（0：正常；1：删除）',
  primary key (id)
)ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;