Hive LAG function - year over year change - to get previous value even if there is no current vaue
<p>I am calculating year over year change and below is the query in hive</p>

<pre><code>    select Name, 
    Year, 
    Month, 
    ID,
    value as Current, 
    LAG(value,1,0) OVER(PARTITION BY name,month,id ORDER BY year ASC) as Previous, 
    value - LAG(value,1,0) OVER(PARTITION BY name,month,id ORDER BY year ASC) as Change
</code></pre>

<p>Current Result</p>

<pre><code>    Name    Year    Month   ID  Current     Previous        Change
    A       2015    1       8320    944     0               944
    O       2015    1       8320    153     0               153
    O       2016    1       8320    133.92  153             -19.08
    B       2015    1       8320    572     0               572
    C       2016    1       8320    126     0               126
</code></pre>

<p>There are 3 cases for the year 2016</p>

<ol>
<li>Name O - Has both values for 2016 and 2015 and I get difference</li>
<li>Name C - Has value only in 2016 and I get the difference keeping 2015 as 0</li>
<li>Name A and B - Has values only in 2015 and not in 2016 - So I need to get two entries with change as -944 and -572 for A and B respectively.</li>
</ol>

<p>My expected result is:</p>

<pre><code>    Name    Year    Month   ID  Current     Previous        Change
    A       2015    1       8320    944     0               944
    A       2016    1       8320    0       944             -944
    O       2015    1       8320    153     0               153
    O       2016    1       8320    133.92  153             -19.08
    B       2015    1       8320    572     0               572
    B       2016    1       8320    0       572             -572
    C       2016    1       8320    126     0               126
</code></pre>

<p>Please guide me how to get the previous year values, even if there are no current year values.Thanks for the help in advance.</p>