title: In hive, how to do a calculation among 2 rows?
body: <p>I have this table.</p>
  
  <pre><code>   +------------------------------------------------------------+
     |     ks      |      time     |     val1      |    val2      | 
     +-------------+---------------+---------------+--------------+
     |     A       |       1       |       1       |      1       |
     |     B       |       1       |       3       |      5       |
     |     A       |       2       |       6       |      7       |
     |     B       |       2       |      10       |     12       |
     |     A       |       4       |       6       |      7       |
     |     B       |       4       |      20       |     26       |
     +------------------------------------------------------------+
  </code></pre>
  
  <p>What I want to get is for each row,</p>
  
  <pre><code>ks |  time |  val1 | val1 of next ts of same ks  |
  </code></pre>
  
  <p>To be clear, result of above example should be,</p>
  
  <pre><code>   +------------------------------------------------------------+
     |     ks      |      time     |     val1      |   next.val1  | 
     +-------------+---------------+---------------+--------------+
     |     A       |       1       |       1       |       6      |
     |     B       |       1       |       3       |       10     |
     |     A       |       2       |       6       |       6      |
     |     B       |       2       |      10       |       20     |
     |     A       |       4       |       6       |      null    |
     |     B       |       4       |      20       |      null    |
     +------------------------------------------------------------+
  </code></pre>
  
  <p>(I need the same next for value2 as well)</p>
  
  <p>I tried a lot to come up with a hive query for this, but still no luck. I was able to write a query for this in sql as mentioned <a href="https://stackoverflow.com/questions/634568/how-to-get-difference-between-two-rows-for-a-column-field">here</a> (Quassnoi's answer), but couldn't create the equivalent in hive because hive doesn't support subqueries in select. </p>
  
  <p>Can someone please help me achieve this? </p>
  
  <p>Thanks in advance. </p>
  
  <p>EDIT:</p>
  
  <p>Query I tried was,</p>
  
  <pre><code>SELECT ks, time, val1, next[0] as next.val1 from
  (SELECT ks, time, val1
         COALESCE(
         (
         SELECT Val1, time
         FROM myTable mi
         WHERE mi.val1 &gt; m.val1 AND mi.ks = m.ks
         ORDER BY time
         LIMIT 1
         ), CAST(0 AS BIGINT)) AS next
  FROM  myTable m
  ORDER BY time) t2;
  </code></pre>