86 lines
1.9 KiB
SQL
86 lines
1.9 KiB
SQL
CREATE TABLE dim_bank_grade AS
|
|
SELECT
|
|
bank_grade AS name,
|
|
COUNT(*) AS value
|
|
FROM
|
|
tb_banks
|
|
GROUP BY
|
|
bank_grade;
|
|
|
|
CREATE TABLE dim_loan_status AS
|
|
SELECT
|
|
loan_status AS name,
|
|
COUNT(*) AS value
|
|
FROM
|
|
tb_loans
|
|
GROUP BY
|
|
loan_status;
|
|
|
|
|
|
CREATE TABLE dim_loan_amount AS
|
|
SELECT
|
|
loan_date AS date,
|
|
ROUND(SUM(loan_amount) / 10000, 2) AS amount
|
|
FROM
|
|
tb_loans
|
|
GROUP BY
|
|
loan_date;
|
|
|
|
|
|
CREATE TABLE dim_repayment_status AS
|
|
SELECT
|
|
repayment_date,
|
|
ROUND(SUM(CASE WHEN repayment_status = '按时还款' THEN repayment_amount ELSE 0 END), 2) AS on_time_amount,
|
|
ROUND(SUM(CASE WHEN repayment_status = '逾期还款' THEN repayment_amount ELSE 0 END), 2) AS overdue_amount,
|
|
ROUND(SUM(repayment_amount), 2) AS total_amount
|
|
FROM
|
|
tb_repayments
|
|
WHERE
|
|
repayment_status IN ('按时还款', '逾期还款')
|
|
GROUP BY
|
|
repayment_date
|
|
ORDER BY
|
|
repayment_date;
|
|
|
|
|
|
CREATE TABLE dim_jie_count AS
|
|
SELECT
|
|
YEAR(FROM_UNIXTIME(UNIX_TIMESTAMP(loan_date, 'yyyy/MM/dd'))) AS name,
|
|
COUNT(*) AS value
|
|
FROM
|
|
tb_loans
|
|
GROUP BY
|
|
YEAR(FROM_UNIXTIME(UNIX_TIMESTAMP(loan_date, 'yyyy/MM/dd')))
|
|
ORDER BY
|
|
name;
|
|
|
|
|
|
|
|
CREATE TABLE dim_total_count AS
|
|
SELECT
|
|
t.year,
|
|
t.loan_status,
|
|
t.status_count,
|
|
ROUND(t.status_count * 100.0 / total.total_count, 2) AS percentage
|
|
FROM (
|
|
SELECT
|
|
YEAR(FROM_UNIXTIME(UNIX_TIMESTAMP(loan_date, 'yyyy/MM/dd'))) AS year,
|
|
loan_status,
|
|
COUNT(*) AS status_count
|
|
FROM
|
|
tb_loans
|
|
GROUP BY
|
|
YEAR(FROM_UNIXTIME(UNIX_TIMESTAMP(loan_date, 'yyyy/MM/dd'))),
|
|
loan_status
|
|
) t
|
|
JOIN (
|
|
SELECT
|
|
YEAR(FROM_UNIXTIME(UNIX_TIMESTAMP(loan_date, 'yyyy/MM/dd'))) AS year,
|
|
COUNT(*) AS total_count
|
|
FROM
|
|
tb_loans
|
|
GROUP BY
|
|
YEAR(FROM_UNIXTIME(UNIX_TIMESTAMP(loan_date, 'yyyy/MM/dd')))
|
|
) total ON t.year = total.year
|
|
ORDER BY
|
|
t.year, t.status_count DESC; |