sql - SQLite中具有多个条件和列的AVG函数

Question

我想出了如何使用 condition/s 执行 avg 函数，但我无法让它满足我的特定需求。
假设我正在使用这张名为 sales_performance 的表

  product_ID  sales_period  sales_qty  sales_index  product_sub goal_met
         C12          0001         15           20          D71        Y
         D71          0001         07           09          C12        N
         F20          0001         25           30          C05        Y
         C05          0001         10           15          F20        N
         C12          0002         15           30          C05        Y
         C05          0002         12           06          C12        N
         D71          0002         30           20          F20        Y
         F20          0002         20           15          D71        N
         C12          0003         05           04          F20        N
         F20          0003         40           35          C12        Y
         D71          0003         20           20          C05        Y
         C05          0003         12           10          D71        N

我想为产品 C12 计算一个名为 sales_index_goal 的新值。然后，该值的公式为:

average of sales_index of product 'C12' when Goal_Met = 'Y' and the sales_index of its sub_product when Goal_Met = 'N' during the sales periods before that one.

例如，如果我想计算产品的 sales_index_goal 'C12'对于 sales_period 0003 , 将计算为:

average of (30,20,15) where 30 and 20 are the sales_indexes of product 'C12' in sales periods 1 and 2 and 15 is the sales_index of Product 'F20' in sales period 2.

我在计算确切销售期间的这个值时没有任何问题。但是，我很难想出一个查询来计算产品 'C12' 的这个值。适用于所有销售期间。目前，我编写了这个不起作用的查询:

SELECT 
    s.*, 
    AVG(CASE 
        WHEN s2.goal_met = "Y" AND s2.product_id = "C2" 
            THEN s2.sales_index 
        WHEN s2.product_id = (
            SELECT s2.product_sub WHERE s2.product_id = "C12"
        ) AND s2.goals_met = "N" 
            THEN s2.sales_index
        ELSE NULL 
    END)
    OVER (
        ORDER BY s.sales_period 
        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS sales_index_goal
FROM 
    sales_performance s, 
    sales_performance s2
WHERE s.product_id = "C12" 

我真的很感激这方面的任何帮助。奖励:为所有销售期间的所有产品计算此值。

编辑:以下答案适用于计算产品 C12 的 sales_index_goal 但是，它不适用于产品 F20(更详细的为什么它不起作用的原因在我在答案下方的评论中)
可以看到查询产品 F20 here 的结果

Answer 1

使用窗口函数是一个很好的起点。一个问题在于您希望对来自不同列的值进行平均，并且可能具有不同的出现次数。

我认为您需要将计算分解以计算平均值:将值相加，然后将其除以出现的总和。

考虑:

SELECT *
FROM (
    SELECT 
        s.*,
        (
            0.0 + 
            COALESCE(SUM(CASE WHEN goal_met = 'Y' THEN sales_index END) OVER(
                PARTITION BY product_id 
                ORDER BY sales_period 
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
            ), 0)
            + COALESCE(SUM(CASE WHEN goal_met = 'N' THEN sales_index END) OVER(
                PARTITION BY product_sub 
                ORDER BY sales_period 
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
            ), 0)
        ) / (
            COALESCE(SUM(goal_met = 'Y') OVER(
                PARTITION BY product_id 
                ORDER BY sales_period 
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
            ), 0)
            + COALESCE(SUM(goal_met = 'N') OVER(
                PARTITION BY product_sub 
                ORDER BY sales_period 
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
            ), 0)
        ) AS sales_index_goal
    FROM sales_performance s
) x WHERE product_ID = 'C12'

在 this DB fiddle ，使用您的示例数据，这将返回:

| product_ID | sales_period | sales_qty | sales_index | product_sub | goal_met | sales_index_goal   |
| ---------- | ------------ | --------- | ----------- | ----------- | -------- | ------------------ |
| C12        | 1            | 15        | 20          | D71         | Y        |                    |
| C12        | 2            | 15        | 30          | C05         | Y        | 20                 |
| C12        | 3            | 5         | 4           | F20         | N        | 21.666666666666668 |