时间戳

首页 > 解决方案 > postgresql 中默认的迟到和第一次日期的百分比

sql - postgresql 中默认的迟到和第一次日期的百分比

问题描述

我有一张表格，我在其中登记债务和付款日期：

CREATE TABLE my_table 
(
    the_debt_id varchar(6) NOT NULL, 
    the_debt_paid timestamp NOT NULL, 
    the_debt_due date NOT NULL
)

INSERT INTO my_table
VALUES ('LMUS01', '2019-05-02 09:00:01', '2019-05-02'), 
       ('LMUS01', '2019-06-03 10:45:12', '2019-06-02'), 
       ('LMUS01', '2019-07-01 15:39:58', '2019-07-02'), 
       ('LMUS02', '2019-05-03 19:43:44', '2019-05-07'), 
       ('LMUS02', '2019-06-07 08:37:05', '2019-06-07')

我想要的是汇总每个债务ID、付款（每个债务ID的付款数量）、迟到（如果支付日期>到期日期）、每个债务ID的第一个到期日期以及每笔债务迟到的百分比。这张表应该给出的想法：

the_debt_id    payments    tardiness    first_due_date    percentage
LMUS01         3           1            2019-05-02        0.33
LMUS02         2           0            2019-05-07        0

所以到目前为止我尝试了这个：

WITH t1 AS(
SELECT the_debt_id, the_debt_due, the_debt_paid, 
CASE
WHEN the_debt_paid::date > the_debt_due THEN 1
ELSE 0
END AS tardiness 
FROM my_table), 
t2 AS(
SELECT the_debt_id, 
sum(tardiness) AS tardiness, 
count(the_debt_id) AS payments, 
first_value(the_debt_due)
FROM t1
GROUP BY the_debt_id),
t3 AS(
SELECT *,
tardiness/payments::float AS percentage 
FROM t2)
SELECT * FROM t3

我收到一个错误，它说我需要一个 OVER 子句，这意味着我需要一个分区，但我不确定如何组合 GROUP BY 和 PARTITION。任何帮助将不胜感激。

标签： sqlpostgresql

解决方案

聚合似乎合适：

select the_debt_id,
       count(*) as payments,
       count(*) filter (where the_debt_paid::date > the_debt_due) as num_tardy,
       min(the_debt_due) as first_due_date,
       avg( (the_debt_paid::date > the_debt_due)::int ) as tardy_ratio
from my_table t
group by the_debt_id;

这是一个 db<>fiddle。

推荐阅读