hive列转行、行转列

最新推荐文章于 2023-12-15 22:17:02 发布
最新推荐文章于 2023-12-15 22:17:02 发布
阅读量360 收藏
点赞数
文章标签： hive
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yuinliu/article/details/112250061
版权

1.列转行

select  t.cust_id
        ,concat_ws(',',collect_list(group_id)) one_pace
  from (select   'A_001' cust_id
                 ,'20191014' group_id
           union all
           select   'A_001' cust_id
                   ,'20191015' group_id
          union all
           select   'A_001' cust_id
                   ,'20191016' group_id) t
 group by t.cust_id；

2.行转列

select  cust_id
       ,one_pace
       ,group_id
  from (select  'A_001' cust_id
               ,'20191014,20191015,20191016' one_pace
           union all
          select  'A_002' cust_id
                 ,'20191014,20191015,20191016' one_pace
           union all
          select   'A_003' cust_id
                  ,'20191014,20191015,20191016' one_pace) t
lateral view explode(split(one_pace,',')) num as group_id;

–客户渗透率–

–客户渗透率–

drop table if exists ads_stat_permeability_group_tmp_0701;

create table if not exists ads_stat_permeability_group_tmp_0701
as
select  count(distinct case when t.cust_source in ('00001','00002','00003') then t.main_customer_id end ) cust_cnt_all  --总客户数
       ,count(distinct case when (t.is_zq_rh = '1' or t.is_zq_bs = '1' or t.is_bs_rh = '1') then t.main_customer_id end ) cust_cnt_two  --持有两家合同及以上的客户数
       ,count(distinct case when t.cust_source in ('00001','00002') then t.main_customer_id end ) cust_cnt_zq_bs_all  --00001-00002总客户数
       ,count(distinct case when t.cust_source in ('00001','00003') then t.main_customer_id end ) cust_cnt_zq_rh_all  --00001-00003总客户数
       ,count(distinct case when t.cust_source in ('00002','00003') then t.main_customer_id end ) cust_cnt_bs_rh_all  --00002-00003总客户数
       ,count(distinct case when t.is_zq_rh = '1' then t.main_customer_id end ) cust_cnt_zq_rh  --00001-00003交叉客户数
       ,count(distinct case when t.is_zq_bs = '1' then t.main_customer_id end ) cust_cnt_zq_bs  --00001-00002交叉客户数
       ,count(distinct case when t.is_bs_rh = '1' then t.main_customer_id end ) cust_cnt_bs_rh  --00002-00003交叉客户数
       ,count(distinct case when (t.is_zq_rh = '1' or t.is_zq_bs = '1') then t.main_customer_id end ) cust_cnt_cross_zq
       ,count(distinct case when (t.is_bs_rh = '1' or t.is_zq_bs = '1') then t.main_customer_id end ) cust_cnt_cross_bs
       ,count(distinct case when (t.is_bs_rh = '1' or t.is_zq_rh = '1') then t.main_customer_id end ) cust_cnt_cross_rh
       ,count(distinct case when t.cust_source in ('00001') then t.main_customer_id end ) cust_cnt_zq 
       ,count(distinct case when t.cust_source in ('00002') then t.main_customer_id end ) cust_cnt_bs
       ,count(distinct case when t.cust_source in ('00003') then t.main_customer_id end ) cust_cnt_rh 
  from dws_coordination_cust_base t
 where t.pdate = '2020-06-30'
   and t.status = '有效'
   and t.cust_source in ('00001','00002','00003')
;


set hive.exec.dynamici.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

insert overwrite table ads_stat_coordination_index_result_mid partition(category_code,pdate)
select  date_add(current_date,-1) stat_date
       ,stat_index_key key
       ,cast(stat_index_value as decimal(19,4)) value
       ,substr(current_timestamp,1,19) create_time
       ,'custPermeability' category_code
       ,'2020-06-30' pdate
  from (select  str_to_map(concat('permeability-all:',round(t.cust_cnt_two / t.cust_cnt_all,6)
                                 ,'&permeability-all-00001-00003:',round(t.cust_cnt_zq_rh / t.cust_cnt_zq_rh_all,6)
                                 ,'&permeability-all-00001-00002:',round(t.cust_cnt_zq_bs / t.cust_cnt_zq_bs_all,6)
                                 ,'&permeability-all-00002-00003:',round(t.cust_cnt_bs_rh / t.cust_cnt_bs_rh_all,6)
                                 ,'&00001:',round(t.cust_cnt_cross_zq / t.cust_cnt_zq,6)
                                 ,'&permeability-00001-00002:',round(t.cust_cnt_zq_bs / t.cust_cnt_zq,6)
                                 ,'&permeability-00001-00003:',round(t.cust_cnt_zq_rh / t.cust_cnt_zq,6)
                                 ,'&00003:',round(t.cust_cnt_cross_rh / t.cust_cnt_rh,6)
                                 ,'&permeability-00003-00001:',round(t.cust_cnt_zq_rh / t.cust_cnt_rh,6)
                                 ,'&permeability-00003-00002:',round(t.cust_cnt_bs_rh / t.cust_cnt_rh,6)
                                 ,'&00002:',round(t.cust_cnt_cross_bs / t.cust_cnt_bs,6)
                                 ,'&permeability-00002-00001:',round(t.cust_cnt_zq_bs / t.cust_cnt_bs,6)
                                 ,'&permeability-00002-00003:',round(t.cust_cnt_bs_rh / t.cust_cnt_bs,6)
                                 ), '&', ':') as stat_index
          from ads_stat_permeability_group_tmp_0701 t
        ) k
  LATERAL VIEW explode(stat_index) myTable1 AS stat_index_key,stat_index_value
;

优化前

with t_cust_trading_flow_i_tmp as
(select serial_no, cust_no,name,cust_type,sec_type,done_amt,done_vol,bs,status,fund_bal,sec_bal,done_counts,net_amt,contract_no,occur_date
   from ods_syn_t_cust_trading_flow_i
  where pdate = '${IncStartAll}'),
t_cust_trading_flow_tmp as
(select serial_no, cust_no,name,cust_type,sec_type,done_amt,done_vol,bs,status,fund_bal,sec_bal,done_counts,net_amt,contract_no,occur_date
   from ods_bak_t_cust_trading_flow
  where pdate = '${IncStartAll}')
insert overwrite table ods_cmzq_bas_t_cust_trading_flow_excep_log
select tmp.member_ent,
       tmp.member_ent_desc,
       tmp.id,
       tmp.id_name,
       tmp.reason,
       tmp.status,
       tmp.operator,
       tmp.handle_time,
       tmp.update_time,
       tmp.account_time,
       tmp.account_tab_name,
       tmp.account_status,
       tmp.account_info_flag
  from (select 'cmzq' as member_ent,
               '交易流水_普通信息' as member_ent_desc,
               a1.serial_no as id,
               'serial_no' as id_name,
               '客户号未更新' reason,
               '未处理' as status,
               '' as operator,
               '' as handle_time,
               '${IncStartAll}' as update_time,
               '${IncEndAll}' as account_time,
               'ods_bas_t_cust_trading_flow_test' as account_tab_name,
               '失败' as account_status,
               '1' account_info_flag
          from t_cust_trading_flow_i_tmp as a1
          join t_cust_trading_flow_tmp as a2
            on a1.serial_no = a2.serial_no
         where a1.cust_no != a2.cust_no
        UNION ALL
        select 'cmzq' as member_ent,
               '交易流水_普通信息' as member_ent_desc,
               a1.serial_no as id,
               'serial_no' as id_name,
               '姓名未更新' reason,
               '未处理' as status,
               '' as operator,
               '' as handle_time,
               '${IncStartAll}' as update_time,
               '${IncEndAll}' as account_time,
               'ods_bas_t_cust_trading_flow_test' as account_tab_name,
               '失败' as account_status,
               '1' account_info_flag
          from t_cust_trading_flow_i_tmp as a1
          join t_cust_trading_flow_tmp as a2
            on a1.serial_no = a2.serial_no
         where a1.name != a2.name
        UNION ALL
        select 'cmzq' as member_ent,
               '交易流水_普通信息' as member_ent_desc,
               a1.serial_no as id,
               'serial_no' as id_name,
               '客户类型未更新' reason,
               '未处理' as status,
               '' as operator,
               '' as handle_time,
               '${IncStartAll}' as update_time,
               '${IncEndAll}' as account_time,
               'ods_bas_t_cust_trading_flow_test' as account_tab_name,
               '失败' as account_status,
               '1' account_info_flag
          from t_cust_trading_flow_i_tmp as a1
          join t_cust_trading_flow_tmp as a2
            on a1.serial_no = a2.serial_no
         where a1.cust_type != a2.cust_type
        UNION ALL
        select 'cmzq' as member_ent,
               '交易流水_普通信息' as member_ent_desc,
               a1.serial_no as id,
               'serial_no' as id_name,
               '证券类型未更新' reason,
               '未处理' as status,
               '' as operator,
               '' as handle_time,
               '${IncStartAll}' as update_time,
               '${IncEndAll}' as account_time,
               'ods_bas_t_cust_trading_flow_test' as account_tab_name,
               '失败' as account_status,
               '1' account_info_flag
          from t_cust_trading_flow_i_tmp as a1
          join t_cust_trading_flow_tmp as a2
            on a1.serial_no = a2.serial_no
         where a1.sec_type != a2.sec_type
        UNION ALL
        select 'cmzq' as member_ent,
               '交易流水_普通信息' as member_ent_desc,
               a1.serial_no as id,
               'serial_no' as id_name,
               '成交金额未更新' reason,
               '未处理' as status,
               '' as operator,
               '' as handle_time,
               '${IncStartAll}' as update_time,
               '${IncEndAll}' as account_time,
               'ods_bas_t_cust_trading_flow_test' as account_tab_name,
               '失败' as account_status,
               '1' account_info_flag
          from t_cust_trading_flow_i_tmp as a1
          join t_cust_trading_flow_tmp as a2
            on a1.serial_no = a2.serial_no
         where a1.done_amt != a2.done_amt
        UNION ALL
        select 'cmzq' as member_ent,
               '交易流水_普通信息' as member_ent_desc,
               a1.serial_no as id,
               'serial_no' as id_name,
               '成交数量未更新' reason,
               '未处理' as status,
               '' as operator,
               '' as handle_time,
               '${IncStartAll}' as update_time,
               '${IncEndAll}' as account_time,
               'ods_bas_t_cust_trading_flow_test' as account_tab_name,
               '失败' as account_status,
               '1' account_info_flag
          from t_cust_trading_flow_i_tmp as a1
          join t_cust_trading_flow_tmp as a2
            on a1.serial_no = a2.serial_no
         where a1.done_vol != a2.done_vol
        UNION ALL
        select 'cmzq' as member_ent,
               '交易流水_普通信息' as member_ent_desc,
               a1.serial_no as id,
               'serial_no' as id_name,
               '业务类别未更新' reason,
               '未处理' as status,
               '' as operator,
               '' as handle_time,
               '${IncStartAll}' as update_time,
               '${IncEndAll}' as account_time,
               'ods_bas_t_cust_trading_flow_test' as account_tab_name,
               '失败' as account_status,
               '1' account_info_flag
          from t_cust_trading_flow_i_tmp as a1
          join t_cust_trading_flow_tmp as a2
            on a1.serial_no = a2.serial_no
         where a1.bs != a2.bs
        UNION ALL
        select 'cmzq' as member_ent,
               '交易流水_普通信息' as member_ent_desc,
               a1.serial_no as id,
               'serial_no' as id_name,
               '状态未更新' reason,
               '未处理' as status,
               '' as operator,
               '' as handle_time,
               '${IncStartAll}' as update_time,
               '${IncEndAll}' as account_time,
               'ods_bas_t_cust_trading_flow_test' as account_tab_name,
               '失败' as account_status,
               '1' account_info_flag
          from t_cust_trading_flow_i_tmp as a1
          join t_cust_trading_flow_tmp as a2
            on a1.serial_no = a2.serial_no
         where a1.status != a2.status
        UNION ALL
        select 'cmzq' as member_ent,
               '交易流水_普通信息' as member_ent_desc,
               a1.serial_no as id,
               'serial_no' as id_name,
               '资金余额未更新' reason,
               '未处理' as status,
               '' as operator,
               '' as handle_time,
               '${IncStartAll}' as update_time,
               '${IncEndAll}' as account_time,
               'ods_bas_t_cust_trading_flow_test' as account_tab_name,
               '失败' as account_status,
               '1' account_info_flag
          from t_cust_trading_flow_i_tmp as a1
          join t_cust_trading_flow_tmp as a2
            on a1.serial_no = a2.serial_no
         where a1.fund_bal != a2.fund_bal
        UNION ALL
        select 'cmzq' as member_ent,
               '交易流水_普通信息' as member_ent_desc,
               a1.serial_no as id,
               'serial_no' as id_name,
               '证券余额未更新' reason,
               '未处理' as status,
               '' as operator,
               '' as handle_time,
               '${IncStartAll}' as update_time,
               '${IncEndAll}' as account_time,
               'ods_bas_t_cust_trading_flow_test' as account_tab_name,
               '失败' as account_status,
               '1' account_info_flag
          from t_cust_trading_flow_i_tmp as a1
          join t_cust_trading_flow_tmp as a2
            on a1.serial_no = a2.serial_no
         where a1.sec_bal != a2.sec_bal
        UNION ALL
        select 'cmzq' as member_ent,
               '交易流水_普通信息' as member_ent_desc,
               a1.serial_no as id,
               'serial_no' as id_name,
               '成交笔数未更新' reason,
               '未处理' as status,
               '' as operator,
               '' as handle_time,
               '${IncStartAll}' as update_time,
               '${IncEndAll}' as account_time,
               'ods_bas_t_cust_trading_flow_test' as account_tab_name,
               '失败' as account_status,
               '1' account_info_flag
          from t_cust_trading_flow_i_tmp as a1
          join t_cust_trading_flow_tmp as a2
            on a1.serial_no = a2.serial_no
         where a1.done_counts != a2.done_counts
        UNION ALL
        select 'cmzq' as member_ent,
               '交易流水_普通信息' as member_ent_desc,
               a1.serial_no as id,
               'serial_no' as id_name,
               '成交净额未更新' reason,
               '未处理' as status,
               '' as operator,
               '' as handle_time,
               '${IncStartAll}' as update_time,
               '${IncEndAll}' as account_time,
               'ods_bas_t_cust_trading_flow_test' as account_tab_name,
               '失败' as account_status,
               '1' account_info_flag
          from t_cust_trading_flow_i_tmp as a1
          join t_cust_trading_flow_tmp as a2
            on a1.serial_no = a2.serial_no
         where a1.net_amt != a2.net_amt
        UNION ALL
        select 'cmzq' as member_ent,
               '交易流水_普通信息' as member_ent_desc,
               a1.serial_no as id,
               'serial_no' as id_name,
               '合同编号未更新' reason,
               '未处理' as status,
               '' as operator,
               '' as handle_time,
               '${IncStartAll}' as update_time,
               '${IncEndAll}' as account_time,
               'ods_bas_t_cust_trading_flow_test' as account_tab_name,
               '失败' as account_status,
               '1' account_info_flag
          from t_cust_trading_flow_i_tmp as a1
          join t_cust_trading_flow_tmp as a2
            on a1.serial_no = a2.serial_no
         where a1.contract_no != a2.contract_no
        UNION ALL
        select 'cmzq' as member_ent,
               '交易流水_普通信息' as member_ent_desc,
               a1.serial_no as id,
               'serial_no' as id_name,
               '发生日期未更新' reason,
               '未处理' as status,
               '' as operator,
               '' as handle_time,
               '${IncStartAll}' as update_time,
               '${IncEndAll}' as account_time,
               'ods_bas_t_cust_trading_flow_test' as account_tab_name,
               '失败' as account_status,
               '1' account_info_flag
          from t_cust_trading_flow_i_tmp as a1
          join t_cust_trading_flow_tmp as a2
            on a1.serial_no = a2.serial_no
         where a1.occur_date != a2.occur_date) tmp
group by tmp.member_ent,
       tmp.member_ent_desc,
       tmp.id,
       tmp.id_name,
       tmp.reason,
       tmp.status,
       tmp.operator,
       tmp.handle_time,
       tmp.update_time,
       tmp.account_time,
       tmp.account_tab_name,
       tmp.account_status,
       tmp.account_info_flag;

优化后

set hive.groupby.skewindata=true;
 
WITH t_cust_trading_flow_i_tmp AS
  (SELECT serial_no,
          cust_no,
          name,
          cust_type,
          sec_type,
          done_amt,
          done_vol,
          bs,
          status,
          fund_bal,
          sec_bal,
          done_counts,
          net_amt,
          contract_no,
          occur_date
   FROM
     (SELECT *,
             row_number() over (partition BY serial_no
                                ORDER BY occur_date DESC) num
      FROM ods_syn_t_cust_trading_flow_i t
      WHERE pdate = '2018-09-07') a
   WHERE a.num=1),
     t_cust_trading_flow_tmp AS
  (SELECT serial_no,
          cust_no,
          name,
          cust_type,
          sec_type,
          done_amt,
          done_vol,
          bs,
          status,
          fund_bal,
          sec_bal,
          done_counts,
          net_amt,
          contract_no,
          occur_date
   FROM
     (SELECT *,
             row_number() over (partition BY serial_no
                                ORDER BY occur_date DESC) num
      FROM ods_bak_t_cust_trading_flow t
      WHERE pdate = '2018-09-07') a
   WHERE a.num=1)
insert overwrite table ods_cmzq_bas_t_cust_trading_flow_excep_log
select 'cmzq' as member_ent,
       '交易流水_普通信息' as member_ent_desc,
       tmp.serial_no as id,
       'serial_no' as id_name,
       reason,
       '未处理' as status,
       '' as operator,
       '' as handle_time,
       '2018-09-07' as update_time,
       '2018-09-07' as account_time,
       'ods_bas_t_cust_trading_flow_test' as account_tab_name,
       '失败' as account_status,
       '1' account_info_flag  
from (select a1.serial_no,
               concat_ws(',',case when a1.cust_no != a2.cust_no then '客户号未更新' else null end,
               case when a1.name != a2.name then '姓名未更新' else null end,
               case when a1.cust_type != a2.cust_type then '客户类型未更新' else null end,
               case when a1.sec_type != a2.sec_type then '证券类型未更新' else null end,
               case when a1.done_amt != a2.done_amt then '成交金额未更新' else null end,
               case when a1.done_vol != a2.done_vol then '成交数量未更新' else null end,
               case when a1.bs != a2.bs then '业务类别未更新' else null end,
               case when a1.status != a2.status then '状态未更新' else null end,
               case when a1.fund_bal != a2.fund_bal then '资金余额未更新' else null end,
               case when a1.sec_bal != a2.sec_bal then '证券余额未更新' else null end,
               case when a1.done_counts != a2.done_counts then '成交笔数未更新' else null end,
               case when a1.net_amt != a2.net_amt then '成交净额未更新' else null end,
               case when a1.contract_no != a2.contract_no then '合同编号未更新' else null end,
               case when a1.occur_date != a2.occur_date then '发生日期未更新' else null end) as reason_set 
          from t_cust_trading_flow_i_tmp a1
          join t_cust_trading_flow_tmp a2
            on a1.serial_no = a2.serial_no) tmp
  lateral view explode(split(reason_set, ',')) num as reason
  group by
       tmp.serial_no,
       reason;
wingwei_liu
关注
Hive查询：行转列和列转行使用场景说明
爱码士的博客
04-13 1324
1. 引言 对于复杂的数据，存在复杂的数据类型，面对复杂的需求，可以考虑使用行转列，或者列转行的操作 1.1 行转列说明 CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串; CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔...
【Hive】（十）Hive 行转列、列转行详解_hive 字段变成行
最新发布
2401_84182318的博客
04-17 1356
(img-a9P0O62O-1713343047398)]把星座和血型一样的人归类到一起。
hive 列转行，行转列
weixin_37042673的博客
12-07 139
1 列转行 select * from student_relation 展示原始数据 select master_student_id,student_id from student_relation lateral view explode(split(relation_student_id,',')) temTable as student_id 结果如下 2 行转列 select * from tmp 原始数据如下： select master_student_...
hive中的列转行和行转列
dijuan6962的博客
08-29 193
https://www.cnblogs.com/zzhangyuhang/p/9791795.html https://www.cnblogs.com/blogyuhan/p/9274784.html 列转行:user_id order_id 104399 1715131 104399 2105395 104399 1758844 104399 9...
Hive--行转列/列转行
热门推荐
qq_46893497的博客
11-22 2万+
一、lateral view 分类：视图 功能：配合UDTF来使用,把某一行数据拆分成多行数据 很多的UDTF不能将结果与源表进行关联，使用lateral view ，可以将UDTF拆分的单个字段数据与原始表数据关联上 使用方式： select …… from tabelA lateral view UDTF(xxx) 视图名 as a,b,c 案例： --准备数据:vim /export/datas/lateral.txt 1 http://facebook.com/path/p1.php?q
hive行转列，列转行
weixin_30915951的博客
02-21 77
实例一：来源： https://www.cnblogs.com/kimbo/p/6208973.html 行转列(对某列拆分，一列拆多行) 使用函数：lateral view explode(split(column, ',')) num eg: 如表：t_row_to_column_tmp 数据如下，对tag列进行拆分 结果： 列转行(根据主键，进行多行合并一列) ...
hive列转行案例
01-07
hive列转行 1．函数说明 EXPLODE(col)：将 hive 一列中复杂的 array 或者 map 结构拆分成多行。 LATERAL VIEW ： 用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias 解释：用于和 split, explode 等 ...
Hive 行转列，Hive 列转行， UDF，UDTF
ZZJXP的博客
04-07 318
Hive 行转列 hive> create table testArray2( > name string, > weight array<string> > )row format delimited > fields terminated by '\t' > COLLECTION ITEMS terminated by ','; OK Time taken: 0.172 seconds hive
Hive SQL实现行转列跟列转行
alie_123的博客
12-15 717
原理：将UDTF（表生成函数）的结果构建成一个类似于视图的表，然后将原表中的每一行和UDTF函数输出的每一行进行连接，生成一张新的虚拟表。from 表名 lateral view UDTF(xxx) 侧视图名 as 字段名;以上就是hive SQL中行列转换的具体解决方案了。1、首先将单列数据先切割再使用炸裂函数炸开；2、同时配合侧视图生成最后完整表。
hive 列转行 行转列
08-29
- *1* [hive列转行案例](https://download.csdn.net/download/weixin_38581777/14037437)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_...
hive 行转列与列转行
weixin_38629422的博客
11-08 184
列转行：lateralviewexplode 公式：select col1,explode_col1 from table_namelateral view explode(split(col2,',')) t as explode_col1; 举个栗子： selectstage_someone,count(distinct UID)fromLifeStagelateralviewexplode(split(stage,','))LifeStage_tmp...
Hive(30):行转列和列转行操作
小蚯蚓的博客
12-19 314
【转载原文：https://blog.csdn.net/bsf5521/article/details/76576180】 版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。 本文链接：https://blog.csdn.net/bsf5521/article/details/76576180 收起 一、行转列的使用 １、问题 hive如...
列转行 简单练习
xiaoliuxxs
11-08 137
第14讲
hive 列转行 和 行转列
Super乐De博客
02-07 1106
列转行 测试数据的格式如下： hive> select * from col_lie limit 10; OK col_lie.user_id col_lie.order_id 104399 1715131 104399 2105395 104399 1758844 104399 981085 104399 2444143 104399 1458638 104
DW(数仓)和RDBS的行转列，列转行总结
m0_61038164的博客
03-10 2941
想了解更多内容，请关注我的vx公众号：大数据分享200
hive中实现行转列_PostgreSQL实现动态行转列的方法汇总
weixin_36051235的博客
01-01 390
PostgreSQL 提供了一个 tablefunc 模块，内置了多个函数，其中就有 crosstab(交叉表，又叫行转列，或者长表转宽表)，具体使用参见 PostgreSQL 文档(中文[1]，英文[2])。如果不清楚生成的宽表有多少列，或者列太多，手工敲很容易吃力不讨好，那么可以借助这个函数(pivotcode)来简化工作，--PL/pgSQLcodetocreatepiv...
sqoop脚本
qq_43827516的博客
05-12 1204
sqoop导出脚本（不同库） #!/bin/sh source ExitCodeCheck.sh opts=$@ getparam(){ arg=$1 echo $opts |xargs -n1 |cut -b 2- |awk -F'=' '{if($1=="'"$arg"'") print $2}' } IncStart=`getparam inc_start` IncEnd=`getparam inc_end` oracle_connection=`getparam jdbc_s
SQL中的常用小知识点
wtfsb的博客
06-28 655
字符转数字型 to_number(字符型字段,'999999999999999999') 字符转日期型 TO_DATE('${TXNDATE}','YYYY-MM-DD') 如果是空设置成date类型nulldate COALESCE(P9_START_DATE,CAST('${NULLDATE}' AS DATE)) 如果是空设置成timestamp类型0001-01-01 00:00:00 COALESCE(TMS,CAST('0001-01-01 00:00:00'
hive日期时间常用函数
jianai858的博客
06-30 506
current_date 返回当前日期 select current_date -- 今天 select date_add(current_date,1) -- 明天 select date_add(current_date,-1) -- 昨天 date_add和date_sub date_add，增加日期 ，负数减少日期 select date_add('2020-01-01',1) -- 返回2020-01-02 select date_add('2020-01-01',-1) -- 返回201
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值