查询累计章节跳出率
```
public function handle()
{
// 获取查询的时间
$date = $this->argument('date') ?? Carbon::yesterday()->format('Ymd');
// 设置内存限制
ini_set('memory_limit', '512M');
try {
// 1：获取章节跳出率累计数据【书本、章节、总阅读uv】
$totalReadUvData = DorisChapterRackDistincts::query()
->selectRaw('book_id,count(distinct user_id) as read_count')
->where('date', '<=', $date)
->groupBy('book_id')
->orderBy('book_id')
->get()
->toArray() ?? [];
foreach (array_chunk($totalReadUvData, 3000) as $chunk) {
$books = array_column($chunk, 'read_count', 'book_id');
Log::info('书本UV：' . json_encode($books));
// 获取书本ID
$bookIds = implode(',', array_keys($books));
// 分页查询数据
$pageSize = 10000;
$page = 1;
do {
// 2：查询章节跳出率累计数据【书本、章节、当前阅读uv、连续阅读uv】
$query = "WITH op AS (
SELECT DISTINCT user_id, book_id, sort_id
FROM `fq_chapter_rack_distincts`
WHERE book_id IN ($bookIds) AND date <= $date
), op1 AS (
SELECT
user_id,
book_id,
sort_id,
LEAD(sort_id, 1, NULL) OVER (PARTITION BY user_id, book_id ORDER BY sort_id) AS next_sort_id
FROM op
)
SELECT
book_id,
sort_id,
COUNT(user_id) AS uv,
COUNT(CASE WHEN next_sort_id IS NOT NULL AND next_sort_id - sort_id = 1 THEN user_id ELSE NULL END) AS next_uv
FROM op1
GROUP BY book_id, sort_id
LIMIT :pageSize OFFSET :offset";
$offset = ($page - 1) * $pageSize;
// 使用预处理语句避免SQL注入
$data = DB::connection("doris_novel_collect")
->select($query, ['pageSize' => $pageSize, 'offset' => $offset]);
if (empty($data)) {
break;
}
// 处理数据
$list = array_map(function ($item) use ($books) {
$item = get_object_vars($item);
$item['read_count'] = $books[$item['book_id']] ?? 0;
return $item;
}, $data);
// 批量插入或更新数据
TotalChapterJumpFinishRate::addOrUpdateResult(['uv', 'next_uv', 'read_count'], $list);
$page++;
} while (!empty($data));
}
return 1;
} catch (\Exception $e) {
// 异常处理
Log::error('累计-章节跳出率数据获取失败: ' . $e->getMessage());
// 发送异常告警
FeiShuRequest::robot(FeiShuConstant::ADMIN_WARNING_CONFIG, true)
->setWarning($e, $e->getTraceAsString())
->sendMsg();
return 0;
}
```
