时间戳

首页 > 解决方案 > 有没有办法将一些列分组为列数组？PostgreSQL

arrays - 有没有办法将一些列分组为列数组？PostgreSQL

问题描述

我有一个查询，我从哪里获取客户信息、他的贷款以及每笔超过约定金额 30% 的付款。就像您必须支付 20 美元并支付 30 美元一样，它会显示该付款。如果您在同一时期进行两到三笔付款，那么所有客户数据都会重复。我已经做了那个查询。但是对于报告，我想将支付数据分组到一个数组中（我正在使用 rails 并且它使用它的方法来减慢它的速度）以在 xlsx 视图中打印。所有渲染部分都完成了。事情是将查询的输出转换为我期望的输出：

  Columns      |   type     |
-----------------------------
|fullname      |text        |  <--- From this
|id_number     |string      |
|loan          |string      |
|obligation    |numeric     |
|balance       |numeric     |
|loan_start    |date        |  <--- Until here, are the data that will repeat. (client data)
|code          |string      |  <--- from this and below are the columns that change for each
|pmt_date      |date        |       payment. (payment data)
|bill_amt      |numeric     |
|pmt_amt       |numeric     |
|dif           |numeric     |  <--- This is the difference between bill_amount and amount
|note          |text        |
-----------------------------

我想要的输出：

  Columns      |   type     |
-----------------------------
|fullname      |text        |  <--- From this
|id_number     |string      |
|loan          |string      |
|obligation    |numeric     |
|balance       |numeric     |
|loan_start    |date        |  <--- Until here (client data)
|payments      |array       |  <--- each array will have an array with the payment data or json
|totals        |array       |  <--- This will have the sum of bill_amt, pmt_amt and dif
-----------------------------

This is actual query: 
SELECT
  alert.id,
  payment.codigo AS code,
  alertable_type,
  payment.payment_date AS date,
  lm.lm AS charge,
  amount AS amount,
  amount - lm.lm AS dif,
  ac.comment AS note,
  fn.full_name AS fullname,
  client.id_number AS cedula,
  loan.codigo AS loan,
  loan.total_loan_amt AS obligation,
  ch.sum AS balance,
  loan.loan_start_date AS pmt_start
FROM 
  prestamas.alerts AS alert
JOIN prestamas.payments AS payment
ON alert.alertable_id = payment.id
JOIN prestamas.loans AS loan
ON payment.loan_id = loan.id
JOIN (
  SELECT
    CASE 
      WHEN loan_periodicity = 'Quincenal'
        THEN payment_amt * 2
    ELSE payment_amt
    END AS lm,
    id
  FROM prestamas.loans
) AS lm
ON loan.id = lm.id
JOIN (
  SELECT
    CASE
      WHEN delete_comment IS NOT NULL THEN delete_comment
      WHEN solve_comment IS NOT NULL THEN solve_comment
      WHEN investigate_comment IS NOT NULL THEN investigate_comment
      WHEN read_comment IS NOT NULL THEN read_comment
      ELSE 'No se ha leído la Alerta'
    END AS "comment",
    id
  FROM prestamas.alerts
) AS ac
ON ac.id = alert.id
JOIN prestamas.clients AS client
ON client.id = loan.client_id
JOIN (
  SELECT
    CONCAT(
      TRIM(first_name),
      ' ',
      TRIM(last_name),
      ' ',
      TRIM(apellido_materno)
    ) AS full_name,
    id
  FROM prestamas.clients
) AS fn
ON fn.id = client.id
JOIN (
  SELECT
    SUM(balance) AS "sum",
    l.id AS "id"
  FROM prestamas.loans AS l
  JOIN prestamas.charges AS ch
  ON ch.loan_id = l.id
  GROUP BY l.id
) AS ch
ON ch.id = loan.id
WHERE alertable_type = 'Payment'
AND payment.payment_date >= DATE('2020-09-01') 
AND payment.payment_date <= DATE('2020-09-26')

谢谢！

标签： arrayspostgresql

解决方案

经过一些研究，我得到了这个解决方案：

SELECT
  id,
  fullname AS "Nombre del cliente",
  id_number AS "Ruc o Cédula",
  loan AS "N° Préstamo",
  loan_start AS "Fecha",
  obligation AS "M.Obligacióm",
  balance AS "Saldo Prest.",
  json_agg(                             <-- using json_agg to make an array of
    json_build_array(                       payments and json_build_array to make
      code,                                 an array with each payment data
      pmt_date,
      bill_amt,
      pmt_amt,
      dif,
      TRIM(
        regexp_replace(
          note, 
          E'[\\n\\r---]+', 
          ' ', 
          'g' 
        )
      )
    )
  ) AS payments,
  json_build_array(                     <-- make an array for the sum of charge
    sum(charge),
    sum(amount),
    sum(dif)
  )
FROM (
  SELECT
    client.id AS id,
    payment.codigo AS code,
    alertable_type,
    payment.payment_date AS date,
    lm.lm AS bill_amt,
    amount AS pmt_amt,
    amount - lm.lm AS dif,
    ac.comment AS note,
    fn.full_name AS fullname,
    client.id_number AS id_number,
    loan.codigo AS loan,
    loan.total_loan_amt AS obligation,
    ch.sum AS balance,
    loan.loan_start_date AS loan_start
  FROM 
    prestamas.alerts AS alert
  JOIN prestamas.payments AS payment
  ON alert.alertable_id = payment.id
  JOIN prestamas.loans AS loan
  ON payment.loan_id = loan.id
  JOIN (
    SELECT
      CASE 
        WHEN loan_periodicity = 'Quincenal'
          THEN payment_amt * 2
      ELSE payment_amt
      END AS lm,
      id
    FROM prestamas.loans
  ) AS lm
  ON loan.id = lm.id
  JOIN (
    SELECT
      CASE
        WHEN delete_comment IS NOT NULL THEN delete_comment
        WHEN solve_comment IS NOT NULL THEN solve_comment
        WHEN investigate_comment IS NOT NULL THEN investigate_comment
        WHEN read_comment IS NOT NULL THEN read_comment
        ELSE 'No se ha leído la Alerta'
      END AS "comment",
      id
    FROM prestamas.alerts
  ) AS ac
  ON ac.id = alert.id
  JOIN prestamas.clients AS client
  ON client.id = loan.client_id
  JOIN (
    SELECT
      CONCAT(
        TRIM(first_name),
        ' ',
        TRIM(last_name),
        ' ',
        TRIM(apellido_materno)
      ) AS full_name,
      id
    FROM prestamas.clients
  ) AS fn
  ON fn.id = client.id
  JOIN (
    SELECT
      SUM(balance) AS "sum",
      l.id AS "id"
    FROM prestamas.loans AS l
    JOIN prestamas.charges AS ch
    ON ch.loan_id = l.id
    GROUP BY l.id
  ) AS ch
  ON ch.id = loan.id
  WHERE alertable_type = 'Payment'
  AND payment.payment_date >= DATE('2020-09-01') 
  AND payment.payment_date <= DATE('2020-09-29')
) AS raw
GROUP BY 
  id, 
  fullname, 
  id_number, 
  loan,
  loan_start,
  obligation,
  balance
;

得到我想要的输出。


  Columns      |   type     |
-----------------------------
|fullname      |text        |  <--- From this
|id_number     |string      |
|loan          |string      |
|obligation    |numeric     |
|balance       |numeric     |
|loan_start    |date        |  <--- Until here (client data)
|payments      |json        |  <--- each array will have an array with the payment data or json
|totals        |json        |  <--- This will have the sum of bill_amt, pmt_amt and dif
-----------------------------

非常感谢每一位尝试或想要帮助的人！

推荐阅读