| create database db1; |
| CREATE EXTERNAL TABLE db1.table1 |
| ( |
| `level1` string, |
| `level1_desc` string, |
| `level2` string, |
| `level2_desc` string, |
| `level3` string, |
| `level3_desc` string, |
| `level4` string, |
| `level4_desc` string, |
| `level5` string, |
| `level5_desc` string, |
| `level6` string, |
| `level6_desc` string, |
| `level7` string, |
| `level7_desc` string, |
| `level8` string, |
| `level8_desc` string, |
| `level9` string, |
| `level9_desc` string, |
| `level10` string, |
| `level10_desc` string, |
| `level11` string, |
| `level11_desc` string, |
| `level12` string, |
| `level12_desc` string, |
| `level13` string, |
| `level13_desc` string, |
| `level14` string, |
| `level14_desc` string, |
| `level15` string, |
| `level15_desc` string, |
| `pack` string, |
| aeoi string, |
| aeoo string) |
| PARTITIONED BY ( |
| data_date string) stored as parquet; |
| |
| |
| explain create view if not exists db1.view1 as |
| SELECT |
| h.level1, |
| h.level1_desc, |
| h.level2, |
| h.level2_desc, |
| h.level3, |
| h.level3_desc, |
| h.level4, |
| h.level4_desc, |
| h.level5, |
| h.level5_desc, |
| h.level6, |
| h.level6_desc, |
| h.level7, |
| h.level7_desc, |
| h.level8, |
| h.level8_desc, |
| h.level9, |
| h.level9_desc, |
| h.level10, |
| h.level10_desc, |
| h.level11, |
| h.level11_desc, |
| h.level12, |
| h.level12_desc, |
| h.level13, |
| h.level13_desc, |
| h.level14, |
| h.level14_desc, |
| h.pack, |
| h.aeoi, |
| h.aeoo, |
| h.data_date, |
| CONCAT('#', h.level1, '#', h.level2, '#', h.level3, '#', h.level4, '#', h.level5, '#', h.level6, '#', h.level7, '#', h.level8, '#', h.level9, '#', h.level10, '#', h.level11, '#', h.level12, '#', h.level13, '#', h.level14, '#') key |
| FROM |
| (SELECT MAX(data_date) data_as_of_date, level1 FROM db1.table1 WHERE level1 = 'N00000' GROUP BY level1) d |
| JOIN db1.table1 h |
| ON (h.data_date = d.data_as_of_date |
| AND h.level1 = d.level1) |
| WHERE |
| 'TM1-5460' NOT IN (h.level1, h.level2, h.level3, h.level4, h.level5, h.level6, h.level7, h.level8, h.level9, h.level10, h.level11, h.level12, h.level13, h.level14, h.level15) |
| UNION ALL SELECT DISTINCT |
| hhh.level1, |
| hhh.level1_desc, |
| hhh.level2, |
| hhh.level2_desc, |
| hhh.level3, |
| hhh.level3_desc, |
| hhh.level4, |
| hhh.level4_desc, |
| hhh.level5, |
| hhh.level5_desc, |
| hhh.level6, |
| hhh.level6_desc, |
| hhh.level7, |
| hhh.level7_desc, |
| hhh.level8, |
| hhh.level8_desc, |
| hhh.level9, |
| hhh.level9_desc, |
| hhh.level10, |
| hhh.level10_desc, |
| hhh.level11, |
| hhh.level11_desc, |
| hhh.level12, |
| hhh.level12_desc, |
| hhh.level13, |
| hhh.level13_desc, |
| hhh.level14, |
| hhh.level14_desc, |
| hhh.pack, |
| hhh.aeoi, |
| hhh.aeoo, |
| hhh.data_date, |
| CONCAT('#', hhh.level1, '#', hhh.level2, '#', hhh.level3, '#', hhh.level4, '#', hhh.level5, '#', hhh.level6, '#', hhh.level7, '#', hhh.level8, '#', hhh.level9, '#', hhh.level10, '#', hhh.level11, '#', hhh.level12, '#', hhh.level13, '#', hhh.level14, '#') key |
| FROM |
| (SELECT |
| hh.level1, |
| hh.level1_desc, |
| CASE WHEN hh.leaf_level < 2 THEN hh.leaf_code ELSE hh.level2 END level2, |
| CASE WHEN hh.leaf_level < 2 THEN hh.leaf_desc ELSE hh.level2_desc END level2_desc, |
| CASE WHEN hh.leaf_level < 3 THEN hh.leaf_code ELSE hh.level3 END level3, |
| CASE WHEN hh.leaf_level < 3 THEN hh.leaf_desc ELSE hh.level3_desc END level3_desc, |
| CASE WHEN hh.leaf_level < 4 THEN hh.leaf_code ELSE hh.level4 END level4, |
| CASE WHEN hh.leaf_level < 4 THEN hh.leaf_desc ELSE hh.level4_desc END level4_desc, |
| CASE WHEN hh.leaf_level < 5 THEN hh.leaf_code ELSE hh.level5 END level5, |
| CASE WHEN hh.leaf_level < 5 THEN hh.leaf_desc ELSE hh.level5_desc END level5_desc, |
| CASE WHEN hh.leaf_level < 6 THEN hh.leaf_code ELSE hh.level6 END level6, |
| CASE WHEN hh.leaf_level < 6 THEN hh.leaf_desc ELSE hh.level6_desc END level6_desc, |
| CASE WHEN hh.leaf_level < 7 THEN hh.leaf_code ELSE hh.level7 END level7, |
| CASE WHEN hh.leaf_level < 7 THEN hh.leaf_desc ELSE hh.level7_desc END level7_desc, |
| CASE WHEN hh.leaf_level < 8 THEN hh.leaf_code ELSE hh.level8 END level8, |
| CASE WHEN hh.leaf_level < 8 THEN hh.leaf_desc ELSE hh.level8_desc END level8_desc, |
| CASE WHEN hh.leaf_level < 9 THEN hh.leaf_code ELSE hh.level9 END level9, |
| CASE WHEN hh.leaf_level < 9 THEN hh.leaf_desc ELSE hh.level9_desc END level9_desc, |
| CASE WHEN hh.leaf_level < 10 THEN hh.leaf_code ELSE hh.level10 END level10, |
| CASE WHEN hh.leaf_level < 10 THEN hh.leaf_desc ELSE hh.level10_desc END level10_desc, |
| CASE WHEN hh.leaf_level < 11 THEN hh.leaf_code ELSE hh.level11 END level11, |
| CASE WHEN hh.leaf_level < 11 THEN hh.leaf_desc ELSE hh.level11_desc END level11_desc, |
| CASE WHEN hh.leaf_level < 12 THEN hh.leaf_code ELSE hh.level12 END level12, |
| CASE WHEN hh.leaf_level < 12 THEN hh.leaf_desc ELSE hh.level12_desc END level12_desc, |
| CASE WHEN hh.leaf_level < 13 THEN hh.leaf_code ELSE hh.level13 END level13, |
| CASE WHEN hh.leaf_level < 13 THEN hh.leaf_desc ELSE hh.level13_desc END level13_desc, |
| CASE WHEN hh.leaf_level < 14 THEN hh.leaf_code ELSE hh.level14 END level14, |
| CASE WHEN hh.leaf_level < 14 THEN hh.leaf_desc ELSE hh.level14_desc END level14_desc, |
| hh.pack, |
| hh.aeoi, |
| hh.aeoo, |
| hh.data_date |
| FROM |
| (SELECT |
| h.*, |
| lc.leaf_level, |
| lc.leaf_code, |
| CASE lc.leaf_code |
| WHEN h.level2 THEN h.level2_desc |
| WHEN h.level3 THEN h.level3_desc |
| WHEN h.level4 THEN h.level4_desc |
| WHEN h.level5 THEN h.level5_desc |
| WHEN h.level6 THEN h.level6_desc |
| WHEN h.level7 THEN h.level7_desc |
| WHEN h.level8 THEN h.level8_desc |
| WHEN h.level9 THEN h.level9_desc |
| WHEN h.level10 THEN h.level10_desc |
| WHEN h.level11 THEN h.level11_desc |
| WHEN h.level12 THEN h.level12_desc |
| WHEN h.level13 THEN h.level13_desc |
| ELSE NULL |
| END leaf_desc |
| FROM |
| (SELECT MAX(data_date) data_as_of_date, level1 FROM db1.table1 WHERE level1 = 'N00000' GROUP BY level1) d |
| JOIN db1.table1 h |
| ON (h.data_date = d.data_as_of_date |
| AND h.level1 = d.level1), |
| (SELECT |
| l.leaf_level, |
| 'TM1-5460' leaf_code |
| FROM |
| (SELECT 3 leaf_level |
| UNION ALL SELECT 4 leaf_level |
| UNION ALL SELECT 5 leaf_level |
| UNION ALL SELECT 6 leaf_level |
| UNION ALL SELECT 7 leaf_level |
| UNION ALL SELECT 8 leaf_level |
| UNION ALL SELECT 9 leaf_level |
| UNION ALL SELECT 10 leaf_level |
| UNION ALL SELECT 11 leaf_level |
| UNION ALL SELECT 12 leaf_level |
| UNION ALL SELECT 13 leaf_level) l) lc |
| WHERE |
| lc.leaf_code = |
| CASE |
| WHEN lc.leaf_level = 2 THEN h.level2 |
| WHEN lc.leaf_level = 3 THEN h.level3 |
| WHEN lc.leaf_level = 4 THEN h.level4 |
| WHEN lc.leaf_level = 5 THEN h.level5 |
| WHEN lc.leaf_level = 6 THEN h.level6 |
| WHEN lc.leaf_level = 7 THEN h.level7 |
| WHEN lc.leaf_level = 8 THEN h.level8 |
| WHEN lc.leaf_level = 9 THEN h.level9 |
| WHEN lc.leaf_level = 10 THEN h.level10 |
| WHEN lc.leaf_level = 11 THEN h.level11 |
| WHEN lc.leaf_level = 12 THEN h.level12 |
| WHEN lc.leaf_level = 13 THEN h.level13 |
| END |
| AND h.level14 <> lc.leaf_code) hh) hhh; |
| |