type
Post
Created date
Nov 7, 2022 11:15 AM
category
Data Science
tags
Data Warehousing
status
Published
Language
English
From
School
summary
slug
password
Author
Jason Ching Yuen Siu
Priority
Featured
Featured
Cover
Origin
Type
URL
Youtube
Youtube
icon

Orders of attributes inside CUBE matter

💡
When using CUBE, think about which ones are ‘NULL’. Those which are (NULL) need to be placed at LAST.
-- wrong SELECT c.time_id As Period, p.emp_num AS Pilot, c.mod_code As Model, SUM(c.tot_fuel) FROM dw.charter_fact c, dw.pilot p, dw.model m WHERE c.emp_num = p.emp_num AND c.mod_code = m.mod_code AND c.time_id LIKE '19951%' AND p.pil_license = 'COM' AND c.mod_code = 'C-90A' GROUP BY CUBE(c.mod_code, p.emp_num, c.time_id) ORDER BY c.time_id; -- correct SELECT c.time_id As Period, p.emp_num AS Pilot, c.mod_code As Model, SUM(c.tot_fuel) FROM dw.charter_fact c, dw.pilot p, dw.model m WHERE c.emp_num = p.emp_num AND c.mod_code = m.mod_code AND c.time_id LIKE '19951%' AND p.pil_license = 'COM' AND c.mod_code = 'C-90A' GROUP BY CUBE( c.time_id, p.emp_num, c.mod_code) ORDER BY c.time_id;
notion image

What is GROUPING ?

notion image
notion image
notion image

What is DECODE ?

notion image

What is PARTIAL CUBE ?

Problem

Code
SELECT DECODE(GROUPING(time_id), 1, 'All Periods', time_id) As Period, DECODE(GROUPING(c.emp_num), 1, 'All Pilots', c.emp_num) AS Pilot, DECODE(GROUPING(mod_code), 1, 'All Models', mod_code) As Model, SUM(tot_fuel) FROM dw.charter_fact c, dw.pilot p WHERE c.emp_num = p.emp_num AND time_id LIKE '19951%' AND mod_code = 'C-90A' AND p.pil_license = 'COM' GROUP BY CUBE (time_id, c.emp_num, mod_code) ORDER BY time_id;
notion image

Solution

notion image
notion image
Code
-- Partial Cube SELECT DECODE(GROUPING(time_id), 1, 'All Periods', time_id) As Period, DECODE(GROUPING(c.emp_num), 1, 'All Pilots', c.emp_num) AS Pilot, DECODE(GROUPING(mod_code), 1, 'All Models', mod_code) As Model, SUM(tot_fuel) FROM dw.charter_fact c, dw.pilot p WHERE c.emp_num = p.emp_num AND time_id LIKE '19951%' AND mod_code = 'C-90A' AND p.pil_license = 'COM' GROUP BY CUBE (time_id, c.emp_num), mod_code ORDER BY time_id;
 
 
 
最高學以致用法 OUTPUTThe 5 Vocal Foundations of Great Communication