SQL: Can a single OVER clause support multiple window functions?
<p>Let's say I've got a list of pageview events, each of which has a session id. For each event I want to add the time and URL of the chronologically first pageview in that event's session.  For example, lets say my events are in table <code>test</code> which looks like this: </p>

<pre><code>uid | session_id | timestamp | url
----------------------------------------------------
u1    0            0           a.com/
u1    1            1           a.com/p1
u1    1            2           a.com/p2
</code></pre>

<p>I want a SQL command that produces the following:</p>

<pre><code>uid | session_id | timestamp | url      | s_timestamp | s_url
---------------------------------------------------------------------
u1    0            0           a.com/     0             a.com/
u1    1            1           a.com/p1   1             a.com/p1
u1    1            2           a.com/p2   1             a.com/p1
</code></pre>

<p>Window functions seem to be the way to go here, but I'm pretty new to them.  The following statement produces the desired table, but I'm wondering if it's suboptimal</p>

<pre><code>SELECT
    uid,
    session_id,
    timestamp,
    url,
    first_value(url) OVER (PARTITION BY uid, session_id ORDER BY timestamp ASC) s_url,
    first_value(timestamp) OVER (PARTITION BY uid, session_id ORDER BY timestamp ASC) s_timestamp
FROM test
</code></pre>

<p>In particular, it seems wrong that I use the <code>OVER</code> clause twice.  Is there a way to select the chronologically first URL and timestamp from a session using a single OVER clause?  I'm using SPARK SQL, but I will accept any answer that is applicable to multiple major SQL systems.</p>