apache - 大数据 hive 查询

Question

我有以下数据

Policy_Name  IssueYear   ExpiryYear
A            2001        2003
B            2003        2006

我需要如下输出:

Policy_Name ActiveYear
A           2001
A           2002
A           2003
B           2003
B           2004 
B           2005
B           2006

Answer 1

select  t.Policy_Name 
       ,t.IssueYear + pe.i  as ActiveYear

from    mytable t
        lateral view posexplode (split (space (ExpiryYear - IssueYear),' ')) pe as i,x
;

---

+---------------+------------+
| t.policy_name | activeyear |
+---------------+------------+
| A             |       2001 |
| A             |       2002 |
| A             |       2003 |
| B             |       2003 |
| B             |       2004 |
| B             |       2005 |
| B             |       2006 |
+---------------+------------+