建表设计稿
下载原文件-- P29 全站埋点与流量统计建表设计稿(PostgreSQL)
-- 说明:仅作数据结构参考,不直接在生产执行;继承站点既有数据库规范。
CREATE TABLE track_log (
id BIGSERIAL PRIMARY KEY,
session_id VARCHAR(64) NOT NULL,
user_id BIGINT DEFAULT 0,
event_type VARCHAR(64) NOT NULL,
page_url VARCHAR(512),
page_title VARCHAR(255),
ref_url VARCHAR(512),
channel VARCHAR(64),
province VARCHAR(32),
ip VARCHAR(64),
ua VARCHAR(512),
extra_json JSONB,
is_spider BOOLEAN DEFAULT FALSE,
created_at TIMESTAMPTZ DEFAULT now()
);
COMMENT ON TABLE track_log IS '全站原始行为日志,冷数据按月归档,保留 90 天';
COMMENT ON COLUMN track_log.user_id IS '0 表示游客';
COMMENT ON COLUMN track_log.event_type IS 'pageview / rfq_submit / tool_dfm_start 等';
CREATE INDEX idx_track_log_session ON track_log (session_id);
CREATE INDEX idx_track_log_event ON track_log (event_type);
CREATE INDEX idx_track_log_time ON track_log (created_at);
CREATE TABLE track_daily_summary (
id BIGSERIAL PRIMARY KEY,
stat_date DATE NOT NULL,
channel VARCHAR(64) DEFAULT '',
event_type VARCHAR(64) DEFAULT '',
uv INTEGER DEFAULT 0,
pv INTEGER DEFAULT 0,
session_cnt INTEGER DEFAULT 0,
created_at TIMESTAMPTZ DEFAULT now(),
CONSTRAINT uk_track_daily UNIQUE (stat_date, channel, event_type)
);
COMMENT ON TABLE track_daily_summary IS '按日汇总,供 BI 看板查询,避免直接扫描原始日志';
CREATE TABLE track_supplier_daily (
id BIGSERIAL PRIMARY KEY,
stat_date DATE NOT NULL,
supplier_id VARCHAR(64) NOT NULL,
shop_uv INTEGER DEFAULT 0,
contact_cnt INTEGER DEFAULT 0,
quote_cnt INTEGER DEFAULT 0,
created_at TIMESTAMPTZ DEFAULT now(),
CONSTRAINT uk_track_supplier_daily UNIQUE (stat_date, supplier_id)
);
CREATE INDEX idx_track_supplier_daily ON track_supplier_daily (supplier_id);
CREATE TABLE track_ip_black (
id BIGSERIAL PRIMARY KEY,
ip VARCHAR(64) NOT NULL,
reason VARCHAR(255),
status SMALLINT DEFAULT 1,
created_at TIMESTAMPTZ DEFAULT now(),
CONSTRAINT uk_track_ip UNIQUE (ip)
);
COMMENT ON TABLE track_ip_black IS '异常访问黑名单,status=1 生效';
-- 汇总任务:按日刷新(示例,可由定时任务调用)
CREATE OR REPLACE FUNCTION refresh_track_daily(p_date DATE)
RETURNS VOID AS $$
BEGIN
DELETE FROM track_daily_summary WHERE stat_date = p_date;
INSERT INTO track_daily_summary (stat_date, channel, event_type, uv, pv, session_cnt)
SELECT
p_date,
COALESCE(channel, ''),
event_type,
COUNT(DISTINCT session_id) AS uv,
COUNT(*) AS pv,
COUNT(DISTINCT session_id) AS session_cnt
FROM track_log
WHERE created_at >= p_date
AND created_at < p_date + INTERVAL '1 day'
AND is_spider = FALSE
GROUP BY COALESCE(channel, ''), event_type;
END;
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION refresh_track_daily(DATE) IS '按日重算流量汇总,爬虫流量不计入';
-- 供应商店铺日汇总(示例)
CREATE OR REPLACE FUNCTION refresh_track_supplier_daily(p_date DATE)
RETURNS VOID AS $$
BEGIN
DELETE FROM track_supplier_daily WHERE stat_date = p_date;
INSERT INTO track_supplier_daily (stat_date, supplier_id, shop_uv, contact_cnt, quote_cnt)
SELECT
p_date,
COALESCE(extra_json->>'supplierId', ''),
COUNT(DISTINCT CASE WHEN event_type = 'supplier_shop_enter' THEN session_id END),
COUNT(*) FILTER (WHERE event_type = 'supplier_contact'),
COUNT(*) FILTER (WHERE event_type = 'quote_send')
FROM track_log
WHERE created_at >= p_date
AND created_at < p_date + INTERVAL '1 day'
AND is_spider = FALSE
AND extra_json ? 'supplierId'
GROUP BY COALESCE(extra_json->>'supplierId', '');
END;
$$ LANGUAGE plpgsql;
