mysql - 如何根据上个月的计算更新每个月的期初余额？

我实质上是想计算SQL中的滚动总数。我每个月都有一些不同的总计（费用，付款，调整，坏账），需要从上个月的期末余额中减去这些金额。 5月份的期末余额为28,814,788美元。鉴于6月份的总额（总计1,030,927美元），6月份的期末余额现在应为27,783,862美元（28,814,788美元-830,998美元）。这笔27,783,862美元现在应该是7月份的期初余额。

我编写了以下代码。 SELECT子句中的子查询提供了应从6月总计中减去5月的未偿余额（期末余额28,814,788美元）。

SELECT
    CASE    WHEN POST_DATE BETWEEN '2019-08-01' AND '2019-08-31' THEN 'August - 2019'
            WHEN POST_DATE BETWEEN '2019-07-01' AND '2019-07-31' THEN 'July - 2019'
            WHEN POST_DATE BETWEEN '2019-06-01' AND '2019-06-30' THEN 'June - 2019'
            ELSE 'Other' END as month_year,
    (SELECT sum(OUTSTANDING_AMT)
    FROM ARPB_TRANSACTIONS
    WHERE POST_DATE BETWEEN '2019-05-01' AND '2019-05-31') as begin_bal,
        sum(CHARGE_AMOUNT)+sum(VOID_AMOUNT) as charge_tot,
        sum(PAYMENT_AMOUNT) as payment_tot,
        sum(ADJUSTMENT_AMOUNT) as adjust_tot,
        sum(ACTIVE_AR_IN_OUT) as bad_debt
FROM F_ARPB_CUBE_ETR_DETAIL
WHERE POST_DATE BETWEEN '2019-06-01' AND '2019-08-31'
GROUP BY
    CASE    WHEN POST_DATE BETWEEN '2019-08-01' AND '2019-08-31' THEN 'August - 2019'
            WHEN POST_DATE BETWEEN '2019-07-01' AND '2019-07-31' THEN 'July - 2019'
            WHEN POST_DATE BETWEEN '2019-06-01' AND '2019-06-30' THEN 'June - 2019'
            ELSE 'Other' END

预期结果将是：DESIRED OUTPUT

最佳答案

因此，我实际上找到了自己问题的答案。我走了几步。

首先，我决定通过添加一栏来反映收费，付款，调整和坏账的总和，来对事情进行一些整理。这对于以后的运行总计很有用。感觉有点“ hacky”，但是，我只是在学习，等等。

sum(CHARGE_AMOUNT)+sum(VOID_AMOUNT)+sum(PAYMENT_AMOUNT)+
    sum(ADJUSTMENT_AMOUNT)+sum(ACTIVE_AR_IN_OUT) as sum_of_values

其次，我创建了一个总计。最初这是一个挑战，因为除非我给别名指定为month_year并将其放在FROM子句的子查询中，否则我无法引用月份和年份。

FROM
    (SELECT
CASE    WHEN POST_DATE BETWEEN '2019-06-01' AND '2019-06-30' THEN 'June - 2019'
        WHEN POST_DATE BETWEEN '2019-07-01' AND '2019-07-31' THEN 'July - 2019'
        WHEN POST_DATE BETWEEN '2019-08-01' AND '2019-08-31' THEN 'August - 2019'
        ELSE 'Other' END as month_year,
        (SELECT sum(OUTSTANDING_AMT)
    FROM ARPB_TRANSACTIONS
    WHERE POST_DATE BETWEEN '2019-05-01' AND '2019-05-31') as beginning_balance,
    sum(CHARGE_AMOUNT)+sum(VOID_AMOUNT) as charge_tot,
    sum(PAYMENT_AMOUNT) as payment_tot,
    sum(ADJUSTMENT_AMOUNT) as adjustment_tot,
    sum(ACTIVE_AR_IN_OUT) as bad_debt_tot,
    sum(CHARGE_AMOUNT)+sum(VOID_AMOUNT)+sum(PAYMENT_AMOUNT)+
    sum(ADJUSTMENT_AMOUNT)+sum(ACTIVE_AR_IN_OUT) as sum_of_values
FROM F_ARPB_CUBE_ETR_DETAIL
WHERE POST_DATE BETWEEN '2019-06-01' AND '2019-08-31'
GROUP BY CASE
    WHEN POST_DATE BETWEEN '2019-06-01' AND '2019-06-30' THEN 'June - 2019'
    WHEN POST_DATE BETWEEN '2019-07-01' AND '2019-07-31' THEN 'July - 2019'
    WHEN POST_DATE BETWEEN '2019-08-01' AND '2019-08-31' THEN 'August - 2019'
    ELSE 'Other' END ) as x

第三，这使我可以在运行的总表达式中具体引用“ month_year”：

 SELECT month_year, beginning_balance,charge_tot, payment_tot, adjustment_tot, bad_debt_tot, sum_of_values, SUM(sum_of_values) OVER (ORDER BY month_year desc) as running_total

最后，我需要从前几个月的运行总计中减去运行总和（而不是运行总和）（即“ beginning_balance”减去“ sum_of_values”），因此要做的就是修改上面的表达式，例如：

SELECT  month_year, beginning_balance,charge_tot, payment_tot, adjustment_tot, bad_debt_tot, sum_of_values, beginning_balance-SUM(sum_of_values) OVER (ORDER BY month_year desc) as running_total

所以这是最终的查询：

SELECT  month_year, beginning_balance,charge_tot, payment_tot, adjustment_tot, bad_debt_tot, sum_of_values, beginning_balance-SUM(sum_of_values) OVER (ORDER BY month_year desc) as running_total
FROM
    (SELECT
CASE    WHEN POST_DATE BETWEEN '2019-06-01' AND '2019-06-30' THEN 'June - 2019'
        WHEN POST_DATE BETWEEN '2019-07-01' AND '2019-07-31' THEN 'July - 2019'
        WHEN POST_DATE BETWEEN '2019-08-01' AND '2019-08-31' THEN 'August - 2019'
        ELSE 'Other' END as month_year,
        (SELECT sum(OUTSTANDING_AMT)
    FROM ARPB_TRANSACTIONS
    WHERE POST_DATE BETWEEN '2019-05-01' AND '2019-05-31') as beginning_balance,
    sum(CHARGE_AMOUNT)+sum(VOID_AMOUNT) as charge_tot,
    sum(PAYMENT_AMOUNT) as payment_tot,
    sum(ADJUSTMENT_AMOUNT) as adjustment_tot,
    sum(ACTIVE_AR_IN_OUT) as bad_debt_tot,
    sum(CHARGE_AMOUNT)+sum(VOID_AMOUNT)+sum(PAYMENT_AMOUNT)+
    sum(ADJUSTMENT_AMOUNT)+sum(ACTIVE_AR_IN_OUT) as sum_of_values
FROM F_ARPB_CUBE_ETR_DETAIL
WHERE POST_DATE BETWEEN '2019-06-01' AND '2019-08-31'
GROUP BY CASE
    WHEN POST_DATE BETWEEN '2019-06-01' AND '2019-06-30' THEN 'June - 2019'
    WHEN POST_DATE BETWEEN '2019-07-01' AND '2019-07-31' THEN 'July - 2019'
    WHEN POST_DATE BETWEEN '2019-08-01' AND '2019-08-31' THEN 'August - 2019'
    ELSE 'Other' END ) as x
ORDER BY month_year desc

Final Output

关于mysql - 如何根据上个月的计算更新每个月的期初余额？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/58329919/