Spreadsheet Export

We've been asked to assemble two queries for export: the chemical data gathered per flyby and the chemical data found over the entirety of the Cassini mission. We can do that using these queries:

<span class="hljs-keyword">set</span> search_path<span class="hljs-operator">=</span><span class="hljs-string">&#x27;enceladus&#x27;</span>;
<span class="hljs-keyword">select</span> flybys.name <span class="hljs-keyword">as</span> flyby,
	inms.date,
	inms.source,
	chemistry.name <span class="hljs-keyword">as</span> compound,
	chemistry.formula,
	<span class="hljs-built_in">sum</span>(inms.high_sensitivity_count) <span class="hljs-keyword">as</span> sum_high,
	<span class="hljs-built_in">sum</span>(inms.low_sensitivity_count) <span class="hljs-keyword">as</span> sum_low
<span class="hljs-keyword">from</span> flybys
<span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> inms <span class="hljs-keyword">on</span> flyby_id <span class="hljs-operator">=</span> flybys.id
<span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> chemistry <span class="hljs-keyword">on</span> chemistry.molecular_weight <span class="hljs-operator">=</span> inms.mass
<span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> flybys.name, inms.date, inms.source, chemistry.name, chemistry.formula;

To speed things up (and uncramp our fingers) we'll create a materialized view. Notice how I'm dropping the view if it exists and then recreating it directly. This keeps our script idempotent, which is required:

<span class="hljs-keyword">set</span> search_path<span class="hljs-operator">=</span><span class="hljs-string">&#x27;enceladus&#x27;</span>;
<span class="hljs-keyword">drop</span> materialized <span class="hljs-keyword">view</span> if <span class="hljs-keyword">exists</span> results_per_flyby;
<span class="hljs-keyword">create</span> materialized <span class="hljs-keyword">view</span> results_per_flyby <span class="hljs-keyword">as</span>
<span class="hljs-keyword">select</span> flybys.name,
  flybys.date,
  inms.source,
  chemistry.name <span class="hljs-keyword">as</span> compound,
  chemistry.formula,
  <span class="hljs-built_in">sum</span>(inms.high_sensitivity_count) <span class="hljs-keyword">as</span> sum_high,
  <span class="hljs-built_in">sum</span>(inms.low_sensitivity_count) <span class="hljs-keyword">as</span> sum_low
<span class="hljs-keyword">from</span> flybys
<span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> inms <span class="hljs-keyword">on</span> flyby_id <span class="hljs-operator">=</span> flybys.id
<span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> chemistry <span class="hljs-keyword">on</span> chemistry.molecular_weight <span class="hljs-operator">=</span> inms.mass
<span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> flybys.name, flybys.date, inms.source, chemistry.name, chemistry.formula
<span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> flybys.date;

The overall query is a bit simpler as all we need to do is remove the flybys table from the select, join and group by list:

<span class="hljs-keyword">select</span>
  inms.source,
  chemistry.name <span class="hljs-keyword">as</span> compound,
  chemistry.formula,
  <span class="hljs-built_in">sum</span>(inms.high_sensitivity_count) <span class="hljs-keyword">as</span> sum_high,
  <span class="hljs-built_in">sum</span>(inms.low_sensitivity_count) <span class="hljs-keyword">as</span> sum_low
<span class="hljs-keyword">from</span> flybys
<span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> inms <span class="hljs-keyword">on</span> flyby_id <span class="hljs-operator">=</span> flybys.id
<span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> chemistry <span class="hljs-keyword">on</span> chemistry.molecular_weight <span class="hljs-operator">=</span> inms.mass
<span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> inms.source, chemistry.name, chemistry.formula)