山形県 最上男性は重度高血圧でも心疾患認識が低い

-- 山形県「最上」男性：重度高血圧・降圧薬使用・心疾患自己申告の年代別指標
WITH sbp AS (
  SELECT
    age_group,
    SUM(CASE WHEN r.range_label IN ('160以上180未満','180以上') THEN b.inspection_count ELSE 0 END) AS severe_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 = '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 = 1
    AND area_type = 'secondary_medical_area'
    AND gender = 'M'
    AND prefecture_name = '山形県'
    AND area_name = '最上'
  GROUP BY age_group
),
heart AS (
  SELECT
    age_group,
    SUM(CASE WHEN answer_value = 'はい' THEN response_count ELSE 0 END) AS heart_history,
    SUM(response_count) AS total_resp
  FROM questionnaire_responses
  WHERE question_id = 5
    AND area_type = 'secondary_medical_area'
    AND gender = 'M'
    AND prefecture_name = '山形県'
    AND area_name = '最上'
  GROUP BY age_group
)
SELECT
  COALESCE(s.age_group, m.age_group) AS age_group,
  ROUND(100.0 * s.severe_bp / NULLIF(s.total_count, 0), 2) AS severe_bp_pct,
  ROUND(100.0 * m.medication_yes / NULLIF(m.total_resp, 0), 2) AS medication_pct,
  ROUND(100.0 * h.heart_history / NULLIF(h.total_resp, 0), 2) AS heart_history_pct
FROM sbp s
FULL JOIN meds m ON s.age_group = m.age_group
FULL JOIN heart h ON COALESCE(s.age_group, m.age_group) = h.age_group
ORDER BY age_group;