秋田県男性は飲酒文化と肝機能異常が重なる

-- 秋田県男性：γ-GT高値と飲酒習慣の年代別指標
WITH ggt AS (
  SELECT
    age_group,
    SUM(CASE WHEN r.range_label = '101以上' THEN b.inspection_count ELSE 0 END) AS high_ggt,
    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 = 'γ-GT')
    AND b.area_type = 'prefecture'
    AND b.gender = 'M'
    AND b.prefecture_name = '秋田県'
  GROUP BY age_group
),
drink_freq AS (
  SELECT
    age_group,
    SUM(CASE WHEN answer_value = '毎日' THEN response_count ELSE 0 END) AS daily_drinkers,
    SUM(response_count) AS total_resp
  FROM questionnaire_responses
  WHERE question_id = 18
    AND area_type = 'prefecture'
    AND gender = 'M'
    AND prefecture_name = '秋田県'
  GROUP BY age_group
),
drink_amount AS (
  SELECT
    age_group,
    SUM(CASE WHEN answer_value = '3合以上' THEN response_count ELSE 0 END) AS heavy_drinkers,
    SUM(response_count) AS total_resp
  FROM questionnaire_responses
  WHERE question_id = 19
    AND area_type = 'prefecture'
    AND gender = 'M'
    AND prefecture_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 = 'prefecture'
    AND b.gender = 'M'
    AND b.prefecture_name = '秋田県'
  GROUP BY age_group
)
SELECT
  COALESCE(g.age_group, f.age_group) AS age_group,
  ROUND(100.0 * g.high_ggt / NULLIF(g.total_count, 0), 2) AS high_ggt_pct,
  ROUND(100.0 * f.daily_drinkers / NULLIF(f.total_resp, 0), 2) AS daily_drink_pct,
  ROUND(100.0 * a.heavy_drinkers / NULLIF(a.total_resp, 0), 2) AS heavy_drink_pct,
  ROUND(100.0 * b.overweight_count / NULLIF(b.total_count, 0), 2) AS overweight_pct
FROM ggt g
FULL JOIN drink_freq f ON g.age_group = f.age_group
FULL JOIN drink_amount a ON COALESCE(g.age_group, f.age_group) = a.age_group
FULL JOIN bmi b ON COALESCE(g.age_group, f.age_group, a.age_group) = b.age_group
ORDER BY age_group;