ODPS自定义函数

入参说明

输出说明

使用示例

-- 注册函数
CREATE FUNCTION bound_index AS 'bound_index.BoundIndex' USING 'bound_index.py';

-- 使用示例：将分数分段 [0, 60), [60, 70), [70, 80), [80, 90), [90, +∞)
SELECT bound_index(score, ARRAY(60.0, 70.0, 80.0, 90.0)) AS grade_level
FROM student_table;
-- score=55 返回 0, score=65 返回 1, score=85 返回 3, score=95 返回 4

入参说明

输出说明

使用示例

-- 注册函数
CREATE FUNCTION tag_combo AS 'tag_combo.TagCombo' USING 'tag_combo.py';

-- 组合多个标签
SELECT tag_combo(category, brand, style) AS combo_result
FROM item_table;
-- 输入: category="服装\x1d鞋帽", brand="Nike\x1dAdidas"
-- 输出: "服装_Nike\x1d服装_Adidas\x1d鞋帽_Nike\x1d鞋帽_Adidas"

入参说明

输出说明

使用示例

SELECT AnalysisIP('114.114.114.114', 'country') AS country,
       AnalysisIP('114.114.114.114', 'region') AS province,
       AnalysisIP('114.114.114.114', 'city') AS city
FROM dual;
-- 返回: 中国, 江苏省, 南京市

入参说明

输出说明

使用示例

-- 注册函数
CREATE FUNCTION ctr_num AS 'ctr_num.CTRNum' USING 'ctr_num.py';

-- 计算各物品的 CTR
SELECT item_id, 
       ctr_num(click_count, impression_count, true) AS ctr
FROM item_stats_table
ORDER BY ctr DESC;

入参说明

输出说明

使用示例

-- 注册函数
CREATE FUNCTION ctr_kv AS 'ctr_kv.CTRKV' USING 'ctr_kv.py';

-- 计算各物品的 CTR
SELECT ctr_kv(clicks_str, impressions_str, true) AS ctr_result
FROM item_stats_table;
-- 输出示例: item1:0.152341\x1ditem2:0.098234\x1ditem3:0.075612

入参说明

输出说明

使用示例

-- 注册函数
CREATE FUNCTION ctr_kv_map AS 'ctr_kv_map.CTRKVMap' USING 'ctr_kv_map.py';

-- 计算各物品的 CTR（MAP 版本）
SELECT ctr_kv_map(clicks_map, impressions_map, true) AS ctr_map
FROM item_stats_table;

入参说明

输出说明

使用示例

-- 注册函数
CREATE FUNCTION map2str AS 'map2str.Map2Str' USING 'map2str.py';

-- 将 MAP 转换为字符串
SELECT map2str(feature_map) AS feature_str
FROM feature_table;
-- 输入: {'age': 25.0, 'gender': 1.0}
-- 输出: age:25.0\x1dgender:1.0

入参说明

输出说明

使用示例

-- 注册函数
CREATE FUNCTION single_seq_replenish AS 'single_seq_replenish.SingleSeqReplenish' USING 'single_seq_replenish.py';

-- 单序列补充
SELECT user_id,
       single_seq_replenish(seq1, seq2, id1, id2, event_time, ';', 50, 1) AS full_seq
FROM user_sequence_table;

入参说明

输出说明

使用示例

-- 注册函数
CREATE FUNCTION seq_replenish AS 'seq_replenish.SeqReplenish' USING 'seq_replenish.py';

-- 用历史序列补充实时序列
SELECT user_id,
       seq_replenish(realtime_seq, history_seq, event_time, ';', 50, 1) AS full_seq
FROM user_sequence_table;

入参说明

输出说明

使用示例

-- 注册函数
CREATE FUNCTION seq_concat AS 'seq_concat.SeqConcat' USING 'seq_concat.py';

-- 合并实时和历史行为序列
SELECT user_id,
       seq_concat(realtime_seq, history_seq, ';', 100) AS full_seq
FROM user_sequence_table;
-- realtime_seq: "item1;item2;item3"
-- history_seq: "item4;item5;item6"
-- 输出: "item1;item2;item3;item4;item5;item6"

入参说明

输出说明

使用示例

-- 注册函数
CREATE FUNCTION count_kv AS 'count_kv.CountKV' USING 'count_kv.py';

-- 统计各类别的点击总数
SELECT category,
       count_kv(item_id, click_count, 'sum', 100, true) AS item_stats
FROM item_click_table
GROUP BY category;
-- 输出示例: "item1:150\x1ditem2:120\x1ditem3:95"

-- 统计各类别的平均停留时长
SELECT category,
       count_kv(item_id, duration, 'avg', 50, true) AS avg_duration_stats
FROM item_view_table
GROUP BY category;
-- 输出示例: "item1:45.5\x1ditem2:32.1\x1ditem3:28.7"

入参说明

输出说明

使用示例

-- 统计每个用户点击次数最多的 Top 10 商品类别
SELECT user_id, 
       CountKV(category, 1.0, 'sum', 10, true) AS top_categories
FROM user_behavior
GROUP BY user_id;

-- 返回示例: {"electronics": 15.0, "clothing": 10.0, "food": 8.0}

注意事项

• 聚合类型为 avg 时，会同时维护总和和计数，最终计算平均值。

• 类别字段支持数组形式的输入，用特殊字符 \u001D (ASCII 29) 分隔。

• topK <= 0 时返回所有类别## 2. CountKV - 类别键值聚合函数。

入参说明

输出说明

使用示例

-- 注册函数
CREATE FUNCTION count_cates_kvs AS 'count_cates_kvs.CountCatesKVS' USING 'count_cates_kvs.py';

-- 统计用户在不同事件下的类别行为
SELECT user_id,
       count_cates_kvs(category, 1, event_type, 'click,purchase', 'purchase/click', '\x1d', 100, false) AS stats_array
FROM user_behavior_table
GROUP BY user_id;
-- 输出示例: ["cate1:10\x1dcate2:5", "cate1:2\x1dcate2:1"]  (分别对应 click 和 purchase/click 的统计)

入参说明

输出说明

使用示例

-- 注册函数
CREATE FUNCTION wm_concat_by_sort AS 'wm_concat_by_sort.WMConcatBySort' USING 'wm_concat_by_sort.py';

-- 构建用户最近的行为序列
SELECT user_id,
       wm_concat_by_sort(item_id, event_time, event_type, 'click,view,purchase', ';', 50) AS behavior_seq
FROM user_behavior_table
GROUP BY user_id;
-- 输出示例: "item50;item49;item48;...;item1" （按时间降序，最近的行为在前）

入参说明

参数说明:

• 当 args.length == 11: 无 requestid,无 scene

• 当 args.length == 12: 有 requestid,无 scene

• 当 args.length == 13: 无 requestid,有 scene

• 当 args.length == 14: 有 requestid,有 scene

输出说明

输出序列键名格式：

• 如果 combine_method = "all"：{all}_{max_seq_len}_seq__{field_name}

• 如果 combine_method != "all"：{event}_{seq_len}_seq__{field_name}

• 如果有场景: 后缀加上 _{scene}，如 all_10_seq_home__item_id

使用示例

-- 生成用户实时行为序列特征
SELECT output.*
FROM user_behavior
LATERAL VIEW RtSeqFeature(
    user_id,                    -- pid
    event_timestamp,            -- event_time
    ARRAY(item_id, category),   -- fields
    ARRAY("item_id", "category"), -- field_names
    event_type,                 -- event
    ARRAY("click", "add", "buy"), -- valid_events
    ARRAY(10, 8, 6),            -- seq_len
    1,                          -- duplicate_method
    "all",                      -- combine_method
    300,                        -- pre_seconds
    ";",                        -- sequence_delim
    request_id,                 -- requestid (可选)
    scene,                      -- scene (可选)
    ARRAY("home", "search")     -- valid_scenes (可选)
) output AS feature_map;

入参说明

与 RtSeqFeature 完全相同。

输出说明

与 RtSeqFeature 完全相同。

与 RtSeqFeature 的区别

使用场景:

• 如果需要根据 item 去重而不考虑事件类型，使用 RtSeqFeature2。

• 如果需要同时考虑事件类型和 item，使用 RtSeqFeature。

入参说明

输出说明

使用示例

-- 为不同时间窗口生成延迟数据行
SELECT output.*
FROM user_events
LATERAL VIEW MessageDelay(
    ARRAY(user_id, event_type),  -- fields
    event_timestamp,              -- event_time
    ARRAY(1800, 3600, 7200),     -- windows (30m, 1h, 2h)
    true                          -- is_save
) output AS field1, field2, trigger_time, window_size;

-- 如果 is_save=true,会为每个窗口输出一行:
-- [user_id, event_type, 1709281800, 1800]
-- [user_id, event_type, 1709283600, 3600]
-- [user_id, event_type, 1709287200, 7200]

注意事项

• 该函数是 UDTF，根据 is_save 参数可能输出多行。

• 窗口值会自动添加 0 作为第一个窗口 (立即触发)。

• 用于实时特征 MapReduce 的 Mapper 阶段，为 Reducer 生成延迟数据行。

入参说明

参数说明:

• 当 args.length == 13：无 requestid，无 scene。

• 当 args.length == 14：有 requestid，无 scene。

• 当 args.length == 15：无 requestid，有 scene。

• 当 args.length == 16：有 requestid，有 scene。

输出说明

输出键名格式：

• 窗口名称格式：rt{X}h{Y}m{Z}s，如 rt30m、rt3h、rt24h。

• 事件计数：user__cnt_click_rt30m。

• 类别键值对：user__kv_style_id_click_rt30m。

• 如果有场景：加上场景后缀，如 user__cnt_click_home_rt30m。

类别键值对格式：

• 使用 (char)29 (即 \u001D) 分隔多个键值对。

• 每个键值对格式为 key:value。

• 示例："cate1:15.0\u001Dcate2:10.0\u001Dcate3:8.0"。

使用示例

-- 生成用户实时行为统计特征
SELECT output.*
FROM user_behavior
LATERAL VIEW SWCountCatesKVS(
    user_id,                              -- pid
    ARRAY(style_id, goods_id),           -- cates
    ARRAY("style_id", "goods_id"),       -- cate_names
    1,                                    -- value
    event_type,                          -- event
    ARRAY("click", "add", "buy"),        -- valid_events
    0,                                    -- delay_flag (0表示立即生效)
    ARRAY(1800, 3600, 7200, 86400),     -- windows (30m, 1h, 2h, 24h)
    100,                                  -- topk
    5,                                    -- pre_seconds
    event_timestamp,                      -- event_time
    "20240101",                          -- output_date
    "user",                              -- side
    request_id,                          -- requestid (可选)
    scene,                               -- scene (可选)
    ARRAY("home", "search")              -- valid_scenes (可选)
) output AS feature_map;

输出示例

{
  "pid": "user123",
  "event_time": "1709280000",
  "user__cnt_click_rt30m": "15",
  "user__cnt_click_rt3h": "45",
  "user__cnt_add_rt30m": "8",
  "user__kv_style_id_click_rt30m": "style1:10.0\u001Dstyle2:5.0",
  "user__kv_goods_id_click_rt30m": "goods1:8.0\u001Dgoods2:7.0"
}

注意事项

• 该函数是 UDTF，输出包含统计结果。

• delay_flag 参数用于实现滑动窗口：

  • 0: 表示数据立即添加到所有窗口。

  • 非 0: 表示对应窗口的数据需要减去（用于维护滑动窗口）。

• pre_seconds 用于防止数据穿越。

• output_date 用于控制输出起始日期，避免输出历史数据。

• 支持按 requestid 去重输出。

• 类别值支持多标签，用 (char)29 (即 \u001D) 分隔。

入参说明

输出说明

使用示例

-- 注册函数
CREATE FUNCTION seq_split AS 'seq_split.SeqSplit' USING 'seq_split.py';

-- 将序列拆分为多行
SELECT t.seq_len, t.item, t.col_name
FROM sequence_table
CROSS APPLY seq_split(ARRAY(seq1, seq2), ARRAY('seq1_col', 'seq2_col'), ';') AS t(seq_len, item, col_name);
-- 输入: seq1="item1;item2;item3", seq2="item4;item5"
-- 输出:
-- seq_len | item  | col_name
-- --------|-------|----------
-- 3       | item1 | seq1_col
-- 2       | item2 | seq1_col
-- 1       | item3 | seq1_col
-- 2       | item4 | seq2_col
-- 1       | item5 | seq2_col