122 lines
2.4 KiB
SQL
122 lines
2.4 KiB
SQL
-- =============================================================================
|
||
-- etl_feiqiu / app（RLS 视图层）
|
||
-- 生成日期：2026-02-23
|
||
-- 来源：测试库（通过脚本自动导出）
|
||
-- =============================================================================
|
||
|
||
CREATE SCHEMA IF NOT EXISTS app;
|
||
|
||
-- 视图
|
||
CREATE OR REPLACE VIEW app.v_assistant AS
|
||
SELECT assistant_id,
|
||
tenant_id,
|
||
site_id,
|
||
real_name,
|
||
nickname,
|
||
mobile,
|
||
level,
|
||
assistant_status,
|
||
leave_status
|
||
FROM core.dim_assistant a;
|
||
;
|
||
|
||
CREATE OR REPLACE VIEW app.v_assistant_daily AS
|
||
SELECT id,
|
||
site_id,
|
||
tenant_id,
|
||
assistant_id,
|
||
assistant_nickname,
|
||
stat_date,
|
||
total_service_count,
|
||
total_hours,
|
||
base_hours,
|
||
bonus_hours,
|
||
room_hours,
|
||
total_ledger_amount,
|
||
unique_customers,
|
||
unique_tables,
|
||
created_at
|
||
FROM dws.dws_assistant_daily_detail d;
|
||
;
|
||
|
||
CREATE OR REPLACE VIEW app.v_finance_daily AS
|
||
SELECT id,
|
||
site_id,
|
||
tenant_id,
|
||
stat_date,
|
||
gross_amount,
|
||
table_fee_amount,
|
||
goods_amount,
|
||
assistant_pd_amount,
|
||
assistant_cx_amount,
|
||
discount_total,
|
||
confirmed_income,
|
||
cash_inflow_total,
|
||
recharge_count,
|
||
recharge_total,
|
||
order_count,
|
||
member_order_count,
|
||
guest_order_count,
|
||
avg_order_amount,
|
||
created_at
|
||
FROM dws.dws_finance_daily_summary f;
|
||
;
|
||
|
||
CREATE OR REPLACE VIEW app.v_member AS
|
||
SELECT member_id,
|
||
system_member_id,
|
||
tenant_id,
|
||
register_site_id AS site_id,
|
||
mobile,
|
||
nickname,
|
||
member_card_grade_name,
|
||
status
|
||
FROM core.dim_member m;
|
||
;
|
||
|
||
CREATE OR REPLACE VIEW app.v_member_consumption AS
|
||
SELECT id,
|
||
site_id,
|
||
tenant_id,
|
||
member_id,
|
||
stat_date,
|
||
member_nickname,
|
||
card_grade_name,
|
||
total_visit_count,
|
||
total_consume_amount,
|
||
total_recharge_amount,
|
||
last_consume_date,
|
||
first_consume_date,
|
||
days_since_last,
|
||
customer_tier,
|
||
created_at
|
||
FROM dws.dws_member_consumption_summary mc;
|
||
;
|
||
|
||
CREATE OR REPLACE VIEW app.v_order_summary AS
|
||
SELECT site_id,
|
||
order_settle_id,
|
||
order_trade_no,
|
||
order_date,
|
||
tenant_id,
|
||
member_id,
|
||
member_flag,
|
||
order_original_amount,
|
||
order_final_amount,
|
||
total_paid_amount,
|
||
refund_amount,
|
||
net_income,
|
||
created_at
|
||
FROM dws.dws_order_summary os;
|
||
;
|
||
|
||
CREATE OR REPLACE VIEW app.v_site AS
|
||
SELECT site_id,
|
||
tenant_id,
|
||
shop_name,
|
||
site_label,
|
||
shop_status
|
||
FROM core.dim_site s;
|
||
;
|
||
|