-- 纯 SQL 无法解决的模糊匹配
SELECT a.name, b.name
FROM list_a a, list_b b
WHERE a.name LIKE b.name;  -- ❌ 根本查不出来

-- 基本语法
CREATE FUNCTION function_name(param1 TYPE, param2 TYPE) RETURNS return_type
AS $$
    -- Python 代码
    return result
$$ LANGUAGE python;

pip install duckdb

-- 安装并加载 DuckDB（如果还没有）
INSTALL python;
LOAD python;

-- 创建模糊匹配函数
CREATE FUNCTION fuzzy_match(a TEXT, b TEXT) RETURNS FLOAT
AS $$
    from difflib import SequenceMatcher
    return SequenceMatcher(None, a, b).ratio()
$$ LANGUAGE python;

-- 一次性匹配所有两两组合
SELECT 
    a.name AS source_name,
    b.name AS target_name,
    fuzzy_match(a.name, b.name) AS similarity_score
FROM customer_list_a a
CROSS JOIN customer_list_b b
WHERE fuzzy_match(a.name, b.name) > 0.75
ORDER BY similarity_score DESC;

-- 找最高匹配度的潜在重复
WITH matched AS (
    SELECT 
        a.id AS a_id,
        a.name AS a_name,
        b.id AS b_id,
        b.name AS b_name,
        fuzzy_match(a.name, b.name) AS score
    FROM dedup_a a
    CROSS JOIN dedup_b b
),
top_matches AS (
    SELECT *, ROW_NUMBER() OVER (
        PARTITION BY a_id ORDER BY score DESC
    ) AS rn
    FROM matched
    WHERE score > 0.8
)
SELECT a_id, a_name, b_id, b_name, ROUND(score, 4) AS score
FROM top_matches
WHERE rn = 1
ORDER BY score DESC;

-- 更智能的中文模糊匹配
CREATE FUNCTION smart_match(a TEXT, b TEXT) RETURNS FLOAT
AS $$
    import re
    
    # 提取关键部分：去掉"有限公司""股份""科技"等通用后缀
    def normalize(name):
        name = re.sub(r'[（(].*?[）)]', '', name)  # 去掉括号内容
        name = re.sub(r'(有限|股份|责任|集团|公司)$', '', name)
        name = name.strip()
        return name
    
    from difflib import SequenceMatcher
    na, nb = normalize(a), normalize(b)
    return SequenceMatcher(None, na, nb).ratio()
$$ LANGUAGE python;

CREATE FUNCTION sentiment_score(text_input TEXT) RETURNS INTEGER
AS $$
    from textblob import TextBlob
    blob = TextBlob(text_input)
    # 返回 -100 ~ 100 的分数
    return int(blob.sentiment.polarity * 100)
$$ LANGUAGE python;

-- 批量分析评论情感
SELECT 
    comment_id,
    comment_text,
    sentiment_score(comment_text) AS score,
    CASE 
        WHEN sentiment_score(comment_text) > 20 THEN '正面'
        WHEN sentiment_score(comment_text) < -20 THEN '负面'
        ELSE '中性'
    END AS sentiment
FROM user_reviews
ORDER BY score ASC;

CREATE FUNCTION validate_id_card(id_num TEXT) RETURNS BOOLEAN
AS $$
    if len(id_num) != 18:
        return False
    weights = [7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2]
    check_codes = '10X98765432'
    total = sum(int(id_num[i]) * weights[i] for i in range(17))
    return id_num[17].upper() == check_codes[total % 11]
$$ LANGUAGE python;

-- 校验用户表中的身份证号
SELECT user_id, id_card, validate_id_card(id_card) AS is_valid
FROM users
WHERE validate_id_card(id_card) = false;

CREATE FUNCTION normalize_address(addr TEXT) RETURNS TEXT
AS $$
    import re
    # 统一地址格式
    addr = addr.replace('，', ',').replace('。', '.')
    addr = re.sub(r'\s+', ' ', addr)
    # 替换常见缩写
    replacements = {
        'BJ': '北京', 'SH': '上海', 'SZ': '深圳',
        'GZ': '广州', 'HZ': '杭州'
    }
    for k, v in replacements.items():
        addr = addr.replace(k, v)
    return addr
$$ LANGUAGE python;

-- 批量标准化地址
SELECT id, normalize_address(raw_address) AS standardized_address
FROM customer_addresses;

-- ✅ 好：先过滤再调 UDF
SELECT *, fuzzy_match(a.name, b.name) AS score
FROM list_a a, list_b b
WHERE a.region = b.region  -- 先缩小数据范围
  AND fuzzy_match(a.name, b.name) > 0.8;

-- ❌ 差：对所有组合都调 UDF
SELECT *, fuzzy_match(a.name, b.name) AS score
FROM list_a a, list_b b;

-- 一步到位
LOAD python;
CREATE FUNCTION my_udf(x TEXT) RETURNS TEXT
AS $$ return x.upper() $$ LANGUAGE python;
SELECT my_udf('hello duckdb');
-- 输出: HELLO DUCKDB