过去3个月的平均值(SQL vertica)

Question

我需要找到每个客户过去3个月的平均逾期天数。不是滚动/移动平均数，而是一个时间数字，无论数据是否发生变化，始终计算最后3个月。

例如，现在我拥有的最后一个数据是2022年9月的，所以我需要2022年9月份、2022年8月和2022年7月的平均值。但如果数据发生变化，现在我有2022年10月，那么我需要10月、9月、8月等的平均值。

我试过了，但它计算错误的

CREATE TABLE AVERAGE_dpd
AS (
SELECT "SUM_WEIGHTED_AVG_PERMONTH"."NAME",
AVG("SUM_WEIGHTED_AVG_PERMONTH"."SUM") 
OVER (PARTITION BY "SUM_WEIGHTED_AVG_PERMONTH"."NAME"
order by MONTH ("SUM_WEIGHTED_AVG_PERMONTH"."LAST DAY OF MONTH_NETDUEDATE") desc 
rows between 2 preceding and CURRENT ROW) 
as AVG3Months
FROM "SUM_WEIGHTED_AVG_PERMONTH");

非常感谢你的帮助！

Answer 1

我想您正在期待这样的结果：

client_name | avg_last_3mth
-------------+--------------
Client 01   |         16.76
Client 02   |          5.75
Client 03   |        -13.95

所以我假设你有这样的输入数据(这就是我们通常希望数据伴随问题的方式(：

month_begin | client_name | dpd
------------+-------------+----
2022-01-05  | Client 01   |  12
2022-01-06  | Client 01   |  14
2022-01-07  | Client 01   |  18
2022-01-08  | Client 01   |  17
2022-01-05  | Client 02   |  12
2022-01-06  | Client 02   |  14
2022-01-07  | Client 02   |  18
2022-01-08  | Client 02   |  17
2022-01-05  | Client 03   |  12
2022-01-06  | Client 03   |  14
2022-01-07  | Client 03   |  18
2022-01-08  | Client 03   |  17

使用此输入数据，您可能需要具有当前月份第一个月(TRUNC(CURRENT_DATE,'MONTH')(的month_begin的行，加上前两个月。这就是我所做的，然后我显然按照客户名称分组：

WITH
-- input data I made up, don't use in query ..
dpd(month_begin,client_name,dpd) AS (
SELECT DATE '2022-05-01','Client 01',12
UNION ALL SELECT DATE '2022-06-01','Client 01',14
UNION ALL SELECT DATE '2022-07-01','Client 01',18
UNION ALL SELECT DATE '2022-08-01','Client 01',17        
UNION ALL SELECT DATE '2022-05-01','Client 02', 2
UNION ALL SELECT DATE '2022-06-01','Client 02', 4
UNION ALL SELECT DATE '2022-07-01','Client 02', 8        
UNION ALL SELECT DATE '2022-08-01','Client 02', 7
UNION ALL SELECT DATE '2022-05-01','Client 03',22        
UNION ALL SELECT DATE '2022-06-01','Client 03',24        
UNION ALL SELECT DATE '2022-07-01','Client 03',28        
UNION ALL SELECT DATE '2022-08-01','Client 03',27
)
-- real query starts here ..
SELECT
client_name
, AVG(dpd)::NUMERIC(5,2)  AS avg_last_3mth
FROM dpd
WHERE month_begin >= TRUNC(CURRENT_DATE,'MONTH') - '2 MONTHS'::INTERVAL YEAR TO MONTH
GROUP BY 
client_name;
-- out  client_name | avg_last_3mth 
-- out -------------+---------------
-- out  Client 02   |          6.33
-- out  Client 01   |         16.33
-- out  Client 03   |         26.33