如何避免在MariaDB视图中重复计算字段

我在MariaDB中创建了一个用于统计库存贷方（Credit）、借方（Debit）和余额（balance）的视图，原SQL如下：

SELECT pitem,
    Bdate,
    credit,
    debit, 
    sum(ifnull(bal, 0)) OVER (PARTITION BY pitem ORDER BY bdate, DESC1) balance, 
    DESC1
FROM (
    SELECT a.Pitem,
        a.Bdate,
        a.Trn,
        If((trn=1 AND a.qty>0), a.Qty, 0) AS Credit,
        If(trn=0 OR a.qty<0, abs(a.Qty), 0) AS Debit,
        (If((trn=1 AND a.qty>0), a.Qty, 0)) - (If(trn=0 OR a.qty<0, abs(a.Qty), 0)) AS Bal,
        a.Desc1
    FROM (
        SELECT tblphmrefill.RfItem AS PItem,
            tblphmrefill.rfDate AS BDate,
            tblphmrefill.rfQty AS Qty,
            CONCAT(
                if(rfqty>0, 'Purchase No_', 'Discard_BMW_'), 
                coalesce(tblphmrefill.RfInvoice, 0), '_Vend_', rfsuppID
            ) AS DESC1,
            1 AS Trn
        FROM tblphmrefill
        UNION (
            SELECT invoicerefundphm.phmItem AS pItem,
                invoice.Bdate,
                invoicerefundphm.Qty,
                CONCAT('Refund_', billno, '_', invoice.BName) AS DESC1,
                1 AS Trn
            FROM invoice
            INNER JOIN invoicerefundphm 
            ON invoice.BilID = invoicerefundphm.Billno
        )
        UNION (
            SELECT invoicephm.phmItem AS PItem,
                invoice.BDate,
                invoicephm.Qty,
                CONCAT('Sales_', billno, '_', invoice.BName),
                0 AS Trn
            FROM invoicephm
            INNER JOIN invoice 
            ON invoicephm.Billno = invoice.BilID 
            ORDER BY invoice.bdate, invoice.bilid ASC
        )
    ) AS a 
) phmtbatch_int 
Order by Bdate, DESC1 Asc 

当前问题是计算Bal字段时，重复执行了Credit和Debit的计算逻辑。我尝试用用户变量复用计算结果：

SELECT a.Pitem,
     a.Bdate,
     a.Trn,
     @Credit := (If((trn=1 AND a.qty>0), a.Qty, 0)) AS Credit,
     @Debit := (If(trn=0 OR a.qty<0, abs(a.Qty), 0)) AS Debit,
     (@Credit - @Debit) AS Bal,
     a.Desc1

但MariaDB抛出Error 1351，提示：

view contains variable or parameter which is not allowed

请问有没有办法避免重复计算Credit、Debit来得到Bal字段？

方法1：嵌套派生表分层计算 #

新增一层派生表先计算Credit和Debit，外层直接引用这两个字段计算Bal，避免重复逻辑：

SELECT pitem,
    Bdate,
    credit,
    debit, 
    sum(ifnull(bal, 0)) OVER (PARTITION BY pitem ORDER BY bdate, DESC1) balance, 
    DESC1
FROM (
    SELECT 
        sub.Pitem,
        sub.Bdate,
        sub.Trn,
        sub.Credit,
        sub.Debit,
        sub.Credit - sub.Debit AS Bal,
        sub.Desc1
    FROM (
        SELECT a.Pitem,
            a.Bdate,
            a.Trn,
            If((trn=1 AND a.qty>0), a.Qty, 0) AS Credit,
            If(trn=0 OR a.qty<0, abs(a.Qty), 0) AS Debit,
            a.Desc1
        FROM (
            SELECT tblphmrefill.RfItem AS PItem,
                tblphmrefill.rfDate AS BDate,
                tblphmrefill.rfQty AS Qty,
                CONCAT(
                    if(rfqty>0, 'Purchase No_', 'Discard_BMW_'), 
                    coalesce(tblphmrefill.RfInvoice, 0), '_Vend_', rfsuppID
                ) AS DESC1,
                1 AS Trn
            FROM tblphmrefill
            UNION (
                SELECT invoicerefundphm.phmItem AS pItem,
                    invoice.Bdate,
                    invoicerefundphm.Qty,
                    CONCAT('Refund_', billno, '_', invoice.BName) AS DESC1,
                    1 AS Trn
                FROM invoice
                INNER JOIN invoicerefundphm 
                ON invoice.BilID = invoicerefundphm.Billno
            )
            UNION (
                SELECT invoicephm.phmItem AS PItem,
                    invoice.BDate,
                    invoicephm.Qty,
                    CONCAT('Sales_', billno, '_', invoice.BName),
                    0 AS Trn
                FROM invoicephm
                INNER JOIN invoice 
                ON invoicephm.Billno = invoice.BilID 
                ORDER BY invoice.bdate, invoice.bilid ASC
            )
        ) AS a 
    ) AS sub
) phmtbatch_int 
Order by Bdate, DESC1 Asc 

方法2：使用CTE（MariaDB 10.2+支持） #

用CTE分层拆解计算逻辑，提升代码可读性：

WITH raw_data AS (
    SELECT tblphmrefill.RfItem AS PItem,
        tblphmrefill.rfDate AS BDate,
        tblphmrefill.rfQty AS Qty,
        CONCAT(
            if(rfqty>0, 'Purchase No_', 'Discard_BMW_'), 
            coalesce(tblphmrefill.RfInvoice, 0), '_Vend_', rfsuppID
        ) AS DESC1,
        1 AS Trn
    FROM tblphmrefill
    UNION (
        SELECT invoicerefundphm.phmItem AS pItem,
            invoice.Bdate,
            invoicerefundphm.Qty,
            CONCAT('Refund_', billno, '_', invoice.BName) AS DESC1,
            1 AS Trn
        FROM invoice
        INNER JOIN invoicerefundphm 
        ON invoice.BilID = invoicerefundphm.Billno
    )
    UNION (
        SELECT invoicephm.phmItem AS PItem,
            invoice.BDate,
            invoicephm.Qty,
            CONCAT('Sales_', billno, '_', invoice.BName),
            0 AS Trn
        FROM invoicephm
        INNER JOIN invoice 
        ON invoicephm.Billno = invoice.BilID 
        ORDER BY invoice.bdate, invoice.bilid ASC
    )
),
calc_credit_debit AS (
    SELECT 
        PItem,
        BDate,
        Trn,
        If((trn=1 AND qty>0), Qty, 0) AS Credit,
        If(trn=0 OR qty<0, abs(Qty), 0) AS Debit,
        DESC1
    FROM raw_data
),
calc_bal AS (
    SELECT 
        PItem,
        BDate,
        Credit,
        Debit,
        Credit - Debit AS Bal,
        DESC1,
        sum(ifnull(Bal, 0)) OVER (PARTITION BY PItem ORDER BY BDate, DESC1) AS balance
    FROM calc_credit_debit
)
SELECT PItem, BDate, Credit, Debit, balance, DESC1
FROM calc_bal
ORDER BY BDate, DESC1 ASC;

方法3：使用LATERAL派生表（MariaDB 10.2+支持） #

通过LATERAL在同一层派生表中复用计算结果：

SELECT pitem,
    Bdate,
    credit,
    debit, 
    sum(ifnull(bal, 0)) OVER (PARTITION BY pitem ORDER BY bdate, DESC1) balance, 
    DESC1
FROM (
    SELECT 
        a.Pitem,
        a.Bdate,
        a.Trn,
        calc.Credit,
        calc.Debit,
        calc.Credit - calc.Debit AS Bal,
        a.Desc1
    FROM (
        SELECT tblphmrefill.RfItem AS PItem,
            tblphmrefill.rfDate AS BDate,
            tblphmrefill.rfQty AS Qty,
            CONCAT(
                if(rfqty>0, 'Purchase No_', 'Discard_BMW_'), 
                coalesce(tblphmrefill.RfInvoice, 0), '_Vend_', rfsuppID
            ) AS DESC1,
            1 AS Trn
        FROM tblphmrefill
        UNION (
            SELECT invoicerefundphm.phmItem AS pItem,
                invoice.Bdate,
                invoicerefundphm.Qty,
                CONCAT('Refund_', billno, '_', invoice.BName) AS DESC1,
                1 AS Trn
            FROM invoice
            INNER JOIN invoicerefundphm 
            ON invoice.BilID = invoicerefundphm.Billno
        )
        UNION (
            SELECT invoicephm.phmItem AS PItem,
                invoice.BDate,
                invoicephm.Qty,
                CONCAT('Sales_', billno, '_', invoice.BName),
                0 AS Trn
            FROM invoicephm
            INNER JOIN invoice 
            ON invoicephm.Billno = invoice.BilID 
            ORDER BY invoice.bdate, invoice.bilid ASC
        )
    ) AS a
    LATERAL (
        SELECT 
            If((a.trn=1 AND a.qty>0), a.Qty, 0) AS Credit,
            If(a.trn=0 OR a.qty<0, abs(a.Qty), 0) AS Debit
    ) AS calc
) phmtbatch_int 
Order by Bdate, DESC1 Asc 

以上方法均符合MariaDB视图语法要求，无需使用用户变量，同时避免了重复计算逻辑。

内容的提问来源于stack exchange，提问作者Viney Kumar