使用Hive的窗口函数进行数据分析——以股票市场分析为例

迷南。 2023-07-24 03:12 116阅读 0赞

**声明：本文主要是实现利用Hive常用的窗口函数和一些数据分析思维分析数据，只是套用在股票数据的例子上，因此并不适用于提高投资技巧！**

我们先看一下常用Hive中常用的窗口：

PRECEDING：往前
    FOLLOWING：往后
    CURRENT ROW：当前行
    UNBOUNDED：起点（一般结合PRECEDING，FOLLOWING使用）
    UNBOUNDED PRECEDING 表示该窗口最前面的行（起点）
    UNBOUNDED FOLLOWING：表示该窗口最后面的行（终点）

接下来我们将会使用Hive的窗口函数分析股票数据

**1.观察数据**  
假设我们有两个市场，分别是代表中国蓝筹股的沪深300指数，一个是美国的标普500指数。数据时通过每天记录下来的，具体我们使用18年3月的数据（为了计算收益率，我们前后延长一天，即18年2月28日到18年4月1日），如下图：

use dmb_dev;
    drop table if exists dmb_dev.market_index_data_dayily;
    
    CREATE TABLE dmb_dev.market_index_data_dayily
     (
      market string comment '股票市场',
      trade_date string comment '交易日期', 
      index_value double comment '价格'
     )
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
    STORED AS TEXTFILE;
    
    set hive.cli.print.header=true; 
    set hive.resultset.use.unique.column.names=false; 
    use dmb_dev;
    load data local inpath 'index_data.csv' into table dmb_dev.market_index_data_dayily;
    
    select * from dmb_dev.market_index_data_dayily limit 10;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE1MTcxMzI_size_16_color_FFFFFF_t_70]  
可以看到数据时按照时间排列，由于沪深300和标普500在2018年2月的起点不同通，因此直接比较指数的值是没有意义的。

**1.LEAD()，LAG()计算收益率**  
先看两个函数

1.  .LEAD(col,n,DEFAULT)  
    用于统计窗口内往下第n行值第一个参数为列名，第二个参数为往下第n行（可选，默认为1，不可为负数），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）
2.  LAG(col,n,DEFAULT)  
    用于统计窗口内往上第n行值第一个参数为列名，第二个参数为往上第n行（可选，默认为1，不可为负数），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

select *
     , lead(index_value,1,-999) over (partition by market order by trade_date) as lead_1_value
     , lag(index_value,1,-999) over (partition by market order by trade_date) as lag_1_value 
    from market_index_data_dayily;

部分结果如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE1MTcxMzI_size_16_color_FFFFFF_t_70 1]  
看到lead\_1\_value是后一行的index\_value，lag\_1\_value 是前一行的index\_value。  
LEAD和LAG在这里有什么用呢，**我们可以利用LEAD或LAG计算收益率。这里我们使用LAG计算收益率，收益率计算公式采用对数收益率： r = l n ( p t / p t − 1 ) r = ln(p\_t/p\_\{t-1\}) r=ln(pt/pt−1)，使用对数收益率的好处是满足可加性。**

use dmb_dev;
    drop table if exists dmb_dev.market_index_profit_dayily;
    create table dmb_dev.market_index_profit_dayily as
    select *
     , round(log(index_value/lag(index_value,1,-999) over (partition by market order by treade_date)), 4) as profit_rate  --计算对数收益率
    from market_index_data_dayily
    ;
    
    select * from market_index_profit_dayily;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE1MTcxMzI_size_16_color_FFFFFF_t_70 2]  
可以看到profit\_rate即是我们计算出的对数收益率，我们同时计算了trade\_date所在的周数和星期几（0代表周天），后面会用到。

**2.FIRST\_VALUE()，LAST\_VALUE()查看排名最早最晚观测值**

1.  FIRST\_VALUE  
    取分组内排序后，截止到当前行，第一个值，这最多需要两个参数。第一个参数是您想要第一个值的列，第二个（可选）参数必须是false默认为布尔值的布尔值。如果设置为true，则跳过空值。
2.  LAST\_VALUE  
    取分组内排序后，截止到当前行,最后一个值，这最多需要两个参数。第一个参数是您想要第一个值的列，第二个（可选）参数必须是false默认为布尔值的布尔值。如果设置为true，则跳过空值。

我们可以查看截止到每个trade\_date的第一个收益率和最后一个收益率：

select *
     --表示从起点到当前行
     ,first_value(profit_rate) over (partition by market order by trade_date rows between unbounded preceding and current row) as first_value_pr
     ,last_value(profit_rate) over (partition by market order by trade_date rows between unbounded preceding and current row) as last_value_pr
    from market_index_profit_dayily
    --除去周六周天
    where trade_date>='2018-03-01' and trade_date<='2018-03-31' and weeks_num not in (0,6);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE1MTcxMzI_size_16_color_FFFFFF_t_70 3]

我们还可以加入window子句，使用自己想使用的窗口长度，来观察数据：

select *
     --不限制窗口
     ,first_value(profit_rate) over (partition by market order by trade_date) as first_value_pr
     --表示从起点到当前行
     ,first_value(profit_rate) over (partition by market order by trade_date rows between unbounded preceding and current row) as window_first_value_pr
     ,last_value(profit_rate) over (partition by market order by trade_date) as last_value_pr
     --表示起点到终点
     ,last_value(profit_rate) over (partition by market order by trade_date rows between unbounded preceding and unbounded following) as window_last_value_pr
     --前后两天窗口
     ,first_value(profit_rate) over (partition by market order by trade_date rows between 2 preceding AND 3 following) as window_2_first_value_pr
     ,last_value(profit_rate) over (partition by market order by trade_date rows between 2 preceding AND 3 following) as window_2_last_value_pr
    from market_index_profit_dayily
    --除去周六周天
    where trade_date>='2018-03-01' and trade_date<='2018-03-31' and weeks_num not in (0,6);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE1MTcxMzI_size_16_color_FFFFFF_t_70 4]  
查询结果如上图

**3.SUM()，MAX()，MIN()，AVG()计算股票窗口内累计收益率等指标**  
实际上，大部分的数学计算相关的聚合函数，大多都能套用在窗口函数中。  
此处我们使用SUM()举例，计算每种指数在当月和每周，以及指定窗口的的累积收益率，对数收益率可以直接sum计算累积收益率。**通过累计收益率、平均收益率、最小收益率（一定程度代表风险）来研究资产的走势和特点**

select *
     --每种之中在当月的累计收益率
     ,SUM(profit_rate) over (partition by market order by trade_date) as sum_month_pr_1 
     --每种之中在每周的累计收益率
     ,SUM(profit_rate) over (partition by market, weeks order by trade_date) as sum_week_pr_1
     --表示从起点到当前行，与sum_month_pr_1结果一致
     ,SUM(profit_rate) over (partition by market order by trade_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sum_pr_2 
     --表示窗口内所有行，和直接sum()  groupby 效果一样
     ,SUM(profit_rate) over (partition by market) as sum_pr_3 
     --表示起点到终点，和直接sum()  groupby 效果一样
     ,SUM(profit_rate) over (partition by market order by trade_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as sum_pr_4 
     ----表示前2行到后面3行的窗口的累积收益率
     ,SUM(profit_rate) over (partition by market order by trade_date ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING) as sum_pv_5 
    from market_index_profit_dayily
    --除去周六周天
    where trade_date>='2018-03-01' and trade_date<='2018-03-31' and weeks_num not in (0,6)
    order by market, trade_date;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE1MTcxMzI_size_16_color_FFFFFF_t_70 5]  
**4.ROW\_NUMBER()，RANK()，DENSE\_RANK()对窗口内数据进行排序**  
.ROW\_NUMBER  
从1开始，按照顺序，生成分组内记录的序列,row\_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列;通常用于获取分组内排序第一的记录;获取一个session中的第一条refer等。  
2.RANK  
生成数据项在分组中的排名，排名相等会在名次中留下空位。产生类似1、1、3的排名  
3.DENSE\_RANK  
生成数据项在分组中的排名，排名相等会在名次中不会留下空位。产生类似1、1、2的排名

我们可以计算股票在窗口期内的排名情况（在实际分析中，其实没太大用，我们更关心股票的趋势，而不是收益率在每天的排名）

select *
     --当月每种资产的净值排名
     ,ROW_NUMBER() over (partition by market order by index_value) as row_number_month_pv_1
     --每周每种资产的净值排名
     ,RANK() over (partition by market, weeks order by index_value) as row_number_week_pv_2
     --当月每种资产的净值排名，会留空位
     ,RANK() over (partition by market order by index_value) as row_number_pv_2
     --当月每种资产的净值排名，不会留空位
     ,DENSE_RANK() over (partition by market order by index_value) as row_number_pv_3
    from market_index_profit_dayily
    where trade_date>='2018-03-01' and trade_date<='2018-03-31' ;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE1MTcxMzI_size_16_color_FFFFFF_t_70 6]  
光靠窗口函数分析股票净值数据肯定是不够的，我们还需要进一步分析收益、风险、风险调整收益和其他环境因素才能对一只股票有一定的了解。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE1MTcxMzI_size_16_color_FFFFFF_t_70]: /images/20230528/2dcbfe47d2154206b05a2f2c0543547e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE1MTcxMzI_size_16_color_FFFFFF_t_70 1]: /images/20230528/9865e37abd3a4473a573a3aa6c428f22.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE1MTcxMzI_size_16_color_FFFFFF_t_70 2]: /images/20230528/177c6e4122f44ae2b7b262bb481164e9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE1MTcxMzI_size_16_color_FFFFFF_t_70 3]: /images/20230528/a52f1242711549d39639b79906e846eb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE1MTcxMzI_size_16_color_FFFFFF_t_70 4]: /images/20230528/f36366452f074417ae476895ba62ef90.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE1MTcxMzI_size_16_color_FFFFFF_t_70 5]: /images/20230528/bd4b1da5f2484947bda99c34204b2794.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE1MTcxMzI_size_16_color_FFFFFF_t_70 6]: /images/20230528/5c4ab49ebea44fa18468b50cd09a2686.png