如何在SQL中统计各地区门店的性别-年龄段分布？

样本数据 #

state      poi_name       gender age
aichi   starbucks shop E    2     3
aichi   starbucks shop G    0     2
aichi   starbucks shop G    1     2
chiba   starbucks shop A    0     1
chiba   starbucks shop D    1     1
chiba   starbucks shop A    0     2
tokyo   starbucks shop B    2     1
tokyo   starbucks shop B    1     0
tokyo   starbucks shop C    2     3
tokyo   starbucks shop F    1     2
aichi   starbucks shop E    1     2

当前已实现的性别分布统计SQL #

SELECT
state, 
poi_name, 
count(gender)  all_cnt,
 count(  gender = '0' or null) as Unknown,
 count(  gender = '1' or null) as Total_Male,
 count(  gender = '2' or null) as Total_Female,
 count('gender') OVER(PARTITION BY state) AS cnt_for_state
FROM `geo_data_working.hw0160_infoDemographic` 
GROUP BY  state,poi_name
ORDER BY state,poi_name

年龄范围映射 #

• -1：13-17岁
• 0：未知年龄
• 1：18-24岁
• 2：25-34岁
• 3：35-44岁
• 4：45岁及以上

预期结果样式 #

样式1：按性别拆分行，列展示各年龄段人数 #

样式2：按性别合并列，一行展示所有性别+年龄段数据 #

解决方案SQL #

对应样式1的SQL #

SELECT
  state,
  poi_name,
  CASE gender
    WHEN '0' THEN '未知'
    WHEN '1' THEN '男'
    WHEN '2' THEN '女'
    ELSE '其他'
  END AS gender,
  COUNT(CASE WHEN age = '0' THEN 1 END) AS ageRange0,
  COUNT(CASE WHEN age = '1' THEN 1 END) AS ageRange1,
  COUNT(CASE WHEN age = '2' THEN 1 END) AS ageRange2,
  COUNT(CASE WHEN age = '3' THEN 1 END) AS ageRange3,
  COUNT(CASE WHEN age = '4' THEN 1 END) AS ageRange4,
  COUNT(CASE WHEN age = '-1' THEN 1 END) AS ageRange_minus1
FROM `geo_data_working.hw0160_infoDemographic`
WHERE gender IN ('1','2','0')
GROUP BY state, poi_name, gender
ORDER BY state, poi_name, gender;

对应样式2的SQL #

SELECT
  state,
  poi_name,
  -- 女性各年龄段统计
  COUNT(CASE WHEN gender = '2' AND age = '0' THEN 1 END) AS F_ageRange0,
  COUNT(CASE WHEN gender = '2' AND age = '1' THEN 1 END) AS F_ageRange1,
  COUNT(CASE WHEN gender = '2' AND age = '2' THEN 1 END) AS F_ageRange2,
  COUNT(CASE WHEN gender = '2' AND age = '3' THEN 1 END) AS F_ageRange3,
  COUNT(CASE WHEN gender = '2' AND age = '4' THEN 1 END) AS F_ageRange4,
  COUNT(CASE WHEN gender = '2' AND age = '-1' THEN 1 END) AS F_ageRange_minus1,
  -- 男性各年龄段统计
  COUNT(CASE WHEN gender = '1' AND age = '0' THEN 1 END) AS M_ageRange0,
  COUNT(CASE WHEN gender = '1' AND age = '1' THEN 1 END) AS M_ageRange1,
  COUNT(CASE WHEN gender = '1' AND age = '2' THEN 1 END) AS M_ageRange2,
  COUNT(CASE WHEN gender = '1' AND age = '3' THEN 1 END) AS M_ageRange3,
  COUNT(CASE WHEN gender = '1' AND age = '4' THEN 1 END) AS M_ageRange4,
  COUNT(CASE WHEN gender = '1' AND age = '-1' THEN 1 END) AS M_ageRange_minus1,
  -- 未知性别各年龄段统计（可选）
  COUNT(CASE WHEN gender = '0' AND age = '0' THEN 1 END) AS Unknown_ageRange0,
  COUNT(CASE WHEN gender = '0' AND age = '1' THEN 1 END) AS Unknown_ageRange1,
  COUNT(CASE WHEN gender = '0' AND age = '2' THEN 1 END) AS Unknown_ageRange2,
  COUNT(CASE WHEN gender = '0' AND age = '3' THEN 1 END) AS Unknown_ageRange3,
  COUNT(CASE WHEN gender = '0' AND age = '4' THEN 1 END) AS Unknown_ageRange4,
  COUNT(CASE WHEN gender = '0' AND age = '-1' THEN 1 END) AS Unknown_ageRange_minus1
FROM `geo_data_working.hw0160_infoDemographic`
GROUP BY state, poi_name
ORDER BY state, poi_name;

说明 #

• 两个方案均使用CASE语句结合COUNT实现条件统计，适配原SQL的BigQuery语法环境
• 样式1将性别作为分组维度，每行对应一个性别，适合按性别细分查看各年龄段分布
• 样式2将性别与年龄段合并为列，一行展示所有统计结果，适合整体对比不同性别、年龄段的人数差异
• 可根据需求调整列名，比如将ageRange0改为未知年龄，让结果更直观

内容的提问来源于stack exchange，提问作者アルサ