sql - Table from a tree structure, how to select branches then partition more info for each branch -
i have table describing geographical structure, built tree structure, column1 'country', column2 contains largest administrative area called 'region1', 'region2' column contains children of region1 etc.
so this
r / | \ 1 2 3 / | \ / | \ / | \ 11 12 13 21 22 23 31 32 33 is represesented each distinct combination:
cntry| r1 | r2 ------------------ r | 1 | 11 r | 1 | 12 r | 1 | 13 r | 2 | 21 r | 2 | 22 r | 2 | 23 r | 3 | 31 r | 3 | 32 r | 3 | 33 in addition, each leaf node contains additional info, can span multiple rows. such zip code. table actually looks this:
cntry| r1 | r2 | zip | --------------------------| r | 1 | 11 | | r | 1 | 11 | aa | r | 1 | 11 | aaa | r | 1 | 11 | aaaa| r | 1 | 12 | b | r | 1 | 13 | c | r | 1 | 13 | cc | r | 1 | 13 | ccc | r | 2 | 21 | d | r | 2 | 22 | e | r | 2 | 22 | ee | r | 2 | 23 | f | r | 3 | 31 | g | r | 3 | 31 | gg | r | 3 | 31 | ggg | r | 3 | 32 | h | r | 3 | 33 | | now, real table contains 2 million rows, lots of text in columns place names. that's lot of duplicate info in additional rows zip code new information. takes bandwidth , slow.
is possible make query select cntry,r1,r2 distinctly , ordered, zip rows following each distinct row null in other columns? result of select this:
cntry| r1 | r2 | zip | --------------------------| r | 1 | 11 | | null| null | null | aa | null| null | null | aaa | null| null | null | aaaa| r | 1 | 12 | b | r | 1 | 13 | c | null| null | null | cc | null| null | null | ccc | r | 2 | 21 | d | r | 2 | 22 | e | null| null | null | ee | r | 2 | 23 | f | r | 3 | 31 | g | null| null | null | gg | null| null | null | ggg | r | 3 | 32 | h | r | 3 | 33 | |
with cte ( select *, row_number() over(partition cntry, r1, r2 order zip) row_num tbl ) select case when c.row_num = 1 cntry end cntry, case when c.row_num = 1 r1 end r1, case when c.row_num = 1 r2 end r2, c.zip cte c order c.r1, c.r2, row_num
Comments
Post a Comment