360 lines
17 KiB
PL/PgSQL
360 lines
17 KiB
PL/PgSQL
-- ============================================================
|
||
-- 报表引擎 — 快手电商演示数据初始化脚本
|
||
-- ============================================================
|
||
-- 使用方式：
|
||
-- PGPASSWORD='Bjang09@686^*^' psql -h 116.204.74.41 \
|
||
-- -p 15432 -U postgres -d engine -f demo_kuaishou.sql
|
||
-- ============================================================
|
||
|
||
BEGIN;
|
||
|
||
-- ============================================================
|
||
-- 第〇步：确保系统表存在（CREATE TABLE IF NOT EXISTS）
|
||
-- ============================================================
|
||
|
||
CREATE TABLE IF NOT EXISTS report_business_config (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
tenant_id BIGINT NOT NULL DEFAULT 0,
|
||
business_code VARCHAR(64) NOT NULL,
|
||
business_name VARCHAR(128) NOT NULL,
|
||
description TEXT DEFAULT '',
|
||
status VARCHAR(16) NOT NULL DEFAULT 'ACTIVE',
|
||
config JSONB DEFAULT '{}',
|
||
creator VARCHAR(64) DEFAULT '',
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
updater VARCHAR(64) DEFAULT '',
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
deleted_at TIMESTAMP WITH TIME ZONE
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS report_report_config (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
tenant_id BIGINT NOT NULL DEFAULT 0,
|
||
business_code VARCHAR(64) NOT NULL,
|
||
report_code VARCHAR(64) NOT NULL,
|
||
report_name VARCHAR(128) NOT NULL,
|
||
description TEXT DEFAULT '',
|
||
status VARCHAR(16) NOT NULL DEFAULT 'ACTIVE',
|
||
stat_table_name VARCHAR(128) NOT NULL,
|
||
stat_table_comment VARCHAR(256) DEFAULT '',
|
||
date_field VARCHAR(64) DEFAULT 'stat_date',
|
||
primary_keys JSONB DEFAULT '["id"]'::jsonb,
|
||
conflict_keys JSONB DEFAULT '["stat_date"]'::jsonb,
|
||
config JSONB DEFAULT '{}',
|
||
creator VARCHAR(64) DEFAULT '',
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
updater VARCHAR(64) DEFAULT '',
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
deleted_at TIMESTAMP WITH TIME ZONE,
|
||
CONSTRAINT uk_business_report_code UNIQUE (tenant_id, business_code, report_code)
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS report_field_config (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
tenant_id BIGINT NOT NULL DEFAULT 0,
|
||
business_code VARCHAR(64) NOT NULL,
|
||
report_code VARCHAR(64) NOT NULL,
|
||
field_code VARCHAR(64) NOT NULL,
|
||
field_name VARCHAR(128) NOT NULL,
|
||
field_type VARCHAR(32) NOT NULL,
|
||
data_type VARCHAR(32) NOT NULL DEFAULT 'STRING',
|
||
field_role VARCHAR(32) NOT NULL,
|
||
is_aggregatable BOOLEAN DEFAULT FALSE,
|
||
is_filterable BOOLEAN DEFAULT TRUE,
|
||
is_queryable BOOLEAN DEFAULT TRUE,
|
||
is_sortable BOOLEAN DEFAULT TRUE,
|
||
default_aggregate VARCHAR(32) DEFAULT '',
|
||
valid_aggregates JSONB DEFAULT '[]'::jsonb,
|
||
filter_operators JSONB DEFAULT '["=","!=",">","<",">=","<=","IN","LIKE","BETWEEN"]'::jsonb,
|
||
expression VARCHAR(512) DEFAULT '',
|
||
expression_type VARCHAR(32) DEFAULT '',
|
||
format_pattern VARCHAR(64) DEFAULT '',
|
||
unit VARCHAR(32) DEFAULT '',
|
||
dict_code VARCHAR(64) DEFAULT '',
|
||
sort_order INT DEFAULT 0,
|
||
group_name VARCHAR(64) DEFAULT '',
|
||
status VARCHAR(16) NOT NULL DEFAULT 'ACTIVE',
|
||
creator VARCHAR(64) DEFAULT '',
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
updater VARCHAR(64) DEFAULT '',
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
deleted_at TIMESTAMP WITH TIME ZONE,
|
||
CONSTRAINT uk_business_report_field_code UNIQUE (tenant_id, business_code, report_code, field_code)
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS report_extract_config (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
tenant_id BIGINT NOT NULL DEFAULT 0,
|
||
business_code VARCHAR(64) NOT NULL,
|
||
report_code VARCHAR(64) NOT NULL,
|
||
extract_code VARCHAR(64) NOT NULL,
|
||
extract_name VARCHAR(128) NOT NULL,
|
||
source_table_name VARCHAR(128) NOT NULL,
|
||
source_table_alias VARCHAR(64) DEFAULT '',
|
||
target_table_name VARCHAR(128) NOT NULL,
|
||
is_enabled BOOLEAN DEFAULT TRUE,
|
||
extract_type VARCHAR(32) NOT NULL DEFAULT 'FULL',
|
||
extract_mode VARCHAR(32) NOT NULL DEFAULT 'DIRECT',
|
||
extract_key_field VARCHAR(64) DEFAULT '',
|
||
extract_key_format VARCHAR(64) DEFAULT '',
|
||
group_by_fields JSONB DEFAULT '[]'::jsonb,
|
||
filter_expression TEXT DEFAULT '',
|
||
join_configs JSONB DEFAULT '[]'::jsonb,
|
||
field_mappings JSONB DEFAULT '[]'::jsonb,
|
||
transform_rules JSONB DEFAULT '[]'::jsonb,
|
||
batch_size INT DEFAULT 1000,
|
||
status VARCHAR(16) NOT NULL DEFAULT 'ACTIVE',
|
||
creator VARCHAR(64) DEFAULT '',
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
updater VARCHAR(64) DEFAULT '',
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
deleted_at TIMESTAMP WITH TIME ZONE,
|
||
CONSTRAINT uk_business_report_extract_code UNIQUE (tenant_id, business_code, report_code, extract_code)
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS report_extract_log (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
tenant_id BIGINT NOT NULL DEFAULT 0,
|
||
business_code VARCHAR(64) NOT NULL,
|
||
report_code VARCHAR(64) NOT NULL,
|
||
extract_code VARCHAR(64) NOT NULL,
|
||
stat_date VARCHAR(16) NOT NULL,
|
||
extract_type VARCHAR(32) NOT NULL,
|
||
status VARCHAR(16) NOT NULL DEFAULT 'RUNNING',
|
||
total_count INT DEFAULT 0,
|
||
success_count INT DEFAULT 0,
|
||
fail_count INT DEFAULT 0,
|
||
start_time TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
end_time TIMESTAMP WITH TIME ZONE,
|
||
error_message TEXT DEFAULT '',
|
||
executor VARCHAR(64) DEFAULT '',
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
CONSTRAINT uk_extract_keys UNIQUE (tenant_id, business_code, report_code, extract_code, stat_date)
|
||
);
|
||
|
||
-- ============================================================
|
||
-- 第一步：创建源数据表 + 模拟数据
|
||
-- ============================================================
|
||
|
||
-- 店铺表
|
||
DROP TABLE IF EXISTS demo_kuaishou_shop CASCADE;
|
||
CREATE TABLE demo_kuaishou_shop (
|
||
shop_id VARCHAR(64) PRIMARY KEY,
|
||
shop_name VARCHAR(128) NOT NULL,
|
||
shop_level VARCHAR(16) DEFAULT '普通',
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
||
);
|
||
|
||
INSERT INTO demo_kuaishou_shop VALUES
|
||
('KS001', '快手官方旗舰店', '旗舰'),
|
||
('KS002', '数码3C专营店', '高级'),
|
||
('KS003', '美妆个护优选', '普通'),
|
||
('KS004', '食品生鲜超市', '高级'),
|
||
('KS005', '服装潮流馆', '普通');
|
||
|
||
-- 订单明细表
|
||
DROP TABLE IF EXISTS demo_kuaishou_orders CASCADE;
|
||
CREATE TABLE demo_kuaishou_orders (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
order_no VARCHAR(64) NOT NULL,
|
||
shop_id VARCHAR(64) NOT NULL,
|
||
order_status VARCHAR(16) NOT NULL DEFAULT '已完成',
|
||
product_name VARCHAR(256),
|
||
product_category VARCHAR(64),
|
||
quantity INT DEFAULT 1,
|
||
unit_price NUMERIC(12,2) DEFAULT 0,
|
||
order_amount NUMERIC(12,2) DEFAULT 0,
|
||
refund_amount NUMERIC(12,2) DEFAULT 0,
|
||
shipping_cost NUMERIC(10,2) DEFAULT 0,
|
||
payment_method VARCHAR(32) DEFAULT '微信',
|
||
buyer_id VARCHAR(64) DEFAULT '',
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
||
);
|
||
|
||
-- 生成30天模拟订单
|
||
DO $$
|
||
DECLARE
|
||
v_date DATE;
|
||
v_order_date DATE := CURRENT_DATE - INTERVAL '30 days';
|
||
v_order_count INT;
|
||
v_shop_id VARCHAR(64);
|
||
v_shop_ids VARCHAR[] := ARRAY['KS001','KS002','KS003','KS004','KS005'];
|
||
v_categories VARCHAR[] := ARRAY['手机','电脑','美妆','食品','服装'];
|
||
v_payments VARCHAR[] := ARRAY['微信','支付宝','快手支付'];
|
||
v_statuses VARCHAR[] := ARRAY['已完成','已完成','已完成','已完成','已完成','已完成','已完成','已取消','已完成'];
|
||
v_price NUMERIC(12,2);
|
||
v_qty INT;
|
||
BEGIN
|
||
FOR day_offset IN 0..29 LOOP
|
||
v_date := v_order_date + day_offset;
|
||
v_order_count := 20 + floor(random() * 31)::int;
|
||
FOR i IN 1..v_order_count LOOP
|
||
v_shop_id := v_shop_ids[1 + floor(random() * 5)::int];
|
||
v_qty := 1 + floor(random() * 5)::int;
|
||
v_price := round((50 + random() * 2000)::numeric, 2);
|
||
INSERT INTO demo_kuaishou_orders (order_no, shop_id, order_status, product_name,
|
||
product_category, quantity, unit_price, order_amount, refund_amount,
|
||
shipping_cost, payment_method, buyer_id, created_at, updated_at)
|
||
VALUES (
|
||
'KS' || to_char(v_date, 'YYYYMMDD') || '-' || lpad(i::text, 4, '0'),
|
||
v_shop_id, v_statuses[1 + floor(random() * 9)::int],
|
||
'商品' || (1000 + floor(random() * 9000)::int),
|
||
v_categories[1 + floor(random() * 5)::int],
|
||
v_qty, v_price, round((v_price * v_qty)::numeric, 2),
|
||
CASE WHEN random() < 0.1 THEN round((v_price * v_qty * random() * 0.5)::numeric, 2) ELSE 0 END,
|
||
round((5 + random() * 15)::numeric, 2),
|
||
v_payments[1 + floor(random() * 3)::int],
|
||
'buyer_' || (10000 + floor(random() * 90000)::int),
|
||
v_date + time '08:00:00' + (random() * interval '12 hours'),
|
||
v_date + time '08:00:00' + (random() * interval '12 hours')
|
||
);
|
||
END LOOP;
|
||
END LOOP;
|
||
END $$;
|
||
|
||
ANALYZE demo_kuaishou_orders;
|
||
|
||
-- ============================================================
|
||
-- 第二步：插入系统配置（先清空避免重复）
|
||
-- ============================================================
|
||
|
||
DELETE FROM report_business_config WHERE business_code = 'KUAISHOU';
|
||
DELETE FROM report_report_config WHERE business_code = 'KUAISHOU';
|
||
DELETE FROM report_field_config WHERE business_code = 'KUAISHOU';
|
||
DELETE FROM report_extract_config WHERE business_code = 'KUAISHOU';
|
||
|
||
-- 1. 业务配置
|
||
INSERT INTO report_business_config (tenant_id, business_code, business_name, description, status, config, creator)
|
||
VALUES (1, 'KUAISHOU', '快手电商', '快手电商平台订单数据', 'ACTIVE', '{}', 'system');
|
||
|
||
-- 2. 报表配置
|
||
INSERT INTO report_report_config (tenant_id, business_code, report_code, report_name, description,
|
||
status, stat_table_name, stat_table_comment, date_field, primary_keys, conflict_keys, config, creator)
|
||
VALUES (1, 'KUAISHOU', 'shop_daily', '店铺日报',
|
||
'按店铺+日期统计订单数据', 'ACTIVE',
|
||
'stat_kuaishou_shop_daily', '快手电商店铺日报统计宽表',
|
||
'stat_date', '["id"]'::jsonb, '["stat_date", "shop_id"]'::jsonb, '{}', 'system');
|
||
|
||
-- 3. 字段配置
|
||
INSERT INTO report_field_config (tenant_id, business_code, report_code, field_code, field_name,
|
||
field_type, data_type, field_role, is_aggregatable, is_filterable, is_queryable, is_sortable,
|
||
default_aggregate, valid_aggregates, filter_operators, sort_order, group_name, status, creator)
|
||
VALUES
|
||
-- 维度
|
||
(1, 'KUAISHOU', 'shop_daily', 'stat_date', '统计日期',
|
||
'DATE', 'STRING', 'DIMENSION', false, true, true, true,
|
||
'', '[]'::jsonb, '["=",">=","<=","BETWEEN"]'::jsonb, 1, '时间维度', 'ACTIVE', 'system'),
|
||
(1, 'KUAISHOU', 'shop_daily', 'order_no', '订单号',
|
||
'STRING', 'STRING', 'DIMENSION', false, true, true, true,
|
||
'', '[]'::jsonb, '["=","IN"]'::jsonb, 0, '订单维度', 'ACTIVE', 'system'),
|
||
(1, 'KUAISHOU', 'shop_daily', 'shop_id', '店铺ID',
|
||
'STRING', 'STRING', 'DIMENSION', false, true, true, true,
|
||
'', '[]'::jsonb, '["=","IN"]'::jsonb, 2, '店铺维度', 'ACTIVE', 'system'),
|
||
(1, 'KUAISHOU', 'shop_daily', 'shop_name', '店铺名称',
|
||
'STRING', 'STRING', 'DIMENSION', false, true, true, true,
|
||
'', '[]'::jsonb, '["=","LIKE"]'::jsonb, 3, '店铺维度', 'ACTIVE', 'system'),
|
||
(1, 'KUAISHOU', 'shop_daily', 'product_category', '商品类目',
|
||
'STRING', 'STRING', 'DIMENSION', false, true, true, true,
|
||
'', '[]'::jsonb, '["=","IN"]'::jsonb, 4, '商品维度', 'ACTIVE', 'system'),
|
||
(1, 'KUAISHOU', 'shop_daily', 'order_status', '订单状态',
|
||
'STRING', 'STRING', 'DIMENSION', false, true, true, true,
|
||
'', '[]'::jsonb, '["=","IN"]'::jsonb, 5, '订单维度', 'ACTIVE', 'system'),
|
||
-- 指标
|
||
(1, 'KUAISHOU', 'shop_daily', 'order_count', '订单数',
|
||
'INT', 'INT', 'INDICATOR', true, false, true, true,
|
||
'SUM', '["SUM","COUNT"]'::jsonb, '[]'::jsonb, 10, '交易指标', 'ACTIVE', 'system'),
|
||
(1, 'KUAISHOU', 'shop_daily', 'order_amount', '订单金额',
|
||
'FLOAT', 'FLOAT', 'INDICATOR', true, false, true, true,
|
||
'SUM', '["SUM","AVG","MAX","MIN"]'::jsonb, '[]'::jsonb, 11, '交易指标', 'ACTIVE', 'system'),
|
||
(1, 'KUAISHOU', 'shop_daily', 'refund_amount', '退款金额',
|
||
'FLOAT', 'FLOAT', 'INDICATOR', true, false, true, true,
|
||
'SUM', '["SUM","AVG"]'::jsonb, '[]'::jsonb, 12, '交易指标', 'ACTIVE', 'system'),
|
||
(1, 'KUAISHOU', 'shop_daily', 'shipping_cost', '运费',
|
||
'FLOAT', 'FLOAT', 'INDICATOR', true, false, true, true,
|
||
'SUM', '["SUM","AVG"]'::jsonb, '[]'::jsonb, 13, '交易指标', 'ACTIVE', 'system'),
|
||
(1, 'KUAISHOU', 'shop_daily', 'total_quantity', '商品数量',
|
||
'INT', 'INT', 'INDICATOR', true, false, true, true,
|
||
'SUM', '["SUM","AVG"]'::jsonb, '[]'::jsonb, 14, '交易指标', 'ACTIVE', 'system'),
|
||
(1, 'KUAISHOU', 'shop_daily', 'avg_price', '平均单价',
|
||
'FLOAT', 'FLOAT', 'INDICATOR', true, false, true, true,
|
||
'AVG', '["AVG","MAX","MIN"]'::jsonb, '[]'::jsonb, 15, '交易指标', 'ACTIVE', 'system');
|
||
|
||
-- 4. 抽取配置（DIRECT 模式：逐行抽取，查询时动态聚合）
|
||
INSERT INTO report_extract_config (tenant_id, business_code, report_code, extract_code, extract_name,
|
||
source_table_name, source_table_alias, target_table_name, is_enabled,
|
||
extract_type, extract_mode, extract_key_field, group_by_fields, filter_expression,
|
||
field_mappings, join_configs, batch_size, status, creator)
|
||
VALUES (1, 'KUAISHOU', 'shop_daily', 'shop_daily_direct', '店铺日订单(逐行,可自由组合查询)',
|
||
'demo_kuaishou_orders', 'o', 'stat_kuaishou_shop_daily',
|
||
true, 'INCREMENTAL', 'DIRECT', 'created_at',
|
||
'[]'::jsonb,
|
||
'o.order_status = ''已完成''',
|
||
'[
|
||
{"sourceField":"order_no", "targetField":"order_no", "fieldType":"STRING", "aggregateFunction":"", "defaultValue":""},
|
||
{"sourceField":"shop_id", "targetField":"shop_id", "fieldType":"STRING", "aggregateFunction":"", "defaultValue":""},
|
||
{"sourceField":"product_category", "targetField":"product_category", "fieldType":"STRING", "aggregateFunction":"", "defaultValue":""},
|
||
{"sourceField":"order_status", "targetField":"order_status", "fieldType":"STRING", "aggregateFunction":"", "defaultValue":""},
|
||
{"sourceField":"order_amount", "targetField":"order_amount", "fieldType":"FLOAT", "aggregateFunction":"", "defaultValue":0},
|
||
{"sourceField":"refund_amount", "targetField":"refund_amount", "fieldType":"FLOAT", "aggregateFunction":"", "defaultValue":0},
|
||
{"sourceField":"shipping_cost", "targetField":"shipping_cost", "fieldType":"FLOAT", "aggregateFunction":"", "defaultValue":0},
|
||
{"sourceField":"quantity", "targetField":"total_quantity", "fieldType":"INT", "aggregateFunction":"", "defaultValue":0},
|
||
{"sourceField":"unit_price", "targetField":"unit_price", "fieldType":"FLOAT", "aggregateFunction":"", "defaultValue":0}
|
||
]'::jsonb,
|
||
'[
|
||
{
|
||
"joinTable":"demo_kuaishou_shop",
|
||
"joinAlias":"s",
|
||
"joinType":"LEFT",
|
||
"joinCondition":"o.shop_id = s.shop_id",
|
||
"fieldMappings":[
|
||
{"sourceField":"shop_name", "targetField":"shop_name", "fieldType":"STRING", "aggregateFunction":""},
|
||
{"sourceField":"shop_level", "targetField":"shop_level", "fieldType":"STRING", "aggregateFunction":""}
|
||
]
|
||
}
|
||
]'::jsonb,
|
||
1000, 'ACTIVE', 'system'
|
||
);
|
||
|
||
-- ============================================================
|
||
-- 第三步：验证
|
||
-- ============================================================
|
||
|
||
SELECT '✅ 源表数据' AS info, COUNT(*) AS 行数 FROM demo_kuaishou_orders
|
||
UNION ALL
|
||
SELECT '✅ 店铺数', COUNT(*) FROM demo_kuaishou_shop
|
||
UNION ALL
|
||
SELECT '✅ 业务配置', COUNT(*) FROM report_business_config WHERE business_code = 'KUAISHOU'
|
||
UNION ALL
|
||
SELECT '✅ 报表配置', COUNT(*) FROM report_report_config WHERE business_code = 'KUAISHOU'
|
||
UNION ALL
|
||
SELECT '✅ 字段配置', COUNT(*) FROM report_field_config WHERE business_code = 'KUAISHOU'
|
||
UNION ALL
|
||
SELECT '✅ 抽取配置', COUNT(*) FROM report_extract_config WHERE business_code = 'KUAISHOU';
|
||
|
||
SELECT '📅 数据日期范围:' AS info, MIN(created_at::date)::text || ' ~ ' || MAX(created_at::date)::text AS 范围 FROM demo_kuaishou_orders;
|
||
|
||
COMMIT;
|
||
|
||
-- ============================================================
|
||
-- 使用说明
|
||
-- ============================================================
|
||
-- 1. 启动项目（会自动创建系统表）
|
||
-- 2. 执行本脚本
|
||
-- 3. 打开 http://localhost:3013/admin/report
|
||
-- 4. 抽取配置 → 选"快手电商/shop_daily" → 点"执行抽取"
|
||
-- 5. 数据查询 → 选"快手电商/shop_daily" → 自由选维度/指标查询
|
||
-- ============================================================
|
||
--
|
||
-- 上线前清理 Demo 数据：
|
||
-- DELETE FROM report_business_config WHERE business_code = 'KUAISHOU';
|
||
-- DELETE FROM report_report_config WHERE business_code = 'KUAISHOU';
|
||
-- DELETE FROM report_field_config WHERE business_code = 'KUAISHOU';
|
||
-- DELETE FROM report_extract_config WHERE business_code = 'KUAISHOU';
|
||
-- DELETE FROM report_extract_log WHERE business_code = 'KUAISHOU';
|
||
-- DROP TABLE IF EXISTS stat_kuaishou_shop_daily CASCADE;
|
||
-- DROP TABLE IF EXISTS demo_kuaishou_orders CASCADE;
|
||
-- DROP TABLE IF EXISTS demo_kuaishou_shop CASCADE;
|
||
-- ============================================================
|