群馬県 高崎・安中男性は糖尿病と腎機能低下が同時進行

-- 群馬県「高崎・安中」男性：HbA1c・eGFR・糖尿病薬使用の年代別指標
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
),
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 * 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 meds m ON COALESCE(h.age_group, e.age_group) = m.age_group
ORDER BY age_group;