SQL数组排序聚合与相似属性记录高效计数方案

针对统计结果的两类偏差问题，直接按分层清洗+标准化+排序聚合的逻辑实现即可，不需要堆砌嵌套正则硬改，后续维护成本更低。 #

核心解决逻辑 #

• 数组顺序不一致导致分组错误：拆分得到单个标签后，对同一条记录下的所有标签做统一字典序排序，再重新聚合为数组作为分组键。元素完全相同的数组不管原始顺序如何，排序后生成的分组键完全一致，自然会被分到同一组。
• 单复数/近义标签拆分统计：不要靠正则自动转换单复数（不规则名词变化太多，正则覆盖不全容易出bug），直接维护一层轻量标签映射表，把所有非标准的原始标签统一映射到标准标签值，后续新增标签规则只需要往映射表里加记录，不需要改动主查询逻辑。

可直接复用的实现代码 #

代码用CTE分层编写，每一步逻辑独立，方便排查问题：

WITH raw_base AS (
    -- 第一层：关联原始表主键，截取横杠后的物业描述段做基础清洗
    SELECT 
        -- 替换为你表的实际主键字段，用于后续把标签聚合回单条记录
        "行号" AS record_id,
        TRIM(REGEXP_REPLACE(
            REGEXP_SUBSTR(REPLACE("Title",'-2',''), '[^-]*$'), 
            '[0-9]+', ''
        )) AS raw_property_desc
    FROM DATA.PROPERTIES
),
raw_tag_split AS (
    -- 第二层：统一分隔符，拆分出单个原始标签，去除前后空格和空值
    SELECT 
        b.record_id,
        TRIM(single_tag) AS raw_tag
    FROM raw_base b,
    UNNEST(SPLIT(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(b.raw_property_desc,'/', '&'),' AND ','&'),'&  ','&'),' &','&'),'& ','&'),
        '&'
    )) AS single_tag
    WHERE TRIM(single_tag) <> ''
),
tag_standardized AS (
    -- 第三层：关联标签映射规则，替换为标准化标签
    SELECT 
        s.record_id,
        COALESCE(m.std_tag, s.raw_tag) AS std_tag
    FROM raw_tag_split s
    LEFT JOIN (
        -- 标签映射规则，后续新增非标准标签直接在VALUES里加行即可
        SELECT * FROM (VALUES
            ('HOUSE', 'HOUSES'),
            ('FLAT', 'FLATS'),
            ('BUNGALOW', 'BUNGALOWS'),
            ('RETAIL', 'RETAIL UNITS'),
            ('RETAIL UNIT', 'RETAIL UNITS'),
            ('INDUSTRIAL UNIT', 'INDUSTRIAL UNITS')
        ) AS tag_map(raw_tag, std_tag)
    ) m ON s.raw_tag = m.raw_tag
),
record_with_std_category AS (
    -- 第四层：同一条记录的标准化标签去重、按字典序排序，聚合成统一分类数组
    SELECT 
        record_id,
        ARRAY_AGG(DISTINCT std_tag ORDER BY std_tag) AS standard_category
    FROM tag_standardized
    GROUP BY record_id
)
-- 最终按标准分类分组计数
SELECT 
    standard_category AS "Category",
    COUNT(1) AS "Frequency"
FROM record_with_std_category
GROUP BY standard_category
ORDER BY "Frequency" DESC;

适配说明 #

• 如果你用的SQL引擎不支持UNNEST语法，替换为对应引擎的数组展开语法即可：Hive/Spark用LATERAL VIEW EXPLODE，ClickHouse用ARRAY JOIN，MySQL用JSON_TABLE。
• 标签映射规则不用一次性写全，先跑一次全量原始标签的去重统计，把出现频次Top的非标准标签先做映射，低频标签遇到再补规则，效率最高。
• 之前嵌套多层REPLACE的写法容易漏特殊全角空格、不可见字符的情况，拆分标签后单独做TRIM可以过滤掉90%以上的这类脏数据。

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