2021-03-30

with a as
 (SELECT 'JB' AS 产品,
         '001' AS 身份证号,
         '1' 贷款用途,
         100 金额,
         '10001' AS 贷款申请单号,
         'T' AS success --T允许,F不允许
    FROM dual
  union all
  SELECT 'JB' AS 产品,
         '001' AS 身份证号,
         '3' 贷款用途,
         200 金额,
         '10002' AS 贷款申请单号,
         'T' AS success --T允许,F不允许
    FROM dual
  union all
  SELECT 'JB' AS 产品,
         '001' AS 身份证号,
         '4' 贷款用途,
         300 金额,
         '10003' AS 贷款申请单号,
         'T' AS success --T允许,F不允许
    FROM dual
  union all
  SELECT 'JB' AS 产品,
         '001' AS 身份证号,
         '5' 贷款用途,
         400 金额,
         '10004' AS 贷款申请单号,
         'T' AS success --T允许,F不允许
    FROM dual
  union all
  SELECT 'JB' AS 产品,
         '001' AS 身份证号,
         '6' 贷款用途,
         500 金额,
         '10005' AS 贷款申请单号,
         'T' AS success --T允许,F不允许
    FROM dual
  union all
  SELECT 'HB' AS 产品,
         '002' AS 身份证号,
         '1' 贷款用途,
         100 金额,
         '20001' AS 贷款申请单号,
         'F' AS success --T允许,F不允许
    FROM dual
  union all
  SELECT 'HB' AS 产品,
         '002' AS 身份证号,
         '3' 贷款用途,
         200 金额,
         '20002' AS 贷款申请单号,
         'F' AS success --T允许,F不允许
    FROM dual
  union all
  SELECT 'HB' AS 产品,
         '002' AS 身份证号,
         '4' 贷款用途,
         300 金额,
         '20003' AS 贷款申请单号,
         'F' AS success --T允许,F不允许
    FROM dual
  union all
  SELECT 'HB' AS 产品,
         '002' AS 身份证号,
         '5' 贷款用途,
         400 金额,
         '20004' AS 贷款申请单号,
         'F' AS success --T允许,F不允许
    FROM dual
  union all
  SELECT 'HB' AS 产品,
         '002' AS 身份证号,
         '6' 贷款用途,
         500 金额,
         '20005' AS 贷款申请单号,
         'F' AS success --T允许,F不允许
    FROM dual
  union all
  SELECT 'JB' AS 产品,
         '003' AS 身份证号,
         '1' 贷款用途,
         100 金额,
         '30003' AS 贷款申请单号,
         'F' AS success --F允许,F不允许
    FROM dual
  union all
  SELECT 'JB' AS 产品,
         '003' AS 身份证号,
         '3' 贷款用途,
         200 金额,
         '30004' AS 贷款申请单号,
         'F' AS success --F允许,F不允许
    FROM dual
  union all
  SELECT 'JB' AS 产品,
         '003' AS 身份证号,
         '4' 贷款用途,
         300 金额,
         '30005' AS 贷款申请单号,
         'F' AS success --F允许,F不允许
    FROM dual
  union all
  SELECT 'JB' AS 产品,
         '003' AS 身份证号,
         '5' 贷款用途,
         400 金额,
         '30006' AS 贷款申请单号,
         'F' AS success --F允许,F不允许
    FROM dual
  union all
  SELECT 'JB' AS 产品,
         '003' AS 身份证号,
         '6' 贷款用途,
         500 金额,
         '30007' AS 贷款申请单号,
         'F' AS success --F允许,F不允许
    FROM dual)
SELECT A.产品,
       SUM(A.拒绝人数) 拒绝人数,
       SUM(放款笔数) 放款笔数,
       (SUM(A.放款人数) - SUM(A.拒绝人数)) / SUM(A.放款人数) * 100 || '%' 通过率,
       SUM(放款人数) 放款人数,
       SUM(消费笔数) 消费笔数,
       SUM(旅游笔数) 旅游笔数,
       SUM(装修笔数) 装修笔数,
       SUM(教育笔数) 教育笔数,
       SUM(医疗笔数) 医疗笔数,
       SUM(消费金额) 消费金额,
       SUM(旅游金额) 旅游金额,
       SUM(装修金额) 装修金额,
       SUM(教育金额) 教育金额,
       SUM(医疗金额) 医疗金额,
       (CASE
         WHEN SUM(总金额) = 0 THEN
          0
         ELSE
          ROUND(SUM(消费金额) / SUM(总金额), 5) * 100
       END) || '%' 消费占比,
       (CASE
         WHEN SUM(总金额) = 0 THEN
          0
         ELSE
          ROUND(SUM(旅游金额) / SUM(总金额), 5) * 100
       END) || '%' 旅游占比,
       (CASE
         WHEN SUM(总金额) = 0 THEN
          0
         ELSE
          ROUND(SUM(装修金额) / SUM(总金额), 5) * 100
       END) || '%' 装修占比,
       (CASE
         WHEN SUM(总金额) = 0 THEN
          0
         ELSE
          ROUND(SUM(教育金额) / SUM(总金额), 5) * 100
       END) || '%' 教育占比,
       (CASE
         WHEN SUM(总金额) = 0 THEN
          0
         ELSE
          ROUND(SUM(医疗金额) / SUM(总金额), 5) * 100
       END) || '%' 医疗占比
  FROM (SELECT a.身份证号,
               a.产品,
               a.success,
               COUNT(distinct 贷款申请单号) AS 放款笔数,
               1 AS 放款人数,
               (CASE
                 WHEN a.success = 'F' THEN
                  1
                 ELSE
                  0
               END) AS 拒绝人数,
               SUM(CASE
                     WHEN a.贷款用途 = '1' AND a.success = 'T' THEN
                      1
                     ELSE
                      0
                   END) 消费笔数,
               SUM(CASE
                     WHEN a.贷款用途 = '3' AND a.success = 'T' THEN
                      1
                     ELSE
                      0
                   END) 旅游笔数,
               SUM(CASE
                     WHEN a.贷款用途 = '4' AND a.success = 'T' THEN
                      1
                     ELSE
                      0
                   END) 装修笔数,
               SUM(CASE
                     WHEN a.贷款用途 = '5' AND a.success = 'T' THEN
                      1
                     ELSE
                      0
                   END) 教育笔数,
               SUM(CASE
                     WHEN a.贷款用途 = '6' AND a.success = 'T' THEN
                      1
                     ELSE
                      0
                   END) 医疗笔数,
               SUM(CASE
                     WHEN a.贷款用途 = '1' AND a.success = 'T' THEN
                      a.金额
                     ELSE
                      0
                   END) 消费金额,
               SUM(CASE
                     WHEN a.贷款用途 = '3' AND a.success = 'T' THEN
                      a.金额
                     ELSE
                      0
                   END) 旅游金额,
               SUM(CASE
                     WHEN a.贷款用途 = '4' AND a.success = 'T' THEN
                      a.金额
                     ELSE
                      0
                   END) 装修金额,
               SUM(CASE
                     WHEN a.贷款用途 = '5' AND a.success = 'T' THEN
                      a.金额
                     ELSE
                      0
                   END) 教育金额,
               SUM(CASE
                     WHEN a.贷款用途 = '6' AND a.success = 'T' THEN
                      a.金额
                     ELSE
                      0
                   END) 医疗金额,
               SUM(CASE
                     WHEN a.success = 'T' THEN
                      A.金额
                     ELSE
                      0
                   END) 总金额
          FROM a
         group by a.身份证号, a.产品, a.success) A
 GROUP BY A.产品