Bagaimana cara mengelompokkan data berdasarkan hari yang telah berlalu dan jumlahnya di PostgreSQL? (‹30, 31-60, 61-90, 91+)

Saya baru mengenal PostgreSQL dan menulis pernyataan SQL secara umum, jadi saya mencari sedikit bantuan!

Semua data saya ada di satu tabel (akun), dan saya perlu mengambil nilai dolar (jumlah_pemulihan), dua tanggal (tanggal_banding dan tanggal_pemulihan), dan terakhir mengelompokkan data berdasarkan selisih tanggal_banding dan tanggal_pemulihan untuk melihat total recovery_amount seiring bertambahnya usia.

Sesuatu seperti (tanggal_pemulihan - tanggal_banding) dalam hari = total jumlah_pemulihan dalam ‹30 Hari, total jumlah_pemulihan dalam 31-60 Hari, total jumlah_pemulihan dalam 61-90 Hari, dan total jumlah_pemulihan dalam 90 hari.

Saya mencoba pernyataan seperti:

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"

Tujuan akhirnya adalah menggunakan data ini pada grafik batang (jika itu membantu Anda memvisualisasikan alasannya.)

Ini jelas salah, karena saya telah mencoba banyak variasi tetapi tidak berhasil.

Bantuan apa pun yang dapat kalian berikan sangat kami hargai! Terima kasih sebelumnya!


postgresql sql database graph
person jmoneygram    schedule 07.10.2016    source sumber

Jawaban (4)


arrow_upward
0
arrow_downward

Tidak sejauh itu...

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

Jika Anda ingin menyimpan data dalam 3 kolom.

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

Akan menghasilkan sesuatu seperti

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
Terima kasih telah membantu saya! - person jmoneygram; 07.10.2016

arrow_upward
0
arrow_downward

Salah satu solusi untuk ini adalah agregasi bersyarat (lihat jawaban Raphaël).

Jika Anda tidak terlalu membutuhkannya sebagai kolom terpisah, Anda dapat membuatnya sedikit lebih dinamis (= lebih mudah dikelola) jika Anda menggunakan daftar rentang. Karena Anda berencana menggunakannya untuk laporan, itu tidak masalah

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;

Contoh: http://rextester.com/UYDOV15732

Sekarang jika Anda menginginkan rentang baru, Anda cukup menambahkannya ke daftar rentang tanpa mengubah kueri sebenarnya. Anda bahkan dapat secara otomatis menghasilkan rentang berukuran sama dari data yang secara otomatis menyesuaikan dengan data dalam tabel.

person a_horse_with_no_name    schedule 07.10.2016

arrow_upward
0
arrow_downward

Seharusnya dapat menggunakan DATE_PART untuk menemukan perbedaan hari, http://www.sqlines.com/postgresql/how-to/datediff.

Jadi, jika saya memahami dengan benar bahwa Anda mencoba mengelompokkan jumlah total pemulihan berdasarkan perbedaan tanggal, seperti:

    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
Saya pasti meneliti tautan itu... Terima kasih atas bantuannya! - person jmoneygram; 07.10.2016