title: Spark SQL Group By Consecutive Sequence of Integers
body: <p>So I have a table which I want to create events from it. My user is watching a video which is defined as a list of sub_parts, with bytes downloaded for every sub_part asked from.</p>
  
  <p>For example Alice is watching a video with 15 parts of 5 seconds, she watched the first three parts then she skipped to the part 7 and played two more parts, but in the end she never finished the video.</p>
  
  <p>So I want to recreate this trace of events for every user with Spark SQL (and most likely UDF but help me with this, I don't understand how can I make it work)</p>
  
  <pre class="lang-none prettyprint-override"><code>+---+------------+-------------+-------------+
  |   |   Name     | Video_part  | Bytes Dl    |
  +---+------------+-------------+-------------+
  | 1 | Alice      |       1     |      200    |
  | 2 | Alice      |       2     |      250    |
  | 3 | Alice      |       3     |      400    |
  | 1 | Alice      |       7     |      100    |
  | 2 | Alice      |       8     |      200    |
  | 3 | Bob        |       1     |     1000    |
  | 1 | Bob        |       32    |      500    |
  | 2 | Bob        |       33    |      400    |
  | 3 | Bob        |       34    |      330    |
  | 1 | Bob        |       15    |      800    |
  | 2 | Bob        |       16    |      400    |
  +---+------------+-------------+-------------+
  </code></pre>
  
  <p>So what I want is to group by consecutive integers in video_part which are my events <strong>play</strong>, and when there is a break in this consecutive list, this is either an event <strong>skin_in</strong> or <strong>skip_out</strong>, for each portion of <strong>play</strong> I wanna get the mean of the bytes downloaded too :</p>
  
  <pre class="lang-none prettyprint-override"><code>+---+------------+-------------+-------------+-------------+-------------+
  |   |   Name     | Number_play |    Event    | Number_skips| Mean_BytesDL|
  +---+------------+-------------+-------------+-------------+-------------+
  | 1 | Alice      |       3     |     Play    |       0     |      283,3  |
  | 2 | Alice      |       0     |    Skip_in  |       4     |      0      |
  | 3 | Alice      |       2     |     Play    |       0     |      150    |
  | 1 | Bob        |       1     |     Play    |       0     |      1000   |
  | 2 | Bob        |       0     |    Skip_in  |       31    |      0      |
  | 3 | Bob        |       3     |     Play    |       0     |      410    |
  | 2 | Bob        |       0     |    Skip_out |       19    |      0      |
  | 3 | Bob        |       2     |     Play    |       0     |      600    |
  +---+------------+-------------+-------------+-------------+-------------+
  </code></pre>
  
  <p>Problem is I can do it in Python or in Scala using respectively sub_pandas df with loops or sublists with map and foreach, but it is taking too long to run it on 1 To of datas. Even if I run it on my cluster of nodes.</p>
  
  <p>So I'm wondering is there a way to do it in Spark SQL, I have researched a little over UDF with Groupby, flatMap or Agg. But I'm having trouble as this is completely new for me, hope you can help me somehow !</p>
  
  <p>I was thinking with something like :</p>
  
  <ul>
  <li>SortBy Name</li>
  <li>Through every unique name :</li>
  <li>Aggregate the video_part with UDF -> which creates three new columns
  with one which the avg of the bytesDL on the portion</li>
  </ul>
  
  <p>I know this is pretty specific but maybe someone can help me,</p>
  
  <p>Thanks in advance and have a good day !</p>