下载中心/建表设计稿

建表设计稿

下载原文件
-- 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;