用 Laravel 定时任务对接 ClickHouse 日志，每小时/每天推送应用流量报告到飞书群。
核心架构
ClickHouse (request_logs) → Laravel Artisan Command → 飞书 Webhook → 交互式卡片定时任务两种模式：
push:feishu-stats— 每小时统计近 1h / 3h 流量push:feishu-stats daily— 每天 0 点统计近 24h / 3d / 7d
Artisan Command
<?php
namespace App\Console\Commands;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\Http;
use Carbon\Carbon;
use App\Services\ClickHouseService;
class PushFeishuStats extends Command
{
protected $signature = 'push:feishu-stats {type=hourly}';
protected $description = '推送应用访问统计数据到飞书群';
protected ClickHouseService $clickHouseService;
public function __construct(ClickHouseService $clickHouseService)
{
parent::__construct();
$this->clickHouseService = $clickHouseService;
}
public function handle()
{
$type = $this->argument('type');
try {
$type === 'daily'
? $this->handleDailyPush()
: $this->handleHourlyPush();
return Command::SUCCESS;
} catch (\Throwable $e) {
$this->error($e->getMessage());
return Command::FAILURE;
}
}
protected function handleHourlyPush()
{
$now = Carbon::now();
$oneHourAgo = $now->copy()->subHour()->toDateTimeString();
$threeHoursAgo = $now->copy()->subHours(3)->toDateTimeString();
$sql = "
SELECT
JSONExtractString(request_params, 'appid') AS appid,
COUNTIf(timestamp >= '{$oneHourAgo}') AS count_1h,
COUNT(1) AS count_3h
FROM request_logs
WHERE timestamp >= '{$threeHoursAgo}'
GROUP BY appid
HAVING count_3h > 10
ORDER BY count_3h DESC
LIMIT 20
";
$rows = $this->query($sql);
$this->sendToFeishu('📊 每小时应用流量监控', $rows, 'hourly');
}
protected function handleDailyPush()
{
$now = Carbon::now();
$oneDayAgo = $now->copy()->subDay()->toDateTimeString();
$threeDaysAgo = $now->copy()->subDays(3)->toDateTimeString();
$sevenDaysAgo = $now->copy()->subDays(7)->toDateTimeString();
$sql = "
SELECT
JSONExtractString(request_params, 'appid') AS appid,
COUNTIf(timestamp >= '{$oneDayAgo}') AS count_24h,
COUNTIf(timestamp >= '{$threeDaysAgo}') AS count_3d,
COUNT(1) AS count_7d
FROM request_logs
WHERE timestamp >= '{$sevenDaysAgo}'
GROUP BY appid
HAVING count_7d > 50
ORDER BY count_7d DESC
LIMIT 20
";
$rows = $this->query($sql);
$this->sendToFeishu('📅 每日应用流量大盘', $rows, 'daily');
}
protected function query(string $sql): array
{
return $this->clickHouseService->getClient()->select($sql)->rows();
}
protected function sendToFeishu(string $title, array $rows, string $type = 'hourly')
{
$webhookUrl = env('FEISHU_WEBHOOK');
if (!$webhookUrl) {
$this->error('未配置 FEISHU_WEBHOOK');
return;
}
$elements = [
['tag' => 'markdown', 'content' => '**🕒 生成时间：** ' . now()->format('Y-m-d H:i:s')],
['tag' => 'markdown', 'content' => '**📈 应用数量：** ' . count($rows)],
['tag' => 'hr'],
];
foreach ($rows as $index => $row) {
$rank = $index + 1;
$appid = $row['appid'] ?: 'unknown';
if ($type === 'hourly') {
$count1h = (int) $row['count_1h'];
$count3h = (int) $row['count_3h'];
$avg = max(1, $count3h / 3);
$rate = round(($count1h / $avg) * 100);
$trend = '🟢';
$status = '正常';
if ($rate >= 200) { $trend = '🚨'; $status = '流量暴涨'; }
elseif ($rate >= 150) { $trend = '🟡'; $status = '流量升高'; }
elseif ($rate <= 50) { $trend = '🔵'; $status = '流量下降'; }
$content = "### {$trend} TOP {$rank} · {$appid}\n\n> {$status}\n\n"
. "- 1小时请求量：**" . number_format($count1h) . "**\n"
. "- 3小时请求量：**" . number_format($count3h) . "**\n"
. "- 当前热度：**{$rate}%**";
} else {
$count24h = (int) $row['count_24h'];
$count3d = (int) $row['count_3d'];
$count7d = (int) $row['count_7d'];
$avg = max(1, $count7d / 7);
$rate = round(($count24h / $avg) * 100);
$trend = $rate >= 180 ? '🚨' : ($rate >= 130 ? '🟡' : '🟢');
$content = "### {$trend} TOP {$rank} · {$appid}\n\n"
. "- 24小时请求量：**" . number_format($count24h) . "**\n"
. "- 近3天：**" . number_format($count3d) . "**\n"
. "- 近7天：**" . number_format($count7d) . "**\n"
. "- 当前热度：**{$rate}%**";
}
$elements[] = ['tag' => 'markdown', 'content' => $content];
$elements[] = ['tag' => 'hr'];
}
$elements[] = ['tag' => 'markdown', 'content' => '⚡ Powered By Laravel + ClickHouse'];
$payload = [
'msg_type' => 'interactive',
'card' => [
'config' => ['wide_screen_mode' => true],
'header' => [
'title' => ['tag' => 'plain_text', 'content' => $title],
'template' => 'turquoise',
],
'elements' => $elements,
],
];
$response = Http::timeout(10)->post($webhookUrl, $payload);
$response->successful()
? $this->info('飞书推送成功')
: $this->error('飞书推送失败: ' . $response->body());
}
}注册定时任务
// routes/console.php
Schedule::command('push:feishu-stats')->hourly();
Schedule::command('push:feishu-stats daily')->dailyAt('00:00');.env 配置
FEISHU_WEBHOOK=https://open.feishu.cn/open-apis/bot/v2/hook/xxxxxxxxClickHouse 性能建议
当前用 JSONExtractString(request_params, 'appid') 实时解析 JSON，数据量大时 CPU 开销高。建议在 request_logs 表冗余一个 appid String 字段，插入时直接写入，便于索引和 GROUP BY 加速。
飞书卡片效果
📊 每小时应用流量监控
🕒 生成时间：2026-05-28 10:00:00
📈 应用数量：15
🚨 TOP 1 · app_main
> 流量暴涨
• 1小时请求量：23,412
• 3小时请求量：41,221
• 当前热度：171%
🟢 TOP 2 · app_test
• 1小时请求量：1,242
• 当前热度：119%比 Markdown 表格可读性高很多，支持趋势颜色标识。