CodePudding user response:
Hive in the intersection and difference set of two kinds of methods:1. The intersection:
Select the id from t1 union select id from t2
Select the id from t1 intersects the select id from t2
2. The difference set
Select Anderson d from t1 left join t2 on t2. Id=t1. The id and t2. The id is null.
Select the id from t1 except the select id from t2
CodePudding user response:
Hive and Oracle conversion instance summary- the hive writing 1
Select dt, 'account as stat_item, if (move_channel is null, ", move_channel) as move_channel, stat_value,
Case the when grouping__id='1' then 'total'
The when grouping__id='3' then 'total'
The else move_path
End as move_path,
Case row_number () over (partition by dt, move_channel, move_path, by stat_value)
1 then the when stat_type [0]
When 2 then stat_type [1]
When 3 then stat_type [2]
The else null
End stat_type
The from (select dt, move_channel move_path, grouping__id,
(split (concat_ws (', ', 'customers',' than the last day ', 'than last month),', ')) as stat_type,
The sum (cust_cnt) as cust_cnt,
The sum (cust_cnt) - the sum (cust_cnt_pre_day) as cust_cnt_pre_day,
The sum (cust_cnt) - the sum (cust_cnt_pre_mon) as cust_cnt_pre_mon
The from pabrdm. Tb1_ca01_move_channel_day
the where move_channel_code like '01%'Group by dt, move_channel move_path
With a rollup
) x
Lateral view explodes (split (concat_ws (', 'cust_cnt, cust_cnt_pre_day, cust_cnt_pre_mon),', ')) cust as stat_value
Where dt is the not null
- the hive writing 2: improve SQL
Select dt, 'account as stat_item, if (move_channel is null, ", move_channel) as move_channel, stat_value,
Case the when grouping__id='1' then 'total'
The when grouping__id='3' then 'total'
The else move_path
End as move_path,
Explodes (map (' customer number, cust_cnt, 'than the previous day, cust_cnt_pre_day,' than last month, cust_cnt_pre_mon)) as stat_type
The from (select dt, move_channel move_path, grouping__id,
The sum (cust_cnt) as cust_cnt,
The sum (cust_cnt) - the sum (cust_cnt_pre_day) as cust_cnt_pre_day,
The sum (cust_cnt) - the sum (cust_cnt_pre_mon) as cust_cnt_pre_mon
The from pabrdm. Tb1_ca01_move_channel_day
the where move_channel_code like '01%'Group by dt, move_channel move_path
With a rollup
) x
Where dt is not null
CodePudding user response:
-- oracle writing 1Select to_date (dt, '- dd yyyy - mm) "statistical date," move_channel "institutions," move_path "migration paths,"' accounts' "index type," cn "index", CV "index"
The from (select dt,
Decode (grouping (move_channel) + grouping (move_path), 2, null, move_channel) move_channel,
Decode (grouping (move_channel) + grouping (move_path), 1, 'total', 2, 'CA02 project total, move_path) move_path,
The sum (cust_cnt) cust_cnt,
The sum (cust_cnt) - the sum (cust_cnt_pre_day) cust_cnt_pre_day,
The sum (cust_cnt) - the sum (cust_cnt_pre_mon) cust_cnt_pre_mon
End cust_cnt_pre_mon
The from tbl_ca01_move_channel_day
the where move_channel_code like '01%'Group by a rollup (dt, move_channel move_path)
)
Where dt is the not null
The model
Return the updated rows
Partition by (dt, move_channel move_path)
Dimension by (0 as n)
Measures (' XXXXXXXXXXXXXXXXXXXX as cn, 'yyyyyyyyyyyyyyyyy as CV, cust_cnt as c1, cust_cnt_pre_day as c2, cust_cnt_pre_mon as c3)
Rules upsert all
(
Cn [1]='customers',
Cn [2]='than the previous day,
Cn [3]='than last month,
CV [1]=c1 [0],
CV [2]=c2 [0],
CV [3]=c3 [0]
)
-- oracle writing 2: improve SQL
Select move_channel "channels", move_path "migration paths," 'accounts' "index type," cn "index", CV "index"
The from (select decode (grouping (move_channel) + grouping (move_path), 2, null, move_channel) move_channel,
Decode (grouping (move_channel) + grouping (move_path), 1, 'total', 2, 'CA01 project total, move_path) move_path,
The sum (cust_cnt) as cust_cnt,
The sum (cust_cnt) - the sum (cust_cnt_pre_day) as cust_cnt_pre_day,
The sum (cust_cnt) - the sum (cust_cnt_pre_mon) as cust_cnt_pre_mon
The from tbl_ca01_move_channel_day
the where move_channel_code like '01%'Group by a rollup (dt, move_channel move_path)
)
Unpivot (CV for cn (in cust_cnt as' customers', cust_cnt_pre_day as' than the previous day, cust_cnt_pre_mon as' than last month)