PostgreSQL Fundamentals/ AnalysisLESSON 25 OF 28Log inSubscribe
Spreadsheet Export lesson slide
LESSON 25 OF 28 · IN THE FULL COURSE

This one's part of the full course

Good to see you. This one's in the full course

4 lessons are free to watch right now, no account needed. If they click, grab the course and every lesson is yours to keep.

You've seen how I teach. The other 24 lessons are in the full course, and your progress comes with you.

WATCH THESE FREE, NO ACCOUNT NEEDEDYOUR FREE LESSONS

Already bought it? Log in and it picks up right here.

Your progress comes with you the moment you buy.

Analysis · LESSON 25

Spreadsheet Export

THE COURSE, END TO ENDNow playingDoneAheadYou are hereFree to watchIn the full course
Introduction
Extraction
Find the Flybys
INMS Data Import
Analysis
ABOUT THIS LESSON

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:

set search_path='enceladus';
select flybys.name as flyby,
	inms.date,
	inms.source,
	chemistry.name as compound,
	chemistry.formula,
	sum(inms.high_sensitivity_count) as sum_high,
	sum(inms.low_sensitivity_count) as sum_low
from flybys
inner join inms on flyby_id = flybys.id
inner join chemistry on chemistry.molecular_weight = inms.mass
group by 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:

set search_path='enceladus';
drop materialized view if exists results_per_flyby;
create materialized view results_per_flyby as
select flybys.name,
  flybys.date,
  inms.source,
  chemistry.name as compound,
  chemistry.formula,
  sum(inms.high_sensitivity_count) as sum_high,
  sum(inms.low_sensitivity_count) as sum_low
from flybys
inner join inms on flyby_id = flybys.id
inner join chemistry on chemistry.molecular_weight = inms.mass
group by flybys.name, flybys.date, inms.source, chemistry.name, chemistry.formula
order by 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:

select
  inms.source,
  chemistry.name as compound,
  chemistry.formula,
  sum(inms.high_sensitivity_count) as sum_high,
  sum(inms.low_sensitivity_count) as sum_low
from flybys
inner join inms on flyby_id = flybys.id
inner join chemistry on chemistry.molecular_weight = inms.mass
group by inms.source, chemistry.name, chemistry.formula)
28lessons across 5 sections
4free to watch right now
Foreverbuy it once, it's yours for life

28 lessons · 2.1 hours · all done

You finished PostgreSQL Fundamentals.

Every lesson, start to finish. That takes some doing, and you did it. Now go build something with it.

Cheers! Rob