统计-模型错误占比

按日期分类-查询模型正确率

SELECT
  stat_date AS 日期,
  total_all AS 总请求数,

  `成功-1次请求` AS 一次成功数量,
  CONCAT(ROUND(`成功-1次请求` / total_all * 100,2),'%') AS 一次成功占比,

  `成功-2次请求` AS 二次成功数量,
  CONCAT(ROUND(`成功-2次请求` / total_all * 100,2),'%') AS 二次成功占比,

  `成功-3次请求` AS 三次成功数量,
  CONCAT(ROUND(`成功-3次请求` / total_all * 100,2),'%') AS 三次成功占比,

  `失败-1次请求` AS 一次失败数量,
  CONCAT(ROUND(`失败-1次请求` / total_all * 100,2),'%') AS 一次失败占比,

  `失败-2次请求` AS 二次失败数量,
  CONCAT(ROUND(`失败-2次请求` / total_all * 100,2),'%') AS 二次失败占比,

  `失败-3次请求` AS 三次失败数量,
  CONCAT(ROUND(`失败-3次请求` / total_all * 100,2),'%') AS 三次失败占比,

  other AS 其他类型数量,
  CONCAT(ROUND(other / total_all * 100,2),'%') AS 其他类型占比,

  success_rate AS 整体成功率,
  fail_rate AS 整体失败率

FROM (
  SELECT

    stat_date,

    COUNT(DISTINCT request_id) AS total_all,

    SUM(IF(title = '成功-1次请求',1,0)) AS `成功-1次请求`,
    SUM(IF(title = '成功-2次请求',1,0)) AS `成功-2次请求`,
    SUM(IF(title = '成功-3次请求',1,0)) AS `成功-3次请求`,

    SUM(IF(title = '失败-1次请求',1,0)) AS `失败-1次请求`,
    SUM(IF(title = '失败-2次请求',1,0)) AS `失败-2次请求`,
    SUM(IF(title = '失败-3次请求',1,0)) AS `失败-3次请求`,

    SUM(IF(title = '其他类型',1,0)) AS other,

    CONCAT(
      ROUND(
        SUM(IF(title LIKE '成功%',1,0))
        /
        COUNT(DISTINCT request_id)
        *100
      ,2),
      '%'
    ) AS success_rate,

    CONCAT(
      ROUND(
        SUM(IF(title LIKE '失败%',1,0))
        /
        COUNT(DISTINCT request_id)
        *100
      ,2),
      '%'
    ) AS fail_rate


  FROM (

    SELECT
      DATE(FROM_UNIXTIME(created_at)) AS stat_date,

      request_id,

      SUM(type) AS type_times,

      CASE SUM(type)
        WHEN 2 THEN '成功-1次请求'
        WHEN 7 THEN '成功-2次请求'
        WHEN 12 THEN '成功-3次请求'

        WHEN 5 THEN '失败-1次请求'
        WHEN 10 THEN '失败-2次请求'
        WHEN 15 THEN '失败-3次请求'

        ELSE '其他类型'
      END AS title


    FROM logs

    WHERE
      user_id = '888889422'
      AND type IN (2,5)
      AND model_name = 'claude-fable-5'

    GROUP BY
      DATE(FROM_UNIXTIME(created_at)),
      request_id

  ) t1

  GROUP BY stat_date

) t2

ORDER BY stat_date DESC;

按小时分类

SELECT
  stat_hour AS 小时,
  total_all AS 总请求数,

  `成功-1次请求` AS 一次成功数量,
  CONCAT(ROUND(`成功-1次请求` / total_all * 100,2),'%') AS 一次成功占比,

  `成功-2次请求` AS 二次成功数量,
  CONCAT(ROUND(`成功-2次请求` / total_all * 100,2),'%') AS 二次成功占比,

  `成功-3次请求` AS 三次成功数量,
  CONCAT(ROUND(`成功-3次请求` / total_all * 100,2),'%') AS 三次成功占比,

  `失败-1次请求` AS 一次失败数量,
  CONCAT(ROUND(`失败-1次请求` / total_all * 100,2),'%') AS 一次失败占比,

  `失败-2次请求` AS 二次失败数量,
  CONCAT(ROUND(`失败-2次请求` / total_all * 100,2),'%') AS 二次失败占比,

  `失败-3次请求` AS 三次失败数量,
  CONCAT(ROUND(`失败-3次请求` / total_all * 100,2),'%') AS 三次失败占比,

  other AS 其他类型数量,
  CONCAT(ROUND(other / total_all * 100,2),'%') AS 其他类型占比,

  success_rate AS 整体成功率,
  fail_rate AS 整体失败率


FROM (

  SELECT

    stat_hour,

    COUNT(DISTINCT request_id) AS total_all,


    SUM(IF(title='成功-1次请求',1,0)) AS `成功-1次请求`,
    SUM(IF(title='成功-2次请求',1,0)) AS `成功-2次请求`,
    SUM(IF(title='成功-3次请求',1,0)) AS `成功-3次请求`,


    SUM(IF(title='失败-1次请求',1,0)) AS `失败-1次请求`,
    SUM(IF(title='失败-2次请求',1,0)) AS `失败-2次请求`,
    SUM(IF(title='失败-3次请求',1,0)) AS `失败-3次请求`,


    SUM(IF(title='其他类型',1,0)) AS other,


    CONCAT(
      ROUND(
        SUM(IF(title LIKE '成功%',1,0))
        /
        COUNT(DISTINCT request_id)
        *100,
      2),
      '%'
    ) AS success_rate,


    CONCAT(
      ROUND(
        SUM(IF(title LIKE '失败%',1,0))
        /
        COUNT(DISTINCT request_id)
        *100,
      2),
      '%'
    ) AS fail_rate



  FROM (

    SELECT

      DATE_FORMAT(
        FROM_UNIXTIME(created_at),
        '%Y-%m-%d %H:00:00'
      ) AS stat_hour,


      request_id,


      SUM(type) AS type_times,


      CASE SUM(type)

        WHEN 2 THEN '成功-1次请求'
        WHEN 7 THEN '成功-2次请求'
        WHEN 12 THEN '成功-3次请求'

        WHEN 5 THEN '失败-1次请求'
        WHEN 10 THEN '失败-2次请求'
        WHEN 15 THEN '失败-3次请求'

        ELSE '其他类型'

      END AS title


    FROM logs


    WHERE
      user_id='888889422'
      AND type IN (2,5)
      AND model_name='claude-fable-5'


    GROUP BY

      DATE_FORMAT(
        FROM_UNIXTIME(created_at),
        '%Y-%m-%d %H:00:00'
      ),

      request_id


  ) t1


  GROUP BY stat_hour


) t2


ORDER BY stat_hour DESC;

提供商超过100秒的

SELECT
     '提供商K' as '提供商',
    -- 总条数
    COUNT(*) AS '总笔数',
    -- use_time>100 的条数
    SUM(IF(use_time > 100, 1, 0)) AS '使用超过100秒笔数',
    -- 计算占比，保留2位小数
    CONCAT(ROUND(SUM(IF(use_time > 100, 1, 0)) / COUNT(*) * 100, 2), '%') AS '占比'
FROM `logs`
WHERE
    channel_id = '69'
    AND created_at >= UNIX_TIMESTAMP(CURDATE()) ;

多提供商分组查

SELECT
    channel_id AS 渠道ID,
    CASE channel_id
        WHEN 69 THEN '提供商K'
        WHEN 64 THEN '提供商I' -- 自行替换64对应的名称
    END AS 提供商,
    COUNT(*) AS 总笔数,
    SUM(IF(use_time > 100, 1, 0)) AS 使用超过100秒笔数,
    CONCAT(ROUND(SUM(IF(use_time > 100, 1, 0)) / COUNT(*) * 100, 2), '%') AS 占比
FROM `logs`
WHERE
    channel_id IN (69,64)
    -- created_at是毫秒bigint，必须 *1000
    AND created_at >= UNIX_TIMESTAMP(CURDATE()) 
GROUP BY channel_id;


SELECT
    channel_id AS 渠道ID,
        c.`name` AS 渠道名称,
    COUNT(*) AS 总笔数,
    SUM(IF(use_time > 50, 1, 0)) AS 使用超过50秒笔数,
    CONCAT(ROUND(SUM(IF(use_time > 50, 1, 0)) / COUNT(*) * 100, 2), '%') AS 超50秒占比,
    SUM(IF(use_time > 100, 1, 0)) AS 使用超过100秒笔数,
    CONCAT(ROUND(SUM(IF(use_time > 100, 1, 0)) / COUNT(*) * 100, 2), '%') AS 超100秒占比
FROM `logs` l
LEFT JOIN channels c
ON l.channel_id = c.id
WHERE
    l.channel_id IN (69,64)
    AND created_at >= UNIX_TIMESTAMP(CURDATE()) 
GROUP BY channel_id;


-- 各渠道明细数据
SELECT
    l.channel_id AS 渠道ID,
    c.`name` AS 渠道名称,
    COUNT(*) AS 总笔数,
    SUM(IF(use_time > 50, 1, 0)) AS 使用超过50秒笔数,
    CONCAT(ROUND(SUM(IF(use_time > 50, 1, 0)) / COUNT(*) * 100, 2), '%') AS 超50秒占比,
    SUM(IF(use_time > 100, 1, 0)) AS 使用超过100秒笔数,
    CONCAT(ROUND(SUM(IF(use_time > 100, 1, 0)) / COUNT(*) * 100, 2), '%') AS 超100秒占比
FROM `logs` l
LEFT JOIN channels c ON l.channel_id = c.id
WHERE created_at >= UNIX_TIMESTAMP(CURDATE()) 
GROUP BY l.channel_id, c.`name`

UNION ALL

-- 全局汇总行
SELECT
    NULL AS 渠道ID,
    '汇总合计' AS 渠道名称,
    COUNT(*) AS 总笔数,
    SUM(IF(use_time > 50, 1, 0)) AS 使用超过50秒笔数,
    CONCAT(ROUND(SUM(IF(use_time > 50, 1, 0)) / COUNT(*) * 100, 2), '%') AS 超50秒占比,
    SUM(IF(use_time > 100, 1, 0)) AS 使用超过100秒笔数,
    CONCAT(ROUND(SUM(IF(use_time > 100, 1, 0)) / COUNT(*) * 100, 2), '%') AS 超100秒占比
FROM `logs` l
WHERE created_at >= UNIX_TIMESTAMP(CURDATE()) ;