PostgreSQL查询中每个月的平均最近三个月

发布时间：2020-12-13 15:52:07 所属栏目：百科来源：网络整理

导读：我正在尝试在 Postgresql中构建一个用于预算的查询. 我目前有一个按月分组的数据列表. 对于一年中的每个月,我需要检索前三个月的平均月销售额.例如,在1月份,我需要从上一年的10月到12月的平均月销售额.所以结果将是这样的： 1 12345.672 54321.563 242412.45

我正在尝试在 Postgresql中构建一个用于预算的查询.

我目前有一个按月分组的数据列表.

对于一年中的每个月,我需要检索前三个月的平均月销售额.例如,在1月份,我需要从上一年的10月到12月的平均月销售额.所以结果将是这样的：

1  12345.67
2  54321.56
3  242412.45

这是按月号分组的.

以下是我的查询中的代码片段,它将获得当前月份的销售额：

LEFT JOIN (SELECT SUM((sti.cost + sti.freight) * sti.case_qty * sti.release_qty)
                  AS trsf_cost,DATE_PART('month',st.invoice_dt) as month
             FROM stransitem sti,stocktrans st
            WHERE sti.invoice_no = st.invoice_no 
              AND st.invoice_dt >= date_trunc('year',current_date) 
              AND st.location_cd = 'SLC' 
              AND st.order_st != 'DEL'
         GROUP BY month) as trsf_cogs ON trsf_cogs.month = totals.month

我需要另一个加入,它会让我得到同样的东西,只是前三个月的平均值,但我不确定如何.

这将始终是1月至12月(1-12)列表,从1月开始到12月结束.

解决方法

这是窗口函数的经典问题.以下是如何解决这个问题：

SELECT month_nr,(COALESCE(m1,0)
      + COALESCE(m2,0)
      + COALESCE(m3,0))
      /
      NULLIF ( CASE WHEN m1 IS NULL THEN 0 ELSE 1 END
             + CASE WHEN m2 IS NULL THEN 0 ELSE 1 END
             + CASE WHEN m3 IS NULL THEN 0 ELSE 1 END,0) AS avg_prev_3_months
      -- or divide by 3 if 3 previous months are guaranteed or you don't care
FROM   (
    SELECT date_part('month',month) as month_nr,lag(trsf_cost,1) OVER w AS m1,2) OVER w AS m2,3) OVER w AS m3
    FROM  (
        SELECT date_part( 'month',month) as trsf_cost -- some dummy nr. for demo,month
        FROM   generate_series('2010-01-01 0:0'::timestamp,'2012-01-01 0:0'::timestamp,'1 month') month
        ) x
    WINDOW w AS (ORDER BY month)
    ) y;

这要求任何月份都不会丢失！另外,看看这个相关的答案：
How to compare the current row with next and previous row in PostgreSQL?

计算每个月的正确平均值.如果只有两个先前的飞蛾然后分开2,等等.如果没有上一个.几个月,结果是NULL.

在子查询中,使用

date_trunc('month',st.invoice_dt)::date AS month

代替

DATE_PART('month',st.invoice_dt) as month

所以你可以轻松地分类几年！

更多信息

>窗口功能lag()
> date_trunc()

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!