80 lines
3.7 KiB
SQL
80 lines
3.7 KiB
SQL
-- 분류 결과 저장을 위한 컬럼 추가
|
|
-- 2024년 BOM 분류 시스템 개선
|
|
|
|
-- materials 테이블에 분류 관련 컬럼 추가
|
|
ALTER TABLE materials ADD COLUMN IF NOT EXISTS subcategory VARCHAR(100);
|
|
ALTER TABLE materials ADD COLUMN IF NOT EXISTS standard VARCHAR(200);
|
|
ALTER TABLE materials ADD COLUMN IF NOT EXISTS grade VARCHAR(200);
|
|
ALTER TABLE materials ADD COLUMN IF NOT EXISTS classification_details JSONB;
|
|
|
|
-- files 테이블에 분류 통계 컬럼 추가
|
|
ALTER TABLE files ADD COLUMN IF NOT EXISTS classification_stats JSONB;
|
|
ALTER TABLE files ADD COLUMN IF NOT EXISTS classification_completed BOOLEAN DEFAULT FALSE;
|
|
ALTER TABLE files ADD COLUMN IF NOT EXISTS classification_timestamp TIMESTAMP;
|
|
|
|
-- 인덱스 추가 (성능 향상)
|
|
CREATE INDEX IF NOT EXISTS idx_materials_classified_category ON materials(classified_category);
|
|
CREATE INDEX IF NOT EXISTS idx_materials_subcategory ON materials(subcategory);
|
|
CREATE INDEX IF NOT EXISTS idx_materials_standard ON materials(standard);
|
|
CREATE INDEX IF NOT EXISTS idx_materials_grade ON materials(grade);
|
|
CREATE INDEX IF NOT EXISTS idx_materials_classification_confidence ON materials(classification_confidence);
|
|
|
|
-- 기존 데이터에 대한 기본값 설정
|
|
UPDATE materials SET
|
|
subcategory = COALESCE(subcategory, ''),
|
|
standard = COALESCE(standard, ''),
|
|
grade = COALESCE(grade, ''),
|
|
classification_details = COALESCE(classification_details, '{}'::jsonb)
|
|
WHERE subcategory IS NULL OR standard IS NULL OR grade IS NULL OR classification_details IS NULL;
|
|
|
|
-- 분류 완료된 파일들 업데이트
|
|
UPDATE files SET
|
|
classification_completed = TRUE,
|
|
classification_timestamp = created_at
|
|
WHERE parsed_count > 0;
|
|
|
|
-- 통계 뷰 생성 (분류 결과 통계 조회용)
|
|
CREATE OR REPLACE VIEW classification_summary AS
|
|
SELECT
|
|
f.job_no,
|
|
f.original_filename,
|
|
f.parsed_count,
|
|
f.classification_completed,
|
|
f.classification_timestamp,
|
|
COUNT(*) as total_materials,
|
|
COUNT(CASE WHEN m.classified_category = 'BOLT' THEN 1 END) as bolt_count,
|
|
COUNT(CASE WHEN m.classified_category = 'FLANGE' THEN 1 END) as flange_count,
|
|
COUNT(CASE WHEN m.classified_category = 'FITTING' THEN 1 END) as fitting_count,
|
|
COUNT(CASE WHEN m.classified_category = 'GASKET' THEN 1 END) as gasket_count,
|
|
COUNT(CASE WHEN m.classified_category = 'INSTRUMENT' THEN 1 END) as instrument_count,
|
|
COUNT(CASE WHEN m.classified_category = 'PIPE' THEN 1 END) as pipe_count,
|
|
COUNT(CASE WHEN m.classified_category = 'VALVE' THEN 1 END) as valve_count,
|
|
COUNT(CASE WHEN m.classified_category = 'MATERIAL' THEN 1 END) as material_count,
|
|
COUNT(CASE WHEN m.classified_category = 'OTHER' THEN 1 END) as other_count,
|
|
AVG(m.classification_confidence) as avg_confidence,
|
|
COUNT(CASE WHEN m.is_verified = TRUE THEN 1 END) as verified_count
|
|
FROM files f
|
|
LEFT JOIN materials m ON f.id = m.file_id
|
|
WHERE f.is_active = TRUE
|
|
GROUP BY f.id, f.job_no, f.original_filename, f.parsed_count, f.classification_completed, f.classification_timestamp;
|
|
|
|
-- 분류 성능 통계 뷰
|
|
CREATE OR REPLACE VIEW classification_performance AS
|
|
SELECT
|
|
classified_category,
|
|
subcategory,
|
|
standard,
|
|
COUNT(*) as total_count,
|
|
AVG(classification_confidence) as avg_confidence,
|
|
COUNT(CASE WHEN is_verified = TRUE THEN 1 END) as verified_count,
|
|
COUNT(CASE WHEN is_verified = FALSE THEN 1 END) as unverified_count,
|
|
ROUND(
|
|
(COUNT(CASE WHEN is_verified = TRUE THEN 1 END)::DECIMAL / COUNT(*) * 100), 2
|
|
) as verification_rate
|
|
FROM materials
|
|
WHERE classified_category IS NOT NULL
|
|
GROUP BY classified_category, subcategory, standard
|
|
ORDER BY total_count DESC;
|
|
|
|
-- 변경사항 확인
|
|
SELECT 'Database schema updated successfully' as status; |