oracle复杂查询
select t.退票原因,
t.客票类型,
t.数据,
t.退票量,
to_char(t.退票量 / max(t.退票量) over (partition by t.客票类型) * 100, 'FM999990.0') || '%' as 占比,
t.自动审核量,
t.自动审核率,
t.人工审核量,
t.人工审核率
from (
select decode(tra.CLAIM_REASON, 10, '自愿', 21, '航变', 22, '病退', 23, '重购全退', 24, '其他', 25, '出票后24小时内退票', 26,
'韩国当天退票',
50, '风控审核失败', 51, '用户拒绝支付',
decode(grouping(tra.CLAIM_REASON), 1, 'TOTAL', tra.CLAIM_REASON)) as 退票原因,
decode(trt.TICKET_TYPE, 'D', '国内', 'I', '海外',
decode(grouping(trt.TICKET_TYPE), 1, 'TOTAL', trt.TICKET_TYPE)) as 客票类型,
substr(to_char(tra.CREAT_DATE, 'yyyymmdd hh24:mi:ss'), 1, 6) as 数据,
count(1) as 退票量,
sum(decode(tra.AUTO_FIRST_AUDIT, 1, 1, 0)) as 自动审核量,
to_char(sum(decode(tra.AUTO_FIRST_AUDIT, 1, 1, 0)) / count(1) * 100, 'FM999990.0') || '%' as 自动审核率,
sum(decode(tra.AUTO_FIRST_AUDIT, 1, 0, 1)) as 人工审核量,
to_char(sum(decode(tra.AUTO_FIRST_AUDIT, 1, 0, 1)) / count(1) * 100, 'FM9999990.0') || '%' as 人工审核率
from T_REFUND_APPLY TRA
left join T_REFUND_TKT TRT on TRA.ID = TRT.APPLY_ID
where tra.STATUS <> '909'
and tra.CREAT_DATE >= to_date('2020-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and tra.CREAT_DATE < to_date('2020-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and tra.CHANNEL_NO in ('7402', '6105', '7401', '7860', '7690', '7701', '1101')
group by rollup (trt.TICKET_TYPE, tra.CLAIM_REASON),
substr(to_char(tra.CREAT_DATE, 'yyyymmdd hh24:mi:ss'), 1, 6)
) t;
select t.退票原因,
t.客票类型,
t.数据,
t.退票量,
t.自动审核量,
t.人工审核量
from (
select decode(tra.CLAIM_REASON, 10, '自愿', 21, '航变', 22, '病退', 23, '重购全退', 24, '其他', 25, '出票后24小时内退票', 26,
'韩国当天退票',
50, '风控审核失败', 51, '用户拒绝支付',
decode(grouping(tra.CLAIM_REASON), 1, 'TOTAL', tra.CLAIM_REASON)) as 退票原因,
decode(trt.TICKET_TYPE, 'D', '国内', 'I', '海外',
decode(grouping(trt.TICKET_TYPE), 1, 'TOTAL', trt.TICKET_TYPE)) as 客票类型,
substr(to_char(tra.CREAT_DATE, 'yyyymmdd hh24:mi:ss'), 1, 6) as 数据,
count(1) as 退票量,
sum(decode(tra.AUTO_FIRST_AUDIT, 1, 1, 0)) as 自动审核量,
sum(decode(tra.AUTO_FIRST_AUDIT, 1, 0, 1)) as 人工审核量,
decode(grouping(tra.CLAIM_REASON), 1, decode(grouping(trt.TICKET_TYPE), 1, 0, 1), 1) as flag
from T_REFUND_APPLY TRA
left join T_REFUND_TKT TRT on TRA.ID = TRT.APPLY_ID
where tra.STATUS <> '909'
and (
(tra.CREAT_DATE >= to_date('2020-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and tra.CREAT_DATE < to_date('2020-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
or
(tra.CREAT_DATE >= to_date('2020-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and tra.CREAT_DATE < to_date('2020-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
or
(tra.CREAT_DATE >= to_date('2019-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and tra.CREAT_DATE < to_date('2019-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
)
and tra.CHANNEL_NO in ('7402', '6105', '7401', '7860', '7690', '7701', '1101')
group by rollup (trt.TICKET_TYPE, tra.CLAIM_REASON),
substr(to_char(tra.CREAT_DATE, 'yyyymmdd hh24:mi:ss'), 1, 6)
) t
where t.flag = 1
2.
select substr(to_char(tra.CREAT_DATE, 'yyyymmdd hh24:mi:ss'), 1, 6) as 月份,
count(1) as 申请总量,
sum(decode(tra.ATTACHMENT_COMMIT_TP, 1, 1, 0)) as 在线提交材料申请量
from T_REFUND_APPLY TRA
where tra.STATUS <> '909'
and tra.CLAIM_REASON = '22'
and tra.CREAT_DATE >= to_date('2019-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and tra.CREAT_DATE < to_date('2020-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and tra.CHANNEL_NO in ('7402', '6105', '7401', '7860', '7690', '7701', '1101')
group by substr(to_char(tra.CREAT_DATE, 'yyyymmdd hh24:mi:ss'), 1, 6)
order by substr(to_char(tra.CREAT_DATE, 'yyyymmdd hh24:mi:ss'), 1, 6) desc;
3.
select *
from (select *
from (select t.CLAIMER_PHONE,
max(decode(t.reason, 'TOTAL', t.total)) 申请总量,
max(decode(t.reason, '10', t.total)) 自愿,
max(decode(t.reason, '21', t.total)) 航变,
max(decode(t.reason, '22', t.total)) 因病,
max(decode(t.reason, '23', t.total)) 重购全退,
max(decode(t.reason, '24', t.total)) 其他
from (
select tra.CLAIMER_PHONE,
decode(grouping(tra.CLAIM_REASON), 1, 'TOTAL', TRA.CLAIM_REASON) as reason,
count(1) as total
from T_REFUND_APPLY TRA
where tra.STATUS <> '909'
and tra.CLAIMER_PHONE is not null
and tra.CREAT_DATE >= to_date('2020-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and tra.CREAT_DATE < to_date('2020-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and tra.CHANNEL_NO in ('7402', '6105', '7401', '7860', '7690', '7701', '1101')
group by rollup (tra.CLAIMER_PHONE, tra.CLAIM_REASON)) t
group by t.CLAIMER_PHONE) t2
order by t2.申请总量 desc) t3
where t3.CLAIMER_PHONE is not null
and ROWNUM < = 10;
4.
select t.退票原因,
t.日期,
t.人工审核量,
to_char(t.人工审核量 / max(t.人工审核量) over (partition by t.日期) * 100, 'FM999990.0') || '%' as 占比
from (
select decode(tra.CLAIM_REASON, 10, '自愿', 21, '航变', 22, '病退', 23, '重购全退', 24, '其他', 25, '出票后24小时内退票', 26,
'韩国当天退票',
50, '风控审核失败', 51, '用户拒绝支付',
decode(grouping(tra.CLAIM_REASON), 1, 'TOTAL', tra.CLAIM_REASON)) as 退票原因,
substr(to_char(tra.CREAT_DATE, 'yyyymmdd hh24:mi:ss'), 1, 6) as 日期,
sum(decode(tra.AUTO_FIRST_AUDIT, 1, 0, 1)) as 人工审核量
from T_REFUND_APPLY TRA
where tra.STATUS <> '909' and
(tra.CREAT_DATE >= to_date('2020-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and tra.CREAT_DATE < to_date('2020-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
or (tra.CREAT_DATE >= to_date('2020-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and tra.CREAT_DATE < to_date('2020-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
or (tra.CREAT_DATE >= to_date('2019-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and tra.CREAT_DATE < to_date('2019-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
and tra.CHANNEL_NO in ('7402', '6105', '7401', '7860', '7690', '7701', '1101')
group by rollup (substr(to_char(tra.CREAT_DATE, 'yyyymmdd hh24:mi:ss'), 1, 6), tra.CLAIM_REASON)
) t;
