在BigQuery/Python中解码含中文UTM参数的URI（Unicode转UTF-8）

你的utm_term参数值是经过三重URL编码的结果，单次解码只能处理一层编码，因此无法得到正确的中文关键词。需要循环解码直到内容不再变化，才能还原出原始中文。

BigQuery解决方案 #

修改临时UDF，添加循环解码逻辑，直到无法继续解码或内容不再更新：

DECLARE uri STRING;
SET uri = 'https://www.random.cn/services/modifcation?utm_medium=cpc&utm_source=baidu&utm_term=%2525E8%2525AE%2525BA%2525E6%252596%252587%2525E6%25259C%25259F%2525E5%252588%25258A%2525E6%25258A%252595%2525E7%2525A8%2525BF';

CREATE TEMP FUNCTION DecodeMultipleTimes(encoded STRING) RETURNS STRING LANGUAGE js AS """
let decoded = encoded;
let prev;
do {
  prev = decoded;
  try {
    decoded = decodeURIComponent(decoded);
  } catch (e) {
    break;
  }
} while (decoded !== prev);
return decoded;
""";

SELECT 
  uri,
  DecodeMultipleTimes(uri) AS full_decoded_uri,
  -- 单独提取并解码utm_term参数
  DecodeMultipleTimes(REGEXP_EXTRACT(uri, r'utm_term=([^&]+)')) AS decoded_utm_term

Python解决方案 #

同样通过循环调用unquote实现多次解码：

import urllib.parse

url = "https://www.random.cn/services/modifcation?utm_medium=cpc&utm_source=baidu&utm_term=%2525E8%2525AE%2525BA%2525E6%252596%252587%2525E6%25259C%25259F%2525E5%252588%25258A%2525E6%25258A%252595%2525E7%2525A8%2525BF"

def decode_multiple_times(encoded_str):
    decoded = encoded_str
    prev = None
    while decoded != prev:
        prev = decoded
        try:
            decoded = urllib.parse.unquote(decoded, encoding='utf-8')
        except UnicodeDecodeError:
            break
    return decoded

# 解码整个URL
full_decoded_url = decode_multiple_times(url)
print("完整解码后的URL:", full_decoded_url)

# 单独解码utm_term参数
query_params = urllib.parse.parse_qs(urllib.parse.urlparse(url).query)
utm_term_encoded = query_params['utm_term'][0]
decoded_utm_term = decode_multiple_times(utm_term_encoded)
print("解码后的utm_term:", decoded_utm_term)

以上两种方案都会自动处理多次编码的情况，最终还原出正确的中文关键词“设施周期开始”。

内容的提问来源于stack exchange，提问作者Shree Kant Das