Как я могу сгруппировать данные по прошедшим дням и сумме в PostgreSQL? (‹30, 31-60, 61-90, 91+)

Я новичок в PostgreSQL и вообще пишу операторы SQL, поэтому мне нужна помощь!

Все мои данные находятся в одной таблице (учетных записях), и мне нужно получить значение в долларах (recovery_amount), две даты (appeal_date и recovery_date) и, наконец, сгруппировать данные по разнице между Appeal_date и recovery_date, чтобы увидеть total recovery_amount по мере созревания дней.

Что-то вроде (дата_восстановления - дата_апелляции) в днях = общая сумма_восстановления за ‹30 дней, общая сумма_восстановления за 31-60 дней, общая сумма_восстановления за 61-90 дней и общая сумма_восстановления за 90 дней.

Я пытаюсь сделать такие утверждения, как:

SELECT recovery_amount AS "Total"
CASE
  when (appeal_date - payment_date) >= 90 then 90
  when (appeal_date - payment_date) >= 60 then 60
  when (appeal_date - payment_date) >= 30 then 30
  else 0
END GROUP
FROM accounts
GROUP BY "Total"

Конечная цель — использовать эти данные на гистограмме (если это поможет вам визуализировать рассуждения).

Это, очевидно, далеко, так как я пробовал множество вариантов безрезультатно.

Любая помощь, которую вы, ребята/девушки, можете оказать, очень ценится! Заранее спасибо!


postgresql sql database graph
person jmoneygram    schedule 07.10.2016    source источник

Ответы (4)


arrow_upward
0
arrow_downward

Не так далеко...

SELECT  
sum(recoveryTotal) as TotalAmount, --if you want the overall total
sum (case when (appeal_date - payment_date) >= 90 then recoveryTotal else 0 end) as total90,
sum (case when (appeal_date - payment_date) >= 60 then recoveryTotal else 0 end)as total60,
sum (case when (appeal_date - payment_date) >= 30 then recoveryTotyl else 0 end) as total30
FROM accounts
person Raphaël Althaus    schedule 07.10.2016

arrow_upward
0
arrow_downward

Если вы хотите сохранить данные в 3 столбцах.

SELECT count(*) NoOfAccts, sum(recovery_amount) AS "GroupTotal"
CASE
  when (appeal_date - payment_date) >= 90 then 90
  when (appeal_date - payment_date) >= 60 then 60
  when (appeal_date - payment_date) >= 30 then 30
  else 0
END GROUP
FROM accounts
GROUP BY CASE
  when (appeal_date - payment_date) >= 90 then 90
  when (appeal_date - payment_date) >= 60 then 60
  when (appeal_date - payment_date) >= 30 then 30
  else 0
END

Получится что-то вроде

NoOfAccts   GroupTotal    Group
22          3754.48       90
96          24834.83      60
123         38348.48      30
9999        1824389.83    0
person xQbert    schedule 07.10.2016
comment
Спасибо за помощь! - person jmoneygram; 07.10.2016

arrow_upward
0
arrow_downward

Одним из решений этого является условное агрегирование (см. ответ Рафаэля).

Если вам это не нужно как отдельные столбцы, вы можете сделать это немного более динамичным (= проще в обслуживании), если вы используете список диапазонов. Поскольку вы планируете использовать это для отчета, это должно быть в порядке.

with limits (range) as (
  values 
    (int4range(0,30,'[)')),
    (int4range(30,60,'[)')),
    (int4range(60,90,'[)')),
    (int4range(90,null,'[)'))
)
select l.range as day_range,
       sum(coalesce(recovery_amount,0)) as total
from limits l
  left join accounts ac
    on (ac.appeal_date - ac.payment_date) <@ l.range
group by range
order by range;

Пример: http://rextester.com/UYDOV15732

Теперь, если вам нужен новый диапазон, вы просто добавляете его в список диапазонов, не изменяя фактический запрос. Вы даже можете автоматически генерировать диапазоны одинакового размера из данных, которые автоматически адаптируются к данным в таблице.

person a_horse_with_no_name    schedule 07.10.2016

arrow_upward
0
arrow_downward

Должна быть возможность использовать DATE_PART, чтобы найти разницу в днях, http://www.sqlines.com/postgresql/how-to/datediff.

Итак, если я правильно понимаю, что вы пытаетесь сгруппировать общую сумму возмещения по разнице дат, что-то вроде:

    SELECT CASE DATE_PART('day', appeal_date - payment_date)
            WHEN <= 30 THEN '<=30'
            WHEN BETWEEN 31 and 60 THEN '31-60'
            WHEN BETWEEN 61 and 90 THEN '61-90'
            WHEN > 90 THEN '>90'
            ELSE 0
        END as 'DateRange',
        SUM(recovery_amount) AS "Total"
    FROM accounts
    GROUP BY CASE DATE_PART('day', appeal_date - payment_date)
            WHEN <= 30 THEN '<=30'
            WHEN BETWEEN 31 and 60 THEN '31-60'
            WHEN BETWEEN 61 and 90 THEN '61-90'
            WHEN > 90 THEN '>90'
            ELSE 0
        END
person William    schedule 07.10.2016
comment
Я определенно изучил эту ссылку... Спасибо за помощь! - person jmoneygram; 07.10.2016