title: Vega-lite aggregate join
body: <p>I have a data set with the following basic structure:</p>
  
  <pre><code>response_id | question_id | question_type | question_key | question_value
  
  1           |           1 | demographic   | age          | 23
  1           |           2 | value         | enjoyment    | 0.75
  1           |           3 | value         | speed        | 0.95
  1           |           4 | demographic   | gender       | o
  2           |           5 | demographic   | age          | 55
  2           |           6 | value         | enjoyment    | 0.2
  2           |           7 | value         | speed        | 0.15
  2           |           8 | demographic   | gender       | m
  ...
  </code></pre>
  
  <p>I need to produce a variety of charts which compare these responses in different ways, aggregated over common <code>response_id</code>.</p>
  
  <p>How can I join on <code>response_id</code> and then filter and aggregate based on lookups of <code>question_type AND question_value</code> pairs?</p>
  
  <h3>Chart 1</h3>
  
  <p>An example of a simple chart would be a horizontal bar chart showing the average of <code>question_value</code> on the Y axis, filtering on <code>question_type = 'value'</code> with a bar for each unique <code>question_key</code>.</p>
  
  <p>So There would be a bar for <code>question_key = 'enjoyment'</code> and a bar for <code>question_key = 'speed'</code>. There can be different numbers of possible <code>question_keys</code> which will dynamically change i.e. <code>speed</code> and <code>enjoyment</code> are not fixed and known before hand and so the chart might have different numbers of bars.</p>
  
  <h3>Chart 2</h3>
  
  <p>Now I want to create a new chart which pivots over chart 1. Each bar in the above chart is split into two different bars over <code>question_type = 'demographic' AND question_key = 'gender'</code>. So for each bar above there would now be two bars for <code>gender='m'</code> and <code>gender='o'</code>. So I'd have bars like: <code>enjoyment,m</code>, <code>enjoyment,o</code>, <code>speed,m</code>, <code>speed,o</code>. All of the <code>gender='m'</code> would be one color and all of the <code>gender='o'</code> would be another color.</p>
  
  <h3>Chart 3</h3>
  
  <p>Next I might want to pivot chart 1 above again but over ages so <code>question_type='demographic' AND question_key='age'</code> and then bin the age results into 10 year bins. So I have <code>enjoyment,age=1-10</code>, <code>enjoyment,age=11-20</code>, <code>enjoyment,age=21-30</code> ... <code>speed,age=1-10</code>, <code>speed,age=11-20</code>..., etc.</p>
  
  <p>These pivots are fixed, so I'll always do a chart with <code>question_type='demographic' AND question_key='gender'</code> with those exact values for the filter, however the <code>question_type='value'</code> keys are not fixed.</p>