- 统计出,最小等待时间、最大等待时间、平均等待时间、90%账单等待时间;
- 最小等待时间、最大等待时间 不排除等待时间<=0的账单;
- 平均等待时间、90%账单等待时间 要剔除掉等待时间<=0的账单
SELECT
MIN(useTimeMinute) AS minUseTimeMinute, # 最小耗时
MAX(useTimeMinute) AS maxUseTimeMinute, # 最大耗时
ROUND(AVG(IF(useTimeMinute > 0, useTimeMinute, NULL))) AS avgUseTimeMinute,# 平均耗时
@normalBillNum AS nomralBillCount, # 正常单总数
MAX(IF(normal_bill_num = CEIL(@normalBillNum * 0.9), useTimeMinute, NULL)) AS universalUseTime # 普遍耗时(取第90%个正常单的耗时)
FROM (
SELECT
bill.bill_id,
bill.suggest_prepare_time, # 建议完成时间
bill.real_prepare_time, # 实际完成时间
CEIL(TIMESTAMPDIFF(SECOND, bill.suggest_prepare_time, bill.real_prepare_time) / 60) AS useTimeMinute, # 秒转分钟,舍尾取整
IF(TIMESTAMPDIFF(SECOND, bill.suggest_prepare_time, bill.real_prepare_time) <= 0, -1, @normalBillNum := @normalBillNum + 1) AS normal_bill_num # 非正常单序号特殊标记为-1,正常单号正数序号排序
FROM
c_bill bill,
(SELECT (@normalBillNum := 0)) row_number_tmp
ORDER BY useTimeMinute ASC
) a