ReadingStatsService.php 13 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280
  1. <?php
  2. namespace App\Services\V3;
  3. use Carbon\CarbonImmutable;
  4. use Illuminate\Database\Query\Builder;
  5. use Illuminate\Support\Facades\Cache;
  6. use Illuminate\Support\Facades\DB;
  7. /**
  8. * 管理后台「阅读统计」。时间范围按 UTC 日,闭区间 [from, to]。
  9. *
  10. * 数据来源与口径(也写在 dashboard-v6 统计页每张卡片下方):
  11. * - **活跃读者**:阅读记录 `recents` 在区间内有更新的去重登录用户。recents 是按(用户, 章节)upsert 的,
  12. * 只记最后一次,所以「打开章节」是「区间内有更新的阅读记录数」,不是逐次打开数。
  13. * - **客户端**:recents.param 里 `client=mobile` 或 `mode=reading`(mobile 的写法;网页写 read / edit / wbw)
  14. * 算 App,其余算 Web。
  15. * - **读经时长**:`reading_dailies`,只有 App 登录用户上报;Web 没有计时。
  16. * - **章节浏览**:`views` 在区间内有更新的行(访客 × 章节),含游客与搜索引擎爬虫,只做趋势参考。
  17. * - **查词**:`user_operation_logs` 里的 dict_lookup(v2 接口的登录用户请求)。
  18. *
  19. * 结果缓存 10 分钟。
  20. */
  21. class ReadingStatsService
  22. {
  23. private const CACHE_SECONDS = 600;
  24. public const PITAKA_TAGS = ['sutta', 'vinaya', 'abhidhamma', 'añña'];
  25. public const LAYER_TAGS = ['mūla', 'aṭṭhakathā', 'ṭīkā'];
  26. public function __construct(private readonly CreditService $credits) {}
  27. /**
  28. * @param 'all'|'web'|'mobile' $client
  29. * @return array<string, mixed>
  30. */
  31. public function stats(CarbonImmutable $from, CarbonImmutable $to, string $client): array
  32. {
  33. $key = "admin:reading-stats:{$from->toDateString()}:{$to->toDateString()}:{$client}";
  34. return Cache::remember($key, self::CACHE_SECONDS, function () use ($from, $to, $client) {
  35. $days = $from->diffInDays($to) + 1;
  36. $prevTo = $from->subDay();
  37. $prevFrom = $prevTo->subDays($days - 1);
  38. return [
  39. 'range' => ['from' => $from->toDateString(), 'to' => $to->toDateString(), 'client' => $client],
  40. 'summary' => $this->summary($from, $to, $client),
  41. 'previous' => $this->summary($prevFrom, $prevTo, $client),
  42. 'sticky' => $this->sticky($to, $client),
  43. 'daily' => $this->daily($from, $to, $client),
  44. 'top_chapters' => $this->topChapters($from, $to, $client),
  45. 'pitaka_share' => $this->share($from, $to, $client, self::PITAKA_TAGS),
  46. 'layer_share' => $this->share($from, $to, $client, self::LAYER_TAGS),
  47. 'duration_buckets' => $client === 'web' ? [] : $this->durationBuckets($from, $to),
  48. 'reading_credits' => $client === 'web' ? null : $this->readingCredits($from, $to),
  49. ];
  50. });
  51. }
  52. /**
  53. * @return array{active_readers: int, chapters_opened: int, reading_seconds: int|null, reading_users: int|null, page_views: int|null, dict_lookups: int}
  54. */
  55. private function summary(CarbonImmutable $from, CarbonImmutable $to, string $client): array
  56. {
  57. $recents = $this->recents($from, $to, $client);
  58. $reading = $client === 'web' ? null : DB::table('reading_dailies')
  59. ->whereBetween('day', [$from->toDateString(), $to->toDateString()])
  60. ->selectRaw('coalesce(sum(seconds), 0) as seconds, count(distinct user_uid) as users')->first();
  61. return [
  62. 'active_readers' => (int) (clone $recents)->distinct()->count('user_uid'),
  63. 'chapters_opened' => (int) (clone $recents)->count(),
  64. 'reading_seconds' => $reading ? (int) $reading->seconds : null,
  65. 'reading_users' => $reading ? (int) $reading->users : null,
  66. 'page_views' => $client === 'mobile' ? null : (int) $this->between(DB::table('views'), 'updated_at', $from, $to)->count(),
  67. 'dict_lookups' => (int) $this->between(DB::table('user_operation_logs'), 'created_at', $from, $to)
  68. ->where('op_type', 'dict_lookup')->count(),
  69. ];
  70. }
  71. /**
  72. * 日活 / 月活(以区间最后一天为准)。
  73. *
  74. * @return array{dau: int, mau: int}
  75. */
  76. private function sticky(CarbonImmutable $to, string $client): array
  77. {
  78. return [
  79. 'dau' => (int) $this->recents($to, $to, $client)->distinct()->count('user_uid'),
  80. 'mau' => (int) $this->recents($to->subDays(29), $to, $client)->distinct()->count('user_uid'),
  81. ];
  82. }
  83. /**
  84. * 每天一行:Web / App 活跃读者、读经分钟、章节浏览、查词。没有数据的日子补 0。
  85. *
  86. * @return list<array{date: string, readers_web: int, readers_mobile: int, reading_minutes: int, page_views: int, dict_lookups: int}>
  87. */
  88. private function daily(CarbonImmutable $from, CarbonImmutable $to, string $client): array
  89. {
  90. $readers = $this->recents($from, $to, $client)
  91. ->selectRaw('date(updated_at) as d, '.$this->clientExpr().' as c, count(distinct user_uid) as n')
  92. ->groupByRaw('1, 2')->get();
  93. $minutes = DB::table('reading_dailies')->whereBetween('day', [$from->toDateString(), $to->toDateString()])
  94. ->selectRaw('day as d, sum(seconds) as s')->groupBy('day')->pluck('s', 'd');
  95. $views = $this->between(DB::table('views'), 'updated_at', $from, $to)
  96. ->selectRaw('date(updated_at) as d, count(*) as n')->groupByRaw('1')->pluck('n', 'd');
  97. $lookups = $this->between(DB::table('user_operation_logs'), 'created_at', $from, $to)->where('op_type', 'dict_lookup')
  98. ->selectRaw('date(created_at) as d, count(*) as n')->groupByRaw('1')->pluck('n', 'd');
  99. $rows = [];
  100. for ($day = $from; $day->lte($to); $day = $day->addDay()) {
  101. $d = $day->toDateString();
  102. $rows[$d] = [
  103. 'date' => $d,
  104. 'readers_web' => 0,
  105. 'readers_mobile' => 0,
  106. 'reading_minutes' => $client === 'web' ? 0 : intdiv((int) ($minutes[$d] ?? 0), 60),
  107. 'page_views' => $client === 'mobile' ? 0 : (int) ($views[$d] ?? 0),
  108. 'dict_lookups' => (int) ($lookups[$d] ?? 0),
  109. ];
  110. }
  111. foreach ($readers as $row) {
  112. $d = substr((string) $row->d, 0, 10);
  113. if (isset($rows[$d])) {
  114. $rows[$d]['readers_'.$row->c] = (int) $row->n;
  115. }
  116. }
  117. return array_values($rows);
  118. }
  119. /**
  120. * 热门章节:区间内阅读记录最多的章节(按去重读者数)。
  121. *
  122. * @return list<array{article_id: string, book: int, paragraph: int, title: string, collection: string|null, readers: int, records: int}>
  123. */
  124. private function topChapters(CarbonImmutable $from, CarbonImmutable $to, string $client, int $limit = 10): array
  125. {
  126. $top = $this->recents($from, $to, $client)
  127. ->where('type', 'chapter')
  128. ->selectRaw('article_id, count(distinct user_uid) as readers, count(*) as records')
  129. ->groupBy('article_id')->orderByDesc('readers')->orderByDesc('records')->limit($limit)->get();
  130. return $top->map(function ($row) {
  131. [$book, $para] = array_map('intval', explode('-', $row->article_id) + [0, 0]);
  132. $text = DB::table('pali_texts')->where('book', $book)->where('paragraph', $para)->first(['toc', 'path']);
  133. $path = $text ? json_decode((string) $text->path, true) : [];
  134. $title = $text && trim((string) $text->toc) !== '' ? trim((string) $text->toc) : (end($path)['title'] ?? $row->article_id);
  135. return [
  136. 'article_id' => $row->article_id,
  137. 'book' => $book,
  138. 'paragraph' => $para,
  139. 'title' => $title,
  140. 'collection' => $path[0]['title'] ?? null,
  141. 'readers' => (int) $row->readers,
  142. 'records' => (int) $row->records,
  143. ];
  144. })->all();
  145. }
  146. /**
  147. * 阅读记录按书的分类标签(三藏 / 根本·义注·复注)分组计数。一本书属于哪一类是固定的,
  148. * 标签挂在 pali_texts 的章节行上(tag_maps.table_name = pali_texts),按书取第一个命中的标签。
  149. *
  150. * @param list<string> $tags
  151. * @return list<array{tag: string, records: int}>
  152. */
  153. private function share(CarbonImmutable $from, CarbonImmutable $to, string $client, array $tags): array
  154. {
  155. $bookTag = $this->bookTags($tags);
  156. $counts = [];
  157. $rows = $this->recents($from, $to, $client)->where('type', 'chapter')
  158. ->selectRaw("split_part(article_id, '-', 1) as book, count(*) as n")->groupByRaw('1')->get();
  159. foreach ($rows as $row) {
  160. $tag = $bookTag[(int) $row->book] ?? 'other';
  161. $counts[$tag] = ($counts[$tag] ?? 0) + (int) $row->n;
  162. }
  163. arsort($counts);
  164. return array_map(fn ($tag, $n) => ['tag' => $tag, 'records' => $n], array_keys($counts), $counts);
  165. }
  166. /**
  167. * 书 → 标签。目录结构很少变,缓存一天。
  168. *
  169. * @param list<string> $tags
  170. * @return array<int, string>
  171. */
  172. private function bookTags(array $tags): array
  173. {
  174. return Cache::remember('admin:book-tags:'.md5(implode(',', $tags)), 86400, fn () => DB::table('tag_maps as m')
  175. ->join('pali_texts as p', 'p.uid', '=', 'm.anchor_id')
  176. ->join('tags as t', 't.id', '=', 'm.tag_id')
  177. ->where('m.table_name', 'pali_texts')
  178. ->whereIn('t.name', $tags)
  179. ->selectRaw('p.book, min(t.name) as tag')
  180. ->groupBy('p.book')
  181. ->pluck('tag', 'book')
  182. ->mapWithKeys(fn ($tag, $book) => [(int) $book => (string) $tag])
  183. ->all());
  184. }
  185. /**
  186. * 单日读经时长分布(人·天)。
  187. *
  188. * @return list<array{bucket: string, user_days: int}>
  189. */
  190. private function durationBuckets(CarbonImmutable $from, CarbonImmutable $to): array
  191. {
  192. $row = DB::table('reading_dailies')->whereBetween('day', [$from->toDateString(), $to->toDateString()])
  193. ->selectRaw('count(*) filter (where seconds < 300) as b1')
  194. ->selectRaw('count(*) filter (where seconds >= 300 and seconds < 900) as b2')
  195. ->selectRaw('count(*) filter (where seconds >= 900 and seconds < 1800) as b3')
  196. ->selectRaw('count(*) filter (where seconds >= 1800 and seconds < 3600) as b4')
  197. ->selectRaw('count(*) filter (where seconds >= 3600) as b5')
  198. ->first();
  199. return [
  200. ['bucket' => '<5', 'user_days' => (int) $row->b1],
  201. ['bucket' => '5-15', 'user_days' => (int) $row->b2],
  202. ['bucket' => '15-30', 'user_days' => (int) $row->b3],
  203. ['bucket' => '30-60', 'user_days' => (int) $row->b4],
  204. ['bucket' => '>60', 'user_days' => (int) $row->b5],
  205. ];
  206. }
  207. /**
  208. * 读经积分发放:总额、获积分人数、达到每日上限的人·天、服务端按墙钟核减掉的比例。
  209. *
  210. * @return array{credits: int, users: int, capped_user_days: int, clamped_ratio: float}
  211. */
  212. private function readingCredits(CarbonImmutable $from, CarbonImmutable $to): array
  213. {
  214. $cap = $this->credits->policy()['rules']['earn']['reading']['daily_cap'] ?? null;
  215. $earn = DB::table('credit_ledger')->where('account_type', 'user')->where('source', 'reading')->where('kind', 'earn')
  216. ->whereBetween('day', [$from->toDateString(), $to->toDateString()])
  217. ->selectRaw('coalesce(sum(delta), 0) as credits, count(distinct account_id) as users')
  218. ->when($cap !== null, fn ($q) => $q->selectRaw('count(*) filter (where delta >= ?) as capped', [(int) $cap]))
  219. ->when($cap === null, fn ($q) => $q->selectRaw('0 as capped'))
  220. ->first();
  221. $seconds = DB::table('reading_dailies')->whereBetween('day', [$from->toDateString(), $to->toDateString()])
  222. ->selectRaw('coalesce(sum(seconds), 0) as kept, coalesce(sum(seconds_reported), 0) as reported')->first();
  223. return [
  224. 'credits' => (int) $earn->credits,
  225. 'users' => (int) $earn->users,
  226. 'capped_user_days' => (int) $earn->capped,
  227. 'clamped_ratio' => (int) $seconds->reported > 0
  228. ? round(1 - (int) $seconds->kept / (int) $seconds->reported, 4)
  229. : 0.0,
  230. ];
  231. }
  232. private function recents(CarbonImmutable $from, CarbonImmutable $to, string $client): Builder
  233. {
  234. $query = $this->between(DB::table('recents'), 'updated_at', $from, $to);
  235. if ($client !== 'all') {
  236. $query->whereRaw($this->clientExpr().' = ?', [$client]);
  237. }
  238. return $query;
  239. }
  240. private function clientExpr(): string
  241. {
  242. return "(case when param->>'client' = 'mobile' or param->>'mode' = 'reading' then 'mobile' else 'web' end)";
  243. }
  244. /**
  245. * 闭区间的 UTC 日 → 半开的时间区间 [from 00:00, to+1 00:00)。
  246. */
  247. private function between(Builder $query, string $column, CarbonImmutable $from, CarbonImmutable $to): Builder
  248. {
  249. return $query->where($column, '>=', $from->startOfDay())->where($column, '<', $to->addDay()->startOfDay());
  250. }
  251. }