Databend 十月月报：存储过程正式可用，数据流程全面自动化

CREATE DATABASE IF NOT EXISTS av_training;
USE av_training;

-- 视频帧标注表（AI 生成的标签）
CREATE TABLE video_frames (
    video_id STRING,
    frame_id INT,
    scene_type STRING,        -- 'urban', 'highway'
    has_pedestrian BOOLEAN,
    has_vehicle BOOLEAN,
    lighting STRING,          -- 'day', 'night', 'dusk'
    weather STRING,           -- 'clear', 'rainy', 'foggy', 'sunny'
    pedestrian_count INT,
    confidence FLOAT,         -- AI 标注置信度
    capture_date DATE
);

INSERT INTO video_frames VALUES
-- 城市夜景，有行人
('V001', 1, 'urban', true, true, 'night', 'clear', 2, 0.95, '2025-10-20'),
('V001', 2, 'urban', true, false, 'night', 'clear', 1, 0.92, '2025-10-20'),

-- 高速白天场景
('V002', 1, 'highway', false, true, 'day', 'sunny', 0, 0.98, '2025-10-21'),

-- 城市夜景雨天
('V003', 1, 'urban', true, true, 'night', 'rainy', 1, 0.89, '2025-10-22'),
('V003', 2, 'urban', true, false, 'night', 'rainy', 2, 0.91, '2025-10-22'),

-- 高速夜景（无行人）
('V004', 1, 'highway', false, true, 'night', 'clear', 0, 0.97, '2025-10-23'),

-- 城市白天场景
('V005', 1, 'urban', true, true, 'day', 'clear', 3, 0.88, '2025-10-24'),
('V005', 2, 'urban', true, false, 'day', 'foggy', 1, 0.93, '2025-10-24');

CREATE PROCEDURE filter_training_frames(
    scene_filter STRING,       -- 'urban', 'highway', 'ALL'
    weather_filter STRING,     -- 'clear', 'rainy', 'ALL'
    date_from DATE,
    date_to DATE
)
RETURNS TABLE (
    scene_type STRING,
    lighting STRING,
    weather STRING,
    frame_count INT,
    pedestrians INT,
    avg_confidence DOUBLE,
    total_frames INT
)
LANGUAGE SQL
AS $$
BEGIN
    LET min_conf := 0.85;
    LET total_frames := 0;

    CREATE OR REPLACE TEMPORARY TABLE matched_frames AS
    SELECT
        video_id, frame_id, scene_type, lighting,
        weather, pedestrian_count, confidence
    FROM video_frames
    WHERE capture_date BETWEEN :date_from AND :date_to
      AND confidence >= min_conf
      AND (:scene_filter = 'ALL' OR scene_type = :scene_filter)
      AND (:weather_filter = 'ALL' OR weather = :weather_filter)
      AND has_pedestrian = true;

    LET total_frames := (SELECT COUNT(*) FROM matched_frames);

    IF total_frames = 0 THEN
        RETURN TABLE(
            SELECT NULL::STRING AS scene_type,
                   NULL::STRING AS lighting,
                   NULL::STRING AS weather,
                   0::INT AS frame_count,
                   0::INT AS pedestrians,
                   0.0::DOUBLE AS avg_confidence,
                   0::INT AS total_frames
        );
    END IF;

    RETURN TABLE(
        SELECT
            scene_type,
            lighting,
            weather,
            COUNT(*) AS frame_count,
            SUM(pedestrian_count) AS pedestrians,
            AVG(confidence) AS avg_confidence,
            :total_frames AS total_frames
        FROM matched_frames
        GROUP BY scene_type, lighting, weather
        ORDER BY frame_count DESC
    );
END;
$$;

CALL PROCEDURE filter_training_frames(
    'urban',                   -- 城市场景
    'ALL',                     -- 任意天气
    DATE '2025-10-20',
    DATE '2025-10-24'
);