● videoAnalysis
Ship It!

Unlock PostgreSQL Fundamentals
2 hours of SQL video using NASA's Cassini data. See why developers become data pros.
SECTION
Analysis
NEXT UP
Summary, and Farewell!
COURSE
PostgreSQL Fundamentals
28 lessons
About this lesson
Our analysts are going to be using Excel to dig into the data so let's setup a simple way to get it to them. We can run a script for this or we can use psql:
psql cassini -c <span class="hljs-string">"\copy (select * from enceladus.results_per_flyby) to '//FILESHARE/results_per_flyby.csv' header csv;</span>
Running a SQL script can be easier, and also a bit more flexible. The first command will output the results of our analysis view (rollups by flyby). The second will dump the overall result:
<span class="hljs-keyword">set</span> search_path<span class="hljs-operator">=</span><span class="hljs-string">'enceladus'</span>;
<span class="hljs-keyword">copy</span> (<span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> enceladus.results_per_flyby)
<span class="hljs-keyword">to</span> <span class="hljs-string">'//share/results_per_flyby.csv'</span> header csv;
<span class="hljs-keyword">copy</span> (<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)
<span class="hljs-keyword">to</span> <span class="hljs-string">'//share/overall_results.csv'</span> header csv;
And that's it!
Unlock PostgreSQL Fundamentals
2 hours of SQL video using NASA's Cassini data. See why developers become data pros.