教學：複利計算器操作

在這個教學中，你會為 Lakeflow Designer 建立一個 Python UDF 運算子，用來計算複利。使用此範例學習建立能轉換單一值或欄位的運算子的基本原理。欲了解更多，請參閱 Lakeflow Designer 中的使用者定義運算子。

Overview

這個教學會一步步帶你使用Python UDF建立使用者定義的運算子。運算子利用複利公式 A = P × (1 + r/n)^(n×t)計算投資的未來價值，其中：

P = 本金（起始金額）
r = 年利率（以十進位計）
n = 每年複利期數
t = 以年為單位的時間

步驟 1：撰寫並測試 Python 函式

首先，定義執行計算的核心 Python 函式。在筆記本儲存格裡測試，確保它正常運作。

def compound_amount(principal: float,
                    annual_rate: float,
                    compounds_per_year: int,
                    years: float) -> float:
    """
    Compute compound interest future value.

    A = P * (1 + r/n)^(n*t)

    principal: starting amount (P)
    annual_rate: annual nominal rate as decimal (r), e.g. 0.05
    compounds_per_year: compounding periods per year (n), e.g. 12
    years: time in years (t), can be fractional
    """
    import math
    if principal is None or annual_rate is None or compounds_per_year is None or years is None:
        return None

    if compounds_per_year <= 0:
        raise ValueError("compounds_per_year must be > 0")

    return principal * math.pow(1.0 + annual_rate / compounds_per_year,
                                 compounds_per_year * years)

你可以用以下程式碼測試這個功能：

# $1,000 invested at 5% annual rate, compounded monthly for 10 years
compound_amount(1000, 0.05, 12, 10)
# Expected result: ~1647.01

步驟 2：為運算元建立 YAML

YAML 設定定義了 Lakeflow Designer 中運算子的呈現方式。對於此運算子：

Principal 使用 expression 一個小工具，讓使用者能從資料中選擇欄位
年利率、年複合利率與年度number使用帶有預設值與限制的小工具
運算子有一個輸入埠，提供表達式參數的欄位資料

schema: user-defined-operator-v0.1.0
type: uc-udf
name: Compound Amount
id: finance.compound_amount
version: '1.0.0'
description: >
  Computes the future value of an investment using compound interest.
  Formula: A = P * (1 + r/n)^(n*t)
config:
  type: object
  properties:
    principal:
      type: string
      format: expression
      title: Principal
      examples:
        - 'Select principal column or expression'
      x-ui:
        widget: expression
        port: in
    annual_rate:
      type: number
      title: Annual rate (decimal)
      default: 0.05
      minimum: 0
      examples:
        - 'e.g. 0.05 for 5%'
      x-ui:
        widget: number
    compounds_per_year:
      type: number
      title: Compounds per year
      default: 12
      minimum: 1
      examples:
        - 'e.g. 12 for monthly'
      x-ui:
        widget: number
    years:
      type: number
      title: Years
      default: 10
      minimum: 0
      examples:
        - 'Time in years (t)'
      x-ui:
        widget: number
  required:
    - principal
    - annual_rate
    - compounds_per_year
    - years
  additionalProperties: false
ports:
  input:
    - name: in
      title: Input
  output:
    - name: out
      title: Output

請參閱使用者定義運算子 YAML 參考，以取得所有可用屬性、資料型態、小工具和選項的完整指南。

步驟 3：建立 Unity 目錄函式

將 YAML 架構與 Python 函式合併成一個 CREATE FUNCTION 陳述式。 YAML 配置放在函式主體開頭的 docstring 裡。

CREATE OR REPLACE FUNCTION main.my_schema.compound_amount(
    principal DOUBLE,
    annual_rate DOUBLE,
    compounds_per_year INT,
    years FLOAT)
RETURNS DOUBLE
LANGUAGE PYTHON
AS $$
  """
  schema: user-defined-operator-v0.1.0
  type: uc-udf
  name: Compound Amount
  id: finance.compound_amount
  version: "1.0.0"
  description: >
    Computes the future value of an investment using compound interest.
    Formula: A = P * (1 + r/n)^(n*t)
  config:
    type: object
    properties:
      principal:
        type: string
        format: expression
        title: Principal
        examples:
          - "Select principal column or expression"
        x-ui:
          widget: expression
          port: in
      annual_rate:
        type: number
        title: Annual rate (decimal)
        default: 0.05
        minimum: 0
        examples:
          - "e.g. 0.05 for 5%"
        x-ui:
          widget: number
      compounds_per_year:
        type: number
        title: Compounds per year
        default: 12
        minimum: 1
        examples:
          - "e.g. 12 for monthly"
        x-ui:
          widget: number
      years:
        type: number
        title: Years
        default: 10
        minimum: 0
        examples:
          - "Time in years (t)"
        x-ui:
          widget: number
    required:
      - principal
      - annual_rate
      - compounds_per_year
      - years
    additionalProperties: false
  ports:
    input:
      - name: in
        title: Input
    output:
      - name: out
        title: Output
  """

  def compound_amount(principal: float,
                      annual_rate: float,
                      compounds_per_year: int,
                      years: float) -> float:
      import math
      if principal is None or annual_rate is None or compounds_per_year is None or years is None:
          return None

      if compounds_per_year <= 0:
          raise ValueError("compounds_per_year must be > 0")

      return principal * math.pow(1.0 + annual_rate / compounds_per_year,
                                   compounds_per_year * years)

  return compound_amount(principal, annual_rate, compounds_per_year, years)
$$

步驟四：測試功能

直接用 SQL 測試 UC 函式：

-- Test 1: $1,000 at 5% compounded monthly for 10 years
SELECT main.my_schema.compound_amount(1000, 0.05, 12, 10)
-- Expected: ~1647.01

-- Test 2: $1,000 at 5% compounded annually for 1 year
SELECT main.my_schema.compound_amount(1000, 0.05, 1, 1)
-- Expected: 1050.00

-- Test 3: $1,000 at 15% compounded monthly for 1 year
SELECT main.my_schema.compound_amount(1000, 0.15, 12, 1)
-- Expected: ~1160.75

步驟五：註冊營運商

將操作員加入你的 .user_defined_operators.yaml 檔案：

operators:
  - catalog: main
    schema: my_schema
    functionName: compound_amount

Note

如果你在使用者資料夾中定義這個檔案，它只會顯示給你。欲了解更多資訊，請參閱「讓您的營運商可被發現」。

步驟 6：設定權限

授權需要使用此操作員的使用者存取權限：

GRANT USE SCHEMA ON SCHEMA main.my_schema TO `<user>`;
GRANT EXECUTE ON FUNCTION main.my_schema.compound_amount TO `<user>`;

使用Lakeflow Designer中的運算元。

註冊完成後，操作員會在 Lakeflow Designer 中顯示：

一個下拉選單，可以從輸入資料中選擇主欄位
利率、複利頻率和年數的數值輸入欄位（設有合理的預設值）

使用者可使用此操作工具計算整列投資資料的未來價值。

意見反應

此頁面對您有幫助嗎？

Last updated on 2026-06-24