# BD_Manual：dws.biz_date() 函数与物化视图营业日重建

> 变更类型：新增函数 + 重建物化视图
> Schema：`dws`
> 迁移脚本 1：`db/etl_feiqiu/migrations/2026-02-27__add_biz_date_function.sql`
> 迁移脚本 2：`db/etl_feiqiu/migrations/2026-02-27__rebuild_mv_with_biz_date.sql`
> DDL 基线：`docs/database/ddl/etl_feiqiu__dws.sql`
> 关联需求：Requirements 9.1, 9.2, 9.3, 9.4

---

## 1. 变更说明

### 1.1 新增函数：`dws.biz_date(timestamptz, int)`

| 属性 | 值 |
|------|-----|
| 函数签名 | `dws.biz_date(ts timestamptz, cutoff_hour int DEFAULT 8) RETURNS date` |
| 语言 | SQL |
| 特性 | `IMMUTABLE PARALLEL SAFE` |
| 逻辑 | `(ts - make_interval(hours => cutoff_hour))::date` |
| 等价 Python | `neozqyy_shared.datetime_utils.business_date()` |

将时间戳减去 `cutoff_hour` 小时后取日期，实现营业日归属。默认 `cutoff_hour=8`，即 08:00 前的时间戳归属前一天。

### 1.2 重建物化视图（8 个）

将 `CURRENT_DATE` 替换为 `dws.biz_date(NOW())`，使物化视图的数据范围与 DWS 任务的营业日口径一致。

| 物化视图 | 原条件 | 新条件 |
|---------|--------|--------|
| `mv_dws_assistant_daily_detail_l1` | `stat_date >= (CURRENT_DATE - '1 day')` | `stat_date >= (dws.biz_date(NOW()) - '1 day')` |
| `mv_dws_assistant_daily_detail_l2` | `stat_date >= (CURRENT_DATE - '30 days')` | `stat_date >= (dws.biz_date(NOW()) - '30 days')` |
| `mv_dws_assistant_daily_detail_l3` | `stat_date >= (CURRENT_DATE - '90 days')` | `stat_date >= (dws.biz_date(NOW()) - '90 days')` |
| `mv_dws_assistant_daily_detail_l4` | `date_trunc('month', CURRENT_DATE) ± 6 mons` | `date_trunc('month', dws.biz_date(NOW())) ± 6 mons` |
| `mv_dws_finance_daily_summary_l1` | `stat_date >= (CURRENT_DATE - '1 day')` | `stat_date >= (dws.biz_date(NOW()) - '1 day')` |
| `mv_dws_finance_daily_summary_l2` | `stat_date >= (CURRENT_DATE - '30 days')` | `stat_date >= (dws.biz_date(NOW()) - '30 days')` |
| `mv_dws_finance_daily_summary_l3` | `stat_date >= (CURRENT_DATE - '90 days')` | `stat_date >= (dws.biz_date(NOW()) - '90 days')` |
| `mv_dws_finance_daily_summary_l4` | `date_trunc('month', CURRENT_DATE) ± 6 mons` | `date_trunc('month', dws.biz_date(NOW())) ± 6 mons` |

索引在重建后重新创建，结构不变。

---

## 2. 兼容性说明

| 影响范围 | 说明 |
|---------|------|
| ETL 任务 | `MvRefreshTask`（`DWS_MV_REFRESH_*`）执行 `REFRESH MATERIALIZED VIEW` 不受影响，视图定义变更对刷新逻辑透明 |
| 后端 API | 无直接影响。后端通过 DWS 表查询，物化视图仅用于加速查询 |
| 管理后台 | 无影响。前端不直接查询物化视图 |
| 小程序 | 无影响 |
| 字段映射 | 物化视图列结构不变（`SELECT *`），仅 WHERE 条件变更 |
| `biz_date()` 函数 | 标记为 `IMMUTABLE`，可安全用于索引表达式和物化视图定义 |

---

## 3. 回滚策略

### 3.1 回滚函数

```sql
DROP FUNCTION IF EXISTS dws.biz_date(timestamptz, int);
```

> 注意：需先回滚物化视图（3.2），否则依赖此函数的视图定义会阻止删除。

### 3.2 回滚物化视图（恢复自然日口径）

使用 `scripts/migrate/migrate_finalize.py` 中的原始定义重建：

```sql
BEGIN;

DROP MATERIALIZED VIEW IF EXISTS dws.mv_dws_assistant_daily_detail_l1;
DROP MATERIALIZED VIEW IF EXISTS dws.mv_dws_assistant_daily_detail_l2;
DROP MATERIALIZED VIEW IF EXISTS dws.mv_dws_assistant_daily_detail_l3;
DROP MATERIALIZED VIEW IF EXISTS dws.mv_dws_assistant_daily_detail_l4;
DROP MATERIALIZED VIEW IF EXISTS dws.mv_dws_finance_daily_summary_l1;
DROP MATERIALIZED VIEW IF EXISTS dws.mv_dws_finance_daily_summary_l2;
DROP MATERIALIZED VIEW IF EXISTS dws.mv_dws_finance_daily_summary_l3;
DROP MATERIALIZED VIEW IF EXISTS dws.mv_dws_finance_daily_summary_l4;

-- 用原始定义重建（CURRENT_DATE 版本）
CREATE MATERIALIZED VIEW dws.mv_dws_assistant_daily_detail_l1 AS
    SELECT * FROM dws.dws_assistant_daily_detail
    WHERE stat_date >= (CURRENT_DATE - '1 day'::interval) WITH DATA;
CREATE MATERIALIZED VIEW dws.mv_dws_assistant_daily_detail_l2 AS
    SELECT * FROM dws.dws_assistant_daily_detail
    WHERE stat_date >= (CURRENT_DATE - '30 days'::interval) WITH DATA;
CREATE MATERIALIZED VIEW dws.mv_dws_assistant_daily_detail_l3 AS
    SELECT * FROM dws.dws_assistant_daily_detail
    WHERE stat_date >= (CURRENT_DATE - '90 days'::interval) WITH DATA;
CREATE MATERIALIZED VIEW dws.mv_dws_assistant_daily_detail_l4 AS
    SELECT * FROM dws.dws_assistant_daily_detail
    WHERE stat_date >= (date_trunc('month', CURRENT_DATE::timestamptz) - '6 mons'::interval)
      AND stat_date < date_trunc('month', CURRENT_DATE::timestamptz) WITH DATA;

CREATE MATERIALIZED VIEW dws.mv_dws_finance_daily_summary_l1 AS
    SELECT * FROM dws.dws_finance_daily_summary
    WHERE stat_date >= (CURRENT_DATE - '1 day'::interval) WITH DATA;
CREATE MATERIALIZED VIEW dws.mv_dws_finance_daily_summary_l2 AS
    SELECT * FROM dws.dws_finance_daily_summary
    WHERE stat_date >= (CURRENT_DATE - '30 days'::interval) WITH DATA;
CREATE MATERIALIZED VIEW dws.mv_dws_finance_daily_summary_l3 AS
    SELECT * FROM dws.dws_finance_daily_summary
    WHERE stat_date >= (CURRENT_DATE - '90 days'::interval) WITH DATA;
CREATE MATERIALIZED VIEW dws.mv_dws_finance_daily_summary_l4 AS
    SELECT * FROM dws.dws_finance_daily_summary
    WHERE stat_date >= (date_trunc('month', CURRENT_DATE::timestamptz) - '6 mons'::interval)
      AND stat_date < date_trunc('month', CURRENT_DATE::timestamptz) WITH DATA;

-- 重建索引
CREATE INDEX idx_mv_assistant_daily_l1 ON dws.mv_dws_assistant_daily_detail_l1 USING btree (site_id, stat_date, assistant_id);
CREATE INDEX idx_mv_assistant_daily_l2 ON dws.mv_dws_assistant_daily_detail_l2 USING btree (site_id, stat_date, assistant_id);
CREATE INDEX idx_mv_assistant_daily_l3 ON dws.mv_dws_assistant_daily_detail_l3 USING btree (site_id, stat_date, assistant_id);
CREATE INDEX idx_mv_assistant_daily_l4 ON dws.mv_dws_assistant_daily_detail_l4 USING btree (site_id, stat_date, assistant_id);
CREATE INDEX idx_mv_finance_daily_l1 ON dws.mv_dws_finance_daily_summary_l1 USING btree (site_id, stat_date);
CREATE INDEX idx_mv_finance_daily_l2 ON dws.mv_dws_finance_daily_summary_l2 USING btree (site_id, stat_date);
CREATE INDEX idx_mv_finance_daily_l3 ON dws.mv_dws_finance_daily_summary_l3 USING btree (site_id, stat_date);
CREATE INDEX idx_mv_finance_daily_l4 ON dws.mv_dws_finance_daily_summary_l4 USING btree (site_id, stat_date);

COMMIT;
```

---

## 4. 验证 SQL

### 4.1 确认 `biz_date()` 函数存在且行为正确

```sql
-- 08:00 前归属前一天
SELECT dws.biz_date('2026-01-15 07:59:59+08'::timestamptz) AS should_be_0114;
-- 预期：2026-01-14

-- 08:00 起归属当天
SELECT dws.biz_date('2026-01-15 08:00:00+08'::timestamptz) AS should_be_0115;
-- 预期：2026-01-15

-- 月末边界
SELECT dws.biz_date('2026-02-01 07:00:00+08'::timestamptz, 8) AS should_be_0131;
-- 预期：2026-01-31
```

### 4.2 确认 8 个物化视图已重建且定义包含 `biz_date`

```sql
SELECT matviewname, definition LIKE '%biz_date%' AS uses_biz_date
FROM pg_matviews
WHERE schemaname = 'dws'
  AND matviewname LIKE 'mv_dws_%'
ORDER BY matviewname;
-- 预期：8 行，uses_biz_date 全部为 true
```

### 4.3 确认物化视图索引完整

```sql
SELECT indexname, tablename
FROM pg_indexes
WHERE schemaname = 'dws'
  AND tablename LIKE 'mv_dws_%'
ORDER BY indexname;
-- 预期：8 个索引（assistant_daily l1-l4 + finance_daily l1-l4）
```

### 4.4 确认物化视图有数据（刷新后）

```sql
SELECT 'assistant_l1' AS mv, COUNT(*) FROM dws.mv_dws_assistant_daily_detail_l1
UNION ALL
SELECT 'assistant_l2', COUNT(*) FROM dws.mv_dws_assistant_daily_detail_l2
UNION ALL
SELECT 'assistant_l3', COUNT(*) FROM dws.mv_dws_assistant_daily_detail_l3
UNION ALL
SELECT 'assistant_l4', COUNT(*) FROM dws.mv_dws_assistant_daily_detail_l4
UNION ALL
SELECT 'finance_l1', COUNT(*) FROM dws.mv_dws_finance_daily_summary_l1
UNION ALL
SELECT 'finance_l2', COUNT(*) FROM dws.mv_dws_finance_daily_summary_l2
UNION ALL
SELECT 'finance_l3', COUNT(*) FROM dws.mv_dws_finance_daily_summary_l3
UNION ALL
SELECT 'finance_l4', COUNT(*) FROM dws.mv_dws_finance_daily_summary_l4;
```
