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
CUBE
matterWhen 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](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2F6b8818f5-b1d5-48a7-8291-167ddc591839%2FUntitled.png%3Ftable%3Dblock%26id%3D4205cd21-cedc-4e0c-807d-5d9190f56835%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725760800000%26signature%3DgmnkTcMHQoGTdDrrUJnHZhB8PEVWFqJzFEVHxusPYac?table=block&id=4205cd21-cedc-4e0c-807d-5d9190f56835&cache=v2)
What is GROUPING
?
GROUPING
?![notion image](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2F1f4bdb56-9004-475e-8dbc-a5409c5f40b9%2FUntitled.png%3Ftable%3Dblock%26id%3D4b07be54-85c8-4a60-b638-a555c13c042a%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725760800000%26signature%3DFm60ZplnnGG6yYJq4MFFjIZSEDh-8soMSX2Uy4BMqA4?table=block&id=4b07be54-85c8-4a60-b638-a555c13c042a&cache=v2)
![notion image](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2Fd37cd769-f0f4-46e8-8659-887fb8aa8a43%2FUntitled.png%3Ftable%3Dblock%26id%3D05942c29-cc32-40fd-9093-b4628fc1d8bb%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725760800000%26signature%3DxWO6WF_fmkfJOfTdL32vN2ww3w5Sy1S4KVWUq3trjvA?table=block&id=05942c29-cc32-40fd-9093-b4628fc1d8bb&cache=v2)
![notion image](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2Fe43ce668-eb49-4f03-a232-c30c64b94a98%2FUntitled.png%3Ftable%3Dblock%26id%3D17098e88-f0ce-4bb9-8770-00c3e3302a8f%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725760800000%26signature%3DJLidLcs3UzROokmW-Cc9D_6Nl8EPeKoDyiU8vKTl3tA?table=block&id=17098e88-f0ce-4bb9-8770-00c3e3302a8f&cache=v2)
What is DECODE
?
DECODE
?![notion image](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2Fa2253810-2110-49aa-82a9-28b7c413dde2%2FUntitled.png%3Ftable%3Dblock%26id%3D386f64a1-7fba-48f0-aa27-7360ab920b3c%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725760800000%26signature%3DHefFdpXT-ubPzDSbJKVb-W7pCt44rMgD-kRQUosHPbg?table=block&id=386f64a1-7fba-48f0-aa27-7360ab920b3c&cache=v2)
What is PARTIAL CUBE
?
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](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2F9473f168-3521-423a-9643-24902363b144%2FUntitled.png%3Ftable%3Dblock%26id%3Dc72bde12-a529-466b-83ab-c8db0b166715%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725760800000%26signature%3Df-giLqOBC-YxJFznlowd4mN-LkPhEVHsMfnjxRHAVaU?table=block&id=c72bde12-a529-466b-83ab-c8db0b166715&cache=v2)
Solution
![notion image](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2F77d07545-8503-4e45-a5f4-449866355db5%2FUntitled.png%3Ftable%3Dblock%26id%3D47cedaf7-cc63-444b-9722-d688c1384a46%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725760800000%26signature%3DO0ughDU0qVo0EjqaWo7-t0p-xS0GOLNHpA0eGLwJfXI?table=block&id=47cedaf7-cc63-444b-9722-d688c1384a46&cache=v2)
![notion image](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2Fc272be2b-4436-4821-84a6-c5004c346999%2FUntitled.png%3Ftable%3Dblock%26id%3De7d68a25-ded3-437f-bdcd-43928ec49409%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725760800000%26signature%3DKzg_osseWRsppMdK1O1eRu-0ebi0j0iSdVrpbEO56t4?table=block&id=e7d68a25-ded3-437f-bdcd-43928ec49409&cache=v2)
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;
- Author:Jason Siu
- URL:https://jason-siu.com/article%2F808b1217-107b-40d6-8535-214bd6a31069
- Copyright:All articles in this blog, except for special statements, adopt BY-NC-SA agreement. Please indicate the source!
Relate Posts