Preamble

Fixes #997

Depends on #5435
Depends on #5408

Summary

This PR introduces the new PIVOT keyword.

In simple terms, PIVOT is a specialised GROUP BY query, that helps to group up a selection of rows into columns - essentially, pivoting from a narrow schema, to a wide schema.

Let's take our example trades table:

CREATE TABLE 'trades' ( 
	symbol SYMBOL CAPACITY 256 CACHE,
	side SYMBOL CAPACITY 256 CACHE,
	price DOUBLE,
	amount DOUBLE,
	timestamp TIMESTAMP
) timestamp(timestamp) PARTITION BY DAY WAL;

In this table, we have some crypto trading data.

Let's say we wanted to get the latest buy and sell prices for some tickers. We could write a group by query:

SELECT side, symbol, last(price) AS price 
FROM trades
WHERE symbol IN ('BTC-USD', 'ETH-USD')
ORDER BY side, symbol;

Which gives:

But what if we want to have a wide schema, with a column per symbol? PIVOT solves this problem, by performing a combined filter plus aggregation on rows, and mapping the values to columns.

For example:

trades
PIVOT (
  last(price)
  FOR symbol IN ('BTC-USD', 'ETH-USD')
  GROUP BY side
  ORDER BY side
);

This query performs a similar GROUP BY to the first query, a GROUP BY side, symbol with appropriate filtering. Then, a second aggregation is performed, along with per-column filtering using CASE expressions, to create the final columns.

Here is the result:

We can now compare the plans for the two queries.

Original query:

Sort light
  keys: [side, symbol]
    Async JIT Group By workers: 16
      keys: [side,symbol]
      values: [last(price)]
      filter: symbol in [BTC-USD,ETH-USD]
        PageFrame
            Row forward scan
            Frame forward scan on: trades

PIVOT query:

Sort light
  keys: [side]
    GroupBy vectorized: false
      keys: [side]
      values: [last_not_null(case([last,NaN,symbol])),last_not_null(case([last,NaN,symbol]))]
        Async JIT Group By workers: 16
          keys: [side,symbol]
          values: [last(price)]
          filter: symbol in [BTC-USD,ETH-USD]
            PageFrame
                Row forward scan
                Frame forward scan on: trades

Observe that the first part of the query, the Async JIT Group By is in fact, identical, for both queries. Only the final step changes, wherein the PIVOT query additionally pivots the data.

What about UNPIVOT? Not all PIVOT queries are reversible, but UNPIVOT can reverse simple ones. For example:

(
  trades
  PIVOT (
    last(price)
    FOR symbol IN ('BTC-USD', 'ETH-USD')
    GROUP BY side
  )
) UNPIVOT (
  price
  FOR symbol IN ('BTC-USD', 'ETH-USD')
);

gives:

Changelist:

Required

• Support SQL Standard PIVOT syntax
  • PIVOT columns
    • single column
    • multiple columns
    • per-column aliasing
  • FOR-IN
    • support single expr
    • support multiple exprs
  • GROUP BY
  • ORDER BY
  • LIMIT
  • SAMPLE BY -> supported using a subquery
• Support SQL Standard UNPIVOT syntax now in feat(sql): support UNPIVOT keyword for rotating columns to rows #5732
• Bugfixes
  • Make sure CTEs require no extra brackets for PIVOT to work
  • Memory leak in testPivotUnpivotRoundTrip
  • Fix parser bug - ')' missing, when ORDER BY expr used in PIVOT and roundtripping
• documentation: WIP docs for PIVOT/UNPIVOT documentation#143
• syntax highlighting: todo
• add sqllogictests

Desirable/future work

• Extended PIVOT
  • support aliasing for FOR title i.e FOR abc IN ('a', 'b', 'c') as def
    • No idea for a use case here?
  • support aliasing columns inside IN expr i.e FOR foo IN ('bah' as baz)
  • support dynamic IN list i.e FOR foo IN (select some_string FROM table)
    • requires two-step compilation, to eagerly execute the subquery and build the PIVOT QueryModel with the correct column count/metadata
  • support an ELSE expression, to capture all rows not included in the FOR filter:
    • i.e FOR foo IN (bah) ELSE 'baz'
    • This is to make it easy to compare a subset of a symbol against the others. For example, how many BTC-USD trades occurred compared to all other trades.
    • the condition for 'baz' would be FOR foo NOT IN (bah)
• Extended UNPIVOT now in feat(sql): support UNPIVOT keyword for rotating columns to rows #5732
  • Support multiple destination columns for UNPIVOT
  • Improve composability of generated column - built in ORDER BY