测试数据可视化：用Superset 10分钟搭建仪表盘，效率提升200%_千锋教育

# 拉取镜像
docker pull apache/superset:latest

# 启动容器
docker run -d -p 8088:8088 \
  -e SUPERSET_SECRET_KEY='your-secret-key-change-this' \
  --name superset apache/superset:latest

# 创建管理员账号
docker exec -it superset superset fab create-admin \
  --username admin \
  --firstname Superset \
  --lastname Admin \
  --email admin@superset.com \
  --password admin

# 初始化数据库
docker exec -it superset superset db upgrade

# 加载示例数据（可选）
docker exec -it superset superset load_examples

# 初始化
docker exec -it superset superset init

-- 创建测试数据表（示例）
CREATE TABLE test_execution_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    test_suite VARCHAR(100),
    test_case VARCHAR(200),
    status ENUM('PASS', 'FAIL', 'SKIP', 'ERROR'),
    duration_ms INT,
    executor VARCHAR(50),
    build_number INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    version VARCHAR(20)
);

-- 插入示例数据
INSERT INTO test_execution_history 
(test_suite, test_case, status, duration_ms, executor, build_number, version)
VALUES 
('login_module', 'test_login_success', 'PASS', 1250, 'zhangsan', 1024, 'v2.1.0'),
('payment_module', 'test_payment_timeout', 'FAIL', 3200, 'lisi', 1024, 'v2.1.0');

mysql://user:password@host:3306/test_db

# Python脚本：构建后自动写入测试结果
import mysql.connector
from datetime import datetime

def write_test_results(build_number, version, test_results):
    conn = mysql.connector.connect(
        host='localhost',
        user='test_user',
        password='password',
        database='test_db'
    )
    cursor = conn.cursor()
    
    for result in test_results:
        query = """
        INSERT INTO test_execution_history 
        (test_suite, test_case, status, duration_ms, executor, build_number, version, created_at)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
        """
        cursor.execute(query, (
            result['suite'],
            result['case'],
            result['status'],
            result['duration_ms'],
            result['executor'],
            build_number,
            version,
            datetime.now()
        ))
    
    conn.commit()
    cursor.close()
    conn.close()

-- 最近5个版本的通过率对比
SELECT 
    version,
    build_number,
    COUNT(CASE WHEN status = 'PASS' THEN 1 END) * 100.0 / COUNT(*) as pass_rate,
    AVG(duration_ms) as avg_duration
FROM test_execution_history
WHERE build_number > (SELECT MAX(build_number) - 5 FROM test_execution_history)
GROUP BY version, build_number
ORDER BY build_number DESC;

-- 检测连续失败模式
SELECT 
    test_suite,
    test_case,
    COUNT(*) as fail_count,
    GROUP_CONCAT(DISTINCT build_number ORDER BY build_number) as failed_builds
FROM test_execution_history
WHERE status = 'FAIL'
  AND created_at > DATE_SUB(NOW(), INTERVAL 24 HOUR)
GROUP BY test_suite, test_case
HAVING fail_count > 3
ORDER BY fail_count DESC;

-- 告警查询：通过率低于阈值
SELECT 
    COUNT(CASE WHEN status = 'PASS' THEN 1 END) * 100.0 / COUNT(*) as pass_rate,
    COUNT(CASE WHEN status = 'FAIL' THEN 1 END) as fail_count
FROM test_execution_history
WHERE build_number = (SELECT MAX(build_number) FROM test_execution_history)
HAVING pass_rate < 95;

# Jenkins Pipeline示例：构建后更新Superset数据
pipeline {
    agent any
    stages {
        stage('Run Tests') {
            steps {
                sh 'pytest tests/ --junitxml=test-results.xml'
            }
        }
        stage('Update Dashboard') {
            steps {
                script {
                    // 解析测试结果并写入数据库
                    sh '''
                    python3 scripts/parse_test_results.py test-results.xml
                    '''
                    // 触发Superset数据刷新
                    sh '''
                    curl -X POST http://superset:8088/api/v1/dataset/refresh/1
                    '''
                }
            }
        }
    }
}

-- 为每个图表添加数据血统注释
-- 来源：test_execution_history
-- 更新频率：每次构建
-- 负责人：张三
-- 用途：监控主干通过率
SELECT 
    DATE(created_at) as date,
    COUNT(CASE WHEN status = 'PASS' THEN 1 END) * 100.0 / COUNT(*) as pass_rate
FROM test_execution_history
WHERE build_number IN (
    SELECT DISTINCT build_number 
    FROM test_execution_history 
    WHERE created_at > DATE_SUB(NOW(), INTERVAL 30 DAY)
)
GROUP BY DATE(created_at)
ORDER BY date;