13 Raw Query：用 SQL 完成经营报表

当查询包含复杂聚合、多层 JOIN、窗口思想或 MySQL 日期函数时，Raw SQL 往往比堆叠 Sequelize Model API 更直观。本章要求使用 sequelize.query() 完成至少五个报表接口。

所有接口必须经过 authenticateAdmin。核心 SQL 留作 TODO，不提供完整答案。

1. Raw Query 的安全调用骨架

import { QueryTypes } from "sequelize";

export interface DailyRevenueRow {
  revenueDate: string;
  paymentCount: number;
  totalAmount: string;
}

const rows = await sequelize.query<DailyRevenueRow>(
  `
    SELECT
      /* TODO：字段 */
    FROM payment AS p
    /* TODO：JOIN / WHERE / GROUP BY / ORDER BY */
  `,
  {
    type: QueryTypes.SELECT,
    replacements: {
      startAt: options.startAt,
      endAtExclusive: options.endAtExclusive,
    },
  },
);

这里的泛型：

sequelize.query<DailyRevenueRow>(...)

描述每一行的 TypeScript 形状。它不会在运行时验证数据库结果，也不会把错误的 SQL 别名自动修正成 DTO 字段。

QueryTypes.SELECT 告诉 Sequelize 这是 SELECT，并让返回值直接表现为行数组。若不指定，返回结构通常还包含 metadata。

2. replacements 与 bind

replacements

命名替换示意：

await sequelize.query<SomeRow>(
  `SELECT ... WHERE payment_date >= :startAt`,
  {
    replacements: { startAt },
    type: QueryTypes.SELECT,
  },
);

bind

绑定参数示意：

await sequelize.query<SomeRow>(
  `SELECT ... WHERE payment_date >= $startAt`,
  {
    bind: { startAt },
    type: QueryTypes.SELECT,
  },
);

练习时可以各使用一次并对照 Sequelize/MySQL 驱动日志。不要在同一条查询中混用两种占位方式。

无论选择哪种，都不要这样拼接外部输入：

const sql = `SELECT * FROM payment WHERE customer_id = ${req.query.customerId}`;

这会产生 SQL 注入风险，也会让字符串、日期和 null 的转义难以维护。

3. 不能参数化的结构：使用白名单

SQL 参数通常用于“值”，不能把列名或 ASC / DESC 当成普通绑定值：

ORDER BY :sort :order

不要期待它安全地变成列标识符。正确思路是代码白名单：

const sortColumnMap = {
  revenueDate: "revenue_date",
  totalAmount: "total_amount",
} as const;

// TODO：校验 key 后，从固定映射选择片段。
// order 也只能从固定的 ASC / DESC 中选择。

LIMIT 也应先校验为有限范围整数。即使某个驱动或语句对 LIMIT 绑定支持不一致，也不能直接拼接未经验证的字符串。

4. 统一日期契约

所有报表接受：

startDate=2026-01-01
endDate=2026-01-31

用户认为包含 1 月 31 日全天，Service 转换为：

startAt = 2026-01-01 00:00:00
endAtExclusive = 2026-02-01 00:00:00

SQL 使用：

column >= :startAt
AND column < :endAtExclusive

还要明确应用、MySQL 与 Sequelize 的时区约定，否则“午夜”可能不是同一个瞬间。

5. Router 与共同类型

const router = Router();
router.use(authenticateAdmin);

router.get("/reports/daily-revenue", reportDateValidation, validateRequest, dailyRevenueController);
router.get("/reports/top-films", topFilmsValidation, validateRequest, topFilmsController);
router.get("/reports/overdue-rentals", overdueValidation, validateRequest, overdueRentalsController);
router.get("/reports/customer-spending", customerSpendingValidation, validateRequest, customerSpendingController);
router.get("/reports/category-revenue", categoryRevenueValidation, validateRequest, categoryRevenueController);
router.get("/reports/store-performance", storePerformanceValidation, validateRequest, storePerformanceController);

export interface DateRangeReportQuery {
  startDate: string;
  endDate: string;
  storeId?: string;
}

export interface DateRangeReportOptions {
  startAt: Date;
  endAtExclusive: Date;
  storeId?: number;
}

Controller 负责把经过校验的 HTTP 字符串转成 DateRangeReportOptions；Service 不再接收 string | undefined 的原始 query。

6. 报表一：按日营业额

GET /api/sakila/reports/daily-revenue?startDate=2026-01-01&endDate=2026-01-31&storeId=1

export interface DailyRevenueRow {
  revenueDate: string;
  paymentCount: number;
  totalAmount: string;
}

export async function findDailyRevenue(
  options: DateRangeReportOptions,
): Promise<DailyRevenueRow[]> {
  // TODO：sequelize.query<DailyRevenueRow>()。
  // TODO：QueryTypes.SELECT。
  // TODO：payment_date 左闭右开。
  // TODO：可选 storeId 需要通过 staff 或相关关系筛选。
  // TODO：按自然日 GROUP BY 和稳定排序。
  throw new Error("TODO");
}

提示：

DATE(payment_date) 是 MySQL 常用日期函数，不是通用 SQL 保证。
WHERE 中不要对索引列套 DATE(payment_date) 再比较范围；对原列使用范围条件更利于索引。
SUM(amount) 仍应按 string 处理。

7. 报表二：出租电影排行榜

GET /api/sakila/reports/top-films?startDate=2026-01-01&endDate=2026-01-31&storeId=1&limit=10

export interface TopFilmRow {
  filmId: number;
  title: string;
  rentalCount: number;
  revenueAmount: string;
}

export interface TopFilmsOptions extends DateRangeReportOptions {
  limit: number;
}

export async function findTopFilms(
  options: TopFilmsOptions,
): Promise<TopFilmRow[]> {
  // TODO：连接 rental -> inventory -> film。
  // TODO：按电影聚合出租次数。
  // TODO：收入应如何关联 payment，避免重复累计？
  // TODO：rentalCount 相同时增加 filmId 等稳定排序。
  // TODO：安全处理 limit。
  throw new Error("TODO");
}

关键问题：一条 rental 可能对应多条 payment。直接 JOIN 后 COUNT(*) 可能统计的是 payment 行数，而不是 rental 数。你需要先确定统计粒度，再选择 COUNT(DISTINCT rental_id) 或子查询等方法。

8. 报表三：逾期未归还列表

业务规则示例：

应归还时间 = rental_date + film.rental_duration 天
return_date IS NULL 且当前时间超过应归还时间，即为逾期

export interface OverdueRentalRow {
  rentalId: number;
  customerId: number;
  customerName: string;
  filmId: number;
  filmTitle: string;
  rentalDate: string;
  dueAt: string;
  overdueDays: number;
}

export async function findOverdueRentals(
  storeId: number | undefined,
  now: Date,
): Promise<OverdueRentalRow[]> {
  // TODO：连接 rental、inventory、film、customer。
  // TODO：return_date IS NULL。
  // TODO：使用 DATE_ADD / TIMESTAMPDIFF，或等价方式计算。
  // TODO：通过绑定参数传入 now，便于测试。
  throw new Error("TODO");
}

不要在 SQL 中到处直接使用 NOW() 而让自动测试难以固定时间。将 now 作为参数传入更容易构造边界案例。

9. 报表四：客户消费排行（带分页 total）

GET /api/sakila/reports/customer-spending?startDate=2026-01-01&endDate=2026-01-31&page=1&pageSize=20

export interface CustomerSpendingRow {
  customerId: number;
  customerName: string;
  paymentCount: number;
  totalAmount: string;
  lastPaymentAt: string;
}

export async function findCustomerSpending(
  options: DateRangeReportOptions & PageOptions,
): Promise<PageResult<CustomerSpendingRow>> {
  // TODO 1：list SQL：聚合后排序、LIMIT、OFFSET。
  // TODO 2：count SQL：统计符合条件的客户数量，而不是 payment 行数。
  // TODO 3：两条 SQL 使用一致的筛选条件。
  // TODO 4：totalPages。
  throw new Error("TODO");
}

这里的 total 是“符合条件的客户组数量”，不是 payment 总行数。不要直接复制 list SQL 去掉 LIMIT 后就认为 count 正确。

可选进阶：在一致性要求较高时，list 与 count 两条查询之间发生新支付会怎样？是否需要事务快照？普通后台报表是否值得付出这个成本？

10. 报表五：分类收入统计

export interface CategoryRevenueRow {
  categoryId: number;
  categoryName: string;
  rentalCount: number;
  totalAmount: string;
}

export async function findCategoryRevenue(
  options: DateRangeReportOptions,
): Promise<CategoryRevenueRow[]> {
  // TODO：category -> film_category -> inventory -> rental -> payment。
  // TODO：明确每一层 JOIN 的粒度。
  // TODO：避免多对多关系导致金额重复。
  // TODO：金额降序并增加稳定排序。
  throw new Error("TODO");
}

这道题故意危险：如果业务允许一部电影属于多个分类，那么同一笔收入出现在每个分类中可能是业务预期；但所有分类收入相加会超过总营业额。报表必须在说明中写清“归因口径”，不能只看 SQL 能运行。

11. 报表六：门店经营对比

export interface StorePerformanceRow {
  storeId: number;
  rentalCount: number;
  activeCustomerCount: number;
  revenueAmount: string;
  averagePaymentAmount: string;
}

export async function findStorePerformance(
  options: Omit<DateRangeReportOptions, "storeId">,
): Promise<StorePerformanceRow[]> {
  // TODO：按 store 分组。
  // TODO：避免 rental、customer、payment 同时 JOIN 时的乘法膨胀。
  // TODO：考虑先分别聚合后再 JOIN 聚合结果。
  throw new Error("TODO");
}

这道题用于训练“先聚合、再连接”的思路。把多组一对多明细全部直接 JOIN 后再 GROUP BY store_id，结果通常看似有数字，但很可能全部被放大。

12. Controller 骨架

export async function dailyRevenueController(
  req: Request<
    Record<string, never>,
    ApiResponse<DailyRevenueRow[]>,
    Record<string, never>,
    DateRangeReportQuery
  >,
  res: Response<ApiResponse<DailyRevenueRow[]>>,
): Promise<void> {
  // TODO 1：把日期字符串转换成左闭右开范围。
  // TODO 2：转换可选 storeId。
  // TODO 3：调用 findDailyRevenue()。
  // TODO 4：返回 code="OK"。
}

其他 Controller 使用相同边界：只解析 HTTP 输入、调用 Service、组织响应，不在 Controller 中拼 SQL。

13. Raw 结果的运行时边界

下面的泛型不会验证 SQL 返回值：

sequelize.query<TopFilmRow>(sql, options);

如果 SQL 写成：

SELECT SUM(p.amount) AS total_amount

而 TypeScript 期待：

totalAmount: string;

除非使用 Sequelize 的字段映射机制，否则对象中可能实际是 total_amount。你需要让 SQL alias 与 DTO 对齐，或增加显式映射函数。

另外，MySQL 的 COUNT()、SUM() 等返回类型要以实际驱动结果为准。不要因为 TypeScript 写了 number，就认为运行时一定是 number。

14. SQL 注入测试

主动测试以下输入：

storeId=1 OR 1=1
sort=totalAmount DESC; DROP TABLE payment
limit=10; SELECT SLEEP(10)

预期：

数字参数在 validator 层被拒绝，或作为一个绑定值处理。
排序只允许固定白名单。
limit 必须是范围受限的整数。
日志可以记录非法请求，但不要把完整 JWT 写入日志。

15. 性能检查

对至少两个报表使用：

EXPLAIN

观察：

type
possible_keys
key
rows
Extra

重点思考：

日期范围是否能使用索引。
WHERE 对日期列使用函数是否阻止索引范围查找。
GROUP BY / ORDER BY 是否出现临时表或 filesort。
是否因为错误 JOIN 读取远多于预期的行。

本练习以学习为主，不要求看到 Using filesort 就盲目新增索引。索引应服务于真实高频查询，并评估写入成本。

16. 错误码与日志

建议业务码：

INVALID_DATE_RANGE
DATE_RANGE_TOO_LARGE
STORE_NOT_FOUND
INVALID_REPORT_SORT
REPORT_QUERY_FAILED

日期范围建议设置上限，例如最多 366 天，防止管理员误操作触发高负载查询。

由于所有业务结果 HTTP 都是 200，日志至少记录：

requestId
adminId
reportName
businessCode
dateRange
duration
rowCount

未知 SQL 错误的完整信息写服务器日志，客户端只收到安全的通用消息。

17. curl 验收

TOKEN="替换为 accessToken"

curl -sS \
  -H "Authorization: Bearer $TOKEN" \
  "http://127.0.0.1:8080/api/sakila/reports/daily-revenue?startDate=2005-05-01&endDate=2005-08-31&storeId=1"

curl -sS \
  -H "Authorization: Bearer $TOKEN" \
  "http://127.0.0.1:8080/api/sakila/reports/top-films?startDate=2005-05-01&endDate=2005-08-31&limit=10"

curl -sS \
  -H "Authorization: Bearer $TOKEN" \
  "http://127.0.0.1:8080/api/sakila/reports/overdue-rentals?storeId=1"

curl -sS \
  -H "Authorization: Bearer $TOKEN" \
  "http://127.0.0.1:8080/api/sakila/reports/customer-spending?startDate=2005-05-01&endDate=2005-08-31&page=1&pageSize=20"

curl -sS \
  -H "Authorization: Bearer $TOKEN" \
  "http://127.0.0.1:8080/api/sakila/reports/category-revenue?startDate=2005-05-01&endDate=2005-08-31"

验收不能只看接口返回 200。必须检查 JSON code、结果行数、金额是否与独立 SQL 抽样一致，并尝试非法日期范围和注入字符串。

18. 常见错误

使用模板字符串拼接 req.query。
误以为 sequelize.query<Row>() 提供运行时校验。
未指定 QueryTypes.SELECT，误解返回结构。
试图绑定 ORDER BY 列名，或直接拼接未经白名单验证的列名。
日期范围对列使用 DATE(column)，导致索引难以使用。
COUNT(*) 统计成支付行数，而业务需要租赁数或客户数。
多表 JOIN 乘法膨胀，金额看似合理但实际放大。
直接把 DECIMAL 聚合结果转为 number。
list SQL 和 count SQL 使用了不同筛选条件。
报表没有日期上限，单次查询覆盖全部历史。

19. 练习题

QueryTypes.SELECT 对返回结构有什么影响？
sequelize.query<Row>() 能否验证数据库真实返回类型？
replacements 和 bind 各解决什么问题？
为什么 ORDER BY 列名不能当成普通值参数绑定？
客户消费排行的 total 为什么不是 payment 行数？
Top Film 同时 JOIN payment 时，为什么 rentalCount 可能放大？
分类收入相加为什么可能超过总营业额？
日期范围为什么在 WHERE 中使用原列比较，而不是 DATE(column)？
什么情况下适合“先聚合再 JOIN”？
如何用注入测试证明接口没有直接拼接外部输入？