工作数据类文档示范（持续更新）-带短视频功能的社交APP数据仓库类型表格附带SQL语句

以下是一个简单的社交APP数据仓库，可以根据功能做增删改，经过上手3个社交类APP总结的表格，会不定期做修改增加

1. 用户类型表

表名	描述
dim_user	用户基本信息维度表，包括用户ID、用户名、性别、年龄等
dim_user_location	用户位置维度表，包括用户所在地理位置信息
dim_user_activity	用户活跃度维度表，包括用户的登录时间、在线时间、参与互动时间等

1.1 用户基本信息表

列名	数据类型	描述
user_id	INT	用户ID，主键
username	VARCHAR	用户名
gender	VARCHAR	性别
age	INT	年龄
birthday	DATE	生日
registration_time	DATETIME	注册时间
last_login_time	DATETIME	最后登录时间

1.2 用户位置信息表

列名	数据类型	描述
user_id	INT	用户ID，外键，指向用户基本信息表中的user_id
country	VARCHAR	国家
province	VARCHAR	省份
city	VARCHAR	城市
longitude	FLOAT	经度
latitude	FLOAT	纬度

1.3 用户活跃度信息表

列名	数据类型	描述
user_id	INT	用户ID，外键，指向用户基本信息表中的user_id
login_time	DATETIME	登录时间
online_time	INT	在线时长，单位为秒
post_count	INT	发布动态数
like_count	INT	点赞数
comment_count	INT	评论数
share_count	INT	分享数

1.4 用户社交关系信息表

列名	数据类型	描述
user_id	INT	用户ID，外键，指向用户基本信息表中的user_id
friend_id	INT	好友ID，外键，指向用户基本信息表中的user_id
follow_id	INT	关注ID，外键，指向用户基本信息表中的user_id
fan_id	INT	粉丝ID，外键，指向用户基本信息表中的user_id

1.5 用户偏好信息表

列名	数据类型	描述
user_id	INT	用户ID，外键，指向用户基本信息表中的user_id
preference_label	VARCHAR	偏好标签
preference_content	VARCHAR	偏好内容

1.6 用户设备信息表

列名	数据类型	描述
user_id	INT	用户ID，外键，指向用户基本信息表中的user_id
device_id	VARCHAR	设备ID
device_type	VARCHAR	设备类型
os_version	VARCHAR	操作系统版本
app_version	VARCHAR	应用版本

下面是SQL语句

1.1 用户基本信息表

CREATE TABLE dim_user (
  user_id INT PRIMARY KEY,
  username VARCHAR(255),
  gender VARCHAR(10),
  age INT,
  birthday DATE,
  register_time DATETIME,
  last_login_time DATETIME
);

1.2 用户位置信息表

CREATE TABLE dim_user_location (
  user_id INT,
  country VARCHAR(255),
  province VARCHAR(255),
  city VARCHAR(255),
  longitude DECIMAL(10, 6),
  latitude DECIMAL(10, 6),
  PRIMARY KEY (user_id),
  FOREIGN KEY (user_id) REFERENCES dim_user(user_id)
);

1.3 用户活跃度信息表

CREATE TABLE dim_user_activity (
  user_id INT,
  login_time DATETIME,
  online_time INT,
  post_count INT,
  like_count INT,
  comment_count INT,
  share_count INT,
  PRIMARY KEY (user_id, login_time),
  FOREIGN KEY (user_id) REFERENCES dim_user(user_id)
);

1.4 用户社交关系信息表

CREATE TABLE dim_user_relationship (
  user_id INT,
  friend_id INT,
  follow_id INT,
  fan_id INT,
  PRIMARY KEY (user_id, friend_id, follow_id, fan_id),
  FOREIGN KEY (user_id) REFERENCES dim_user(user_id),
  FOREIGN KEY (friend_id) REFERENCES dim_user(user_id),
  FOREIGN KEY (follow_id) REFERENCES dim_user(user_id),
  FOREIGN KEY (fan_id) REFERENCES dim_user(user_id)
);

1.5 用户偏好信息表

CREATE TABLE dim_user_preference (
  user_id INT,
  preference_tag VARCHAR(255),
  preference_content VARCHAR(255),
  PRIMARY KEY (user_id, preference_tag, preference_content),
  FOREIGN KEY (user_id) REFERENCES dim_user(user_id)
);

1.6 用户设备信息表​​​​​​​

CREATE TABLE dim_user_device (
  user_id INT,
  device_id VARCHAR(255),
  device_type VARCHAR(255),
  os_version VARCHAR(255),
  app_version VARCHAR(255),
  PRIMARY KEY (user_id, device_id),
  FOREIGN KEY (user_id) REFERENCES dim_user(user_id)
);

注意：以上SQL语句仅是一个示例，实际建表不论是My SQL 还是SQL Sever 需要根据具体需求和数据库类型进行调整。

2. 帖子类型表

表名	描述
dim_post	帖子基本信息维度表，包括帖子ID、帖子标题、发布时间等
dim_post_content	帖子内容维度表，包括帖子正文、图片等
dim_post_location	帖子位置维度表，包括帖子所在地理位置信息
dim_post_interaction	帖子交互维度表，包括帖子的点赞数、转发数、评论数等

下面是细分列表

2.1 dim_post

列名	数据类型	描述
post_id	INT	帖子ID，主键
user_id	INT	发布帖子的用户ID，外键指向dim_user表中的user_id
post_title	VARCHAR	帖子标题
post_type	VARCHAR	帖子类型，例如文字帖、图片帖、视频帖等
post_create_time	DATETIME	帖子创建时间
post_update_time	DATETIME	帖子更新时间
post_location_id	INT	帖子所在位置的ID，外键指向dim_post_location表中的location_id

2.2 dim_post_content

列名	数据类型	描述
post_id	INT	帖子ID，主键，外键指向dim_post表中的post_id
post_content	TEXT	帖子内容
post_image	VARCHAR	帖子中包含的图片地址

2.3 dim_post_location

列名	数据类型	描述
location_id	INT	位置ID，主键
location_name	VARCHAR	位置名称
location_latitude	FLOAT	位置纬度
location_longitude	FLOAT	位置经度
location_country	VARCHAR	所在国家
location_province	VARCHAR	所在省份
location_city	VARCHAR	所在城市
location_district	VARCHAR	所在区/县

2.4 dim_post_interaction

列名	数据类型	描述
post_id	INT	帖子ID，主键，外键指向dim_post表中的post_id
like_count	INT	点赞数
share_count	INT	转发数
comment_count	INT	评论数
collect_count	INT	收藏数
play_count	INT	播放次数

-- 创建 dim_post 表
CREATE TABLE dim_post (
  post_id INT NOT NULL AUTO_INCREMENT,
  user_id INT NOT NULL,
  post_title VARCHAR(255),
  post_type VARCHAR(50),
  post_create_time DATETIME,
  post_update_time DATETIME,
  post_location_id INT,
  PRIMARY KEY (post_id),
  FOREIGN KEY (user_id) REFERENCES dim_user(user_id),
  FOREIGN KEY (post_location_id) REFERENCES dim_post_location(location_id)
);

-- 创建 dim_post_content 表
CREATE TABLE dim_post_content (
  post_id INT NOT NULL,
  post_content TEXT,
  post_image VARCHAR(255),
  PRIMARY KEY (post_id),
  FOREIGN KEY (post_id) REFERENCES dim_post(post_id)
);

-- 创建 dim_post_location 表
CREATE TABLE dim_post_location (
  location_id INT NOT NULL AUTO_INCREMENT,
  location_name VARCHAR(255),
  location_latitude FLOAT,
  location_longitude FLOAT,
  location_country VARCHAR(255),
  location_province VARCHAR(255),
  location_city VARCHAR(255),
  location_district VARCHAR(255),
  PRIMARY KEY (location_id)
);

-- 创建 dim_post_interaction 表
CREATE TABLE dim_post_interaction (
  post_id INT NOT NULL,
  like_count INT,
  share_count INT,
  comment_count INT,
  collect_count INT,
  play_count INT,
  PRIMARY KEY (post_id),
  FOREIGN KEY (post_id) REFERENCES dim_post(post_id)
);

3. 评论类型表

表名	描述
dim_comment	评论基本信息维度表，包括评论ID、评论内容、评论时间等
dim_comment_user	评论用户维度表，包括评论用户ID、用户名等
dim_comment_post	评论帖子维度表，包括评论的帖子ID、帖子标题等
dim_comment_interaction	评论交互维度表，包括评论的点赞数、回复数等

3.1 dim_comment:

列名	数据类型	描述
comment_id	INT	评论ID，主键
comment_user_id	INT	评论用户ID，外键指向dim_comment_user表中的user_id
comment_post_id	INT	评论帖子ID，外键指向dim_post表中的post_id
comment_content	TEXT	评论内容
comment_time	DATETIME	评论时间

3.2 dim_comment_user:

列名	数据类型	描述
user_id	INT	评论用户ID，主键
user_name	VARCHAR	评论用户的用户名
user_gender	VARCHAR	评论用户的性别
user_age	INT	评论用户的年龄
user_birthday	DATE	评论用户的生日
user_reg_time	DATETIME	评论用户的注册时间

3.3 dim_comment_post:

列名	数据类型	描述
post_id	INT	评论帖子ID，主键
post_title	VARCHAR	评论帖子的标题
post_create_time	DATETIME	评论帖子的创建时间

3.4 dim_comment_interaction:

列名	数据类型	描述
comment_id	INT	评论ID，主键
like_count	INT	评论的点赞数
reply_count	INT	评论的回复数

CREATE TABLE dim_user (
  user_id INT PRIMARY KEY,
  username VARCHAR(50),
  gender VARCHAR(10),
  age INT,
  birthday DATE,
  register_time DATETIME,
  last_login_time DATETIME
);

以下是上面四个表格的 My SQL 建表语句：

3.1用户基本信息表

CREATE TABLE dim_user (
  user_id INT PRIMARY KEY,
  username VARCHAR(50),
  gender VARCHAR(10),
  age INT,
  birthday DATE,
  register_time DATETIME,
  last_login_time DATETIME
);

3.2评论用户维度表

CREATE TABLE dim_comment_user (
    user_id INT PRIMARY KEY,
    user_name VARCHAR(255),
    user_gender VARCHAR(10),
    user_age INT,
    user_birthday DATE,
    user_reg_time DATETIME
);

3.3评论帖子维度表

CREATE TABLE dim_comment_post (
    post_id INT PRIMARY KEY,
    post_title VARCHAR(255),
    post_create_time DATETIME
);

3.4 评论交互维度表

CREATE TABLE dim_comment_interaction (
    comment_id INT PRIMARY KEY,
    like_count INT,
    reply_count INT,
    FOREIGN KEY (comment_id) REFERENCES dim_comment(comment_id)
);

4. 好友类型表

表名	描述
dim_friendship	好友关系维度表，包括好友关系的用户ID、好友的用户ID等

列名	数据类型	描述
friendship_id	INT	好友关系ID，主键
friendship_user_id	INT	好友关系的用户ID，外键指向dim_user表中的user_id
friendship_friend_id	INT	好友关系的好友ID，外键指向dim_user表中的user_id
friendship_create_time	DATETIME	好友关系创建时间
friendship_update_time	DATETIME	好友关系更新时间
friendship_remark_name	VARCHAR	好友关系的备注名
friendship_friend_group	VARCHAR	好友关系的好友分组

CREATE TABLE dim_friendship (
    friendship_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    friendship_user_id INT NOT NULL,
    friendship_friend_id INT NOT NULL,
    friendship_create_time DATETIME,
    friendship_update_time DATETIME,
    friendship_remark_name VARCHAR(255),
    friendship_friend_group VARCHAR(255),
    FOREIGN KEY (friendship_user_id) REFERENCES dim_user(user_id),
    FOREIGN KEY (friendship_friend_id) REFERENCES dim_user(user_id)
);

5. 消息类型表

表名	描述
dim_message	消息基本信息维度表，包括消息ID、消息内容、发送时间等
dim_message_sender	消息发送方维度表，包括发送方用户ID、用户名等
dim_message_receiver	消息接收方维度表，包括接收方用户ID、用户名等
dim_message_type	消息类型维度表，包括私信、群聊、公告等不同类型的消息
dim_message_status	消息状态维度表，包括消息的发送状态、接收状态等

5.1消息基本信息维度表

列名	数据类型	描述
message_id	INT	消息ID，主键
message_content	TEXT	消息内容
message_time	DATETIME	消息发送时间
message_type_id	INT	消息类型ID，外键指向dim_message_type表中的type_id
message_sender_id	INT	消息发送方ID，外键指向dim_message_sender表中的user_id

5.2消息发送方维度表

列名	数据类型	描述
sender_id	INT	发送方用户ID，主键
sender_name	VARCHAR	发送方用户名
sender_gender	VARCHAR	发送方用户性别
sender_age	INT	发送方用户年龄
sender_birthday	DATE	发送方用户生日
sender_reg_time	DATETIME	发送方用户注册时间

5.3消息接收方维度表

列名	数据类型	描述
receiver_id	INT	接收方用户ID，主键
receiver_name	VARCHAR	接收方用户名
receiver_gender	VARCHAR	接收方用户性别
receiver_age	INT	接收方用户年龄
receiver_birthday	DATE	接收方用户生日
receiver_reg_time	DATETIME	接收方用户注册时间

5.4消息类型维度表

列名	数据类型	描述
type_id	INT	消息类型ID，主键
type_name	VARCHAR	消息类型名称
type_desc	TEXT	消息类型描述

5.5消息状态维度表

列名	数据类型	描述
status_id	INT	消息状态ID，主键
status_name	VARCHAR	消息状态名称
status_description	TEXT	消息状态描述

dim_user:

CREATE TABLE dim_user (
    user_id INT PRIMARY KEY,
    user_name VARCHAR(255),
    user_gender VARCHAR(255),
    user_age INT,
    user_birthday DATE,
    user_reg_time DATETIME
);

dim_post:

CREATE TABLE dim_post (
    post_id INT PRIMARY KEY,
    post_title VARCHAR(255),
    post_content TEXT,
    post_create_time DATETIME,
    post_user_id INT,
    FOREIGN KEY (post_user_id) REFERENCES dim_user(user_id)
);

dim_comment:

CREATE TABLE dim_comment (
    comment_id INT PRIMARY KEY,
    comment_user_id INT,
    comment_post_id INT,
    comment_content TEXT,
    comment_time DATETIME,
    FOREIGN KEY (comment_user_id) REFERENCES dim_user(user_id),
    FOREIGN KEY (comment_post_id) REFERENCES dim_post(post_id)
);

dim_friendship:

CREATE TABLE dim_friendship (
    friendship_id INT PRIMARY KEY,
    friendship_user_id INT,
    friendship_friend_id INT,
    friendship_create_time DATETIME,
    friendship_update_time DATETIME,
    friendship_remark_name VARCHAR(255),
    friendship_friend_group VARCHAR(255),
    FOREIGN KEY (friendship_user_id) REFERENCES dim_user(user_id),
    FOREIGN KEY (friendship_friend_id) REFERENCES dim_user(user_id)
);

dim_message:

CREATE TABLE dim_message (
    message_id INT PRIMARY KEY,
    message_content TEXT,
    message_time DATETIME,
    message_type_id INT,
    message_sender_id INT,
    FOREIGN KEY (message_type_id) REFERENCES dim_message_type(type_id),
    FOREIGN KEY (message_sender_id) REFERENCES dim_message_sender(user_id)
);

dim_message_type:

CREATE TABLE dim_message_type (
    type_id INT PRIMARY KEY,
    type_name VARCHAR(255)
);

dim_message_receiver:

CREATE TABLE dim_message_receiver (
    user_id INT PRIMARY KEY,
    user_name VARCHAR(255)
);

dim_message_status:

CREATE TABLE dim_message_status (
    status_id INT PRIMARY KEY,
    status_name VARCHAR(255)
);

​​​​​​​

6.视频类型表

表名	描述
dim_video	视频基本信息维度表，包括视频ID、标题、描述等
dim_video_location	视频位置维度表，包括视频所在地理位置信息
dim_video_interaction	视频交互维度表，包括视频的观看数、点赞数、评论数等

6.1视频基本信息维度表

列名	数据类型	描述
video_id	INT	视频ID，主键
video_title	VARCHAR	视频标题
video_description	TEXT	视频描述
video_upload_time	DATETIME	视频上传时间
video_location_id	INT	视频位置ID，外键指向dim_video_location表中的location_id
video_interaction_id	INT	视频交互ID，外键指向dim_video_interaction表中的interaction_id

6.2视频位置维度表

列名	数据类型	描述
location_id	INT	位置ID，主键
location_name	VARCHAR	位置名称
location_address	VARCHAR	位置详细地址
location_latitude	FLOAT	位置的纬度
location_longitude	FLOAT	位置的经度

6.3视频交互维度表

列名	数据类型	描述
interaction_id	INT	交互ID，主键
video_id	INT	视频ID，外键指向dim_video表中的video_id
watch_count	INT	观看数
like_count	INT	点赞数
dislike_count	INT	点踩数
favorite_count	INT	收藏数
comment_count	INT	评论数

CREATE TABLE dim_video (
    video_id INT PRIMARY KEY,
    video_title VARCHAR(255),
    video_description TEXT,
    video_create_time DATETIME,
    video_duration INT,
    video_uploader_id INT,
    video_category_id INT,
    video_location_id INT,
    video_interaction_id INT
);

CREATE TABLE dim_video_location (
    location_id INT PRIMARY KEY,
    location_name VARCHAR(255),
    location_latitude DECIMAL(10, 6),
    location_longitude DECIMAL(10, 6),
    location_city VARCHAR(255),
    location_province VARCHAR(255),
    location_country VARCHAR(255)
);

CREATE TABLE dim_video_interaction (
    interaction_id INT PRIMARY KEY,
    video_id INT,
    view_count INT,
    like_count INT,
    dislike_count INT,
    comment_count INT,
    FOREIGN KEY (video_id) REFERENCES dim_video (video_id)
);

在实际情况中，还可能需要其他类型的表来记录不同类型的数据，例如搜索记录表、广告点击表、活动参与表等。这些表的具体列名和数据类型需要根据实际需求进行调整。