本文档介绍如何利用Hive进行电影票房数据的统计分析，涵盖五个典型的业务分析场景。

场景一：2020年度票房冠军榜TOP10

首先进入Hive环境并创建专用数据库：

hive

create database if not exists cinema_db;

use cinema_db;

建立票房数据存储表，使用制表符作为字段分隔符：

create table movie_data(
    movie_name string,
    box_office string,
    box_rate string,
    sessions string,
    show_count_rate string,
    avg_number string,
    attendance string,
    total_box_office string,
    movie_days string,
    record_time string,
    release_date string
) 
row format delimited fields terminated by '\t' 
stored as textfile;

加载本地预处理数据文件：

load data local inpath '/data/workspace/myshixun/data/movies.txt' into table movie_data;

创建结果汇总表：

create table top10_gross(
    movie_name string, 
    gross_revenue float
) 
row format delimited fields terminated by '\t' 
stored as textfile;

执行查询并导出结果：

insert overwrite table top10_gross 
select movie_name, max(round(total_box_office, 1)) as revenue
from movie_data 
where release_date like '2020%'
group by movie_name  
order by revenue desc 
limit 10;

场景二：2020年国庆档票房增长分析

统计国庆黄金周期间票房表现前三的影片及其逐日票房数据。

create table national_day_gross(
    movie_name string, 
    daily_gross float,
    record_date string
) 
row format delimited fields terminated by '\t' 
stored as textfile;

insert overwrite table national_day_gross 
select movie_name, box_office, record_time 
from movie_data 
where movie_name in (
    select t.movie_name from (
        select movie_name, sum(box_office) as total 
        from movie_data 
        where record_time between '2020-10-01' and '2020-10-07' 
        group by movie_name 
        order by total desc 
        limit 3
    ) as t
) 
and record_time between '2020-10-01' and '2020-10-07';

场景三：2020年度单日票房峰值统计

筛选2020年度单日综合票房最高的十天。

create table daily_peak_gross(
    record_date string, 
    daily_total float
) 
row format delimited fields terminated by '\t' 
stored as textfile;

insert overwrite table daily_peak_gross 
select record_time, round(sum(box_office), 2) as total
from movie_data 
where release_date like '2020%'
group by record_time  
order by total desc 
limit 10;

场景四：2020年首映影片首周票房追踪

提取2020年首映的电影上映后七天的完整票房记录。

create table first_week_gross(
    movie_name string,
    record_date string, 
    daily_gross float
) 
row format delimited fields terminated by '\t' 
stored as textfile;

insert overwrite table first_week_gross 
select 
    t.movie_name,
    movie_data.record_time,
    box_office 
from movie_data 
left join (
    select movie_name, record_time 
    from movie_data 
    where movie_days = '上映首日' and release_date like '2020%'
    group by movie_name, record_time
) t on movie_data.movie_name = t.movie_name 
where movie_data.record_time between t.record_time and date_add(t.record_time, 6)
order by t.movie_name, movie_data.record_time;

场景五：节假日观影人次对比分析

统计2020年元旦假期与国庆假期后七天的观影总人数。

create table holiday_attendance(
    day_string string,
    holiday_name string, 
    total_attendance bigint
) 
row format delimited fields terminated by '\t' 
stored as textfile;

insert overwrite table holiday_attendance 
select 
    split(record_time, '-')[2],
    case 
        when t.record_time between '2020-10-01' and '2020-10-07' then 'national_day' 
        when t.record_time between '2020-01-01' and '2020-01-07' then 'new_year_day' 
        else 'other' 
    end as holiday_name,
    cast(sum(attendance_num) as bigint) 
from (
    select record_time, avg_number * sessions as attendance_num 
    from movie_data 
    where record_time between '2020-10-01' and '2020-10-07' 
       or record_time between '2020-01-01' and '2020-01-07'
) t 
group by record_time;

总结

通过上述五个典型业务场景的Hive SQL实现，可以掌握数据仓库中进行票房数据分析的核心方法，包括数据导入、分组聚合、多表连接、条件筛选等操作。这些分析结果对于电影行业的票房预测、排片决策等具有重要参考价值。