SQL按姓名分组计算自定义日期范围内数值列乘积的实现咨询

实现思路 #

• 首先需要先建立每个name和其专属fromdate的映射关系，确保每一条数据都能关联到对应name的fromdate
• 计算乘积时用CASE WHEN过滤符合区间条件的apples值，不符合条件的取值为1（乘1不会改变最终乘积结果，对应的ln(1)=0也不会影响求和结果）
• 用你提到的EXP+SUM+LN组合实现分组乘积计算，窗口函数按name分区即可

注意事项 #

• 要确保参与计算的apples都是正整数，若存在0值会导致LN计算报错，可额外加过滤条件排除0值
• pricedate和fromdate要统一为DATE类型比较，避免字符串比较出现逻辑错误

完整SQL示例 #

WITH name_fromdate_map AS (
    -- 此处替换为你实际获取每个name对应fromdate的业务逻辑，下方为示例映射数据
    SELECT 'Heidi' AS name, TO_DATE('26-03-2021','dd-mm-yyyy') AS fromdate FROM DUAL
    UNION ALL
    SELECT 'Elsa' AS name, TO_DATE('28-03-2021','dd-mm-yyyy') AS fromdate FROM DUAL
    UNION ALL
    SELECT 'Carl' AS name, TO_DATE('24-03-2021','dd-mm-yyyy') AS fromdate FROM DUAL
),
base_data AS (
    SELECT 
        m.fromdate,
        TO_CHAR(A.Pricdate, 'dd-mm-yyyy') AS Pricedate,
        A.apples, 
        A.name,
        RANK() OVER (PARTITION BY A.name ORDER BY A.pricedate ASC) AS rank,
        -- 新增Total列计算符合区间的apples乘积
        CAST(
            EXP(
                SUM(
                    LN(
                        CAST(
                            CASE WHEN A.pricedate >= m.fromdate AND A.pricedate <= TO_DATE('30-03-2021','dd-mm-yyyy') 
                            THEN A.apples 
                            ELSE 1 
                            END AS NUMBER
                        )
                    )
                ) OVER (PARTITION BY A.name)
            ) AS NUMBER
        ) AS Total
    FROM Table1 A
    LEFT JOIN name_fromdate_map m ON A.name = m.name
    WHERE 1=1
        AND A.Pricdate >= TO_DATE('01-01-2021', 'dd-mm-yyyy')
        AND A.Pricdate <= TO_DATE('30-06-2021', 'dd-mm-yyyy')
        AND A.apples >= 0
)
SELECT fromdate, pricedate, name, apples, Total
FROM base_data
-- 若需要保留原有取每个name最早pricedate行的逻辑，可取消下方注释
-- WHERE rank = 1

如果你的fromdate本身可以从现有业务逻辑直接获取，不需要单独构建映射表，直接把关联逻辑替换为你自己的逻辑即可。按示例数据计算，Heidi的Total会得到882，和你给出的预期结果一致。

内容的提问来源于stack exchange，提问作者Cecilie S. K