title: Apache Spark - How to design own aggregation with Window Operations on Event Time
body: <p>I want to calculate the <strong>ratio of supply and demand</strong>. Data recode with time_to_pick_up,time_to_drop_off, pick_up_location,drop_off_location fields.</p>
  <p>2019-01-01 00:22:21,2019-01-01 00:43:43,Union Sq,SoHo</p>
  <p>I am going to split the record to two record so data will looks like below:</p>
  <p>2019-01-01 00:22:21, Union Sq, PICK</p>
  <p>2019-01-01 00:43:43, SoHo, DROP</p>
  <p>2019-01-01 00:22:23, Union Sq, DROP</p>
  <p>....</p>
  <p>The ratio = (PICK+ DROP)/PICK = 2/1 = 2
  (because DROP means the car is available so the supply count was added one.
  ; the record for PICK will contribute demand and supply at same time
  ) And I want to report the ratio <strong>within one hour every 5 mins</strong></p>
  <pre><code> val perMinCount = ds.filter(col(&quot;zone&quot;)== &quot;Union Sq&quot;).withWatermark(&quot;datetime&quot;, &quot;10 minutes&quot;)
      .groupBy($&quot;zone&quot;,window($&quot;datetime&quot;, &quot;1 hours&quot;,&quot;5 minutes&quot;)).agg(?)
  </code></pre>
  <p>This is what I do so far. How can I achieve it ?</p>