INSERT OVERWRITE Table dw/fact_loan_apply partition(partition_date)
SELECT
    ...
    u.idty_type,
    u.channel_id,
    u.user_type,
    u.manager_id,
    u.sex,
    a.user_id,
    a.id AS apply_id,
    a.apply_time,
    a.is_current,
    b.salary_cnt,
    b.first_salary_time,
    b.last_salary_time,
    c.cereport_cnt,
    c.first_cereport_time,
    c.last_cereport_time,
    ...
    etl_time,
    partition_date
FROM
    ods.loan_apply a
    LEFT JOIN ods.users u ON a.user_id = u.id
    LEFT JOIN (
    SELECT
        user_id,
        loan_apply_id,
        count( id ) AS salary_cnt,
        min( created_at ) AS first_salary_time,
        max( created_at ) AS last_salary_time
    FROM
        ods.loan_apply_salary 
    GROUP BY
        user_id,
        loan_apply_id 
    ) b ON a.user_id = b.user_id 
    AND a.id = b.loan_apply_id
    LEFT JOIN (
    SELECT
        user_id,
        loan_apply_id,
        count( id ) AS cereport_cnt,
        min( created_at ) AS first_cereport_time,
        max( created_at ) AS last_cereport_time
    FROM
        ods.loan_apply_credit_report 
    GROUP BY
        user_id,
        loan_apply_id 
    ) c ON a.user_id = c.user_id 
    AND a.id = c.loan_apply_id

--- 借款申请量, 申请人数
CREATE TABLE dm/fact_loan_apply_sum (
	data_date string,
	idty_type string,
	channel_id BIGINT,
	user_type string,
	manager_id BIGINT,
	sex string,
	short_loan_term INT,
	long_loan_term INT,
	apply_num INT,
	apply_user_num INT,
	salary_cnt INT,
	cereport_cnt INT,
	short_loan_amount deciman ( 11, 2 ),
	long_loan_amount deciman ( 11, 2 ),
	etl_time string 
) COMMENT '' partitioned BY ( partition_date string COMMENT '分区日期' ) ROW format delimited FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';

INSERT overwrite TABLE dm: fact_loan_apply_sum PARTITION ( partition_date ) 
SELECT
	data_date,
	idty_type,
	channel_id,
	user_type,
	manager_id,
	sex,
	short_loan_term,
	long_loan_term,
	sum( loan_app_cnt ) AS apply_sum,
	count( DISTINCT user_id ) AS apply_user_sum,
	sum( salary_cnt ) AS salary_cnt,
	sum( cereport_cnt ) AS cereport_cnt,
	sum( short_loan_amount ) AS short_loan_amount,
	sum( long_loan_amount ) AS long_loan_amount,
	max( from_unixtime(...) ) AS etl_time 
FROM
	dw: fact_user_regiter_dtl
WHERE
	partition_data = form_unixtime (...) 
GROUP BY
	data_date,
	idty_date,
	channel_id,
	user_type,
	manager_id,
	sex
未完待续

insert overwrite table dwd/fact_credit_dtl partition(partition_date)
select
    from_unixtime(a.audit_date, 'yyyy-MM-dd') as data_date,
    u.idty_type,
    u.channel_id,
    u.user_type,
    u.manager_id,
    u.sex,
    a.id as credit_id,
    a.apply_id,
    a.user_id,
    a.audit_status,
    a.audit_result,
    a.passwd_products,
    a.product_terms,
    a.credit_type,
    a.credit_user_id,
    a.score,
    a.amount,
    (case when upper(audit_status) = 'PASS' then 1 else 0 end) as pass_cnt,
    (case when upper(audit_status) = 'DENY' then 1 else 0 end) as deny_cnt,
    from_unixtime(unix_timestamp(), 'yyyy-MM-dd JH:mm:ss') as etl_time,
    from_unixtime(a.created_time, 'yyyy-MM-dd') as partition_date,
from ods.loan_credit a left join (dim).users u on a.user_id=u.id

insert overwrite table dm/fact_loan_credit_sum partition(partition_date)
select
    data_date,
    idty_type,
    channel_id,
    user_type,
    manager_id,
    sex,
    credit_user_id,
    count(case when credit_type='cs' then credit_id else null end) as cs_num,
    count(distinct (case when credit_type='cs' then user_id else null end) as cs_user_num,    
    count(distinct (case when credit_type='cs' then apply_id else null end) as cs_app_num,
    count(distinct (case when credit_type='cs' and upper(audit_status) = 'PASS' then 1 else 0 end) as cs_pass_num,
    count(distinct ((case when credit_type='cs' and upper(audit_status) = 'DENY' then 1 else 0 end) as cs_deny_num,
    sum(distinct (case when credit_type='cs' and upper(audit_status) = 'PASS' then amount else null end) as cs_pass_amt,
    max(from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss')) as etl_time,
    max(from_unixtime(partition_date, 'yyyy-MM-dd HH:mm:ss')) as partition_date
from
    dw: fact_credit_dtl
where 
   data_date=from_unixtime(unix_timestamp(), 'yyyy-MM-dd')
group by
    data_date, idty_type, channel_id, user_type, manager_id, sex, credit_user_id;