title: Spark SQL can use FIRST_VALUE and LAST_VALUE in a GROUP BY aggregation (but it's not standard)
body: <p>(Tested on Spark 2.2 and 2.3)</p>
  <p>I am using Spark to aggregate stock trading ticks into daily OHLC (open-high-low-close) records.</p>
  <p>The input data is like</p>
  <pre><code>val data = Seq((&quot;2018-07-11 09:01:00&quot;, 34.0), (&quot;2018-07-11 09:04:00&quot;, 32.0), (&quot;2018-07-11 09:02:00&quot;, 35.0), (&quot;2018-07-11 09:03:00&quot;, 30.0), (&quot;2018-07-11 09:00:00&quot;, 33.0), (&quot;2018-07-12 09:01:00&quot;, 56.0), (&quot;2018-07-12 09:04:00&quot;, 54.0), (&quot;2018-07-12 09:02:00&quot;, 51.0), (&quot;2018-07-12 09:03:00&quot;, 50.0), (&quot;2018-07-12 09:00:00&quot;, 51.0)).toDF(&quot;time&quot;, &quot;price&quot;)
  
  data.createOrReplaceTempView(&quot;ticks&quot;)
  
  data.show
  
  scala&gt;
  </code></pre>
  <p>shown as</p>
  <pre><code>+-------------------+-----+
  |               time|price|
  +-------------------+-----+
  |2018-07-11 09:01:00| 34.0|
  |2018-07-11 09:04:00| 32.0|
  |2018-07-11 09:02:00| 35.0|
  |2018-07-11 09:03:00| 30.0|
  |2018-07-11 09:00:00| 33.0|
  |2018-07-12 09:01:00| 56.0|
  |2018-07-12 09:04:00| 54.0|
  |2018-07-12 09:02:00| 51.0|
  |2018-07-12 09:03:00| 50.0|
  |2018-07-12 09:00:00| 51.0|
  +-------------------+-----+
  </code></pre>
  <p>Desired output is</p>
  <pre><code>+----------+----+----+----+-----+
  |      date|open|high| low|close|
  +----------+----+----+----+-----+
  |2018-07-11|33.0|35.0|30.0| 32.0|
  |2018-07-12|51.0|56.0|50.0| 54.0|
  +----------+----+----+----+-----+
  </code></pre>
  <p>There have been many SQL solutions such as <a href="https://stackoverflow.com/questions/12520257/group-ohlc-stockmarket-data-into-multiple-timeframes-mysql">this</a> and <a href="https://stackoverflow.com/questions/41840829/using-group-by-with-first-value-and-last-value">this</a>.</p>
  <pre><code>SELECT
      TO_DATE(time) AS date,
      FIRST_VALUE(price) OVER (PARTITION BY TO_DATE(time) ORDER BY time) AS open,
      MAX(price) OVER (PARTITION BY TO_DATE(time) ORDER BY time) AS high,
      MIN(price) OVER (PARTITION BY TO_DATE(time) ORDER BY time) AS low,
      LAST_VALUE(price) OVER (PARTITION BY TO_DATE(time) ORDER BY time) AS close
  FROM ticks
  </code></pre>
  <p>Due to the limitation of SQL, these solutions are cumbersome.</p>
  <p>Today, I found Spark SQL can use <code>FIRST_VALUE</code> and <code>LAST_VALUE</code> in a <code>GROUP BY</code> context, <a href="http://julianhyde.blogspot.com/2008/09/sql-extension-to-allow-firstvalue-and.html" rel="nofollow noreferrer">which is not allowed in standard SQL</a>.</p>
  <p>This unlimitation of Spark SQL derives a neat and tidy solution like this:</p>
  <pre><code>SELECT
      TO_DATE(time) AS date,
      FIRST_VALUE(price) AS open,
      MAX(price) AS high,
      MIN(price) AS low,
      LAST_VALUE(price) AS close
  FROM ticks
  GROUP BY TO_DATE(time)
  </code></pre>
  <p>You can try it</p>
  <pre><code>spark.sql(&quot;SELECT TO_DATE(time) AS date, FIRST(price) AS open, MAX(price) AS high, MIN(price) AS low, LAST(price) AS close FROM ticks GROUP BY TO_DATE(time)&quot;).show
  
  scala&gt;
  </code></pre>
  <p>shown as</p>
  <pre><code>+----------+----+----+----+-----+
  |      date|open|high| low|close|
  +----------+----+----+----+-----+
  |2018-07-11|34.0|35.0|30.0| 33.0|
  |2018-07-12|56.0|56.0|50.0| 51.0|
  +----------+----+----+----+-----+
  </code></pre>
  <p>However, the above result is incorrect. (Please compare with the above desired result.)</p>
  <p><code>FIRST_VALUE</code> and <code>LAST_VALUE</code> need a deterministic ordering to get deterministic results.</p>
  <p>I can correct it by adding an <code>orderBy</code> before grouping.</p>
  <pre><code>import org.apache.spark.sql.functions._
  
  data.orderBy(&quot;time&quot;).groupBy(expr(&quot;TO_DATE(time)&quot;).as(&quot;date&quot;)).agg(first(&quot;price&quot;).as(&quot;open&quot;), max(&quot;price&quot;).as(&quot;high&quot;), min(&quot;price&quot;).as(&quot;low&quot;), last(&quot;price&quot;).as(&quot;close&quot;)).show
  
  scala&gt;
  </code></pre>
  <p>shown as</p>
  <pre><code>+----------+----+----+----+-----+
  |      date|open|high| low|close|
  +----------+----+----+----+-----+
  |2018-07-11|33.0|35.0|30.0| 32.0|
  |2018-07-12|51.0|56.0|50.0| 54.0|
  +----------+----+----+----+-----+
  </code></pre>
  <p>which is correct as desired !!!</p>
  <p>My question is, is the above code 'orderBy then groupBy' valid?  Is this ordering guaranteed?  Can we use this non-standard feature in serious productions?</p>
  <p>The point of this question is that, in standard SQL, we can only do a <code>GROUP BY</code> then <code>ORDER BY</code> to sort the aggregation, but not <code>ORDER BY</code> then <code>GROUP BY</code>.
  The <code>GROUP BY</code> will ignore the ordering of <code>ORDER BY</code>.</p>
  <p>I also wonder if Spark SQL can do such a <code>GROUP BY</code> under desired ordering, can standard SQL also invent such a syntax for this?</p>
  <p>P.S.</p>
  <p>I can think of some aggregation functions that depend on deterministic ordering.</p>
  <pre><code>WITH ORDER BY time SELECT COLLECT_LIST(price) GROUP BY stockID
  
  WITH ORDER BY time SELECT SUM(SQUARE(price - LAG(price, 1, 0))) GROUP BY stockID
  </code></pre>
  <p>Without the <code>WITH ORDER BY time</code>, how can we sort the COLLECTed_LIST in standard SQL?</p>
  <p>These examples show that &quot;<code>GROUP BY</code> under desired ordering&quot; is still useful.</p>