嵌套结构中按personnel_guid数量分组job_id的SQL查询求助

场景1：person数组中无重复personnel_guid #

如果每个job_id对应的person数组里，personnel_guid都是唯一的，直接用ARRAY_LENGTH()统计数量即可，无需展开数组：

SELECT
  job_id,
  ARRAY_LENGTH(personnel_data.person) AS personnel_count,
  CASE
    WHEN ARRAY_LENGTH(personnel_data.person) < 10 THEN '少于10人'
    WHEN ARRAY_LENGTH(personnel_data.person) BETWEEN 10 AND 25 THEN '10-25人'
    WHEN ARRAY_LENGTH(personnel_data.person) BETWEEN 26 AND 75 THEN '25-75人'
    ELSE '75人以上'
  END AS personnel_group
FROM
  your_table_name;

场景2：person数组中存在重复personnel_guid #

如果数组里有重复的personnel_guid，需要先通过UNNEST()展开数组，去重后再统计：

WITH job_personnel_stats AS (
  SELECT
    job_id,
    COUNT(DISTINCT p.personnel_guid) AS personnel_count
  FROM
    your_table_name,
    UNNEST(personnel_data.person) AS p
  GROUP BY
    job_id
)
SELECT
  job_id,
  personnel_count,
  CASE
    WHEN personnel_count < 10 THEN '少于10人'
    WHEN personnel_count BETWEEN 10 AND 25 THEN '10-25人'
    WHEN personnel_count BETWEEN 26 AND 75 THEN '25-75人'
    ELSE '75人以上'
  END AS personnel_group
FROM
  job_personnel_stats;

关键说明 #

• 替换代码中的your_table_name为你的实际表名
• CASE语句的区间边界可根据需求调整，比如若要让25-75人包含25和75，可把第二个WHEN的上限设为24，第三个设为BETWEEN 25 AND 75
• 第一种方法效率更高，适合无重复数据的场景；第二种方法通过COUNT(DISTINCT)确保统计唯一标识的数量

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