Compare commits
1 Commits
| Author | SHA1 | Date | |
|---|---|---|---|
 左辰勇
|
96444d67fa |
@@ -0,0 +1,52 @@
|
||||
"""add composite index (user_id, created_at, id) on comparison_record
|
||||
|
||||
C 端「我的比价记录」列表(GET /api/v1/compare/records)是
|
||||
`WHERE user_id=? ORDER BY created_at DESC, id DESC LIMIT n` —— 原来只有单列 user_id 索引,
|
||||
过滤完还要把该用户的**全部**记录取出来排序才能拿前 n 条,重度用户随记录数线性变慢。
|
||||
|
||||
本复合索引的反向扫恰好等于 (created_at DESC, id DESC),规划器直接取前 n 条、免排序。
|
||||
列序 (user_id, created_at, id) 与查询一一对应,不要调整。
|
||||
|
||||
Revision ID: comparison_user_created_idx
|
||||
Revises: merge_active_phone
|
||||
Create Date: 2026-07-21
|
||||
"""
|
||||
|
||||
from __future__ import annotations
|
||||
|
||||
from alembic import op
|
||||
|
||||
revision = "comparison_user_created_idx"
|
||||
down_revision = "merge_active_phone"
|
||||
branch_labels = None
|
||||
depends_on = None
|
||||
|
||||
INDEX_NAME = "ix_comparison_user_created"
|
||||
COLUMNS = ["user_id", "created_at", "id"]
|
||||
|
||||
|
||||
def upgrade() -> None:
|
||||
bind = op.get_bind()
|
||||
if bind.dialect.name == "postgresql":
|
||||
# 线上 comparison_record 已有数据量,普通 CREATE INDEX 持表写锁会阻塞比价 harvest 写入;
|
||||
# 用 CONCURRENTLY 不锁表(须脱离事务,autocommit_block 切到自动提交)。
|
||||
# 同 comparison_status_created_idx 的做法。
|
||||
with op.get_context().autocommit_block():
|
||||
op.create_index(
|
||||
INDEX_NAME, "comparison_record", COLUMNS,
|
||||
unique=False, postgresql_concurrently=True,
|
||||
)
|
||||
else:
|
||||
op.create_index(INDEX_NAME, "comparison_record", COLUMNS, unique=False)
|
||||
|
||||
|
||||
def downgrade() -> None:
|
||||
bind = op.get_bind()
|
||||
if bind.dialect.name == "postgresql":
|
||||
with op.get_context().autocommit_block():
|
||||
op.drop_index(
|
||||
INDEX_NAME, table_name="comparison_record",
|
||||
postgresql_concurrently=True,
|
||||
)
|
||||
else:
|
||||
op.drop_index(INDEX_NAME, table_name="comparison_record")
|
||||
@@ -33,29 +33,7 @@ from app.admin.repositories import stats as admin_stats
|
||||
from app.core import rewards
|
||||
from app.models.ad_ecpm import AdEcpmRecord
|
||||
from app.models.user import User
|
||||
from app.repositories import ad_pangle_revenue, app_config
|
||||
|
||||
# 已上线过的正式业务代码位要永久保留，避免运营切换当前配置后，历史报表把旧业务位误判成测试流量。
|
||||
_KNOWN_PROD_BUSINESS_CODE_IDS = frozenset({"104098712", "104099389"})
|
||||
|
||||
# 测试应用中实际承载业务链路的代码位。广告测试 demo 的插屏/半屏/信息流测试位不在这里，
|
||||
# 避免“业务口径”把开发诊断曝光混进客户端与穿山甲对账。
|
||||
_TEST_BUSINESS_CODE_IDS = frozenset({"104127529", "104127626", "104137445"})
|
||||
|
||||
|
||||
def _business_code_ids(db: Session, app_env: str | None) -> set[str]:
|
||||
"""返回指定应用环境下可用于业务收益对账的 GroMore 聚合代码位。"""
|
||||
prod_config = app_config.get_ad_config(db)
|
||||
prod_ids = set(_KNOWN_PROD_BUSINESS_CODE_IDS) | {
|
||||
str(prod_config.get(key) or "").strip()
|
||||
for key in ("reward_code_id", "compare_draw_code_id", "coupon_draw_code_id")
|
||||
}
|
||||
prod_ids.discard("")
|
||||
if app_env == "prod":
|
||||
return prod_ids
|
||||
if app_env == "test":
|
||||
return set(_TEST_BUSINESS_CODE_IDS)
|
||||
return prod_ids | set(_TEST_BUSINESS_CODE_IDS)
|
||||
from app.repositories import ad_pangle_revenue
|
||||
|
||||
|
||||
def _cn_hour(dt: datetime) -> int:
|
||||
@@ -104,7 +82,6 @@ def ad_revenue_report(
|
||||
ad_type: str | None = None,
|
||||
feed_scene: str | None = None,
|
||||
app_env: str | None = None,
|
||||
revenue_scope: str = "all",
|
||||
granularity: str = "day",
|
||||
limit: int = 500,
|
||||
offset: int = 0,
|
||||
@@ -300,18 +277,14 @@ def ad_revenue_report(
|
||||
if feed_scene is not None:
|
||||
events = [e for e in events if e.get("feed_scene") == feed_scene]
|
||||
|
||||
# app_env 过滤:显式传 "prod"/"test" 只看该环境;不传=全部。该参数也会传给下方穿山甲聚合，
|
||||
# 保证客户端预估与 GroMore 汇总使用同一应用环境口径。
|
||||
# app_env 过滤(2026-06-29 新增能力,修隐患:测试应用上报的假 eCPM 如 ¥678 CPM 会污染正式收益合计/平均):
|
||||
# 显式传 "prod"/"test" 只看该环境;不传=全部(维持现状)。**不擅自把默认改成排除 test**——本地 dev 库多为
|
||||
# test 数据、默认排除会使本地报表空,且「正式报表是否含 test」属产品口径。建议前端报表页加 app_env 筛选器
|
||||
# (默认选 prod),或产品确认后再把默认改成排除 test。注:穿山甲后台收益列(total_pangle_*)暂未联动此过滤
|
||||
# (它是独立对照列,且 pangle 的 test 是真实小额、非客户端那种假值)。
|
||||
if app_env is not None:
|
||||
events = [e for e in events if e.get("app_env") == app_env]
|
||||
|
||||
# 业务口径仅保留正式配置/测试业务链路实际使用的代码位。穿山甲“全量”还包含广告测试
|
||||
# demo、插屏等没有客户端收益上报的曝光，两边直接比较会天然产生假差额。
|
||||
business_code_ids: set[str] | None = None
|
||||
if revenue_scope == "business":
|
||||
business_code_ids = _business_code_ids(db, app_env)
|
||||
events = [e for e in events if e.get("our_code_id") in business_code_ids]
|
||||
|
||||
# 排序:time=按时间倒序(新→旧);ecpm=按 eCPM 数值倒序(eCPM 原值是字符串「分」,转数值排;
|
||||
# 纯发奖行用其发奖采用的 eCPM,缺失/非法计 0 排末尾)。
|
||||
if sort == "ecpm":
|
||||
@@ -363,13 +336,7 @@ def ad_revenue_report(
|
||||
total_pangle_revenue_yuan: float | None = None
|
||||
total_pangle_api_revenue_yuan: float | None = None
|
||||
if pangle_filterable:
|
||||
pangle_aggs = ad_pangle_revenue.aggregate_by_date(
|
||||
db,
|
||||
date_from=date_from,
|
||||
date_to=date_to,
|
||||
app_env=app_env,
|
||||
our_code_ids=business_code_ids,
|
||||
)
|
||||
pangle_aggs = ad_pangle_revenue.aggregate_by_date(db, date_from=date_from, date_to=date_to)
|
||||
if pangle_aggs:
|
||||
by_date = {a["date"]: a for a in pangle_aggs}
|
||||
for d in daily:
|
||||
|
||||
@@ -5,7 +5,7 @@
|
||||
from __future__ import annotations
|
||||
|
||||
from datetime import date as _date
|
||||
from typing import Annotated, Literal
|
||||
from typing import Annotated
|
||||
|
||||
from fastapi import APIRouter, Depends, HTTPException, Query
|
||||
|
||||
@@ -63,13 +63,6 @@ def get_ad_revenue_report(
|
||||
"建议正式收益报表选 prod,避免测试应用的假 eCPM 污染收益合计/平均"
|
||||
),
|
||||
] = None,
|
||||
revenue_scope: Annotated[
|
||||
Literal["business", "all"],
|
||||
Query(
|
||||
description="business=仅业务代码位（用于客户端与穿山甲同口径对账）/ "
|
||||
"all=穿山甲应用全部代码位（包含广告测试等非业务曝光）"
|
||||
),
|
||||
] = "all",
|
||||
granularity: Annotated[
|
||||
str, Query(description="day=按天 / hour=按小时(北京时间);区间>1 天建议用 day")
|
||||
] = "day",
|
||||
@@ -90,7 +83,6 @@ def get_ad_revenue_report(
|
||||
result = ad_revenue.ad_revenue_report(
|
||||
db, date_from=d_from.isoformat(), date_to=d_to.isoformat(),
|
||||
user_id=user_id, ad_type=ad_type, feed_scene=feed_scene, app_env=app_env,
|
||||
revenue_scope=revenue_scope,
|
||||
granularity=granularity, limit=limit, offset=offset, sort=sort,
|
||||
)
|
||||
return AdRevenueReportOut(
|
||||
|
||||
@@ -115,13 +115,22 @@ def list_records(
|
||||
db: DbSession,
|
||||
limit: int = Query(20, ge=1, le=100),
|
||||
cursor: int | None = Query(None, description="上一页末条 id"),
|
||||
ordered: bool | None = Query(
|
||||
None,
|
||||
description="true=只看「已下单」(店名命中本人真实下单)的记录;不传=全部",
|
||||
),
|
||||
keyword: str | None = Query(
|
||||
None,
|
||||
max_length=64,
|
||||
description="按店名 / 菜名模糊搜索,忽略大小写;空白串等同不传",
|
||||
),
|
||||
include_trace: bool = Query(
|
||||
False,
|
||||
description="客户端开了本机 agent 调试模式时带 true,放行本人记录的 trace_url",
|
||||
),
|
||||
) -> ComparisonRecordPage:
|
||||
items, next_cursor = crud_compare.list_records(
|
||||
db, user.id, limit=limit, cursor=cursor
|
||||
db, user.id, limit=limit, cursor=cursor, ordered=ordered, keyword=keyword
|
||||
)
|
||||
outs = [ComparisonRecordOut.model_validate(it) for it in items]
|
||||
# 权限闸:未开 debug_trace_enabled 的用户不下发 trace_url(列表页「复制调试链接」靠它)。
|
||||
|
||||
@@ -45,6 +45,10 @@ class ComparisonRecord(Base):
|
||||
# 首页轮播 / 省钱战绩聚合都按 status='success' 过滤 + created_at 近期排序;
|
||||
# 复合索引避免随数据量增大退化成全表扫(单列 created_at 索引不含 status)。
|
||||
Index("ix_comparison_status_created", "status", "created_at"),
|
||||
# C 端「我的比价记录」列表:WHERE user_id=? ORDER BY created_at DESC, id DESC LIMIT n。
|
||||
# 单列 user_id 索引只能过滤,排序仍要把该用户全部记录取出来排一遍;这条复合索引的**反向扫**
|
||||
# 恰好等于 (created_at DESC, id DESC),PG 直接取前 n 条、免排序。列序不能动。
|
||||
Index("ix_comparison_user_created", "user_id", "created_at", "id"),
|
||||
)
|
||||
|
||||
id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
|
||||
|
||||
@@ -6,7 +6,6 @@
|
||||
"""
|
||||
from __future__ import annotations
|
||||
|
||||
from collections.abc import Collection
|
||||
from typing import Any, TypedDict
|
||||
|
||||
from sqlalchemy import func, select
|
||||
@@ -74,7 +73,6 @@ def aggregate_by_date(
|
||||
date_to: str,
|
||||
app_env: str | None = None,
|
||||
our_code_id: str | None = None,
|
||||
our_code_ids: Collection[str] | None = None,
|
||||
) -> list[PangleDateAgg]:
|
||||
"""按日期汇总穿山甲收益(闭区间,北京时间),供报表趋势 + 合计。
|
||||
|
||||
@@ -99,8 +97,6 @@ def aggregate_by_date(
|
||||
stmt = stmt.where(AdPangleDailyRevenue.app_env == app_env)
|
||||
if our_code_id is not None:
|
||||
stmt = stmt.where(AdPangleDailyRevenue.our_code_id == our_code_id)
|
||||
if our_code_ids is not None:
|
||||
stmt = stmt.where(AdPangleDailyRevenue.our_code_id.in_(our_code_ids))
|
||||
|
||||
out: list[PangleDateAgg] = []
|
||||
for report_date, rev, api_rev, imp in db.execute(stmt).all():
|
||||
|
||||
@@ -7,8 +7,8 @@ from __future__ import annotations
|
||||
|
||||
from datetime import datetime
|
||||
|
||||
from sqlalchemy import func, select
|
||||
from sqlalchemy.orm import Session
|
||||
from sqlalchemy import func, or_, select
|
||||
from sqlalchemy.orm import Session, defer
|
||||
|
||||
from app.core.rewards import CN_TZ
|
||||
from app.models.ad_feed_reward import AdFeedRewardRecord
|
||||
@@ -375,19 +375,48 @@ def harvest_abort(
|
||||
return rec
|
||||
|
||||
|
||||
def _ordered_shop_names(db: Session, user_id: int) -> set[str]:
|
||||
"""该用户「真实下单」(source='compare')覆盖到的店名集合,用来给比价记录打「已下单」。
|
||||
def _ordered_shop_name_select(user_id: int):
|
||||
"""该用户「真实下单」(source='compare')覆盖到的店名 select,给「已下单」筛选当子查询。
|
||||
|
||||
口径与 [_ordered_shop_names] 完全一致,只是时机不同:那边是**拿到本页之后**按 candidates
|
||||
反查打标;这边是**分页之前**就要过滤,拿不到 candidates,只能整段下推成子查询。
|
||||
没有先捞成集合再展开 IN (...) 字面量 —— 重度用户下单过的店名可能上千,展开会撞 SQLite
|
||||
的绑定变量上限,而且又变回了那个「随下单量线性变慢」的老写法。
|
||||
"""
|
||||
return select(SavingsRecord.shop_name).where(
|
||||
SavingsRecord.user_id == user_id,
|
||||
SavingsRecord.source == "compare",
|
||||
SavingsRecord.shop_name.is_not(None),
|
||||
)
|
||||
|
||||
|
||||
def _like_escape(kw: str) -> str:
|
||||
"""转义 LIKE 通配符(百分号 / 下划线 / 反斜杠),让用户输入只按字面量匹配(配合 escape 参数)。
|
||||
|
||||
不转义的话搜一个「%」就等于把整表拉回来。
|
||||
"""
|
||||
return kw.replace("\\", "\\\\").replace("%", "\\%").replace("_", "\\_")
|
||||
|
||||
|
||||
def _ordered_shop_names(db: Session, user_id: int, candidates: set[str]) -> set[str]:
|
||||
"""[candidates] 里哪些店名被该用户「真实下单」(source='compare')覆盖过,用来打「已下单」。
|
||||
|
||||
只认 compare(归因命中后真实上报),demo 演示数据不算。下单上报不带 trace_id,
|
||||
只能按店名对齐——两边店名同源(都来自比价意图识别阶段的门店名 query),精确相等即视为同店。
|
||||
语义=店级:同一家店比价过多次,这些记录会一并标「已下单」。
|
||||
|
||||
⚠️ 只查**本页出现过的店名**(candidates ≤ limit 条),不再把该用户全部下单店名捞回内存:
|
||||
老写法随下单量线性增长,重度用户几千行全读一遍只为跟 50 条记录取交集。空集合直接返回
|
||||
(避免 IN () 非法)。
|
||||
"""
|
||||
if not candidates:
|
||||
return set()
|
||||
rows = db.execute(
|
||||
select(SavingsRecord.shop_name).where(
|
||||
SavingsRecord.user_id == user_id,
|
||||
SavingsRecord.source == "compare",
|
||||
SavingsRecord.shop_name.is_not(None),
|
||||
)
|
||||
SavingsRecord.shop_name.in_(candidates),
|
||||
).distinct()
|
||||
).scalars().all()
|
||||
return {s for s in rows if s}
|
||||
|
||||
@@ -415,17 +444,60 @@ def _ad_coins_by_trace(db: Session, user_id: int, trace_ids: list[str]) -> dict[
|
||||
return {tid: int(coin) for tid, coin in rows if tid}
|
||||
|
||||
|
||||
# 列表出参(ComparisonRecordOut)根本不读、但 select(ORM) 默认会一并捞回来的重型 JSON 列:
|
||||
# - raw_payload:done.params 上报体全量,**每条记录都有**(harvest 与 POST 两条写路径都落)。
|
||||
# 单条几 KB~几十 KB,一页 50 条就是稳定几百 KB~几 MB 的白读 + 白反序列化。
|
||||
# - llm_calls:每次 LLM 调用的 input_messages + output 全文。只有走老客户端 POST /compare/record
|
||||
# 的记录才有(_backfill_llm_calls 回填;harvest 路径不落),但有的时候单条就能到 MB 级 —— 一页里
|
||||
# 混进几条这种记录,整个请求就被它们拖住。
|
||||
# - llm_price_snapshot:逐模型单价快照,同样只在回填时落。
|
||||
# 三列全部读出来再被 pydantic 丢掉,是「比价记录/全部记录」页慢的主要来源。
|
||||
# ⚠️ defer 的列一旦在别处被读到会触发**逐行**懒加载(N+1);列表这条链路(ComparisonRecordOut
|
||||
# 不声明这三个字段 → 不会 getattr 到)是安全的。详情接口 get_record 不 defer,raw_payload 照常返回。
|
||||
_LIST_DEFERRED = (
|
||||
ComparisonRecord.raw_payload,
|
||||
ComparisonRecord.llm_calls,
|
||||
ComparisonRecord.llm_price_snapshot,
|
||||
)
|
||||
|
||||
|
||||
def list_records(
|
||||
db: Session,
|
||||
user_id: int,
|
||||
*,
|
||||
limit: int = 20,
|
||||
cursor: int | None = None,
|
||||
ordered: bool | None = None,
|
||||
keyword: str | None = None,
|
||||
) -> tuple[list[ComparisonRecord], int | None]:
|
||||
"""比价记录分页(按创建时间倒序、id 兜底,游标式)。附「已下单」店级标记 + 「看广告赚的金币」(瞬态,不写库)。"""
|
||||
stmt = select(ComparisonRecord).where(ComparisonRecord.user_id == user_id)
|
||||
stmt = (
|
||||
select(ComparisonRecord)
|
||||
.where(ComparisonRecord.user_id == user_id)
|
||||
.options(*(defer(col) for col in _LIST_DEFERRED))
|
||||
)
|
||||
if cursor is not None:
|
||||
stmt = stmt.where(ComparisonRecord.id < cursor)
|
||||
# 「已下单」tab 与搜索框的过滤都下推到这里,不能留给客户端对整页结果 filter ——
|
||||
# 分页之后一页里可能一条都不命中,列表看着就是空的/卡住的,得翻很多页才蹦出一条。
|
||||
if ordered:
|
||||
stmt = stmt.where(
|
||||
ComparisonRecord.store_name.in_(_ordered_shop_name_select(user_id))
|
||||
)
|
||||
kw = (keyword or "").strip()
|
||||
if kw:
|
||||
# product_names 是写路径从 items[].name 派生的普通文本列(items 本身是 JSON,SQLite 下
|
||||
# 中文被 ensure_ascii 转义,没法直接 LIKE)—— 搜「菜名」靠的就是它。
|
||||
# ilike:PG 原生 ILIKE,SQLite 渲染成 lower() LIKE lower(),两边都忽略大小写。
|
||||
pattern = f"%{_like_escape(kw)}%"
|
||||
stmt = stmt.where(
|
||||
or_(
|
||||
ComparisonRecord.store_name.ilike(pattern, escape="\\"),
|
||||
ComparisonRecord.product_names.ilike(pattern, escape="\\"),
|
||||
)
|
||||
)
|
||||
# 排序与 ix_comparison_user_created(user_id, created_at, id)对齐 —— DESC/DESC 正好是该索引的
|
||||
# 反向扫,PG 免排序直接取前 limit 条。改排序方向前先想清楚索引还吃不吃得上。
|
||||
stmt = stmt.order_by(ComparisonRecord.created_at.desc(), ComparisonRecord.id.desc()).limit(limit)
|
||||
|
||||
items = list(db.execute(stmt).scalars().all())
|
||||
@@ -433,7 +505,10 @@ def list_records(
|
||||
|
||||
# 「已下单」标记:本页记录的 store_name 若落在该用户真实下单的店名集合里即 True。
|
||||
# ordered / ad_coins_earned 均非 ORM 列,仅挂实例上供 ComparisonRecordOut(from_attributes) 读出,不持久化。
|
||||
ordered_shops = _ordered_shop_names(db, user_id)
|
||||
page_shops = {it.store_name for it in items if it.store_name}
|
||||
# ordered=True 时上面已按同一口径(_ordered_shop_name_select)筛过,本页必然全是已下单,
|
||||
# 省掉这次反查;其余情况照旧按本页店名反查 savings。
|
||||
ordered_shops = page_shops if ordered else _ordered_shop_names(db, user_id, page_shops)
|
||||
# 「本次比价看广告赚的金币」:按本页 trace_id 一次性聚合(同 ordered 范式)。
|
||||
ad_coins = _ad_coins_by_trace(db, user_id, [it.trace_id for it in items])
|
||||
for it in items:
|
||||
|
||||
@@ -25,6 +25,18 @@ server {
|
||||
# (纯文字反馈体积小、不受影响 → 呈现为「时好时坏」)。根治仍需客户端上传前压缩。
|
||||
client_max_body_size 32m;
|
||||
|
||||
# JSON 响应压缩。nginx 默认 gzip off,且就算 on 了 gzip_types 也只含 text/html、
|
||||
# gzip_proxied 默认 off(反代来的响应一律不压)—— 三个默认值凑一起 = 我们所有接口都在裸奔。
|
||||
# 比价记录列表这种一次 50 条、字段名 + 中文店名/菜名高度重复的 JSON,gzip 压缩比稳定在 8~10 倍
|
||||
# (几百 KB → 几十 KB),弱网下省的就是首屏那几秒。
|
||||
# 只压 JSON:APK 直链(/media/shaguabijia.apk)、图片本身已是压缩格式,再压纯浪费 CPU。
|
||||
gzip on;
|
||||
gzip_proxied any; # 反代响应也压(默认 off = 对我们这套反代等于没开)
|
||||
gzip_types application/json;
|
||||
gzip_min_length 1024; # 小响应压了反而更大(gzip 头开销),不值当
|
||||
gzip_comp_level 5; # 5 是体积/CPU 的常用折中点,再往上收益递减
|
||||
gzip_vary on; # 给 CDN/中间缓存正确按 Accept-Encoding 分桶
|
||||
|
||||
location / {
|
||||
proxy_pass http://127.0.0.1:8770;
|
||||
proxy_http_version 1.1;
|
||||
|
||||
@@ -28,8 +28,6 @@
|
||||
| `user_id` | int | 全部 | 只看某用户;不传=所有用户 |
|
||||
| `ad_type` | string | 全部 | `reward_video` / `feed` / `draw`;不传=全部类型 |
|
||||
| `feed_scene` | string | 全部 | `comparison`(比价)/ `coupon`(领券)/ `welfare`(福利);**全局筛选**,同时作用于明细 / 合计 / `daily`·`hourly` 趋势;不传=全部场景 |
|
||||
| `app_env` | string | 全部 | `prod`=正式应用 / `test`=测试应用;同时过滤客户端预估与穿山甲汇总 |
|
||||
| `revenue_scope` | string | `all` | `business`=仅业务代码位,用于同口径对账 / `all`=应用全部代码位,包含广告测试等非业务曝光 |
|
||||
| `granularity` | string | `day` | `day`=按天 / `hour`=按小时(聚合键再加北京时间小时 0–23);**区间>1 天建议用 day** |
|
||||
| `limit` | int(1~1000) | 500 | **每页条数**(分页大小);`total`/`total_*`/`daily`/`hourly` 按全量统计不受分页影响 |
|
||||
| `offset` | int(≥0) | 0 | 分页偏移(已跳过条数)=(页码−1)×`limit` |
|
||||
@@ -133,5 +131,5 @@
|
||||
- **历史 Draw 不可拆**:迁移(Draw→普通信息流)前,Draw 发奖混在 `ad_feed_reward_record` 且无类型标记,金币侧统一记 `feed`;迁移后 Draw 不再产生新数据。展示侧 `ad_type` 由客户端上报区分,故 `draw` 桶基本为空。
|
||||
- **来源字段从上线起齐全**:`app_env`/`our_code_id` 是本期新增列,历史记录为 NULL(报表来源列留空)。
|
||||
- **逐条/明细的收益是预估**:`items[].revenue_yuan` 基于客户端上报的 eCPM 折算,非穿山甲后台结算值。
|
||||
- **穿山甲后台收益(汇总/趋势级)**:`total_pangle_revenue_yuan`(预估 `revenue`)与 `total_pangle_api_revenue_yuan`(收益Api `api_revenue`,更接近结算)来自穿山甲 **GroMore 数据 API**(`integrations/pangle_report` + `scripts/sync_pangle_revenue` 按天 T+1 拉取入 [ad_pangle_daily_revenue](../database/ad_pangle_daily_revenue.md))。穿山甲**不提供分用户/设备/类型/场景维度**(官方明确),最细到 日期×应用×代码位,故只用于汇总与按天趋势的对照,**不挂到逐条事件行**;且仅在未按 user/类型/场景过滤时展示。`app_env` 与 `revenue_scope` 会同时过滤客户端和穿山甲数据，其中 `business` 排除广告测试等非业务代码位。配置见 `.env` 的 `PANGLE_REPORT_*`。
|
||||
- **穿山甲后台收益(汇总/趋势级)**:`total_pangle_revenue_yuan`(预估 `revenue`)与 `total_pangle_api_revenue_yuan`(收益Api `api_revenue`,更接近结算)来自穿山甲 **GroMore 数据 API**(`integrations/pangle_report` + `scripts/sync_pangle_revenue` 按天 T+1 拉取入 [ad_pangle_daily_revenue](../database/ad_pangle_daily_revenue.md))。穿山甲**不提供分用户/设备/类型/场景维度**(官方明确),最细到 日期×应用×代码位,故只用于汇总与按天趋势的对照,**不挂到逐条事件行**;且仅在全量视图(未按 user/类型/场景过滤)展示。配置见 `.env` 的 `PANGLE_REPORT_*`。
|
||||
- **对账聚合级 + 逐条下钻**:行级 `matched` 给出该组(用户×类型×应用×代码位)应发是否==实发;**展开 `records` 即可看该组逐条明细**(eCPM/因子1/份数/LT/因子2/应发/实发/一致)定位到具体记录。独立逐条审计接口 [admin-ad-coin-audit](./admin-ad-coin-audit.md) 仍保留(同一复算口径,可全局按场景/只看不符筛选)。
|
||||
|
||||
@@ -10,6 +10,12 @@
|
||||
|---|---|---|---|---|
|
||||
| `limit` | int | ❌ | 20 | 1–100 |
|
||||
| `cursor` | int | ❌ | null | 上一页末条 `id`,首页不传 |
|
||||
| `ordered` | bool | ❌ | null | `true`=只出「已下单」(店名命中本人真实下单)的记录;不传=不筛 |
|
||||
| `keyword` | string | ❌ | null | 按店名 / 菜名模糊搜索,忽略大小写,≤64 字符;纯空白等同不传 |
|
||||
| `include_trace` | bool | ❌ | false | 客户端开了本机 agent 调试模式时带 `true`,放行**本人**记录的 `trace_url` |
|
||||
|
||||
`ordered` / `keyword` 都在服务端过滤后再分页,客户端不要拿一页结果自己 filter ——
|
||||
分页之后一页里可能一条都不命中,列表会看着像空的。
|
||||
|
||||
## 出参
|
||||
响应 `200`：`{ items: ComparisonRecordOut[], next_cursor: int|null }`（分页见 [索引#游标分页约定](./README.md#游标分页约定)）
|
||||
@@ -38,6 +44,9 @@
|
||||
| `items` | object[] | 下单菜品 `{name, qty, specs?}` |
|
||||
| `comparison_results` | object[] | 逐平台对比（price 单位元，已按 rank 升序） |
|
||||
| `skipped_dish_names` | string[] | 被跳过的菜名 |
|
||||
| `ordered` | bool | 「已下单」店级标记:店名命中本人 `source='compare'` 的下单记录即 `true`。**瞬态字段,不在表里**,每次查询现算 |
|
||||
| `ad_coins_earned` | int | 本次比价看信息流广告实发的金币(按 `trace_id` 聚合)。同为瞬态字段 |
|
||||
| `trace_url` | string \| null | pricebot 调试链接。未开 `debug_trace_enabled` 且未带 `include_trace=true` 时为 `null` |
|
||||
| `created_at` | datetime | 时间 |
|
||||
|
||||
## 错误
|
||||
|
||||
@@ -1,129 +0,0 @@
|
||||
"""广告收益报表的环境与业务代码位口径。"""
|
||||
from __future__ import annotations
|
||||
|
||||
from datetime import UTC, datetime
|
||||
|
||||
from sqlalchemy import delete
|
||||
|
||||
from app.admin.repositories import ad_revenue
|
||||
from app.db.session import SessionLocal
|
||||
from app.models.ad_ecpm import AdEcpmRecord
|
||||
from app.models.ad_pangle_revenue import AdPangleDailyRevenue
|
||||
from app.models.user import User
|
||||
|
||||
REPORT_DATE = "2040-02-03"
|
||||
|
||||
|
||||
def test_business_scope_filters_client_and_pangle_by_env_and_code(monkeypatch) -> None:
|
||||
db = SessionLocal()
|
||||
try:
|
||||
user = User(
|
||||
phone="18800009991",
|
||||
username="29999999991",
|
||||
register_channel="sms",
|
||||
)
|
||||
db.add(user)
|
||||
db.flush()
|
||||
db.add_all([
|
||||
AdEcpmRecord(
|
||||
user_id=user.id, ad_type="reward_video", ad_session_id="scope-prod-business",
|
||||
app_env="prod", our_code_id="prod-reward", ecpm_raw="10000",
|
||||
report_date=REPORT_DATE, created_at=datetime(2040, 2, 3, tzinfo=UTC),
|
||||
),
|
||||
AdEcpmRecord(
|
||||
user_id=user.id, ad_type="draw", ad_session_id="scope-prod-demo",
|
||||
app_env="prod", our_code_id="prod-demo", ecpm_raw="20000",
|
||||
report_date=REPORT_DATE, created_at=datetime(2040, 2, 3, tzinfo=UTC),
|
||||
),
|
||||
AdEcpmRecord(
|
||||
user_id=user.id, ad_type="draw", ad_session_id="scope-prod-known-business",
|
||||
app_env="prod", our_code_id="104098712", ecpm_raw="40000",
|
||||
report_date=REPORT_DATE, created_at=datetime(2040, 2, 3, tzinfo=UTC),
|
||||
),
|
||||
AdEcpmRecord(
|
||||
user_id=user.id, ad_type="reward_video", ad_session_id="scope-test-business",
|
||||
app_env="test", our_code_id="104127529", ecpm_raw="30000",
|
||||
report_date=REPORT_DATE, created_at=datetime(2040, 2, 3, tzinfo=UTC),
|
||||
),
|
||||
AdPangleDailyRevenue(
|
||||
report_date=REPORT_DATE, app_env="prod", our_code_id="prod-reward",
|
||||
adn="", revenue_yuan=1.5, api_revenue_yuan=1.2, impressions=10,
|
||||
),
|
||||
AdPangleDailyRevenue(
|
||||
report_date=REPORT_DATE, app_env="prod", our_code_id="prod-demo",
|
||||
adn="", revenue_yuan=8.0, api_revenue_yuan=7.0, impressions=40,
|
||||
),
|
||||
AdPangleDailyRevenue(
|
||||
report_date=REPORT_DATE, app_env="prod", our_code_id="104098712",
|
||||
adn="", revenue_yuan=2.5, api_revenue_yuan=2.0, impressions=20,
|
||||
),
|
||||
AdPangleDailyRevenue(
|
||||
report_date=REPORT_DATE, app_env="test", our_code_id="104127529",
|
||||
adn="", revenue_yuan=9.0, api_revenue_yuan=8.0, impressions=50,
|
||||
),
|
||||
])
|
||||
db.commit()
|
||||
monkeypatch.setattr(
|
||||
ad_revenue.app_config,
|
||||
"get_ad_config",
|
||||
lambda _db: {
|
||||
"reward_code_id": "prod-reward",
|
||||
"compare_draw_code_id": "prod-draw",
|
||||
"coupon_draw_code_id": "prod-draw",
|
||||
},
|
||||
)
|
||||
|
||||
business = ad_revenue.ad_revenue_report(
|
||||
db,
|
||||
date_from=REPORT_DATE,
|
||||
date_to=REPORT_DATE,
|
||||
app_env="prod",
|
||||
revenue_scope="business",
|
||||
)
|
||||
assert business["total_impressions"] == 2
|
||||
assert business["total_revenue_yuan"] == 0.5
|
||||
assert business["total_pangle_revenue_yuan"] == 4.0
|
||||
assert business["total_pangle_api_revenue_yuan"] == 3.2
|
||||
|
||||
all_codes = ad_revenue.ad_revenue_report(
|
||||
db,
|
||||
date_from=REPORT_DATE,
|
||||
date_to=REPORT_DATE,
|
||||
app_env="prod",
|
||||
revenue_scope="all",
|
||||
)
|
||||
assert all_codes["total_impressions"] == 3
|
||||
assert all_codes["total_revenue_yuan"] == 0.7
|
||||
assert all_codes["total_pangle_revenue_yuan"] == 12.0
|
||||
assert all_codes["total_pangle_api_revenue_yuan"] == 10.2
|
||||
|
||||
test_business = ad_revenue.ad_revenue_report(
|
||||
db,
|
||||
date_from=REPORT_DATE,
|
||||
date_to=REPORT_DATE,
|
||||
app_env="test",
|
||||
revenue_scope="business",
|
||||
)
|
||||
assert test_business["total_impressions"] == 1
|
||||
assert test_business["total_revenue_yuan"] == 0.3
|
||||
assert test_business["total_pangle_revenue_yuan"] == 9.0
|
||||
assert test_business["total_pangle_api_revenue_yuan"] == 8.0
|
||||
|
||||
all_env_business = ad_revenue.ad_revenue_report(
|
||||
db,
|
||||
date_from=REPORT_DATE,
|
||||
date_to=REPORT_DATE,
|
||||
app_env=None,
|
||||
revenue_scope="business",
|
||||
)
|
||||
assert all_env_business["total_impressions"] == 3
|
||||
assert all_env_business["total_revenue_yuan"] == 0.8
|
||||
assert all_env_business["total_pangle_revenue_yuan"] == 13.0
|
||||
assert all_env_business["total_pangle_api_revenue_yuan"] == 11.2
|
||||
finally:
|
||||
db.rollback()
|
||||
db.execute(delete(AdPangleDailyRevenue).where(AdPangleDailyRevenue.report_date == REPORT_DATE))
|
||||
db.execute(delete(AdEcpmRecord).where(AdEcpmRecord.report_date == REPORT_DATE))
|
||||
db.execute(delete(User).where(User.phone == "18800009991"))
|
||||
db.commit()
|
||||
db.close()
|
||||
@@ -222,6 +222,210 @@ def test_stats_compare_count_and_saved(client) -> None:
|
||||
assert s2["compare_count"] == 2 # 仍 2(failed 不计)
|
||||
|
||||
|
||||
def test_records_ordered_flag(client) -> None:
|
||||
"""「已下单」店级标记:店名命中该用户 source='compare' 的下单记录才 True。
|
||||
|
||||
覆盖 list_records 只按**本页店名**反查 savings 的写法(原来是把该用户全部下单店名捞回内存
|
||||
再取交集,随下单量线性变慢)——两种写法结果必须一致,故这里按店名逐条断言。
|
||||
"""
|
||||
token = _login(client, "13800002010")
|
||||
|
||||
# 两条比价记录:一条海底捞(稍后会有对应下单),一条没下过单的店
|
||||
client.post("/api/v1/compare/record", json=_food_payload("ord-1"), headers=_auth(token))
|
||||
other = _food_payload("ord-2")
|
||||
other["store_name"] = "没下过单的店"
|
||||
client.post("/api/v1/compare/record", json=other, headers=_auth(token))
|
||||
|
||||
# 下单前:两条都不该带「已下单」
|
||||
items = client.get("/api/v1/compare/records", headers=_auth(token)).json()["items"]
|
||||
assert {it["store_name"]: it["ordered"] for it in items} == {
|
||||
"海底捞(朝阳店)": False,
|
||||
"没下过单的店": False,
|
||||
}
|
||||
|
||||
# 对海底捞真实下单一笔(order/report 写 source='compare' 的 savings_record)
|
||||
r = client.post(
|
||||
"/api/v1/order/report",
|
||||
json={
|
||||
"client_event_id": "evt-ordered-flag",
|
||||
"platform": "美团",
|
||||
"platform_package": "com.sankuai.meituan",
|
||||
"pay_channel": "wechat",
|
||||
"compared_price_cents": 12350,
|
||||
"paid_amount_cents": 12350,
|
||||
"shop_name": "海底捞(朝阳店)",
|
||||
"original_price_cents": 12850,
|
||||
},
|
||||
headers=_auth(token),
|
||||
)
|
||||
assert r.status_code == 200, r.text
|
||||
|
||||
# 下单后:只有同店名那条翻成 True,另一条不受影响
|
||||
items = client.get("/api/v1/compare/records", headers=_auth(token)).json()["items"]
|
||||
assert {it["store_name"]: it["ordered"] for it in items} == {
|
||||
"海底捞(朝阳店)": True,
|
||||
"没下过单的店": False,
|
||||
}
|
||||
|
||||
# 别人的下单不该影响本人标记(_ordered_shop_names 按 user_id 过滤)
|
||||
token_b = _login(client, "13800002011")
|
||||
client.post("/api/v1/compare/record", json=_food_payload("ord-b"), headers=_auth(token_b))
|
||||
items_b = client.get("/api/v1/compare/records", headers=_auth(token_b)).json()["items"]
|
||||
assert [it["ordered"] for it in items_b] == [False]
|
||||
|
||||
|
||||
def test_records_list_omits_raw_payload(client) -> None:
|
||||
"""列表出参不含 raw_payload(仓库层 defer 掉了重型 JSON 列);详情接口照常返回。
|
||||
|
||||
defer 的列一旦被 ORM 实例读到会触发逐行懒加载(N+1),而列表 schema 本就不该带 raw_payload
|
||||
—— 这条同时守住「列表不泄露上报体全量」和「没人不小心把它加回出参」。
|
||||
"""
|
||||
token = _login(client, "13800002012")
|
||||
rid = client.post(
|
||||
"/api/v1/compare/record", json=_food_payload("no-raw"), headers=_auth(token)
|
||||
).json()["id"]
|
||||
|
||||
items = client.get("/api/v1/compare/records", headers=_auth(token)).json()["items"]
|
||||
assert len(items) == 1
|
||||
assert "raw_payload" not in items[0]
|
||||
# 概要字段照常齐全(defer 没误伤列表要用的列)
|
||||
assert items[0]["store_name"] == "海底捞(朝阳店)"
|
||||
assert items[0]["best_platform_id"] == "meituan"
|
||||
assert items[0]["comparison_results"] and items[0]["items"]
|
||||
|
||||
# 详情不 defer:raw_payload 全量还在
|
||||
d = client.get(f"/api/v1/compare/records/{rid}", headers=_auth(token)).json()
|
||||
assert d["raw_payload"]["trace_id"] == "no-raw"
|
||||
|
||||
|
||||
def test_records_ordered_filter(client) -> None:
|
||||
"""ordered=true 只出「已下单」的记录,且过滤结果自身能翻页。
|
||||
|
||||
这个筛选必须在服务端做:客户端早先是对「已经拉回来的那一页」做 filter,分页之后一页里
|
||||
很可能一条已下单都没有 ——「已下单」tab 就会看着像空的,得手动翻很多页才蹦出一条。
|
||||
"""
|
||||
token = _login(client, "13800002013")
|
||||
|
||||
# 3 条「下过单的店」+ 2 条没下过单的店,交错写入,确保过滤不是靠顺序碰巧对上
|
||||
for i in range(3):
|
||||
p = _food_payload(f"of-ordered-{i}")
|
||||
p["store_name"] = "下过单的店"
|
||||
client.post("/api/v1/compare/record", json=p, headers=_auth(token))
|
||||
if i < 2:
|
||||
q = _food_payload(f"of-plain-{i}")
|
||||
q["store_name"] = "没下过单的店"
|
||||
client.post("/api/v1/compare/record", json=q, headers=_auth(token))
|
||||
|
||||
client.post(
|
||||
"/api/v1/order/report",
|
||||
json={
|
||||
"client_event_id": "evt-ordered-filter",
|
||||
"platform": "美团",
|
||||
"platform_package": "com.sankuai.meituan",
|
||||
"pay_channel": "wechat",
|
||||
"compared_price_cents": 12350,
|
||||
"paid_amount_cents": 12350,
|
||||
"shop_name": "下过单的店",
|
||||
"original_price_cents": 12850,
|
||||
},
|
||||
headers=_auth(token),
|
||||
)
|
||||
|
||||
# 不传 ordered:5 条全出(「全部记录」tab 口径不变)
|
||||
assert len(client.get("/api/v1/compare/records", headers=_auth(token)).json()["items"]) == 5
|
||||
|
||||
# ordered=true:只出那 3 条,且每条都自带 ordered=True
|
||||
page = client.get("/api/v1/compare/records?ordered=true", headers=_auth(token)).json()
|
||||
assert [it["store_name"] for it in page["items"]] == ["下过单的店"] * 3
|
||||
assert all(it["ordered"] for it in page["items"])
|
||||
assert page["next_cursor"] is None
|
||||
|
||||
# 游标只在「已下单」集合内走 —— 不会把没下单的记录算进一页的 limit 里
|
||||
p1 = client.get(
|
||||
"/api/v1/compare/records?ordered=true&limit=2", headers=_auth(token)
|
||||
).json()
|
||||
assert len(p1["items"]) == 2
|
||||
assert p1["next_cursor"] is not None
|
||||
p2 = client.get(
|
||||
f"/api/v1/compare/records?ordered=true&limit=2&cursor={p1['next_cursor']}",
|
||||
headers=_auth(token),
|
||||
).json()
|
||||
assert [it["store_name"] for it in p2["items"]] == ["下过单的店"]
|
||||
# 两页不重叠,合起来正好 3 条
|
||||
assert len({it["id"] for it in p1["items"] + p2["items"]}) == 3
|
||||
|
||||
# 别人的下单不该让本人记录进「已下单」
|
||||
token_b = _login(client, "13800002014")
|
||||
pb = _food_payload("of-b")
|
||||
pb["store_name"] = "下过单的店"
|
||||
client.post("/api/v1/compare/record", json=pb, headers=_auth(token_b))
|
||||
assert client.get(
|
||||
"/api/v1/compare/records?ordered=true", headers=_auth(token_b)
|
||||
).json()["items"] == []
|
||||
|
||||
|
||||
def test_records_keyword_search(client) -> None:
|
||||
"""keyword 按店名 / 菜名模糊搜(忽略大小写),LIKE 通配符只当字面量;搜索结果也能翻页。
|
||||
|
||||
菜名走写路径派生的 product_names 文本列 —— items 是 JSON,SQLite 下中文被 ensure_ascii
|
||||
转义,直接 LIKE 搜不到。
|
||||
"""
|
||||
token = _login(client, "13800002015")
|
||||
|
||||
b = _food_payload("kw-b")
|
||||
b["store_name"] = "Pizza Hut"
|
||||
b["items"] = [{"name": "榴莲比萨", "qty": 1}]
|
||||
client.post("/api/v1/compare/record", json=b, headers=_auth(token))
|
||||
|
||||
c = _food_payload("kw-c")
|
||||
c["store_name"] = "100%纯牛肉汉堡"
|
||||
c["items"] = [{"name": "双层牛肉堡", "qty": 1}]
|
||||
client.post("/api/v1/compare/record", json=c, headers=_auth(token))
|
||||
|
||||
# 默认 payload 的店名是「海底捞(朝阳店)」
|
||||
client.post("/api/v1/compare/record", json=_food_payload("kw-a"), headers=_auth(token))
|
||||
|
||||
def _search(kw: str, **extra) -> list[str]:
|
||||
r = client.get(
|
||||
"/api/v1/compare/records",
|
||||
params={"keyword": kw, **extra},
|
||||
headers=_auth(token),
|
||||
)
|
||||
assert r.status_code == 200, r.text
|
||||
return [it["store_name"] for it in r.json()["items"]]
|
||||
|
||||
assert _search("海底捞") == ["海底捞(朝阳店)"] # 店名命中
|
||||
assert _search("榴莲") == ["Pizza Hut"] # 菜名命中(product_names)
|
||||
assert _search("pizza") == ["Pizza Hut"] # 忽略大小写
|
||||
assert _search("PIZZA") == ["Pizza Hut"]
|
||||
assert _search("不存在的店") == [] # 没命中就是空
|
||||
# 通配符只当普通字符:搜 % 不该把整表拉回来,搜 _ 也不该匹配任意单字符
|
||||
assert _search("%") == ["100%纯牛肉汉堡"]
|
||||
assert _search("_") == []
|
||||
# 纯空白等同不传 → 不过滤
|
||||
assert len(_search(" ")) == 3
|
||||
|
||||
# 搜索结果自身可翻页
|
||||
for i in range(3):
|
||||
p = _food_payload(f"kw-page-{i}")
|
||||
p["store_name"] = "连锁烤鱼店"
|
||||
client.post("/api/v1/compare/record", json=p, headers=_auth(token))
|
||||
p1 = client.get(
|
||||
"/api/v1/compare/records",
|
||||
params={"keyword": "烤鱼", "limit": 2},
|
||||
headers=_auth(token),
|
||||
).json()
|
||||
assert len(p1["items"]) == 2
|
||||
assert p1["next_cursor"] is not None
|
||||
p2 = client.get(
|
||||
"/api/v1/compare/records",
|
||||
params={"keyword": "烤鱼", "limit": 2, "cursor": p1["next_cursor"]},
|
||||
headers=_auth(token),
|
||||
).json()
|
||||
assert [it["store_name"] for it in p2["items"]] == ["连锁烤鱼店"]
|
||||
assert len({it["id"] for it in p1["items"] + p2["items"]}) == 3
|
||||
|
||||
|
||||
def test_requires_auth(client) -> None:
|
||||
"""不带 token 统一 401。"""
|
||||
assert client.post("/api/v1/compare/record", json={"trace_id": "t"}).status_code == 401
|
||||
|
||||
Reference in New Issue
Block a user