人工智能 多场景实战 报表部分SQL命令总结

代码段及函数说明

WITH temp AS (
	SELECT
		u.id AS user_id,
		al.id AS list_id,
		ld.id AS order_id,
		date( u.inserttime ) AS regist_time,
		max( CASE WHEN pi.user_id IS NOT NULL THEN 1 ELSE 0 END ) AS if_fillin_pi,
		max( CASE WHEN al.borrower_id IS NOT NULL THEN 1 ELSE 0 END ) AS if_apply,
		max( CASE WHEN al.STATUS > 70 THEN 1 ELSE 0 END ) AS if_pass,
		max( CASE WHEN ld.borrower_id IS NOT NULL THEN 1 ELSE 0 END ) AS if_loan,
		max( CASE WHEN ld.payment_amount > 0 THEN 1 ELSE 0 END ) AS if_pay,
		max( CASE WHEN ld.owing_principal = 0 THEN 1 ELSE 0 END ) AS if_pay_1done 
	FROM
		u_user AS u
		LEFT JOIN u_personal_info AS pi ON u.id = pi.user_id
		LEFT JOIN loan_list AS al ON al.borrower_id = u.id
		LEFT JOIN loan_debt AS ld ON al.id = ld.list_id 
	GROUP BY
		u.id,
		al.id,
		ld.id 
	ORDER BY
		u.id 
	) SELECT
	regist_time,
	count( user_id ) AS regist_num,
	sum( if_fillin_pi ) AS fill_in_pi_num,
	sum( if_apply ) AS apply_num,
	sum( if_pass ) AS pass_num,
	sum( if_loan ) AS loan_num,
	sum( if_pay ) AS pay_num,
	sum( if_apply )/ count( user_id ) AS '注册→申请',
	sum( if_pass )/ sum( if_apply ) AS '申请→通过',
	sum( if_loan )/ sum( if_pass ) AS '通过→放款',
	sum( if_pay )/ sum( if_loan ) AS '放款→还过款',
	sum( if_pay_1done )/ sum( if_loan ) AS '还款→至少1期还完' 
FROM
	temp 
GROUP BY
	regist_time 
ORDER BY
	regist_time

涉及sql

with [临时表名] as (组成临时表的sql)

select from ：基本查询语句

date() ： 提取时间的日期部分

max() ： 聚合函数，求最大值，数据源为分组后每组中的所有数据

case when [字段] then [满足条件的返回值] else [不满足条件的返回值] end

left join： 左连接（保留左表全部数据和右表满足拼接条件的数据）

group by ：按字段分组，分组字段须被select

order by ：按字段排序 [asc] [desc]，默认升序

查询结果

心得：
通过实例记忆sql命令的作用及效果，加深印象，加强应用能力