北海道 釧路男性は肥満・糖代謝・腎機能低下が連動

-- 北海道「釧路」男性：HbA1c・eGFR・BMI25以上・糖尿病薬の年代別指標
WITH hb AS (
  SELECT
    age_group,
    SUM(CASE WHEN r.range_label IN ('6.5以上8.0未満','8.0以上8.4未満','8.4以上') THEN b.inspection_count ELSE 0 END) AS high_hba1c,
    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 = 'HbA1c')
    AND b.area_type = 'secondary_medical_area'
    AND b.gender = 'M'
    AND b.prefecture_name = '北海道'
    AND b.area_name = '釧路'
  GROUP BY age_group
),
egfr AS (
  SELECT
    age_group,
    SUM(CASE WHEN r.range_label IN ('30未満','30以上45未満','45以上60未満') THEN b.inspection_count ELSE 0 END) AS low_egfr,
    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 = 'eGFR')
    AND b.area_type = 'secondary_medical_area'
    AND b.gender = 'M'
    AND b.prefecture_name = '北海道'
    AND b.area_name = '釧路'
  GROUP BY age_group
),
bmi AS (
  SELECT
    age_group,
    SUM(CASE WHEN r.range_label IN ('25.0以上30.0未満','30.0以上35.0未満','35.0以上40.0未満','40.0以上') THEN b.inspection_count ELSE 0 END) AS overweight_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 = 'secondary_medical_area'
    AND b.gender = 'M'
    AND b.prefecture_name = '北海道'
    AND b.area_name = '釧路'
  GROUP BY age_group
),
meds AS (
  SELECT
    age_group,
    SUM(CASE WHEN answer_value = 'はい' THEN response_count ELSE 0 END) AS medication_yes,
    SUM(response_count) AS total_resp
  FROM questionnaire_responses
  WHERE question_id = 2
    AND area_type = 'secondary_medical_area'
    AND gender = 'M'
    AND prefecture_name = '北海道'
    AND area_name = '釧路'
  GROUP BY age_group
)
SELECT
  COALESCE(h.age_group, e.age_group) AS age_group,
  ROUND(100.0 * h.high_hba1c / NULLIF(h.total_count, 0), 2) AS hba1c_pct,
  ROUND(100.0 * e.low_egfr / NULLIF(e.total_count, 0), 2) AS egfr_low_pct,
  ROUND(100.0 * b.overweight_count / NULLIF(b.total_count, 0), 2) AS bmi25_plus_pct,
  ROUND(100.0 * m.medication_yes / NULLIF(m.total_resp, 0), 2) AS dm_med_pct
FROM hb h
FULL JOIN egfr e ON h.age_group = e.age_group
FULL JOIN bmi b ON COALESCE(h.age_group, e.age_group) = b.age_group
FULL JOIN meds m ON COALESCE(h.age_group, e.age_group, b.age_group) = m.age_group
ORDER BY age_group;