// index.js - 主逻辑入口
const fs = require("fs");
const moment = require("moment");
// 加载配置
const config = JSON.parse(fs.readFileSync("config.json", "utf8"));
// 引入工具函数
const { getStream, writeFile, generateTotalStats, generateDailyErrorCodeStats, filterPlayIdWithSuccessPriority, generateErrorCodeDeviceCoverageCSV, generateDailyErrorCodeCoverageMatrix } = require("./func");
// 输出目录
if (!fs.existsSync("general_info")) {
fs.mkdirSync("general_info", { recursive: true });
}
if (!fs.existsSync("device_stats_by_category")) {
fs.mkdirSync("device_stats_by_category", { recursive: true });
}
// 辅助函数:添加 pullStreamDuration 字段
const pullStreamDuration = (item) => {
return item.pullStreamDuration !== undefined ? item : { ...item, pullStreamDuration: 0 };
};
/**
* 主任务执行器
*/
async function startTask({ path: dirPath, outputName, itemHandler }) {
let result = [];
try {
const files = fs.readdirSync(dirPath);
const validFiles = files.filter((name) => name !== outputName && /\.csv$/i.test(name) && name.includes("sqllab"));
for (const name of validFiles) {
const dayItems = await getStream({ path: dirPath, name, itemHandler }, config);
result.push(...dayItems);
}
if (result.length === 0) return;
result.sort((a, b) => moment(a.etime) - moment(b.etime));
// 输出整体指标
writeFile(result, outputName, "general_info");
// 输出总请求成功率与错误码占比
generateTotalStats(result, "err_summary.csv", "general_info");
// 输出用户感知成功率与错误码占比
generateTotalStats(filterPlayIdWithSuccessPriority(result), "err_summary_user_impact.csv", "general_info");
// 输出所有错误码在不同设备中的占比
generateErrorCodeDeviceCoverageCSV(result, "err_device_cov.csv", "device_stats_by_category");
// 输出所有错误码在不同设备中的占比(日、周、月)
generateDailyErrorCodeCoverageMatrix(result, "err_device_cov_daily_weekly_monthly.csv", "device_stats_by_category");
// 提取 除 '0' 外 出现次数最多的 Top 10 错误码
const countMap = {};
// 1. 统计所有 errorCode 频次(跳过 errorCode === '0')
for (const item of result) {
const code = item.errorCode;
if (code === "0") continue; // 排除 errorCode 为 '0' 的项
countMap[code] = (countMap[code] || 0) + 1;
}
// 2. 按频次降序排序,取前 10 个错误码
const top10ErrorCodes = Object.entries(countMap)
.sort(([, a], [, b]) => b - a) // 按频次降序
.slice(0, 10)
.map(([code]) => code); // 只取错误码
top10ErrorCodes.push("0");
// 3. 过滤原始数据:只保留属于这 Top 10 错误码的数据和0的数据
const filteredResult = result.filter((item) => top10ErrorCodes.includes(item.errorCode));
// Top10错误按(日、周、月)统计
generateDailyErrorCodeStats(filteredResult, top10ErrorCodes, "err_daily_weekly_monthly.csv", "general_info");
} catch (err) {
console.error("Task failed:", err);
}
}
/**
* 启动任务
*/
function getAllTask() {
startTask({
path: "./",
outputName: "general_info.csv",
itemHandler: pullStreamDuration,
});
}
getAllTask();
// func.js - 工具函数模块
const fs = require("fs");
const csv = require("csv-parser");
const fastcsv = require("fast-csv");
const moment = require("moment");
const path = require("path");
/**
* 数据处理:解析 ep 字段,提取 playId/deviceId/errorCode/etime
*/
function dataHandler(row) {
let epObj = {};
try {
if (row.ep && typeof row.ep === "string") {
epObj = JSON.parse(row.ep.trim());
}
} catch (err) {}
const ct = Number(row.ct);
const etime = !isNaN(ct) && row.ct ? moment(ct).format("YYYY-MM-DD HH:mm:ss") : "";
return {
playId: epObj.playId ?? "",
deviceId: epObj.deviceId ?? "",
eid: row.eid ?? "",
errorCode: epObj.errorCode ? `${epObj.errorCode}` : "0",
etime,
};
}
/**
* 流式读取 CSV 文件并返回 Promise<Array>
*/
function getStream(file, config) {
return new Promise((resolve, reject) => {
const { path: dirPath, name, itemHandler } = file;
const result = [];
const url = path.join(dirPath, name);
// 安全提取配置
const startTime = config.start_time ? moment(config.start_time, "YYYY-MM-DD HH:mm:ss") : null;
const endTime = config.end_time ? moment(config.end_time, "YYYY-MM-DD HH:mm:ss") : null;
const excludeErrorCodes = Array.isArray(config.exclude_error_codes) ? config.exclude_error_codes : [];
const targetEidType = config.target_eid ? config.target_eid : "Playback.Video.StartPlay";
fs.createReadStream(url)
.pipe(csv())
.on("data", (row) => {
let item = dataHandler(row);
if (itemHandler) item = itemHandler(item);
if (!item.etime) return;
const itemTime = moment(item.etime, "YYYY-MM-DD HH:mm:ss");
if (!itemTime.isValid()) return;
if (startTime && itemTime.isBefore(startTime)) return;
if (endTime && itemTime.isAfter(endTime)) return;
if (excludeErrorCodes.includes(item.errorCode)) return;
if (item.eid !== targetEidType) return;
result.push(item);
})
.on("end", () => {
resolve(result);
})
.on("error", reject);
});
}
/**
* 写入 CSV 文件
*/
function writeFile(data, filename, outputDir) {
const filepath = path.join(outputDir, filename);
const ws = fs.createWriteStream(filepath);
fastcsv.write(data, { headers: true }).pipe(ws);
ws.on("finish", () => {});
ws.on("error", (err) => {
console.error(`Failed to write ${filename}:`, err);
});
}
/**
* 生成总错误码统计(带百分比)
*/
function generateTotalStats(result, filename, outputDir) {
const countMap = {};
let totalCount = 0;
for (const item of result) {
const code = item.errorCode;
countMap[code] = (countMap[code] || 0) + 1;
totalCount++;
}
const totalStats = Object.entries(countMap)
.map(([errorCode, count]) => ({
errorCode,
percentage: ((count / totalCount) * 100).toFixed(2) + "%",
count,
}))
.sort((a, b) => b.count - a.count);
writeFile(totalStats, filename, outputDir);
}
/**
* 生成每日错误码分布趋势
*/
function generateDailyErrorCodeStats(result, topErrorCodes = [], filename, outputDir) {
function getDate(etime) {
return etime.split(" ")[0];
}
const dailyStats = {};
// Step 1: 收集每日各 errorCode 出现次数
for (const item of result) {
const date = getDate(item.etime);
const errorCode = item.errorCode;
if (!date || !topErrorCodes.includes(errorCode)) continue;
if (!dailyStats[date]) {
dailyStats[date] = {};
}
dailyStats[date][errorCode] = (dailyStats[date][errorCode] || 0) + 1;
}
// Step 2: 获取所有日期并排序
const sortedDates = Object.keys(dailyStats).sort();
const tableData = [];
const WEEK_SIZE = 7;
const MONTH_SIZE = 30;
for (let i = 0; i < sortedDates.length; i++) {
const date = sortedDates[i];
const counts = dailyStats[date] || {};
const dailyTotal = Object.values(counts).reduce((a, b) => a + b, 0);
const row = { date };
// === 日维度统计 ===
topErrorCodes.forEach((code) => {
const count = counts[code] || 0;
if (count > 0) {
const percentage = ((count / dailyTotal) * 100).toFixed(2);
row[`${code}_count`] = count;
row[`${code}_percentage`] = `${percentage}%`;
} else {
row[`${code}_count`] = "";
row[`${code}_percentage`] = "";
}
});
row.total = dailyTotal; // 就放在日维度结尾
// === 周维度:是否为本周第一天?===
const isInFirstDayOfWeek = i % WEEK_SIZE === 0;
if (isInFirstDayOfWeek) {
const weekGroup = sortedDates.slice(i, i + WEEK_SIZE);
const weeklyCount = {};
let weeklyTotal = 0;
for (const d of weekGroup) {
const dayData = dailyStats[d] || {};
for (const code of topErrorCodes) {
const c = dayData[code] || 0;
weeklyCount[code] = (weeklyCount[code] || 0) + c;
weeklyTotal += c;
}
}
topErrorCodes.forEach((code) => {
const count = weeklyCount[code] || 0;
const percentage = weeklyTotal > 0 ? ((count / weeklyTotal) * 100).toFixed(2) : 0;
row[`${code}_weekly_count`] = count;
row[`${code}_weekly_percentage`] = `${percentage}%`;
});
row.weekly_total = weeklyTotal;
} else {
topErrorCodes.forEach((code) => {
row[`${code}_weekly_count`] = "";
row[`${code}_weekly_percentage`] = "";
});
row.weekly_total = "";
}
// === 月维度:是否为本月第一天?===
const isInFirstDayOfMonth = i % MONTH_SIZE === 0;
if (isInFirstDayOfMonth) {
const monthGroup = sortedDates.slice(i, i + MONTH_SIZE);
const monthlyCount = {};
let monthlyTotal = 0;
for (const d of monthGroup) {
const dayData = dailyStats[d] || {};
for (const code of topErrorCodes) {
const c = dayData[code] || 0;
monthlyCount[code] = (monthlyCount[code] || 0) + c;
monthlyTotal += c;
}
}
topErrorCodes.forEach((code) => {
const count = monthlyCount[code] || 0;
const percentage = monthlyTotal > 0 ? ((count / monthlyTotal) * 100).toFixed(2) : 0;
row[`${code}_monthly_count`] = count;
row[`${code}_monthly_percentage`] = `${percentage}%`;
});
row.monthly_total = monthlyTotal;
} else {
topErrorCodes.forEach((code) => {
row[`${code}_monthly_count`] = "";
row[`${code}_monthly_percentage`] = "";
});
row.monthly_total = "";
}
tableData.push(row);
}
// === 构建带分隔列的 headers(不包含 final_total)===
const headers = [
"date",
// --- 日维度 ---
...topErrorCodes.flatMap((code) => [`${code}_count`, `${code}_percentage`]),
"total",
"", // 🔹 空列:日与周之间的分隔
// --- 周维度 ---
...topErrorCodes.flatMap((code) => [`${code}_weekly_count`, `${code}_weekly_percentage`]),
"weekly_total",
"", // 🔹 空列:周与月之间的分隔
// --- 月维度 ---
...topErrorCodes.flatMap((code) => [`${code}_monthly_count`, `${code}_monthly_percentage`]),
"monthly_total",
// ✅ 不再添加 final_total
];
// 手动写入 CSV,支持空列和精确顺序
const ws = fs.createWriteStream(path.join(outputDir, filename));
// 先写 header
ws.write(headers.join(",") + "\n");
// 再逐行写数据
tableData.forEach((row) => {
const line = [
row.date,
// 日维度
...topErrorCodes.flatMap((code) => [row[`${code}_count`] || "", row[`${code}_percentage`] || ""]),
row.total,
"", // 空列分隔
// 周维度
...topErrorCodes.flatMap((code) => [row[`${code}_weekly_count`] || "", row[`${code}_weekly_percentage`] || ""]),
row.weekly_total,
"", // 空列分隔
// 月维度
...topErrorCodes.flatMap((code) => [row[`${code}_monthly_count`] || "", row[`${code}_monthly_percentage`] || ""]),
row.monthly_total,
]
.map((field) => `"${field}"`)
.join(",");
ws.write(line + "\n");
});
ws.on("finish", () => {
console.log("Successfully wrote dimension_errorcode_distribution.csv without final_total");
});
ws.on("error", (err) => {
console.error("Failed to write file:", err);
});
}
function generateErrorCodeDeviceCoverageCSV(result, filename, outputDir) {
const errorToDeviceSet = new Map();
const allDevicesSet = new Set();
// 1. 遍历数据,收集:
// a) 每个 errorCode 对应的唯一 deviceId
// b) 全局所有 deviceId(用于计算分母)
for (const item of result) {
const { errorCode, deviceId } = item;
if (!errorCode || !deviceId) continue;
// 收集全局设备
allDevicesSet.add(deviceId);
// 收集 error -> device 映射
if (!errorToDeviceSet.has(errorCode)) {
errorToDeviceSet.set(errorCode, new Set());
}
errorToDeviceSet.get(errorCode).add(deviceId);
}
const totalUniqueDevices = allDevicesSet.size;
if (totalUniqueDevices === 0) {
console.warn("No valid devices found in data.");
return;
}
// 2. 构建结果:覆盖率 = deviceCount / totalUniqueDevices
const coverageStats = [];
for (const [errorCode, deviceSet] of errorToDeviceSet) {
const deviceCount = deviceSet.size;
const coverageRate = deviceCount / totalUniqueDevices;
const percentageStr = (coverageRate * 100).toFixed(2) + "%"; // 如 "45.23%"
coverageStats.push({
errorCode,
deviceCount, // 可选:保留原始计数
totalDevices: totalUniqueDevices,
coverageRate: percentageStr, // 输出为百分比字符串
// coverageRateDecimal: coverageRate // 如果需要小数形式(0.4523),可额外加字段
});
}
// 排序:按覆盖设备数降序
coverageStats.sort((a, b) => b.deviceCount - a.deviceCount);
// 3. 写入 CSV 文件
const ws = fs.createWriteStream(path.join(outputDir, filename));
fastcsv.write(coverageStats, { headers: true }).pipe(ws);
}
function generateDailyErrorCodeCoverageMatrix(result, filename, outputDir) {
const dailyDataMap = new Map(); // date -> { allDevices: Set, errorToDeviceSet: Map<code, Set<deviceId>> }
// 1. 遍历数据,按日期分组并收集设备和错误覆盖情况
for (const item of result) {
const { errorCode, deviceId, etime } = item;
if (!errorCode || !deviceId || !etime) continue;
const date = etime.split(" ")[0]; // 提取 YYYY-MM-DD
if (!moment(date, "YYYY-MM-DD", true).isValid()) continue;
if (!dailyDataMap.has(date)) {
dailyDataMap.set(date, {
allDevices: new Set(),
errorToDeviceSet: new Map(),
});
}
const dayRecord = dailyDataMap.get(date);
dayRecord.allDevices.add(deviceId);
if (!dayRecord.errorToDeviceSet.has(errorCode)) {
dayRecord.errorToDeviceSet.set(errorCode, new Set());
}
dayRecord.errorToDeviceSet.get(errorCode).add(deviceId);
}
// 2. 收集所有唯一 errorCodes(用于表头),去掉空码和"0"
const allErrorCodes = [...dailyDataMap.values()].flatMap((day) => Array.from(day.errorToDeviceSet.keys())).filter((code) => code && code !== "0");
const uniqueErrorCodes = [...new Set(allErrorCodes)].sort();
// 3. 构建基础表格数据,并按日期升序排列
const tableData = [];
const sortedDates = [...dailyDataMap.keys()].sort((a, b) => moment(a) - moment(b));
for (const date of sortedDates) {
const dayRecord = dailyDataMap.get(date);
const totalDevices = dayRecord.allDevices.size;
const rowData = { date, totalDevices };
// --- 单日错误码覆盖 ---
for (const errorCode of uniqueErrorCodes) {
const coveredDevices = dayRecord.errorToDeviceSet.get(errorCode)?.size || 0;
rowData[errorCode] = coveredDevices > 0 ? String(coveredDevices) : "";
}
// --- 初始化 7天 聚合字段 ---
rowData["totalDevices_7d"] = "";
for (const errorCode of uniqueErrorCodes) {
rowData[`${errorCode}_7d`] = "";
}
// --- 初始化 30天 聚合字段 ---
rowData["totalDevices_30d"] = "";
for (const errorCode of uniqueErrorCodes) {
rowData[`${errorCode}_30d`] = "";
}
tableData.push(rowData);
}
// 4. 每7天聚合一次(从第0天开始,连续7天为一组)
const groupSize7 = 7;
for (let i = 0; i < tableData.length; i += groupSize7) {
const weekGroup = tableData.slice(i, i + groupSize7);
const startDate = weekGroup[0].date;
const deviceUnion = new Set(); // 统计7天内的总设备数
const cumulativeCoverage = new Map(); // errorCode -> Set<deviceId>
for (const row of weekGroup) {
const date = row.date;
const dayRecord = dailyDataMap.get(date);
if (!dayRecord) continue;
// 合并设备
for (const deviceId of dayRecord.allDevices) {
deviceUnion.add(deviceId);
}
// 合并错误码覆盖
for (const [errorCode, deviceSet] of dayRecord.errorToDeviceSet) {
if (!cumulativeCoverage.has(errorCode)) {
cumulativeCoverage.set(errorCode, new Set());
}
for (const dev of deviceSet) {
cumulativeCoverage.get(errorCode).add(dev);
}
}
}
const firstDayRow = tableData.find((r) => r.date === startDate);
if (firstDayRow) {
firstDayRow["totalDevices_7d"] = String(deviceUnion.size);
for (const errorCode of uniqueErrorCodes) {
const count = cumulativeCoverage.get(errorCode)?.size || 0;
firstDayRow[`${errorCode}_7d`] = count > 0 ? String(count) : "";
}
}
}
// 5. 每30天聚合一次
const groupSize30 = 30;
for (let i = 0; i < tableData.length; i += groupSize30) {
const monthGroup = tableData.slice(i, i + groupSize30);
const startDate = monthGroup[0].date;
const deviceUnion = new Set(); // 30天内所有设备(去重)
const cumulativeCoverage = new Map(); // errorCode -> Set<deviceId>
for (const row of monthGroup) {
const date = row.date;
const dayRecord = dailyDataMap.get(date);
if (!dayRecord) continue;
// 合并设备
for (const deviceId of dayRecord.allDevices) {
deviceUnion.add(deviceId);
}
// 合并错误码
for (const [errorCode, deviceSet] of dayRecord.errorToDeviceSet) {
if (!cumulativeCoverage.has(errorCode)) {
cumulativeCoverage.set(errorCode, new Set());
}
for (const dev of deviceSet) {
cumulativeCoverage.get(errorCode).add(dev);
}
}
}
const firstDayRow = tableData.find((r) => r.date === startDate);
if (firstDayRow) {
firstDayRow["totalDevices_30d"] = String(deviceUnion.size);
for (const errorCode of uniqueErrorCodes) {
const count = cumulativeCoverage.get(errorCode)?.size || 0;
firstDayRow[`${errorCode}_30d`] = count > 0 ? String(count) : "";
}
}
}
// 6. 构造最终表头:单日 | 空列 | 7天聚合 | 空列 | 30天聚合
const headers = [
"date",
...uniqueErrorCodes,
"totalDevices",
"-", // 分隔列
...uniqueErrorCodes.map((code) => `${code}_7d`),
"totalDevices_7d",
"-", // 分隔列
...uniqueErrorCodes.map((code) => `${code}_30d`),
"totalDevices_30d",
];
// 7. 写出 CSV 文件
const ws = fs.createWriteStream(path.join(outputDir, filename));
// 在输出时处理分隔列:填充 "-"
const dataWithSeparators = tableData.map((row) => {
const newRow = { ...row };
newRow["-"] = ""; // 所有分隔列为空值
return newRow;
});
fastcsv.write(dataWithSeparators, { headers }).pipe(ws);
}
/**
* 对 playId 去重:优先保留 errorCode=0;否则保留最后一条
* 空 playId 被视为各自独立,全部保留
*/
function filterPlayIdWithSuccessPriority(result) {
const map = new Map(); // 存储按 playId 分组的结果(仅非空 playId)
const emptyPlayIdItems = []; // 保存 playId 为空的条目,全部保留
for (const item of result) {
const { playId, errorCode, etime } = item;
if (!playId) {
// ✅ 空 playId:直接加入独立数组,不参与任何去重
emptyPlayIdItems.push(item);
continue;
}
const existing = map.get(playId);
const isCurrentSuccess = String(errorCode).trim() === "0";
const isExistingSuccess = existing && String(existing.errorCode).trim() === "0";
if (!existing) {
map.set(playId, item);
} else if (isCurrentSuccess) {
// 当前是成功的,优先保留
map.set(playId, item);
} else if (!isExistingSuccess) {
// 已存在的也不是成功的,则按时间保留更晚的
if (etime && existing.etime && new Date(etime) > new Date(existing.etime)) {
map.set(playId, item);
}
}
// 如果 existing 是 success 而当前不是,则跳过(不更新)
}
// 合并结果:非空 playId 的去重结果 + 所有空 playId 条目
const filteredByPlayId = Array.from(map.values());
return [...filteredByPlayId, ...emptyPlayIdItems].sort((a, b) => new Date(a.etime) - new Date(b.etime));
}
// 导出所有函数
module.exports = {
getStream,
writeFile,
generateTotalStats,
generateDailyErrorCodeStats,
filterPlayIdWithSuccessPriority,
generateErrorCodeDeviceCoverageCSV,
generateDailyErrorCodeCoverageMatrix,
};
能否实现不导出多个csv,而是一个csv中的多个sheet
最新发布