Oracle SQL按相似度分组：含相似条目表的统计查询需求

Got it, let's work through how to group and count those nearly identical error messages where only tokens differ. The core idea is to standardize the variable tokens into a consistent placeholder first, then group by that standardized message.

Step 1: Identify your token pattern #

First, you need to nail down what your variable tokens look like. Are they UUIDs? Random alphanumeric strings? Or tokens wrapped in specific characters (like {{user_id}})? For example:

• UUIDs follow this pattern: 8-4-4-4-12 hex characters, e.g., 123e4567-e89b-12d3-a456-426614174000
• Numeric tokens: 12345 or 9876
• Wrapped tokens: {{order_token}}

Step 2: Standardize error descriptions with regex replacement #

Most modern SQL databases support regex-based string replacement, which is perfect for this. Here's how to adapt your existing query to do this:

Example for PostgreSQL/MySQL 8.0+/SQL Server 2017+ #

SELECT 
    -- Replace all variable tokens with a fixed placeholder (<TOKEN>)
    REGEXP_REPLACE(err.error_desc, '[a-f0-9]{8}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{12}', '<UUID_TOKEN>', 'g') AS standardized_error,
    COUNT(err.id) AS error_count
FROM entry_message err_entries
FULL OUTER JOIN entry_error err ON err_entries.id = err.id
FULL OUTER JOIN error_code ec ON err.error_code = ec.error_code
WHERE NOT EXISTS(
    SELECT id_father FROM entry_message creator WHERE err_entries... -- Keep your existing subquery logic here
)
GROUP BY standardized_error
ORDER BY error_count DESC;

Adjust the regex for your token type #

• If your tokens are numeric: Change the regex to '\d+'
• If tokens are wrapped in {{...}}: Use '\{\{[a-zA-Z0-9_]+\}\}'
• If you have multiple token types, chain regex replacements:

  REGEXP_REPLACE(REGEXP_REPLACE(err.error_desc, '[a-f0-9]{36}', '<UUID>', 'g'), '\d+', '<NUM_TOKEN>', 'g')
  

For older databases without regex support #

If you're stuck on an older DB that doesn't support regex (like pre-8.0 MySQL), you can use nested REPLACE functions—though this only works if you know all possible token values upfront:

REPLACE(REPLACE(err.error_desc, 'token_value_1', '<TOKEN>'), 'token_value_2', '<TOKEN>')

Step 3: Verify and refine #

Before running the full group query, test the regex replacement alone to make sure it's working correctly:

SELECT 
    error_desc,
    REGEXP_REPLACE(error_desc, '[a-f0-9]{36}', '<UUID_TOKEN>', 'g') AS standardized
FROM entry_error
LIMIT 10;

This lets you check if you're not accidentally replacing non-token text.

Notes to keep in mind #

• If your FULL OUTER JOIN is pulling in NULLs, use COALESCE to handle them (e.g., COALESCE(standardized_error, 'Unknown Error')) to avoid grouping NULLs separately.
• Adjust the GROUP BY clause to match your standardized field name exactly.

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