title: How to write such query effectively in postgresql: aggregating consecutive rows into arrays(identified by a pair of columns)?
body: <p>I have a query like:</p>
  
  <pre><code>(
      SELECT
          t1.person_id,
          t1.created_at,
          't1' AS type,
          t1.extra_data AS extra_data
      FROM table1 AS t1
  )
  UNION
  (
      SELECT
          t2.person_id,
          t2.created_at,
          't2' AS type,
          t2.extra_data AS extra_data
      FROM table2 AS t2
  )
  UNION
  (
      SELECT
          t3.person_id,
          t3.created_at,
          't3' AS type,
          t3.extra_data AS extra_data
      FROM table3 AS t3
  )
  ORDER BY created_at DESC;
  </code></pre>
  
  <p>which will result in something like (<code>created_at</code> is a timestamp, and I omit the concrete values, and use simple integers to represent the order)</p>
  
  <pre><code>person_id  | type  |  created_at | extra_data
  ---------  | ----  |  ---------- | ----------
  1          | t1    |  9          | a
  1          | t1    |  8          | b
  2          | t2    |  7          | c
  2          | t2    |  6          | c
  2          | t2    |  5          | d
  1          | t3    |  4          | e
  3          | t3    |  3          | f
  </code></pre>
  
  <p>I want to group the consecutive (person_id, type) pairs, with the max <code>created_at</code> as the final created_at, and aggregate the <code>extra_data</code> into an array, i.e. I want to get the following result:</p>
  
  <pre><code>person_id  | type  |  created_at | extra_data_array
  ---------  | ----  |  ---------- | ----------
  1          | t1    |  9          | [a, b]
  2          | t2    |  7          | [c, c, d]
  1          | t3    |  5          | e
  3          | t3    |  4          | f
  </code></pre>
  
  <p>I have tried window functions, but failed to figure out how to achieve this.</p>
  
  <p>My question is:</p>
  
  <p>1) How to write a SINGLE query to achieve my goal?</p>
  
  <p>2) Can such query be fast by using index?</p>
  
  <p>My concern to the second question is that since the base results is selected from a UNION query, I doubt if there is any chance to utilise the index.</p>
  
  <p>Anyone, thanks to all kinds of help! </p>