如何补全衍生字段缺失日期并按公式计算对应字段值？

需求说明 #

需为标记为derived=1的记录补全其公式关联基础字段的所有日期，并根据给定公式计算two字段的值（需包含计算过程），最终合并基础数据与计算结果。

输入数据 #

date       | one | two | derived | Formula
------------------------------------------
2020-08-15 | A     | 1.0     | 0 | null
2020-08-14 | A     | 2.0     | 0 | null
2020-08-15 | B     | 4.0     | 0 | null
2020-08-14 | B     | 5.0     | 0 | null
null       | C     | null    | 1 | (A+B)/2
null       | D     | null    | 1 | (B)*2

预期输出 #

date       | one | two |
------------------------
2020-08-15 | A     | 1.0
2020-08-14 | A     | 2.0
2020-08-15 | B     | 4.0
2020-08-14 | B     | 5.0
2020-08-15 | C     | 2.5(1.0+4.0)/2
2020-08-14 | C     | 3.5(2.0+5.0)/2
2020-08-15 | D     | 8.0(4.0)*2
2020-08-14 | D     | 10.0(5.0)*2

实现思路 #

1. 分离数据与规则：将原始数据拆分为基础数据（derived=0）和派生计算规则（derived=1）。
2. 收集日期维度：提取基础数据中的所有唯一日期，作为派生字段的补全日历。
3. 构建基础映射：建立基础字段（A、B）的日期-值映射关系，方便快速取值计算。
4. 生成派生记录：为每个派生规则（C、D）遍历所有日期，替换公式中的变量为对应日期的基础值，计算结果并拼接过程文本。
5. 合并结果：将基础数据与派生计算结果合并，按日期和字段排序得到最终输出。

具体实现方案 #

方案一：SQL实现（适用于数据库环境） #

利用CTE拆分数据，通过交叉连接生成日期组合，结合条件计算与字符串拼接实现需求：

WITH base_data AS (
    SELECT date, one, two
    FROM your_table
    WHERE derived = 0
),
derived_rules AS (
    SELECT one AS derived_one, Formula
    FROM your_table
    WHERE derived = 1
),
all_dates AS (
    SELECT DISTINCT date FROM base_data
),
base_mapping AS (
    SELECT one, date, two
    FROM base_data
)
-- 合并基础数据与派生计算结果
SELECT date, one, two
FROM base_data
UNION ALL
SELECT 
    ad.date,
    dr.derived_one,
    CONCAT(
        -- 计算公式结果
        CASE 
            WHEN dr.Formula = '(A+B)/2' THEN ROUND((bmA.two + bmB.two)/2, 1)
            WHEN dr.Formula = '(B)*2' THEN bmB.two * 2
        END,
        -- 拼接计算过程
        '(', REPLACE(REPLACE(dr.Formula, 'A', bmA.two), 'B', bmB.two), ')'
    ) AS two
FROM derived_rules dr
CROSS JOIN all_dates ad
LEFT JOIN base_mapping bmA ON bmA.one = 'A' AND bmA.date = ad.date
LEFT JOIN base_mapping bmB ON bmB.one = 'B' AND bmB.date = ad.date
ORDER BY date DESC, one;

方案二：Python Pandas实现（适用于数据处理脚本） #

通过数据拆分、映射构建、循环计算生成派生记录，最终合并输出：

import pandas as pd

# 加载输入数据
data = [
    {"date": "2020-08-15", "one": "A", "two": 1.0, "derived": 0, "Formula": None},
    {"date": "2020-08-14", "one": "A", "two": 2.0, "derived": 0, "Formula": None},
    {"date": "2020-08-15", "one": "B", "two": 4.0, "derived": 0, "Formula": None},
    {"date": "2020-08-14", "one": "B", "two": 5.0, "derived": 0, "Formula": None},
    {"date": None, "one": "C", "two": None, "derived": 1, "Formula": "(A+B)/2"},
    {"date": None, "one": "D", "two": None, "derived": 1, "Formula": "(B)*2"},
]
df = pd.DataFrame(data)

# 分离基础数据与派生规则
base_df = df[df["derived"] == 0].drop(columns=["derived", "Formula"])
derived_rules = df[df["derived"] == 1][["one", "Formula"]]

# 获取所有唯一日期
all_dates = base_df["date"].unique()

# 构建基础字段的日期-值映射
base_map = {(row["one"], row["date"]): row["two"] for _, row in base_df.iterrows()}

# 生成派生记录
derived_rows = []
for _, rule in derived_rules.iterrows():
    derived_one = rule["one"]
    formula = rule["Formula"]
    for date in all_dates:
        # 替换公式变量为对应值
        formula_with_vals = formula
        calc_vars = {}
        for var in ["A", "B"]:
            if var in formula:
                calc_vars[var] = base_map[(var, date)]
                formula_with_vals = formula_with_vals.replace(var, str(calc_vars[var]))
        # 计算结果
        result = eval(formula, {}, calc_vars)
        # 拼接two字段内容
        two_str = f"{result}({formula_with_vals})"
        derived_rows.append({"date": date, "one": derived_one, "two": two_str})

# 合并并排序结果
base_df["two"] = base_df["two"].astype(str)
final_df = pd.concat([base_df, pd.DataFrame(derived_rows)], ignore_index=True)
final_df = final_df.sort_values(by=["date", "one"], ascending=[False, True]).reset_index(drop=True)

# 打印输出
print(final_df.to_string(index=False))

注意事项 #

• SQL方案中若公式较多，可考虑使用动态SQL或自定义函数解析公式，避免硬编码判断。
• Python方案中使用eval存在安全风险，若公式来自不可信来源，建议使用ast模块解析表达式进行计算。

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