求用于识别相似数据组合组数量的MySQL查询语句

First, let's start with a reasonable assumption about your database schema (since you didn't share it explicitly). I'll assume you have a table named district_systems with these core columns:

• district_id: Unique identifier for each school district
• category_id: ID for the 40 system categories
• product_name: Name of the system used in that category for the district

Approach: Group Districts by Their Full System Combination #

The key idea is to generate a unique "fingerprint" for each district's complete set of (category + product) pairs, then count how many districts share each fingerprint.

Option 1: Using GROUP_CONCAT to Create a Readable Combination String

This method builds a concatenated string of all category-product pairs for each district, then groups by that string to count duplicates.

-- Step 1: Generate a unique string for each district's system combination
WITH district_combinations AS (
    SELECT
        district_id,
        GROUP_CONCAT(
            CONCAT(category_id, ':', product_name)
            ORDER BY category_id ASC  -- Critical: ensures consistent ordering for identical combinations
            SEPARATOR '|'
        ) AS system_combination
    FROM district_systems
    GROUP BY district_id
)
-- Step 2: Count how many districts share each combination
SELECT
    system_combination,
    COUNT(district_id) AS district_count
FROM district_combinations
GROUP BY system_combination
HAVING COUNT(district_id) > 1  -- Optional: only show combinations shared by multiple districts
ORDER BY district_count DESC;

Option 2: Using a Hash for Faster Grouping (Better for Large Datasets)

If the concatenated string gets too long (since you have 40 categories), using a hash function like MD5 or SHA2 will create a fixed-length fingerprint, making grouping more efficient.

WITH district_combinations AS (
    SELECT
        district_id,
        MD5(
            GROUP_CONCAT(
                CONCAT(category_id, ':', product_name)
                ORDER BY category_id ASC
                SEPARATOR '|'
            )
        ) AS combination_hash
    FROM district_systems
    GROUP BY district_id
)
SELECT
    combination_hash,
    COUNT(district_id) AS district_count,
    -- Optional: Include one example district to inspect the combination
    MIN(district_id) AS example_district_id
FROM district_combinations
GROUP BY combination_hash
HAVING COUNT(district_id) > 1
ORDER BY district_count DESC;

Key Notes: #

• Order Matters: The ORDER BY category_id ASC inside GROUP_CONCAT is essential. Without it, the same set of category-product pairs could be ordered differently for different districts, leading to different strings/hashes even though the combinations are identical.
• Handling Missing Data: If some districts don't have a system in every category, you might want to include COALESCE(product_name, 'No_System') in the concatenation to account for missing entries, so empty categories don't break the combination fingerprint.
• Filtering by Category: If you only want to compare combinations across a subset of categories, add a WHERE category_id IN (1,2,3) clause in the CTE to narrow down the data.

Example Result: #

This will show you exactly how many districts share identical system combinations across all 40 categories.

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