SQLite计算四个计算列平均值报错，求正确实现方法

错误原因 #

SQLite的AVG()是聚合函数，仅接受单个参数，用于对一列数据进行分组聚合计算平均值。你试图直接传入4个CASE生成的列作为多个参数，这不符合函数语法，因此触发语法错误。

正确实现方式 #

要实现逐行计算4个CASE结果的平均值（行内平均），需要手动计算这4个值的总和，再除以非NULL值的数量（避免NULL影响结果）。具体代码如下：

-- 先计算4个间隔值，再求行内平均
SELECT
    cust_id,
    order_entry_date,
    -- 计算第1个间隔
    CASE 
        WHEN LAG(order_entry_date,1) OVER (PARTITION BY cust_id ORDER BY order_entry_date ASC) <> ""
        THEN JULIANDAY("orders_tbl"."order_entry_date") - JULIANDAY(LAG(order_entry_date,1) OVER (PARTITION BY cust_id ORDER BY order_entry_date ASC))
    END AS order_interval_using_pvs1_order_date,
    -- 计算第2个间隔
    CASE 
        WHEN LAG(order_entry_date,2) OVER (PARTITION BY cust_id ORDER BY order_entry_date ASC) <> ""
        THEN JULIANDAY(LAG(order_entry_date,1) OVER (PARTITION BY cust_id ORDER BY order_entry_date ASC)) - JULIANDAY(LAG(order_entry_date,2) OVER (PARTITION BY cust_id ORDER BY order_entry_date ASC))
    END AS order_interval_using_pvs2_order_date,
    -- 计算第3个间隔
    CASE 
        WHEN LAG(order_entry_date,3) OVER (PARTITION BY cust_id ORDER BY order_entry_date ASC) <> ""
        THEN JULIANDAY(LAG(order_entry_date,2) OVER (PARTITION BY cust_id ORDER BY order_entry_date ASC)) - JULIANDAY(LAG(order_entry_date,3) OVER (PARTITION BY cust_id ORDER BY order_entry_date ASC))
    END AS order_interval_using_pvs3_order_date,
    -- 计算第4个间隔
    CASE 
        WHEN LAG(order_entry_date,4) OVER (PARTITION BY cust_id ORDER BY order_entry_date ASC) <> ""
        THEN JULIANDAY(LAG(order_entry_date,3) OVER (PARTITION BY cust_id ORDER BY order_entry_date ASC)) - JULIANDAY(LAG(order_entry_date,4) OVER (PARTITION BY cust_id ORDER BY order_entry_date ASC))
    END AS order_interval_using_pvs4_order_date,
    -- 计算行内平均值：处理NULL，仅统计有效数值的平均
    (
        COALESCE(order_interval_using_pvs1_order_date, 0) +
        COALESCE(order_interval_using_pvs2_order_date, 0) +
        COALESCE(order_interval_using_pvs3_order_date, 0) +
        COALESCE(order_interval_using_pvs4_order_date, 0)
    ) / NULLIF(
        (order_interval_using_pvs1_order_date IS NOT NULL) +
        (order_interval_using_pvs2_order_date IS NOT NULL) +
        (order_interval_using_pvs3_order_date IS NOT NULL) +
        (order_interval_using_pvs4_order_date IS NOT NULL),
        0
    ) AS Avg_ordr_interval
FROM orders_tbl;

关键细节说明 #

1. COALESCE函数：将NULL值转为0，避免NULL参与求和导致总和为NULL。
2. NULLIF函数：当4个CASE结果全为NULL时，分母为0，此时返回NULL（避免除以0报错）。
3. 布尔值转数值：SQLite中IS NOT NULL返回1（真）或0（假），直接相加即可统计有效数值的个数。

如果不需要保留单独的4个间隔列，可以简化为嵌套计算：

SELECT
    cust_id,
    order_entry_date,
    (
        COALESCE(
            CASE WHEN LAG(order_entry_date,1) OVER (PARTITION BY cust_id ORDER BY order_entry_date ASC) <> "" THEN JULIANDAY(order_entry_date) - JULIANDAY(LAG(order_entry_date,1) OVER (PARTITION BY cust_id ORDER BY order_entry_date ASC)) END,
            0
        ) +
        COALESCE(
            CASE WHEN LAG(order_entry_date,2) OVER (PARTITION BY cust_id ORDER BY order_entry_date ASC) <> "" THEN JULIANDAY(LAG(order_entry_date,1) OVER (PARTITION BY cust_id ORDER BY order_entry_date ASC)) - JULIANDAY(LAG(order_entry_date,2) OVER (PARTITION BY cust_id ORDER BY order_entry_date ASC)) END,
            0
        ) +
        COALESCE(
            CASE WHEN LAG(order_entry_date,3) OVER (PARTITION BY cust_id ORDER BY order_entry_date ASC) <> "" THEN JULIANDAY(LAG(order_entry_date,2) OVER (PARTITION BY cust_id ORDER BY order_entry_date ASC)) - JULIANDAY(LAG(order_entry_date,3) OVER (PARTITION BY cust_id ORDER BY order_entry_date ASC)) END,
            0
        ) +
        COALESCE(
            CASE WHEN LAG(order_entry_date,4) OVER (PARTITION BY cust_id ORDER BY order_entry_date ASC) <> "" THEN JULIANDAY(LAG(order_entry_date,3) OVER (PARTITION BY cust_id ORDER BY order_entry_date ASC)) - JULIANDAY(LAG(order_entry_date,4) OVER (PARTITION BY cust_id ORDER BY order_entry_date ASC)) END,
            0
        )
    ) / NULLIF(
        (CASE WHEN LAG(order_entry_date,1) OVER (PARTITION BY cust_id ORDER BY order_entry_date ASC) <> "" THEN 1 ELSE 0 END) +
        (CASE WHEN LAG(order_entry_date,2) OVER (PARTITION BY cust_id ORDER BY order_entry_date ASC) <> "" THEN 1 ELSE 0 END) +
        (CASE WHEN LAG(order_entry_date,3) OVER (PARTITION BY cust_id ORDER BY order_entry_date ASC) <> "" THEN 1 ELSE 0 END) +
        (CASE WHEN LAG(order_entry_date,4) OVER (PARTITION BY cust_id ORDER BY order_entry_date ASC) <> "" THEN 1 ELSE 0 END),
        0
    ) AS Avg_ordr_interval
FROM orders_tbl;

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