freedakgmail
d04e837d9c
- 新增V3.0数据库表结构(12-15)和数据填充脚本(16)
- 新增后端路由: store-grade, enterprise, product, intelligence, alert, scheduler, target
- 新增前端页面: StoreGradePage, EnterprisePage, ProductLifecyclePage, IntelligencePage, AlertManagementPage, DataImportPage, SchedulerManagementPage, TargetManagementPage
- 门店分级: 自动分级算法(达成率40%+毛利率25%+会员15%+风险20%)
- 风险等级: 基于mv_store_risk_rating模型展示风险分布和原因
- 弹窗: 雷达图+详细指标+分级原因+风险原因(primary_issue)
- FilterableTable: filterOptions支持{value,label}对象格式
97 lines
3.3 KiB
SQL
97 lines
3.3 KiB
SQL
-- ============================================================
|
|
-- 15_v3_phase3_tables.sql
|
|
-- Phase 3: IoT + AI决策 + 全链路 + 实时数据 + 战略驾驶舱
|
|
-- ============================================================
|
|
|
|
-- T-310: IoT设备数据
|
|
CREATE TABLE IF NOT EXISTS analytics.v3_iot_device (
|
|
id SERIAL PRIMARY KEY,
|
|
device_code TEXT NOT NULL UNIQUE,
|
|
device_name TEXT,
|
|
device_type TEXT, -- 温控/湿度/秤/扫码枪
|
|
store_code TEXT,
|
|
location TEXT,
|
|
status TEXT DEFAULT 'offline', -- online/offline/alarm
|
|
last_reading JSONB,
|
|
last_reading_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- T-310: IoT温度记录
|
|
CREATE TABLE IF NOT EXISTS analytics.v3_iot_temperature (
|
|
id SERIAL PRIMARY KEY,
|
|
device_code TEXT NOT NULL,
|
|
store_code TEXT,
|
|
temperature NUMERIC(5,2),
|
|
humidity NUMERIC(5,2),
|
|
recorded_at TIMESTAMPTZ NOT NULL,
|
|
is_alarm BOOLEAN DEFAULT false,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- T-320: AI目标测算
|
|
CREATE TABLE IF NOT EXISTS analytics.v3_ai_target_adjustment (
|
|
id SERIAL PRIMARY KEY,
|
|
target_date DATE NOT NULL,
|
|
store_code TEXT NOT NULL,
|
|
original_target NUMERIC(12,2),
|
|
adjusted_target NUMERIC(12,2),
|
|
factors JSONB, -- 天气/节假日/商圈活动
|
|
confidence NUMERIC(5,2),
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
UNIQUE(target_date, store_code)
|
|
);
|
|
|
|
-- T-321: 趋势预测
|
|
CREATE TABLE IF NOT EXISTS analytics.v3_forecast (
|
|
id SERIAL PRIMARY KEY,
|
|
forecast_type TEXT NOT NULL, -- revenue/traffic/sales
|
|
target_date DATE NOT NULL,
|
|
store_code TEXT,
|
|
predicted_value NUMERIC(14,2),
|
|
confidence_lower NUMERIC(14,2),
|
|
confidence_upper NUMERIC(14,2),
|
|
model_type TEXT, -- linear/arima/ema
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- T-350: 投资回报模型
|
|
CREATE TABLE IF NOT EXISTS analytics.v3_store_roi (
|
|
id SERIAL PRIMARY KEY,
|
|
store_code TEXT NOT NULL,
|
|
store_name TEXT,
|
|
initial_investment NUMERIC(14,2),
|
|
monthly_revenue NUMERIC(14,2),
|
|
monthly_profit NUMERIC(14,2),
|
|
payback_months NUMERIC(8,2),
|
|
roi_pct NUMERIC(8,2),
|
|
city_penetration_pct NUMERIC(5,2),
|
|
calculated_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
|
UNIQUE(store_code)
|
|
);
|
|
|
|
-- T-352: 品牌资产
|
|
CREATE TABLE IF NOT EXISTS analytics.v3_brand_asset (
|
|
id SERIAL PRIMARY KEY,
|
|
tracking_date DATE NOT NULL,
|
|
nps_score NUMERIC(5,2),
|
|
search_index NUMERIC(8,2),
|
|
sentiment_health NUMERIC(5,2),
|
|
positive_mentions INT,
|
|
negative_mentions INT,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
UNIQUE(tracking_date)
|
|
);
|
|
|
|
-- 索引
|
|
CREATE INDEX IF NOT EXISTS idx_v3_iot_device_store ON analytics.v3_iot_device(store_code);
|
|
CREATE INDEX IF NOT EXISTS idx_v3_iot_device_status ON analytics.v3_iot_device(status);
|
|
CREATE INDEX IF NOT EXISTS idx_v3_iot_temp_device ON analytics.v3_iot_temperature(device_code);
|
|
CREATE INDEX IF NOT EXISTS idx_v3_iot_temp_recorded ON analytics.v3_iot_temperature(recorded_at);
|
|
CREATE INDEX IF NOT EXISTS idx_v3_ai_target_date ON analytics.v3_ai_target_adjustment(target_date);
|
|
CREATE INDEX IF NOT EXISTS idx_v3_forecast_date ON analytics.v3_forecast(target_date);
|
|
CREATE INDEX IF NOT EXISTS idx_v3_store_roi_store ON analytics.v3_store_roi(store_code);
|
|
CREATE INDEX IF NOT EXISTS idx_v3_brand_asset_date ON analytics.v3_brand_asset(tracking_date);
|