with g_z as
(
    select cast(og3.id as text) cd, og2.gongzhong clss2 from public.ods_gongzhong og3
    left outer join public.ods_gongzhong og2 on og3.pid = og2.id
    where og2.gongzhong is not null
)
, b_gz as 
(
    select distinct user_id as dis_user_id
    ,count( user_id ) as user_id_cnt
    , view_count
    ,o_ctiy."name" as ctiy_cnt
    ,UNNEST(regexp_split_to_array( profession , '\,')) gz_num
    ,gz_cnt.clss2
    from public.ods_gczdw 
    left join ods_area o_ctiy on province_id = o_ctiy.pid
    left join g_z gz_cnt on ods_gczdw.gz_num = g_z.cd
    where to_char(to_timestamp( add_time ) ,'yyyy-mm-dd') 
    between '2021-01-01' and '2021-09-01'
    group by dis_user_id,view_count,o_ctiy."name"
)
select * from b_gz

复制