釧路男性のBMI・HbA1c・eGFRを確認

-- 第11回NDBインサイト更新で使った release-aware 集計SQLの代表形。
-- 実際の静的ページ値は scripts/validation/insights_release11_refresh.py で、
-- release_id=2 / data_year=2024 を明示してローカルDBから生成している。

-- 健診検査値の地域別・年代別割合
-- 例: BMI30以上、腹囲90cm以上、収縮期血圧140以上、eGFR 60未満など。
SELECT
  b.age_group,
  SUM(CASE WHEN r.range_label = ANY(:positive_ranges) THEN b.inspection_count ELSE 0 END)::bigint AS numerator,
  SUM(b.inspection_count)::bigint AS denominator,
  ROUND(
    SUM(CASE WHEN r.range_label = ANY(:positive_ranges) THEN b.inspection_count ELSE 0 END)::numeric
    * 100
    / NULLIF(SUM(b.inspection_count), 0),
    2
  ) AS rate_percent
FROM basic_checkup_results b
JOIN health_inspection_items i ON i.item_id = b.item_id
JOIN health_inspection_value_ranges r ON r.range_id = b.range_id
WHERE b.release_id = 2
  AND b.data_year = 2024
  AND b.data_source_type = 'basic'
  AND i.item_name = :item_name
  AND b.area_type = :area_type
  AND b.prefecture_name = :prefecture_name
  AND (:area_name IS NULL OR b.area_name = :area_name)
  AND b.gender = :gender
GROUP BY b.age_group
ORDER BY b.age_group;

-- 全国比較は、同じ release_id/data_year/item/gender/range 条件で
-- area_type='prefecture' の47都道府県を母集団にした加重割合として算出する。
SELECT
  b.age_group,
  SUM(CASE WHEN r.range_label = ANY(:positive_ranges) THEN b.inspection_count ELSE 0 END)::bigint AS numerator,
  SUM(b.inspection_count)::bigint AS denominator,
  ROUND(
    SUM(CASE WHEN r.range_label = ANY(:positive_ranges) THEN b.inspection_count ELSE 0 END)::numeric
    * 100
    / NULLIF(SUM(b.inspection_count), 0),
    2
  ) AS benchmark_rate_percent
FROM basic_checkup_results b
JOIN health_inspection_items i ON i.item_id = b.item_id
JOIN health_inspection_value_ranges r ON r.range_id = b.range_id
WHERE b.release_id = 2
  AND b.data_year = 2024
  AND b.data_source_type = 'basic'
  AND i.item_name = :item_name
  AND b.area_type = 'prefecture'
  AND b.gender = :gender
GROUP BY b.age_group
ORDER BY b.age_group;

-- 質問票系インサイトは、現行マスタで設問・回答値が安全に復元できるものだけを採用する。
-- 回答率は、選択した回答値を分子、同じ地域・性別・年代・設問の全回答値合計を分母にする。
SELECT
  q.age_group,
  SUM(CASE WHEN q.answer_value = ANY(:positive_answers) THEN q.response_count ELSE 0 END)::bigint AS numerator,
  SUM(q.response_count)::bigint AS denominator,
  ROUND(
    SUM(CASE WHEN q.answer_value = ANY(:positive_answers) THEN q.response_count ELSE 0 END)::numeric
    * 100
    / NULLIF(SUM(q.response_count), 0),
    2
  ) AS response_rate_percent
FROM questionnaire_responses q
WHERE q.release_id = 2
  AND q.data_year = 2024
  AND q.question_id = :question_id
  AND q.area_type = :area_type
  AND q.prefecture_name = :prefecture_name
  AND (:area_name IS NULL OR q.area_name = :area_name)
  AND q.gender = :gender
GROUP BY q.age_group
ORDER BY q.age_group;