基于Big Query的文本相似度检测：识别相似特征数据行需求

Got it, let's work through this problem with a BigQuery-specific solution tailored to your needs. The goal is to identify rows where the numeric prefixes are highly similar, plus the text segment and date are identical—edit distance is perfect here because it measures the number of changes needed to turn one string into another, which catches cases like "420" vs "4-20" perfectly.

Step 1: Structure your raw data reliably #

Since your numeric prefix can have variable segments (1 part like "420" or 2 parts like "4-20"), fixed SPLIT() offsets will break. Instead, use regex to extract the consistent parts (text segment and date) and isolate the variable numeric prefix:

WITH structured_data AS (
  SELECT
    raw_string,
    -- Grab everything before the first "xyz-" as the numeric prefix
    REGEXP_EXTRACT(raw_string, r'^(.*)-xyz-') AS num_prefix,
    -- Extract the text segment between "xyz-" and the date
    REGEXP_EXTRACT(raw_string, r'xyz-(.*)-(\d{1,2}\.\d{1,2}\.\d{4})') AS text_segment,
    -- Pull the date (formatted as dd.mm.yyyy)
    REGEXP_EXTRACT(raw_string, r'-(\d{1,2}\.\d{1,2}\.\d{4})-') AS date_part
  FROM
    `your-project.your-dataset.your-table` -- Replace with your actual table path
)

Step 2: Compare prefixes with Edit Distance #

Next, join the structured data to itself to find pairs where the text segment and date match. Use BigQuery's built-in EDIT_DISTANCE() function to measure how similar the numeric prefixes are. We'll set a threshold (I used 2 here, adjust based on your tolerance for similarity):

, matching_pairs AS (
  SELECT
    a.raw_string AS row_a,
    b.raw_string AS row_b,
    a.num_prefix AS prefix_a,
    b.num_prefix AS prefix_b,
    EDIT_DISTANCE(a.num_prefix, b.num_prefix) AS similarity_score
  FROM
    structured_data a
  INNER JOIN
    structured_data b
  ON
    a.text_segment = b.text_segment
    AND a.date_part = b.date_part
    AND a.raw_string < b.raw_string -- Avoid duplicate pairs (e.g., rowA-rowB and rowB-rowA)
  WHERE
    EDIT_DISTANCE(a.num_prefix, b.num_prefix) <= 2 -- Adjust threshold as needed
)

Step 3: Get all matching rows #

If you want a flat list of all rows that have a matching partner (instead of just pairs), union the results from the pair table:

SELECT row_a AS matched_row FROM matching_pairs
UNION ALL
SELECT row_b AS matched_row FROM matching_pairs
ORDER BY matched_row

Optional: Normalize prefixes first #

If you want to ignore non-numeric characters (like the "-" in "4-20") when comparing, clean the prefixes with REGEXP_REPLACE() before calculating edit distance:

REGEXP_REPLACE(num_prefix, r'[^0-9]', '') AS cleaned_prefix

Use this cleaned prefix in the EDIT_DISTANCE() call, and "4-20" and "420" will have a similarity score of 0—even more precise for your use case.

Why this works #

• BigQuery's EDIT_DISTANCE() is optimized for string comparison, so it’s fast even on large datasets.
• Regex extraction handles variable-length prefixes, so you don’t have to worry about inconsistent segment counts.
• The self-join ensures we only compare rows that share the same text and date, narrowing down the candidates before checking similarity.

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