정확도 고도화 — COMMENT & 샘플 질문

ccm_factory.gold 스키마의 모든 테이블과 컬럼에 한국어 COMMENT를 추가해줘.

규칙은 이렇게 해줘.
- 테이블 COMMENT: 비즈니스 담당자가 이해할 수 있는 설명 (50자 이내)
- 컬럼 COMMENT: 값의 의미, 단위, 범위 포함

이런 형식으로 달아줘.
COMMENT ON COLUMN @ccm_factory.gold.daily_yield_summary.yield_pct IS
  '일 수율(%). (양품 수 / 투입 수) × 100. 범위 0~100';
COMMENT ON COLUMN @ccm_factory.gold.oee_by_equipment.oee_pct IS
  '종합설비효율(%). 가용성×성능×품질. 85 이상이 World Class';
COMMENT ON COLUMN @ccm_factory.gold.spc_process_capability.cpk IS
  '공정능력지수. 1.33 이상이 관리 기준, 1.0 미만은 개선 필요';

확인했으면 모든 Gold 테이블의 모든 컬럼에 COMMENT를 달아줘.

"생산·수율 분석", "품질·불량 분석", "설비·OEE 분석" 3개 Genie Space에 아래 샘플 질문과 검증된 SQL을 각 Space 주제에 맞게 등록해줘.

SELECT
  line_id,
  ROUND(AVG(yield_pct), 1) AS avg_yield_pct,
  SUM(input_qty) AS total_input,
  SUM(good_qty) AS total_good
FROM ccm_factory.gold.daily_yield_summary
WHERE event_date >= CURRENT_DATE - INTERVAL 7 DAYS
GROUP BY line_id
ORDER BY avg_yield_pct ASC

SELECT
  y.model_code,
  p.customer,
  p.module_type,
  ROUND(AVG(y.yield_pct), 1) AS avg_yield_pct,
  COUNT(DISTINCT y.event_date) AS days
FROM ccm_factory.gold.daily_yield_summary y
JOIN ccm_factory.bronze.product_master p ON y.model_code = p.model_code
WHERE y.event_date >= CURRENT_DATE - INTERVAL 30 DAYS
GROUP BY y.model_code, p.customer, p.module_type
ORDER BY avg_yield_pct ASC
LIMIT 10

SELECT
  station,
  ROUND(AVG(fpy_pct), 1) AS avg_fpy_pct,
  SUM(first_pass_count) AS first_pass_count,
  SUM(input_count) AS input_count
FROM ccm_factory.gold.first_pass_yield
WHERE event_date >= CURRENT_DATE - INTERVAL 7 DAYS
GROUP BY station
ORDER BY avg_fpy_pct ASC

WITH weekly AS (
  SELECT
    DATE_TRUNC('week', event_date) AS week_start,
    ROUND(AVG(yield_pct), 1) AS avg_yield
  FROM ccm_factory.gold.daily_yield_summary
  WHERE event_date >= CURRENT_DATE - INTERVAL 30 DAYS
  GROUP BY DATE_TRUNC('week', event_date)
)
SELECT
  week_start,
  avg_yield,
  ROUND(avg_yield - LAG(avg_yield) OVER (ORDER BY week_start), 1) AS wow_change
FROM weekly
ORDER BY week_start

SELECT
  station,
  ROUND(AVG(avg_cycle_time_sec), 1) AS avg_cycle_sec,
  ROUND(PERCENTILE(avg_cycle_time_sec, 0.95), 1) AS p95_cycle_sec
FROM ccm_factory.gold.station_cycle_time
WHERE event_date >= CURRENT_DATE - INTERVAL 7 DAYS
GROUP BY station
ORDER BY avg_cycle_sec DESC

SELECT
  defect_code,
  defect_category,
  SUM(defect_count) AS total_defects,
  ROUND(SUM(defect_count) * 100.0 / SUM(SUM(defect_count)) OVER (), 1) AS pct_of_total
FROM ccm_factory.gold.defect_pareto
WHERE event_date >= CURRENT_DATE - INTERVAL 7 DAYS
GROUP BY defect_code, defect_category
ORDER BY total_defects DESC
LIMIT 10

SELECT
  supplier,
  ROUND(AVG(defect_rate_pct), 2) AS avg_defect_rate,
  SUM(defect_qty) AS total_defects,
  SUM(used_qty) AS total_used
FROM ccm_factory.gold.material_defect_correlation
WHERE event_date >= CURRENT_DATE - INTERVAL 30 DAYS
GROUP BY supplier
ORDER BY avg_defect_rate DESC

SELECT
  model_code,
  ROUND(AVG(mtf_center_avg), 1) AS avg_mtf_center,
  ROUND(AVG(below_spec_pct), 1) AS avg_below_spec_pct
FROM ccm_factory.gold.aa_quality_trend
WHERE event_date >= CURRENT_DATE - INTERVAL 7 DAYS
GROUP BY model_code
ORDER BY avg_below_spec_pct DESC
LIMIT 10

SELECT
  process,
  parameter,
  ROUND(AVG(cpk), 2) AS avg_cpk,
  ROUND(AVG(mean_value), 2) AS mean_value
FROM ccm_factory.gold.spc_process_capability
WHERE event_date >= CURRENT_DATE - INTERVAL 7 DAYS
GROUP BY process, parameter
HAVING AVG(cpk) < 1.33
ORDER BY avg_cpk ASC

SELECT
  defect_category,
  SUM(scrap_count) AS scrap_qty,
  SUM(rework_count) AS rework_qty,
  ROUND(SUM(scrap_count) * 100.0
    / NULLIF(SUM(defect_count), 0), 1) AS scrap_pct
FROM ccm_factory.gold.defect_pareto
WHERE event_date >= CURRENT_DATE - INTERVAL 30 DAYS
GROUP BY defect_category
ORDER BY scrap_qty DESC

SELECT
  o.equipment_id,
  e.equipment_type,
  e.line_id,
  ROUND(AVG(o.oee_pct), 1) AS avg_oee,
  ROUND(AVG(o.availability_pct), 1) AS availability,
  ROUND(AVG(o.performance_pct), 1) AS performance,
  ROUND(AVG(o.quality_pct), 1) AS quality
FROM ccm_factory.gold.oee_by_equipment o
JOIN ccm_factory.bronze.equipment_master e ON o.equipment_id = e.equipment_id
WHERE o.event_date >= CURRENT_DATE - INTERVAL 7 DAYS
GROUP BY o.equipment_id, e.equipment_type, e.line_id
ORDER BY avg_oee ASC
LIMIT 5

SELECT
  h.equipment_id,
  e.equipment_type,
  ROUND(AVG(h.health_score), 1) AS avg_health,
  ANY_VALUE(h.risk_factors) AS risk_factors
FROM ccm_factory.gold.equipment_health_score h
JOIN ccm_factory.bronze.equipment_master e ON h.equipment_id = e.equipment_id
WHERE h.event_date >= CURRENT_DATE - INTERVAL 7 DAYS
GROUP BY h.equipment_id, e.equipment_type
HAVING AVG(h.health_score) < 60
ORDER BY avg_health ASC

SELECT
  downtime_type,
  reason_code,
  COUNT(*) AS occurrences,
  ROUND(SUM(duration_min), 0) AS total_downtime_min
FROM ccm_factory.bronze.equipment_downtime
WHERE start_time >= CURRENT_DATE - INTERVAL 7 DAYS
GROUP BY downtime_type, reason_code
ORDER BY total_downtime_min DESC
LIMIT 10

SELECT
  e.line_id,
  ROUND(AVG(o.oee_pct), 1) AS avg_oee,
  COUNT(DISTINCT o.equipment_id) AS equipment_count
FROM ccm_factory.gold.oee_by_equipment o
JOIN ccm_factory.bronze.equipment_master e ON o.equipment_id = e.equipment_id
WHERE o.event_date >= CURRENT_DATE - INTERVAL 7 DAYS
GROUP BY e.line_id
ORDER BY avg_oee DESC

SELECT
  e.equipment_type,
  COUNT(*) AS breakdown_count,
  ROUND(AVG(d.duration_min), 0) AS avg_repair_min
FROM ccm_factory.bronze.equipment_downtime d
JOIN ccm_factory.bronze.equipment_master e ON d.equipment_id = e.equipment_id
WHERE d.downtime_type = 'BREAKDOWN'
  AND d.start_time >= CURRENT_DATE - INTERVAL 30 DAYS
GROUP BY e.equipment_type
ORDER BY breakdown_count DESC