ODPS一些SQL使用场景标注

wm_concat实现排序和数据去重组合

select id
    , wm_concat(distinct ',', name) within group (order by name desc)
from (
    select 1 id ,'1' name union all
    select 1 id ,'1' name union all
    select 1 id ,'2' name
) t
group by id
;

生成ODPS建表语句

with base as (
    select project_name
        , table_name
        , concat_ws(' ',name,type,'comment',concat("'",col_comment,"'")) as col
        , is_pk
        , concat(project_name,table_name,case when is_pk then '1' else '0' end,seq) as _order
    from odps_project_dwh_stg_prd.meta__m_column
    where ds=date_format(date_sub(current_date,1),'yyyyMMdd')
        and project_name='xxxx'
        -- and table_name='ads_fas_selfdefrpt_tjfx_ptmzjjzfblzb_e'
    order by concat(project_name,table_name,case when is_pk then '1' else '0' end,seq)
),
cols as (
    select project_name
        , table_name
        , wm_concat('\n    , ', col) within group (order by _order)  as _sql
    from base
    where not is_pk
    group by project_name
        , table_name
),
parts as (
    select project_name
        , table_name
        , wm_concat('\n    , ', col) within group (order by _order) as _sql
    from base
    where is_pk
    group by project_name
        , table_name
)
select concat(
          '\ncreate table if not exists '
        , a.project_name
        , '.'
        , a.table_name
        , ' (\n      '
        , a._sql
        , case when b.table_name is not null then '\n) partitioned by (\n      ' else '' end
        , case when b.table_name is not null then b._sql else '' end
        , '\n) stored as aliorc\n;\n'
    ) as _sql
from cols a
left join parts b on a.project_name=b.project_name and a.table_name=b.table_name
;