使用PHP開發小程式的資料分析與統計技巧

// 获取小程序API接口返回的数据
$data = wxApiCall('get_user_behavior', $params);

// 连接数据库
$mysqli = new mysqli('localhost', 'username', 'password', 'database');

// 插入数据到数据库中
$sql = "INSERT INTO user_behavior (user_id, action, time) VALUES (?, ?, ?)";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('sss', $data['user_id'], $data['action'], $data['time']);
$stmt->execute();
$stmt->close();
$mysqli->close();

// 去除重复数据
$mysqli = new mysqli('localhost', 'username', 'password', 'database');
$sql = "DELETE FROM user_behavior WHERE id NOT IN (SELECT id FROM (SELECT MIN(id) FROM user_behavior GROUP BY user_id, action) AS t)";
$mysqli->query($sql);
$mysqli->close();

// 处理缺失值和异常值
$mysqli = new mysqli('localhost', 'username', 'password', 'database');
$sql = "UPDATE user_behavior SET action = COALESCE(action, 'Unknown'), time = COALESCE(time, NOW()) WHERE action IS NULL OR time IS NULL";
$mysqli->query($sql);
$mysqli->close();

// 求和
$mysqli = new mysqli('localhost', 'username', 'password', 'database');
$sql = "SELECT SUM(time) AS total_time FROM user_behavior";
$result = $mysqli->query($sql);
$row = $result->fetch_assoc();
$total_time = $row['total_time'];
$mysqli->close();

// 平均值
$mysqli = new mysqli('localhost', 'username', 'password', 'database');
$sql = "SELECT AVG(time) AS average_time FROM user_behavior";
$result = $mysqli->query($sql);
$row = $result->fetch_assoc();
$average_time = $row['average_time'];
$mysqli->close();

// 标准差
$mysqli = new mysqli('localhost', 'username', 'password', 'database');
$sql = "SELECT STDDEV(time) AS std_dev FROM user_behavior";
$result = $mysqli->query($sql);
$row = $result->fetch_assoc();
$std_dev = $row['std_dev'];
$mysqli->close();

// 方差
$mysqli = new mysqli('localhost', 'username', 'password', 'database');
$sql = "SELECT VARIANCE(time) AS variance FROM user_behavior";
$result = $mysqli->query($sql);
$row = $result->fetch_assoc();
$variance = $row['variance'];
$mysqli->close();