【某公司SQL 面试题-视频案例分析及解答】

最新推荐文章于 2024-09-10 20:50:28 发布

终有一刻

最新推荐文章于 2024-09-10 20:50:28 发布

阅读量719

点赞数 6

分类专栏：大数据文章标签： sql 音视频数据库

本文链接：https://blog.csdn.net/weixin_42732268/article/details/140532656

版权

题目

3 张表表结构，写出具体求解的 SQL 代码
（搞笑品类定义：视频分类或者视频创建者分类为搞笑）
具体表结构如下
表1 用户行为表： t_user_video_action_d

分区：ds 格式 yyyyMMdd）
主键：user_id 、video_id
含义：一个用户对一个视频的所有行为聚合，每天增量

在这里插入图片描述
表2 视频表： t_video_d

分区：

ds （格式 yyyyMMdd）
主键： video_id
含义：当天全量视频数据

表3 视频创建者表： t_video_user_d
分区： ds （格式 yyyyMMdd）
主键： video_user_id
含义：当天全量视频创建者数据

问题解答：

题目 1 ：输出一张搞笑品类 dws 表，用于分析作者近 3 条视频情况：输出每个视频创建者user_id 最近发布的 3 个视频，在过去一周内的曝光，点赞总数；需要产出字段：视频创建者 user_id ，曝光用户数，点赞用户数，曝光次数、点赞次数

思路如下：
1、利用窗口函数row_number() 排序函数将每个用户的每个视频发布日期进行排序，并选择最近三天的视频，即rank<=3
2、使用left join 关联 t_user_video_action_d表，得出视频的曝光数据，点赞数据
3、最后利用分组函数求每个用户总的点赞数和曝光数

构建表和相应的数据如下：

--创建用户行为表--
create table t_user_video_action_d
(
ds int,
user_id int,
video_id VARCHAR2(20),
action_type int,
timestamp  number(19,0),
CONSTRAINT action_type_check CHECK(action_type in(1,2))
);
--创建视频表--

create table t_video_d
(
ds int,
video_id VARCHAR2(20),
video_type  VARCHAR2(20),
video_user_id VARCHAR2(20),
video_create_time  number(19,0),
video_description VARCHAR2(20),
CONSTRAINT video_type_check CHECK(video_type in('娱乐','欣慰','搞笑'))
);
--drop table t_video_d

--视频创建者表
create table t_video_user_d
(
ds int,
video_user_id VARCHAR2(20),
video_user_name VARCHAR2(20),
video_user_type  VARCHAR2(20),
CONSTRAINT video_user_type_check CHECK(video_user_type in('娱乐','欣慰','搞笑'))
);

insert all 
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100000,'000000101',1,202409030201)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100001,'000000102',2,202409011502)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100002,'000000103',1,202409032115)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100000,'000000104',1,202409031101)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100001,'000000105',1,202409021402)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100002,'000000106',2,202409012215)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100000,'000000107',1,202409031101)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100001,'000000118',1,202409041402)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100002,'000000119',2,202409041221)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100000,'000000110',2,202409072225)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100001,'000000111',1,202409041402)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100002,'000000112',1,202409041402)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100000,'000000113',2,202409072225)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100001,'000000114',1,202409041402)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100002,'000000115',2,202409072225)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100000,'000000116',1,202409041402)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100001,'000000117',1,202409041402)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100002,'000000118',2,202409072225)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100000,'000000119',1,202409041402)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100001,'000000120',1,202409041402)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100002,'000000121',2,202409072225)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100000,'000000122',1,202409041402)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100001,'000000123',1,202409041402)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100002,'000000124',2,202409072225)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100000,'000000125',1,202409041402)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100001,'000000126',1,202409041402)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100002,'000000127',1,202409041402)

into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100000,'000000121',2,202409072125)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100001,'000000116',1,202409041302)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100002,'000000119',2,202409041021)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100000,'000000125',1,202409041102)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100001,'000000104',1,202409032201)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100002,'000000126',1,202409041122)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100000,'000000101',1,202409030901)
into t_user_video_action_d(ds,user_id,video_id,action_type,timestamp)VALUES(20240709,100001,'000000126'