amazon红移查询以获取拖欠金额和月底过期天数

x8goxv8g 于 2021-06-18 发布在 Mysql

关注(0)|答案(1)|浏览(251)

由于用例中的误解而更改问题。
amazon红移查询以下问题语句。
数据结构：
id-主键
账户id-贷款账户的唯一id（该id对于特定贷款账户的所有emi都是相同的，根据贷款期限可能重复6次或12次，分别为6个月或12个月）
状态-有薪或无薪（emi的无薪仅在我的无薪emi之后）
s\U id-只是一个调度id，它是特定贷款id的连续数字
到期日-特定emi的到期日
本金-到期金额
表格：

id       acc_id status   s_id    due_date            principal
9999957     10003   PAID    102  2018-07-02 12:00:00        4205
9999958     10003   UNPAID  103  2018-08-02 12:00:00        4100
9999959     10003   UNPAID  104  2018-09-02 12:00:00        4266
9999960     10003   UNPAID  105  2018-10-02 12:00:00        4286
9999962     10004   PAID    106  2018-07-02 12:00:00        3200
9999963     10004   PAID    107  2018-08-02 12:00:00        3100
9999964     10004   UNPAID  108  2018-09-02 12:00:00        3266
9999965     10004   UNPAID  109  2018-10-02 12:00:00        3286

用例-
未付金额在到期日后变为拖欠（逾期）。
因此，我需要在每个月底计算拖欠金额，从第一个到期日（本例中为7月2日）到最后一个到期日（假设为当前月份的11月2日）
我还需要计算月底逾期天数。
以上数据说明：
从提供的样本数据来看，7月底没有emi到期，因此拖欠金额为0
但在8月底，id 999958到期，截至8月31日，拖欠金额为4100，逾期天数为29天（8月31日减去8月2日）
问题是：我需要计算贷款（accïid）而不是emi。
为了进一步解释，第一个emi将在第一个月到期29天，第二个月到期59天，第二个emi也将在第二个月到期29天。但我需要这个在贷款水平（帐户id）。
同样的例子延续到9月30日，账户id 10003自8月2日起到期，因此截至9月30日，到期金额为8366（4100+4266），dpd（逾期天数）为59（29+30）。
另外，acc_id 10004到期日为3100，dpd为28（9月30日-9月2日）。
最终输出如下：

Month_End   DPD_Band    Amount
2018/08/31  0-29        4100
2018/08/31  30-59       0
2018/08/31  60-89       0
2018/08/31  90+         0
2018/09/30  0-29        3100
2018/09/30  30-59       8366
2018/09/30  60-89       0
2018/09/30  90+         0

查询尝试：可以基于拖欠天数的case语句创建dpd带。我需要真正的帮助，首先创建月底，然后找到投资组合水平的金额如上所述，为不同的拖欠天数。

sql mysql amazon-redshift

来源：https://stackoverflow.com/questions/53428626/amazon-redshift-query-to-get-delinquent-amount-and-days-past-due-at-the-end-of-m

1条答案

按热度按时间

l3zydbqr1#

在op澄清了哪些rdbms之后编辑为红移兼容(mysql需要一个不同的答案）
从第一个记录到上个月底，下面为每个月创建一个记录。
然后它加入到您的未付记录中，聚合选择将结果放入哪个括号中。

WITH
  first_month AS
(
  SELECT LAST_DAY(MIN(due_date)) AS end_date FROM yourTable
),
  months AS
(
  SELECT
    LAST_DAY(ADD_MONTHS(first_month.end_date, s.id))    AS end_date
  FROM
    first_month
  CROSS JOIN
    generate_series(
      1,
      DATEDIFF(month, (SELECT end_date FROM first_month), CURRENT_DATE)
    )
      AS s(id)
),
  monthly_delinquents AS
(
  SELECT
    yourTable.*,
    months.end_date                                        AS month_end_date,
    DATEDIFF(DAY, yourTable.due_date, months.end_date)     AS days_past_due
  FROM
    months
  LEFT JOIN
    yourTable
      ON  yourTable.status   = 'UNPAID'
      AND yourTable.due_date < months.end_date
)
SELECT
  month_end_date,
  SUM(CASE WHEN days_past_due >= 00 AND days_past_due < 30 THEN principal ELSE 0 END)  AS dpd_00_29,
  SUM(CASE WHEN days_past_due >= 30 AND days_past_due < 60 THEN principal ELSE 0 END)  AS dpd_30_59,
  SUM(CASE WHEN days_past_due >= 60 AND days_past_due < 90 THEN principal ELSE 0 END)  AS dpd_60_89,
  SUM(CASE WHEN days_past_due >= 90                        THEN principal ELSE 0 END)  AS dpd_90plus
FROM
  monthly_delinquents
GROUP BY
  month_end_date
ORDER BY
  month_end_date

也就是说，通常情况下，像这样的想法是一个坏主意。过期一年怎么办？它只是坐在90多个类别，从来没有移动。而且，如果您想扩展它，您需要更改查询和任何其他您曾经编写的依赖于它的查询。
相反，你可以正常化你的输出。。。

WITH
  first_month AS
(
  SELECT LAST_DAY(MIN(due_date)) AS end_date FROM yourTable
),
  months AS
(
  SELECT
    LAST_DAY(ADD_MONTHS(first_month.end_date, s.id))    AS end_date
  FROM
    first_month
  CROSS JOIN
    generate_series(
      1,
      DATEDIFF(month, (SELECT end_date FROM first_month), CURRENT_DATE)
    )
      AS s(id)
),
  monthly_delinquents AS
(
  SELECT
    yourTable.*,
    months.end_date                                        AS month_end_date,
    DATEDIFF(DAY, yourTable.due_date, months.end_date)     AS days_past_due
  FROM
    months
  LEFT JOIN
    yourTable
      ON  yourTable.status   = 'UNPAID'
      AND yourTable.due_date < months.end_date
)
SELECT
  month_end_date,
  (days_past_due / 30) * 30   AS days_past_due_band,
  SUM(principal)              AS total_principal,
  COUNT(*)                    AS total_rows
FROM
  monthly_delinquents
GROUP BY
  month_end_date,
  (days_past_due / 30) * 30
ORDER BY
  month_end_date,
  (days_past_due / 30) * 30

赞(0）回复(0）举报 2021-06-18

我来回答

amazon红移查询以获取拖欠金额和月底过期天数

1条答案

相关问题

热门标签

最新问答