One monthly report from five places
A wholesaler gets orders from the web and from the shop counter. Prices live in a price list. Customers type their names with mistakes. Depots deliver by area.
The manager wants one report in pounds, ready to read.
Key words
| Word | What it means |
|---|---|
| Dataset | a collection of related data about one subject |
| Blending | bringing data from several sources into one dataset |
| Consolidation | stacking same-shaped datasets into one |
| Fuzzy matching | matching values that are close but not the same |
| Spatial matching | matching by where things are |
| Output format | the shape the report must have |
| Requirement | something the result must do or show |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can combine several techniques to blend sources and present the result as required.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Review the sources, then pick techniques
List each source and ask what is wrong with it. Then match the problem to a technique.
Two sources of the same shape need consolidation. A shared id needs a join. Misspelt names need fuzzy matching. Positions need spatial matching.
Problem and technique
| Problem | Technique |
|---|---|
| Web orders and shop orders have the same columns | Consolidation (union all) |
| Each order needs its price | Join on product |
| Customer names are typed wrongly | Fuzzy matching |
| Each order needs the nearest depot | Spatial matching |
The cells students fill in are left blank in this preview.
Try it: consolidate and join prices
with all_orders as (
select * from web_orders union all select * from shop_orders
)
select o.customer, o.product, o.units * p.price_pence as pence
from all_orders o
join price_list p on p.product = o.product
order by o.customer, o.product;
Try it: fix typed names
select t.typed, a.account, t.note
from typed_names t
cross join account_names a
qualify row_number() over (partition by t.typed order by jaro_winkler_similarity(lower(t.typed), lower(a.account)) desc) = 1;
-- Each typed name now has its real account.
Check
Have a go
Check
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.