3. SQL环境准备：数据库与示例数据表结构

在进行阈值分层试验的SQL实现之前，需要搭建一个可复现的数据库环境。本章将提供MySQL与PostgreSQL两种主流数据库的DDL语句，并详细说明三张核心表（用户表、行为表、标签表）的结构设计与字段含义。

3.1 数据库选型与版本要求

本教程的SQL代码兼容以下数据库环境，建议使用对应版本或更高版本：

3.2 数据库与Schema创建

首先创建专用数据库，避免与现有业务库冲突：

-- MySQL 版本
CREATE DATABASE IF NOT EXISTS threshold_experiment
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE threshold_experiment;

-- PostgreSQL 版本
-- CREATE DATABASE threshold_experiment
--   ENCODING 'UTF8'
--   LC_COLLATE 'zh_CN.UTF-8'
--   LC_CTYPE 'zh_CN.UTF-8';
-- \c threshold_experiment

3.3 用户表（users）

用户表存储试验参与者的基础画像信息，是分层与分桶的核心依据表。

-- MySQL DDL
CREATE TABLE users (
  user_id          BIGINT UNSIGNED AUTO_INCREMENT,
  user_name        VARCHAR(64)      NOT NULL,
  registration_date DATE            NOT NULL,
  user_level       TINYINT UNSIGNED DEFAULT 1,
  membership_type  VARCHAR(16)      DEFAULT 'normal',
  city             VARCHAR(32)      DEFAULT '',
  age_group        VARCHAR(8)       DEFAULT 'unknown',
  created_at       DATETIME         DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id),
  INDEX idx_membership (membership_type),
  INDEX idx_age_group (age_group)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- PostgreSQL DDL
-- CREATE TABLE users (
--   user_id          BIGSERIAL,
--   user_name        VARCHAR(64)      NOT NULL,
--   registration_date DATE            NOT NULL,
--   user_level       SMALLINT         DEFAULT 1,
--   membership_type  VARCHAR(16)      DEFAULT 'normal',
--   city             VARCHAR(32)      DEFAULT '',
--   age_group        VARCHAR(8)       DEFAULT 'unknown',
--   created_at       TIMESTAMP        DEFAULT CURRENT_TIMESTAMP,
--   PRIMARY KEY (user_id)
-- );
-- CREATE INDEX idx_users_membership ON users (membership_type);
-- CREATE INDEX idx_users_age_group ON users (age_group);

3.4 行为表（user_actions）

行为表记录用户在试验期间产生的各类事件，是计算阈值指标（如点击率、转化率、停留时长）的数据源。

-- MySQL DDL
CREATE TABLE user_actions (
  action_id        BIGINT UNSIGNED AUTO_INCREMENT,
  user_id          BIGINT UNSIGNED   NOT NULL,
  action_type      VARCHAR(32)       NOT NULL,
  action_value     DECIMAL(12,4)     DEFAULT 0.0000,
  page_url         VARCHAR(512)      DEFAULT '',
  action_time      DATETIME          NOT NULL,
  experiment_group VARCHAR(16)       DEFAULT 'control',
  PRIMARY KEY (action_id),
  INDEX idx_user_id (user_id),
  INDEX idx_action_type (action_type),
  INDEX idx_action_time (action_time),
  INDEX idx_experiment_group (experiment_group),
  CONSTRAINT fk_actions_user FOREIGN KEY (user_id) REFERENCES users(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- PostgreSQL DDL
-- CREATE TABLE user_actions (
--   action_id        BIGSERIAL,
--   user_id          BIGINT            NOT NULL,
--   action_type      VARCHAR(32)       NOT NULL,
--   action_value     NUMERIC(12,4)     DEFAULT 0.0000,
--   page_url         VARCHAR(512)      DEFAULT '',
--   action_time      TIMESTAMP         NOT NULL,
--   experiment_group VARCHAR(16)       DEFAULT 'control',
--   PRIMARY KEY (action_id),
--   CONSTRAINT fk_actions_user FOREIGN KEY (user_id) REFERENCES users(user_id)
-- );
-- CREATE INDEX idx_actions_user_id ON user_actions (user_id);
-- CREATE INDEX idx_actions_type ON user_actions (action_type);
-- CREATE INDEX idx_actions_time ON user_actions (action_time);

3.5 标签表（user_tags）

标签表存储用户的分层标签与试验分组信息，是阈值分层试验中“分层变量”与“试验组别”的核心映射表。

-- MySQL DDL
CREATE TABLE user_tags (
  tag_id           BIGINT UNSIGNED AUTO_INCREMENT,
  user_id          BIGINT UNSIGNED   NOT NULL,
  stratum_name     VARCHAR(32)       NOT NULL,
  stratum_value    VARCHAR(64)       NOT NULL,
  experiment_id    VARCHAR(32)       NOT NULL,
  group_assignment VARCHAR(16)       NOT NULL,
  assigned_at      DATETIME          DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (tag_id),
  UNIQUE KEY uk_user_experiment (user_id, experiment_id, stratum_name),
  INDEX idx_experiment (experiment_id),
  INDEX idx_stratum (stratum_name, stratum_value),
  CONSTRAINT fk_tags_user FOREIGN KEY (user_id) REFERENCES users(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- PostgreSQL DDL
-- CREATE TABLE user_tags (
--   tag_id           BIGSERIAL,
--   user_id          BIGINT            NOT NULL,
--   stratum_name     VARCHAR(32)       NOT NULL,
--   stratum_value    VARCHAR(64)       NOT NULL,
--   experiment_id    VARCHAR(32)       NOT NULL,
--   group_assignment VARCHAR(16)       NOT NULL,
--   assigned_at      TIMESTAMP         DEFAULT CURRENT_TIMESTAMP,
--   PRIMARY KEY (tag_id),
--   UNIQUE (user_id, experiment_id, stratum_name)
-- );
-- CREATE INDEX idx_tags_experiment ON user_tags (experiment_id);
-- CREATE INDEX idx_tags_stratum ON user_tags (stratum_name, stratum_value);

3.6 三表关系与数据流向

下图展示了三张表之间的关联关系以及数据在阈值分层试验中的流转路径：

3.7 环境验证脚本

执行完DDL后，建议运行以下验证脚本，确保所有表创建成功且外键约束生效：

-- MySQL 验证
SELECT 
  TABLE_NAME, 
  ENGINE, 
  TABLE_ROWS, 
  CREATE_TIME
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'threshold_experiment';

-- 检查外键约束
SELECT 
  TABLE_NAME, 
  CONSTRAINT_NAME, 
  REFERENCED_TABLE_NAME, 
  REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE 
WHERE TABLE_SCHEMA = 'threshold_experiment' 
  AND REFERENCED_TABLE_NAME IS NOT NULL;

-- PostgreSQL 验证
-- SELECT table_name, table_schema 
-- FROM information_schema.tables 
-- WHERE table_schema = 'public';

3.8 示例数据快速填充

为方便后续章节的SQL练习，提供以下示例数据插入脚本（仅插入少量数据用于功能验证）：

-- 插入5个测试用户
INSERT INTO users (user_name, registration_date, user_level, membership_type, city, age_group) VALUES
('张三', '2024-01-15', 3, 'vip', '北京', '25-34'),
('李四', '2024-03-20', 1, 'normal', '上海', '18-24'),
('王五', '2024-06-10', 5, 'svip', '广州', '35-44'),
('赵六', '2024-08-05', 2, 'normal', '深圳', '25-34'),
('孙七', '2024-11-01', 4, 'vip', '杭州', '45+');

-- 插入行为数据
INSERT INTO user_actions (user_id, action_type, action_value, page_url, action_time, experiment_group) VALUES
(1, 'click', 0, '/home', '2025-01-10 10:30:00', 'control'),
(1, 'purchase', 199.00, '/product/101', '2025-01-10 10:35:00', 'control'),
(2, 'view', 0, '/search', '2025-01-10 11:00:00', 'treatment'),
(3, 'click', 0, '/promo', '2025-01-10 12:15:00', 'treatment'),
(3, 'purchase', 599.00, '/product/202', '2025-01-10 12:20:00', 'treatment'),
(4, 'share', 0, '/article/5', '2025-01-11 09:00:00', 'control'),
(5, 'view', 0, '/home', '2025-01-11 14:30:00', 'treatment');

-- 插入标签数据（试验ID: exp_2025_01，按年龄分层）
INSERT INTO user_tags (user_id, stratum_name, stratum_value, experiment_id, group_assignment) VALUES
(1, 'age_group', '25-34', 'exp_2025_01', 'control'),
(2, 'age_group', '18-24', 'exp_2025_01', 'treatment'),
(3, 'age_group', '35-44', 'exp_2025_01', 'treatment'),
(4, 'age_group', '25-34', 'exp_2025_01', 'control'),
(5, 'age_group', '45+', 'exp_2025_01', 'treatment');