How to find the nearest neighbor in Hive? Any windowing function?
<p>Given a table </p>

<blockquote>
<pre><code>$cat data.csv

ID,State,City,Price,Flag
1,CA,A,95,0
2,CA,A,96,1
3,CA,A,195,1
4,NY,B,124,0
5,NY,B,128,1
6,NY,C,24,0
7,NY,C,27,1
8,NY,C,29,0
9,NY,C,39,1
</code></pre>
</blockquote>

<p>Expected Result:</p>

<pre><code>ID0, ID1
1,2
4,5
6,7
8,7
</code></pre>

<p>for each ID with Flag=0 above, we want to find another ID from Flag=1, with the same "State" and "City", and the nearest Price.</p>

<p>I have two rough stupid ideas:</p>

<p>Method 1. </p>

<pre><code>Use a left outer join with the table itself on 
    (a.State=b.State and a.City=b.city and a.Flag=0 and b.Flag=1),
     where a.Flag=0 and b.Flag=1, 

    and then use RANK() over (partitioned by a.State,a.City order by a.Price - b.Price) as rank
    where rank=1
</code></pre>

<p>Method 2. </p>

<pre><code>Use a left outer join with the table itself, 
on 
(a.State=b.State and a.City=b.city and a.Flag=0 and b.Flag=1),
 where a.Flag=0 and b.Flag=1, 

and then Use Distribute by a.State,a.City Sort by Price_Diff ASC limit 1 
</code></pre>

<p>What's the best way to find the nearest neighbor in Hive? 
Any valuable tips will be greatly appreciated!</p>