沖縄県男性は肥満・内臓脂肪・高血圧が同時進行

-- 沖縄県男性：BMI30以上・腹囲90cm以上・高血圧・歩行習慣の年代別指標
WITH bmi AS (
  SELECT
    age_group,
    SUM(CASE WHEN r.range_label IN ('30.0以上35.0未満','35.0以上40.0未満','40.0以上') THEN b.inspection_count ELSE 0 END) AS obese_count,
    SUM(b.inspection_count) AS total_count
  FROM basic_checkup_results b
  JOIN health_inspection_value_ranges r ON b.range_id = r.range_id
  WHERE b.item_id = (SELECT item_id FROM health_inspection_items WHERE item_name = 'BMI')
    AND b.area_type = 'prefecture'
    AND b.gender = 'M'
    AND b.prefecture_name = '沖縄県'
  GROUP BY age_group
),
waist AS (
  SELECT
    age_group,
    SUM(CASE WHEN r.range_label = '90.0以上' THEN b.inspection_count ELSE 0 END) AS high_waist,
    SUM(b.inspection_count) AS total_count
  FROM basic_checkup_results b
  JOIN health_inspection_value_ranges r ON b.range_id = r.range_id
  WHERE b.item_id = (SELECT item_id FROM health_inspection_items WHERE item_name = '腹囲')
    AND b.area_type = 'prefecture'
    AND b.gender = 'M'
    AND b.prefecture_name = '沖縄県'
  GROUP BY age_group
),
sbp AS (
  SELECT
    age_group,
    SUM(CASE WHEN r.range_label IN ('140以上160未満','160以上180未満','180以上') THEN b.inspection_count ELSE 0 END) AS high_bp,
    SUM(b.inspection_count) AS total_count
  FROM basic_checkup_results b
  JOIN health_inspection_value_ranges r ON b.range_id = r.range_id
  WHERE b.item_id = (SELECT item_id FROM health_inspection_items WHERE item_name = '収縮期血圧')
    AND b.area_type = 'prefecture'
    AND b.gender = 'M'
    AND b.prefecture_name = '沖縄県'
  GROUP BY age_group
),
walking AS (
  SELECT
    age_group,
    SUM(CASE WHEN answer_value = 'はい' THEN response_count ELSE 0 END) AS walkers,
    SUM(response_count) AS total_resp
  FROM questionnaire_responses
  WHERE question_id = 11
    AND area_type = 'prefecture'
    AND gender = 'M'
    AND prefecture_name = '沖縄県'
  GROUP BY age_group
)
SELECT
  COALESCE(b.age_group, w.age_group) AS age_group,
  ROUND(100.0 * b.obese_count / NULLIF(b.total_count, 0), 2) AS obese_pct,
  ROUND(100.0 * wa.high_waist / NULLIF(wa.total_count, 0), 2) AS waist_pct,
  ROUND(100.0 * s.high_bp / NULLIF(s.total_count, 0), 2) AS sbp_pct,
  ROUND(100.0 * w.walkers / NULLIF(w.total_resp, 0), 2) AS walk_pct
FROM bmi b
FULL JOIN waist wa ON b.age_group = wa.age_group
FULL JOIN sbp s ON COALESCE(b.age_group, wa.age_group) = s.age_group
FULL JOIN walking w ON COALESCE(b.age_group, wa.age_group, s.age_group) = w.age_group
ORDER BY age_group;