1、DWD产品访问明细
1.1、用户产品权限数据
INSERT OVERWRITE TABLE temp_lms.dm_lms_platform_usergroup_app_tmpselect '仓储司南' as pro_name,'CCSN' as pro_code,c.user_name as user_name,d.account_name as user_mip,c.org_name as org_name,c.org_code as org_code,case when c.org_name like '%_财经_%' then '财经'when c.org_name like '%营销中心%' then '营销中心'when c.org_name like '%产品中心%' then '产品中心'when c.org_name like '%技术中心%' then '技术中心'when c.org_name like '%营运与人力资源%' then '营运与人力资源'when c.org_name like '%_运营本部_基地干线公司%' then '基地干线公司'when c.org_name like '%_运营本部_城配送装公司%' then '城配送装公司'when c.org_name like '%_运营本部_生产供应链系统工程公司%' then '生产供应链系统工程公司'when c.org_name like '%_运营本部_订单与品质部%' then '订单与品质部'when c.org_name like '%_运营本部_规划与变革部%' then '规划与变革部'when c.org_name like '%_运营本部_仓储管理部%' then '仓储管理部'when c.org_name like '%_运营本部_营运支持部%' then '营运支持部'when c.org_name like '%_运营本部_运营管理部%' then '运营管理部'when c.org_name like '%区' or c.org_name like '%京津冀' then replace(c.org_name,'_集团_数字化创新业务_ANDE智联科技公司_区域经营中心_','')when c.org_name like '%分公司%' and c.org_name like '%京津冀%' then regexp_replace(regexp_replace(c.org_name,'.*京津冀_',''),'分公司_.*','分公司')when c.org_name like '%分公司%' then regexp_replace(regexp_replace(c.org_name,'.*区_',''),'分公司.*','分公司')when c.org_name like '%_运营本部_%' then regexp_replace(replace(c.org_name,'_集团_数字化创新业务_ANDE智联科技公司_运营本部_',''),'_.*','')when c.org_name like '%_区域经营中心%' then '区域经营中心'when c.org_name='_集团_数字化创新业务_ANDE智联科技公司' then 'ANDE'else 'NO-ANNTO'end as shortorg_name,case when c.org_name like '%_财经_%' then '总部'when c.org_name like '%营销中心%' then '总部'when c.org_name like '%产品中心%' then '总部'when c.org_name like '%技术中心%' then '总部'when c.org_name like '%营运与人力资源%' then '总部'when c.org_name like '%_运营本部_基地干线公司%' then '总部'when c.org_name like '%_运营本部_城配送装公司%' then '总部'when c.org_name like '%_运营本部_生产供应链系统工程公司%' then '总部'when c.org_name like '%_运营本部_订单与品质部%' then '总部'when c.org_name like '%_运营本部_规划与变革部%' then '总部'when c.org_name like '%_运营本部_仓储管理部%' then '总部'when c.org_name like '%_运营本部_营运支持部%' then '总部'when c.org_name like '%_运营本部_运营管理部%' then '总部'when c.org_name like '%_区域经营中心_%' then '分公司'when c.org_name='_集团_数字化创新业务_ANDE智联科技公司' then 'ANDE'else '非ANDE'end as org_catery,c.position_name as position_name,a.gmt_create as perm_time,case when a.gmt_create is not null then '是' else '否' end as is_perm,from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as w_insert_dt,current_date() as period_wid from (select usergroup_id,user_id,min(gmt_create) as gmt_create from platform.yunbi_usergroup_member group by usergroup_id,user_id) aleft join platform.yunbi_usergroup b on a.usergroup_id=b.usergroup_idleft join platform.yunbi_user d on a.user_id=d.user_idleft join lms.logistics_auth_user_info c on d.account_name =c.user_code where b.usergroup_name like '%仓储司南%'union allselect '万象台' as pro_name,'WXTYL' as pro_code,c.user_name as user_name,d.account_name as user_mip,c.org_name as org_name,c.org_code as org_code,case when c.org_name like '%_财经_%' then '财经'when c.org_name like '%营销中心%' then '营销中心'when c.org_name like '%产品中心%' then '产品中心'when c.org_name like '%技术中心%' then '技术中心'when c.org_name like '%营运与人力资源%' then '营运与人力资源'when c.org_name like '%_运营本部_基地干线公司%' then '基地干线公司'when c.org_name like '%_运营本部_城配送装公司%' then '城配送装公司'when c.org_name like '%_运营本部_生产供应链系统工程公司%' then '生产供应链系统工程公司'when c.org_name like '%_运营本部_订单与品质部%' then '订单与品质部'when c.org_name like '%_运营本部_规划与变革部%' then '规划与变革部'when c.org_name like '%_运营本部_仓储管理部%' then '仓储管理部'when c.org_name like '%_运营本部_营运支持部%' then '营运支持部'when c.org_name like '%_运营本部_运营管理部%' then '运营管理部'when c.org_name like '%区' or c.org_name like '%京津冀' then replace(c.org_name,'_集团_数字化创新业务_ANDE智联科技公司_区域经营中心_','')when c.org_name like '%分公司%' and c.org_name like '%京津冀%' then regexp_replace(regexp_replace(c.org_name,'.*京津冀_',''),'分公司_.*','分公司')when c.org_name like '%分公司%' then regexp_replace(regexp_replace(c.org_name,'.*区_',''),'分公司.*','分公司')when c.org_name like '%_运营本部_%' then regexp_replace(replace(c.org_name,'_集团_数字化创新业务_ANDE智联科技公司_运营本部_',''),'_.*','')when c.org_name like '%_区域经营中心%' then '区域经营中心'when c.org_name='_集团_数字化创新业务_ANDE智联科技公司' then 'ANDE'else '非ANDE'end as shortorg_name,case when c.org_name like '%_财经_%' then '总部'when c.org_name like '%营销中心%' then '总部'when c.org_name like '%产品中心%' then '总部'when c.org_name like '%技术中心%' then '总部'when c.org_name like '%营运与人力资源%' then '总部'when c.org_name like '%_运营本部_基地干线公司%' then '总部'when c.org_name like '%_运营本部_城配送装公司%' then '总部'when c.org_name like '%_运营本部_生产供应链系统工程公司%' then '总部'when c.org_name like '%_运营本部_订单与品质部%' then '总部'when c.org_name like '%_运营本部_规划与变革部%' then '总部'when c.org_name like '%_运营本部_仓储管理部%' then '总部'when c.org_name like '%_运营本部_营运支持部%' then '总部'when c.org_name like '%_运营本部_运营管理部%' then '总部' when c.org_name like '%_区域经营中心_%' then '分公司'when c.org_name='_集团_数字化创新业务_ANDE智联科技公司' then 'ANDE'else '非ANDE'end as org_catery,c.position_name as position_name,a.gmt_create as perm_time,case when a.gmt_create is not null then '是' else '否' end as is_perm,from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as w_insert_dt,current_date() as period_wid from (select usergroup_id,user_id,min(gmt_create) as gmt_create from platform.yunbi_usergroup_member group by usergroup_id,user_id) aleft join platform.yunbi_usergroup b on a.usergroup_id=b.usergroup_idleft join platform.yunbi_user d on a.user_id=d.user_idleft join lms.logistics_auth_user_info c on a.user_id =c.user_code where b.usergroup_name like '%万象台%'union all select '智慧星' as pro_name,'ZHXCJ' as pro_code,c.user_name as user_name,d.account_name as user_mip,c.org_name as org_name,c.org_code as org_code,case when c.org_name like '%_财经_%' then '财经'when c.org_name like '%营销中心%' then '营销中心'when c.org_name like '%产品中心%' then '产品中心'when c.org_name like '%技术中心%' then '技术中心'when c.org_name like '%营运与人力资源%' then '营运与人力资源'when c.org_name like '%_运营本部_基地干线公司%' then '基地干线公司'when c.org_name like '%_运营本部_城配送装公司%' then '城配送装公司'when c.org_name like '%_运营本部_生产供应链系统工程公司%' then '生产供应链系统工程公司'when c.org_name like '%_运营本部_订单与品质部%' then '订单与品质部'when c.org_name like '%_运营本部_规划与变革部%' then '规划与变革部'when c.org_name like '%_运营本部_仓储管理部%' then '仓储管理部'when c.org_name like '%_运营本部_营运支持部%' then '营运支持部'when c.org_name like '%_运营本部_运营管理部%' then '运营管理部' when c.org_name like '%区' or c.org_name like '%京津冀' then replace(c.org_name,'_集团_数字化创新业务_ANDE智联科技公司_区域经营中心_','')when c.org_name like '%分公司%' and c.org_name like '%京津冀%' then regexp_replace(regexp_replace(c.org_name,'.*京津冀_',''),'分公司_.*','分公司')when c.org_name like '%分公司%' then regexp_replace(regexp_replace(c.org_name,'.*区_',''),'分公司.*','分公司')when c.org_name like '%_运营本部_%' then regexp_replace(replace(c.org_name,'_集团_数字化创新业务_ANDE智联科技公司_运营本部_',''),'_.*','')when c.org_name like '%_区域经营中心%' then '区域经营中心'when c.org_name='_集团_数字化创新业务_ANDE智联科技公司' then 'ANDE'else '非ANDE'end as shortorg_name,case when c.org_name like '%_财经_%' then '总部'when c.org_name like '%营销中心%' then '总部'when c.org_name like '%产品中心%' then '总部'when c.org_name like '%技术中心%' then '总部'when c.org_name like '%营运与人力资源%' then '总部'when c.org_name like '%_运营本部_基地干线公司%' then '总部'when c.org_name like '%_运营本部_城配送装公司%' then '总部'when c.org_name like '%_运营本部_生产供应链系统工程公司%' then '总部'when c.org_name like '%_运营本部_订单与品质部%' then '总部'when c.org_name like '%_运营本部_规划与变革部%' then '总部'when c.org_name like '%_运营本部_仓储管理部%' then '总部'when c.org_name like '%_运营本部_营运支持部%' then '总部'when c.org_name like '%_运营本部_运营管理部%' then '总部'when c.org_name like '%_区域经营中心_%' then '分公司'when c.org_name='_集团_数字化创新业务_ANDE智联科技公司' then 'ANDE'else '非ANDE'end as org_catery,c.position_name as position_name,a.gmt_create as perm_time,case when a.gmt_create is not null then '是' else '否' end as is_perm,from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as w_insert_dt,current_date() as period_wid
from (select usergroup_id,user_id,min(gmt_create) as gmt_create from platform.yunbi_usergroup_member group by usergroup_id,user_id) aleft join platform.yunbi_usergroup b on a.usergroup_id=b.usergroup_idleft join platform.yunbi_user d on a.user_id=d.user_idleft join lms.logistics_auth_user_info c on a.user_id =c.user_code where b.usergroup_name like '%智慧星-经营分析角色%' or b.usergroup_name in ('智慧星-项目盈利分析角色','智慧星-白名单角色')union all select '管理报表' as pro_name,'ZHXGL' as pro_code,c.user_name as user_name,d.account_name as user_mip,c.org_name as org_name,c.org_code as org_code,case when c.org_name like '%_财经_%' then '财经'when c.org_name like '%营销中心%' then '营销中心'when c.org_name like '%产品中心%' then '产品中心'when c.org_name like '%技术中心%' then '技术中心'when c.org_name like '%营运与人力资源%' then '营运与人力资源'when c.org_name like '%_运营本部_基地干线公司%' then '基地干线公司'when c.org_name like '%_运营本部_城配送装公司%' then '城配送装公司'when c.org_name like '%_运营本部_生产供应链系统工程公司%' then '生产供应链系统工程公司'when c.org_name like '%_运营本部_订单与品质部%' then '订单与品质部'when c.org_name like '%_运营本部_规划与变革部%' then '规划与变革部'when c.org_name like '%_运营本部_仓储管理部%' then '仓储管理部'when c.org_name like '%_运营本部_营运支持部%' then '营运支持部'when c.org_name like '%_运营本部_运营管理部%' then '运营管理部' when c.org_name like '%区' or c.org_name like '%京津冀' then replace(c.org_name,'_集团_数字化创新业务_ANDE智联科技公司_区域经营中心_','')when c.org_name like '%分公司%' and c.org_name like '%京津冀%' then regexp_replace(regexp_replace(c.org_name,'.*京津冀_',''),'分公司_.*','分公司')when c.org_name like '%分公司%' then regexp_replace(regexp_replace(c.org_name,'.*区_',''),'分公司.*','分公司')when c.org_name like '%_运营本部_%' then regexp_replace(replace(c.org_name,'_集团_数字化创新业务_ANDE智联科技公司_运营本部_',''),'_.*','')when c.org_name like '%_区域经营中心%' then '区域经营中心'when c.org_name='_集团_数字化创新业务_ANDE智联科技公司' then 'ANDE'else '非ANDE'end as shortorg_name,case when c.org_name like '%_财经_%' then '总部'when c.org_name like '%营销中心%' then '总部'when c.org_name like '%产品中心%' then '总部'when c.org_name like '%技术中心%' then '总部'when c.org_name like '%营运与人力资源%' then '总部'when c.org_name like '%_运营本部_基地干线公司%' then '总部'when c.org_name like '%_运营本部_城配送装公司%' then '总部'when c.org_name like '%_运营本部_生产供应链系统工程公司%' then '总部'when c.org_name like '%_运营本部_订单与品质部%' then '总部'when c.org_name like '%_运营本部_规划与变革部%' then '总部'when c.org_name like '%_运营本部_仓储管理部%' then '总部'when c.org_name like '%_运营本部_营运支持部%' then '总部'when c.org_name like '%_运营本部_运营管理部%' then '总部'when c.org_name like '%_区域经营中心_%' then '分公司'when c.org_name='_集团_数字化创新业务_ANDE智联科技公司' then 'ANDE'else '非ANDE'end as org_catery,c.position_name as position_name,a.gmt_create as perm_time,case when a.gmt_create is not null then '是' else '否' end as is_perm,from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as w_insert_dt,current_date() as period_wid from (select usergroup_id,user_id,min(gmt_create) as gmt_create from platform.yunbi_usergroup_member group by usergroup_id,user_id) aleft join platform.yunbi_usergroup b on a.usergroup_id=b.usergroup_idleft join platform.yunbi_user d on a.user_id=d.user_idleft join lms.logistics_auth_user_info c on a.user_id =c.user_codewhere b.usergroup_name like '%智慧星-管报%'union all select'摘星台' as pro_name,'ZXTCP' as pro_code,ui.user_name as user_name,ui.user_code as user_code,ui.org_name as org_name,ui.org_code as org_code,case when ui.org_name like '%_财经_%' then '财经'when ui.org_name like '%营销中心%' then '营销中心'when ui.org_name like '%产品中心%' then '产品中心'when ui.org_name like '%技术中心%' then '技术中心'when ui.org_name like '%营运与人力资源%' then '营运与人力资源'when ui.org_name like '%_运营本部_基地干线公司%' then '基地干线公司'when ui.org_name like '%_运营本部_城配送装公司%' then '城配送装公司'when ui.org_name like '%_运营本部_生产供应链系统工程公司%' then '生产供应链系统工程公司'when ui.org_name like '%_运营本部_订单与品质部%' then '订单与品质部'when ui.org_name like '%_运营本部_规划与变革部%' then '规划与变革部'when ui.org_name like '%_运营本部_仓储管理部%' then '仓储管理部'when ui.org_name like '%_运营本部_营运支持部%' then '营运支持部'when ui.org_name like '%_运营本部_运营管理部%' then '运营管理部'when ui.org_name like '%区' or ui.org_name like '%京津冀' then replace(ui.org_name,'_集团_数字化创新业务_ANDE智联科技公司_区域经营中心_','')when ui.org_name like '%分公司%' and ui.org_name like '%京津冀%' then regexp_replace(regexp_replace(ui.org_name,'.*京津冀_',''),'分公司_.*','分公司')when ui.org_name like '%分公司%' then regexp_replace(regexp_replace(ui.org_name,'.*区_',''),'分公司.*','分公司')when ui.org_name like '%_运营本部_%' then regexp_replace(replace(ui.org_name,'_集团_数字化创新业务_ANDE智联科技公司_运营本部_',''),'_.*','')when ui.org_name like '%_区域经营中心%' then '区域经营中心'when ui.org_name='_集团_数字化创新业务_ANDE智联科技公司' then 'ANDE'else '非ANDE'end as shortorg_name,case when ui.org_name like '%_财经_%' then '总部'when ui.org_name like '%营销中心%' then '总部'when ui.org_name like '%产品中心%' then '总部'when ui.org_name like '%技术中心%' then '总部'when ui.org_name like '%营运与人力资源%' then '总部'when ui.org_name like '%_运营本部_基地干线公司%' then '总部'when ui.org_name like '%_运营本部_城配送装公司%' then '总部'when ui.org_name like '%_运营本部_生产供应链系统工程公司%' then '总部'when ui.org_name like '%_运营本部_订单与品质部%' then '总部'when ui.org_name like '%_运营本部_规划与变革部%' then '总部'when ui.org_name like '%_运营本部_仓储管理部%' then '总部'when ui.org_name like '%_运营本部_营运支持部%' then '总部'when ui.org_name like '%_运营本部_运营管理部%' then '总部'when ui.org_name like '%_区域经营中心_%' then '分公司'when ui.org_name='_集团_数字化创新业务_ANDE智联科技公司' then 'ANDE'else '非ANDE'end as org_catery,ui.position_name as position_name,ur.create_time as perm_time,case when ur.create_time is not null then '是' else '否' end as is_perm,from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as w_insert_dt,current_date() as period_wid from (select role_codefrom lms.logistics_auth_role where delete_flag=0 and enable_flag=1 and role_name in ( '线长维度-管理岗','线长规则维护') group by role_code) r
inner join (select role_code from lms.logistics_auth_role_application where application_code='APP201904250002') ra on r.role_code=ra.role_code
inner join (select role_code,user_code,min(create_time) as create_time from lms.logistics_auth_user_role where delete_flag = 0 and tenant_code ='annto' and enable_flag=1group by role_code,user_code) ur on r.role_code =ur.role_code
inner join (select org_code,org_name,user_code,user_name,position_name from lms.logistics_auth_user_info where delete_flag=0) ui on ur.user_code = ui.user_codeunion all select '北极星' as pro_name,'ACCSX' as pro_code,ui.user_name as user_name,ui.user_code as user_code,ui.org_name as org_name,ui.org_code as org_code,case when ui.org_name like '%_财经_%' then '财经'when ui.org_name like '%营销中心%' then '营销中心'when ui.org_name like '%产品中心%' then '产品中心'when ui.org_name like '%技术中心%' then '技术中心'when ui.org_name like '%营运与人力资源%' then '营运与人力资源'when ui.org_name like '%_运营本部_基地干线公司%' then '基地干线公司'when ui.org_name like '%_运营本部_城配送装公司%' then '城配送装公司'when ui.org_name like '%_运营本部_生产供应链系统工程公司%' then '生产供应链系统工程公司'when ui.org_name like '%_运营本部_订单与品质部%' then '订单与品质部'when ui.org_name like '%_运营本部_规划与变革部%' then '规划与变革部'when ui.org_name like '%_运营本部_仓储管理部%' then '仓储管理部'when ui.org_name like '%_运营本部_营运支持部%' then '营运支持部'when ui.org_name like '%_运营本部_运营管理部%' then '运营管理部'when ui.org_name like '%区' or ui.org_name like '%京津冀' then replace(ui.org_name,'_集团_数字化创新业务_ANDE智联科技公司_区域经营中心_','')when ui.org_name like '%分公司%' and ui.org_name like '%京津冀%' then regexp_replace(regexp_replace(ui.org_name,'.*京津冀_',''),'分公司_.*','分公司')when ui.org_name like '%分公司%' then regexp_replace(regexp_replace(ui.org_name,'.*区_',''),'分公司.*','分公司')when ui.org_name like '%_运营本部_%' then regexp_replace(replace(ui.org_name,'_集团_数字化创新业务_ANDE智联科技公司_运营本部_',''),'_.*','')when ui.org_name like '%_区域经营中心%' then '区域经营中心'when ui.org_name='_集团_数字化创新业务_ANDE智联科技公司' then 'ANDE'else '非ANDE'end as shortorg_name,case when ui.org_name like '%_财经_%' then '总部'when ui.org_name like '%营销中心%' then '总部'when ui.org_name like '%产品中心%' then '总部'when ui.org_name like '%技术中心%' then '总部'when ui.org_name like '%营运与人力资源%' then '总部'when ui.org_name like '%_运营本部_基地干线公司%' then '总部'when ui.org_name like '%_运营本部_城配送装公司%' then '总部'when ui.org_name like '%_运营本部_生产供应链系统工程公司%' then '总部'when ui.org_name like '%_运营本部_订单与品质部%' then '总部'when ui.org_name like '%_运营本部_规划与变革部%' then '总部'when ui.org_name like '%_运营本部_仓储管理部%' then '总部'when ui.org_name like '%_运营本部_营运支持部%' then '总部'when ui.org_name like '%_运营本部_运营管理部%' then '总部'when ui.org_name like '%_区域经营中心_%' then '分公司'when ui.org_name='_集团_数字化创新业务_ANDE智联科技公司' then 'ANDE'else '非ANDE'end as org_catery,ui.position_name as position_name,ur.create_time as perm_time,case when ur.create_time is not null then '是' else '否' end as is_perm,from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as w_insert_dt,current_date() as period_wid from (select role_codefrom lms.logistics_auth_rolewhere delete_flag=0 and enable_flag=1 and role_name in ('ACC管理中心-业务','ACC-时效看板','ACC-指标中心','ACC-管理员') group by role_code) r
inner join (select role_code from lms.logistics_auth_role_application where application_code='APP202303160047') ra on ra.role_code=r.role_code
inner join (select role_code,user_code,min(create_time) as create_time from lms.logistics_auth_user_role where delete_flag = 0 and enable_flag=1 and tenant_code ='annto'group by role_code,user_code) ur on r.role_code =ur.role_code
inner join (select org_code,org_name,user_code,user_name,position_name from lms.logistics_auth_user_info where delete_flag=0) ui on ur.user_code = ui.user_codeunion all select'驾驶舱' as pro_name ,'U-MCP' as pro_code ,a.user_name as user_name ,a.user_code as user_mip ,a.department_name as org_name ,a.department_code as org_code ,a.shortorg_name as shortorg_name ,a.org_catery as org_catery ,a.position_name as position_name ,min(a.valid_time) as perm_time ,'是' as is_perm ,from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as w_insert_dt,a.period_wid as period_wid from
(select user_name,user_code,department_name,department_code,case when department_name like '%_财经_%' then '财经'when department_name like '%营销中心%' then '营销中心'when department_name like '%产品中心%' then '产品中心'when department_name like '%技术中心%' then '技术中心'when department_name like '%营运与人力资源%' then '营运与人力资源'when department_name like '%_运营本部_基地干线公司%' then '基地干线公司'when department_name like '%_运营本部_城配送装公司%' then '城配送装公司'when department_name like '%_运营本部_生产供应链系统工程公司%' then '生产供应链系统工程公司'when department_name like '%_运营本部_订单与品质部%' then '订单与品质部'when department_name like '%_运营本部_规划与变革部%' then '规划与变革部'when department_name like '%_运营本部_仓储管理部%' then '仓储管理部'when department_name like '%_运营本部_营运支持部%' then '营运支持部'when department_name like '%_运营本部_运营管理部%' then '运营管理部'when department_name like '%区' or department_name like '%京津冀' then replace(department_name,'_集团_数字化创新业务_ANDE智联科技公司_区域经营中心_','')when department_name like '%分公司%' and department_name like '%京津冀%' then regexp_replace(regexp_replace(department_name,'.*京津冀_',''),'分公司_.*','分公司')when department_name like '%分公司%' then regexp_replace(regexp_replace(department_name,'.*区_',''),'分公司.*','分公司')when department_name like '%_运营本部_%' then regexp_replace(replace(department_name,'_集团_数字化创新业务_ANDE智联科技公司_运营本部_',''),'_.*','')when department_name like '%_区域经营中心%' then '区域经营中心'when department_name='_集团_数字化创新业务_ANDE智联科技公司' then 'ANDE'else '非ANDE'end as shortorg_name,case when department_name like '%_财经_%' then '总部'when department_name like '%营销中心%' then '总部'when department_name like '%产品中心%' then '总部'when department_name like '%技术中心%' then '总部'when department_name like '%营运与人力资源%' then '总部'when department_name like '%_运营本部_基地干线公司%' then '总部'when department_name like '%_运营本部_城配送装公司%' then '总部'when department_name like '%_运营本部_生产供应链系统工程公司%' then '总部'when department_name like '%_运营本部_订单与品质部%' then '总部'when department_name like '%_运营本部_规划与变革部%' then '总部'when department_name like '%_运营本部_仓储管理部%' then '总部'when department_name like '%_运营本部_营运支持部%' then '总部'when department_name like '%_运营本部_运营管理部%' then '总部'when department_name like '%_区域经营中心_%' then '分公司'when department_name='_集团_数字化创新业务_ANDE智联科技公司' then 'ANDE'else '非ANDE'end as org_catery,main_position as position_name,valid_time as valid_time,period_wid as period_wid,(case when role_code='RL2022042820140' and (is_area+is_company+is_center+is_professional_comp+is_transaction_type+is_big_industry+is_customer)=7 then '有效用户' when role_code<>'RL2022042820140' and (is_area+is_company+is_center+is_professional_comp+is_transaction_type+is_big_industry)=6 then '有效用户' when role_code in ('RL2022052725507','RL2022052725508','RL2022052725509') and (is_area+is_company+is_center)=3 then '有效用户' when role_code in ('RL2022090946149','RL2022090946147') and (is_company+is_center)=2 then '有效用户' when role_code in ('RL2022052725501','RL2022052725502','RL2022052725504') and is_center=1 then '有效用户' when role_code='RL2022061729550' and (is_area+is_company+is_professional_comp+is_transaction_type+is_big_industry)=5 then '有效用户' when role_code='RL2022031013212' and (is_company+is_center+is_professional_comp+is_transaction_type+is_big_industry)=5 then '有效用户' when role_code='RL2022031013213' and (is_company+is_center+is_transaction_type+is_big_industry)=4 then '有效用户' when role_code in ('RL2022111158820','RL2022111860264') and (is_area+is_company+is_center+is_transaction_type+is_big_industry)=5 then '有效用户' when role_code in ('RL2022093050616','RL202306010842','RL202305180644') and (is_area+is_company+is_transaction_type+is_big_industry)=4 then '有效用户' else '缺数据权限' end) as user_typefrom dm_lms.dm_mcp_valid_user_dtlwhere 1=1 and delete_flag=0and role_name not like '%_annto'and role_code<>'RL202112153873' and role_code<>'RL2022123067721' and user_code not in ('qianglei1','liangpf','zhangyuan65','xuanjm1','wangpc18') and period_wid<substr(to_date(current_timestamp()),1,10))a where a.user_type='有效用户'group by a.user_name,a.user_code,a.department_name,a.department_code,a.shortorg_name,a.org_catery,a.position_name,a.period_wid;
1.2、用户产品权限数据去重
INSERT OVERWRITE TABLE temp_lms.dm_lms_platform_usergroup_app_dist_tmpselect pro_name ,pro_code ,user_name ,user_mip ,org_name ,org_code ,shortorg_name ,org_catery ,position_name ,substr(perm_time,1,10) as perm_time ,is_perm ,from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as w_insert_dt ,period_wid fromtemp_lms.dm_lms_platform_usergroup_app_tmpwhere user_mip is not nullgroup by pro_name ,pro_code ,user_name ,user_mip ,org_name ,org_code ,shortorg_name ,org_catery ,position_name ,substr(perm_time,1,10) ,is_perm ,period_wid ;
1.3、产品访问数据
INSERT OVERWRITE TABLE temp_lms.dm_lms_platform_app_visit_tmpselectmip_code as user_mip ,'仓储司南' as pro_name ,'CCSN' as pro_code ,report_name as visit_menu ,report_name as visit_table ,case when star_dt is not null then '是' else '否' end as is_visit ,substr(star_dt,1,10) as visit_time ,from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as w_insert_dt fromplatform.ads_user_qbi_visit_log
where 1=1 and mip_code is not nulland portal_name = '仓储司南'and workspace_name = '安得'and star_dt >= '${START_DATE}'and star_dt < '${END_DATE}'union all selectmip_code as user_mip ,'万象台' as pro_name ,'WXTYL' as pro_code ,report_name as visit_menu ,report_name as visit_table ,case when star_dt is not null then '是' else '否' end as is_visit ,substr(star_dt,1,10) as visit_time ,from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as w_insert_dt fromplatform.ads_user_qbi_visit_log
where 1=1 and mip_code is not nulland portal_name like '%万象台%'and workspace_name = '安得'and star_dt >= '${START_DATE}'and star_dt < '${END_DATE}'union all selectmip_code as user_mip ,'智慧星' as pro_name ,'ZHXCJ' as pro_code ,report_name as visit_menu ,report_name as visit_table ,case when star_dt is not null then '是' else '否' end as is_visit ,substr(star_dt,1,10) as visit_time ,from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as w_insert_dt fromplatform.ads_user_qbi_visit_log
where 1=1 and mip_code is not nulland portal_name = '智慧星'and workspace_name = '安得'and report_name in ('整体经营达成情况_整体分析', '整体经营达成情况_分公司分析', '整体经营达成情况_经营中心分析', '整体经营达成情况_片区分析', '采购毛利_分公司分析', '采购毛利_经营中心分析', '采购毛利率预算_经营中心', '采购毛利率预算_专司', '采购毛利率预算_行业', '销售预测', '智慧星', '整体经营达成情况', '经营中心利润明细', '客户经营中心利润', '采购毛利', '月累计采购毛利波动分析', '年累计采购毛利波动分析', '毛利明细', '客户毛利', '采购毛利率预算', '收入预算', '收入成本明细')and star_dt >= '${START_DATE}'and star_dt < '${END_DATE}'union all selectmip_code as user_mip ,'管理报表' as pro_name ,'ZHXGL' as pro_code ,report_name as visit_menu ,report_name as visit_table ,case when star_dt is not null then '是' else '否' end as is_visit ,substr(star_dt,1,10) as visit_time ,from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as w_insert_dt fromplatform.ads_user_qbi_visit_log
where 1=1 and mip_code is not nulland portal_name='智慧星'and workspace_name='安得'and (report_name='管理报表' or report_name='管报')and star_dt >= '${START_DATE}'and star_dt < '${END_DATE}'union all selectusername as user_mip ,'摘星台' as pro_name ,'ZXTCP' as pro_code ,model_name1 as visit_menu ,model_name2 as visit_table ,case when part_dt is not null then '是' else '否' end as is_visit ,part_dt as visit_time ,from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as w_insert_dt fromdm.dm_sys_product_visit_detail_log_jt
where 1=1 and username is not null and module_code_en like '%C-TMS%'and model_name1 like '%摘星台%'and part_dt >= '${START_DATE}'and part_dt < '${END_DATE}'union all select username as user_mip ,'北极星' as pro_name ,'ACCSX' as pro_code ,model_name1 as visit_menu ,model_name2 as visit_table ,case when part_dt is not null then '是' else '否' end as is_visit ,part_dt as visit_time ,from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as w_insert_dt
from dm.dm_sys_product_visit_detail_log_jt
where 1=1 and username is not null and module_code_en like '%U-ACC%'and model_name1 in('首页','指标中心','个性报表','配置化报表','时效看板')and part_dt >= '${START_DATE}'and part_dt < '${END_DATE}'union all select a.user_code as user_mip ,'驾驶舱' as pro_name ,'U-MCP' as pro_code ,a.active_index as visit_menu ,'' as visit_table ,'是' as is_visit ,a.period_wid as visit_time ,from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as w_insert_dt FROM
(select period_wid,user_code,(case when page_L3='驾驶舱-送装' then '送装专题'when page_L3='专题' or (page_L3='指标库' and page_L4='' and page_L5='日月年筛选框') then '客户画像' when (page_L6 is not null and page_L6<>'') or (page_L5 is not null and page_L5<>'') then if((page_L4='' or page_L4 is null),if(page_L5 like '%收入%','收入','指标汇总'),page_L4)when (page_L4 is not null and page_L4<>'') or (page_L3 is not null and page_L3<>'') then page_L3else '指标汇总' end) as active_indexfrom dm_lms.dm_mcp_log_trck_bsc_byte_dtl_diwhere 1=1
and event_name<>'app_launch'and period_wid >= '${START_DATE}'and period_wid < '${END_DATE}'group by period_wid,user_code,(case when page_L3='驾驶舱-送装' then '送装专题'when page_L3='专题' or (page_L3='指标库' and page_L4='' and page_L5='日月年筛选框') then '客户画像' when (page_L6 is not null and page_L6<>'') or (page_L5 is not null and page_L5<>'') then if((page_L4='' or page_L4 is null),if(page_L5 like '%收入%','收入','指标汇总'),page_L4)when (page_L4 is not null and page_L4<>'') or (page_L3 is not null and page_L3<>'') then page_L3else '指标汇总' end)
) a
left join
( select a.period_wid, a.user_code, (case when role_name like '责任制时效%' or role_name like '时长监控%' then '时长监控'when role_name like '责任制得分%' then '责任制得分'when role_name like '销售空仓率%' then '销售空仓率'when role_name like '送装专题%' then '送装专题'when role_name like '客户画像%' then '客户画像'when role_name='营销个人销售达成' then '个人销售达成'when role_name='付款额度' then '资金周期'when role_name='采购毛利率' then '采购毛利'else role_name end) as index_namefrom dm_lms.dm_mcp_valid_user_dtl awhere 1=1 and delete_flag=0and role_name not like '%_annto'and role_code<>'RL202112153873' and role_code<>'RL2022123067721' and user_code not in ('qianglei1','liangpf','zhangyuan65','xuanjm1','wangpc18') and (case when a.role_code='RL2022042820140' and is_area+is_company+is_center+is_professional_comp+is_transaction_type+is_big_industry+is_customer=7 then 1=1 when a.role_code<>'RL2022042820140' and is_area+is_company+is_center+is_professional_comp+is_transaction_type+is_big_industry=6 then 1=1 when a.role_code in ('RL2022052725507','RL2022052725508','RL2022052725509') and is_area+is_company+is_center=3 then 1=1 when a.role_code in ('RL2022090946149','RL2022090946147') and is_company+is_center=2 then 1=1 when a.role_code in ('RL2022052725501','RL2022052725502','RL2022052725504') and is_center=1 then 1=1 when a.role_code='RL2022061729550' and is_area+is_company+is_professional_comp+is_transaction_type+is_big_industry=5 then 1=1 when a.role_code='RL2022031013212' and is_company+is_center+is_professional_comp+is_transaction_type+is_big_industry=5 then 1=1 when a.role_code='RL2022031013213' and is_company+is_center+is_transaction_type+is_big_industry=4 then 1=1 when a.role_code in ('RL2022111158820','RL2022111860264') and is_area+is_company+is_center+is_transaction_type+is_big_industry=5 then 1=1 when a.role_code in ('RL2022093050616','RL202306010842','RL202305180644') and is_area+is_company+is_transaction_type+is_big_industry=4 then 1=1 else 1=0 end)group by a.period_wid, a.user_code, (case when role_name like '责任制时效%' or role_name like '时长监控%' then '时长监控'when role_name like '责任制得分%' then '责任制得分'when role_name like '销售空仓率%' then '销售空仓率'when role_name like '送装专题%' then '送装专题'when role_name like '客户画像%' then '客户画像'when role_name='营销个人销售达成' then '个人销售达成'when role_name='付款额度' then '资金周期'when role_name='采购毛利率' then '采购毛利'else role_name end)
)b on a.period_wid=b.period_wid and a.user_code=b.user_code and b.index_name=a.active_index
where b.user_code is not null;
1.4、产品访问数据去重
INSERT OVERWRITE TABLE temp_lms.dm_lms_platform_app_visit_dist_tmpselectuser_mip,pro_name ,pro_code ,visit_menu ,visit_table ,is_visit ,visit_time ,from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as w_insert_dt fromtemp_lms.dm_lms_platform_app_visit_tmpwhere user_mip is not nullgroup by user_mip,pro_name ,pro_code ,visit_menu ,visit_table ,is_visit ,visit_time ;
1.5、产品访问数据明细临时表
insert overwrite table temp_lms.dwd_lms_app_visit_detail_di_tmpselectp.pro_name ,p.pro_code ,p.user_name ,p.user_mip ,p.org_name ,p.org_code ,p.shortorg_name ,p.org_catery ,p.position_name ,p.perm_time ,case when substr(p.perm_time,1,10)<=substr(f.part_dt,1,10) then '是' else '否' end as is_perm ,v.visit_menu ,v.visit_table ,case when v.visit_time is not null then '是' else '否' end as is_visit ,f.part_dt ,from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as w_insert_dt ,f.part_dt as period_wid from (select pro_name ,pro_code ,user_name ,user_mip ,org_name ,org_code ,shortorg_name ,org_catery ,position_name ,substr(perm_time,1,10) as perm_time ,is_perm ,period_wid fromtemp_lms.dm_lms_platform_usergroup_app_dist_tmpwhere pro_name <> '驾驶舱' and pro_code <> 'U-MCP') pjoin (select period_wid as part_dt from dm_lms.dm_bi_dashboard_business_kpi_dim_date_dimension where period_wid >='${START_DATE}' and period_wid < '${END_DATE}') f
on 1=1left join (selectuser_mip,pro_name ,pro_code ,visit_menu ,visit_table ,is_visit ,visit_time fromtemp_lms.dm_lms_platform_app_visit_dist_tmp) von f.part_dt=v.visit_timeand p.user_mip=v.user_mip and p.pro_code=v.pro_code and p.pro_name=v.pro_nameunion allselectp.pro_name ,p.pro_code ,p.user_name ,p.user_mip ,p.org_name ,p.org_code ,p.shortorg_name ,p.org_catery ,p.position_name ,p.perm_time ,'是' as is_perm ,v.visit_menu ,v.visit_table ,case when v.visit_time is not null then '是' else '否' end as is_visit ,v.visit_time ,from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as w_insert_dt ,p.period_wid as period_wid from (select pro_name ,pro_code ,user_name ,user_mip ,org_name ,org_code ,shortorg_name ,org_catery ,position_name ,substr(perm_time,1,10) as perm_time ,is_perm ,period_wid fromtemp_lms.dm_lms_platform_usergroup_app_dist_tmpwhere pro_name = '驾驶舱' and pro_code = 'U-MCP') pleft join (selectuser_mip,pro_name ,pro_code ,visit_menu ,visit_table ,is_visit ,visit_time fromtemp_lms.dm_lms_platform_app_visit_dist_tmp) von p.period_wid=v.visit_timeand p.user_mip=v.user_mip and p.pro_code=v.pro_code and p.pro_name=v.pro_name;
1.6、产品访问数据明细
insert overwrite table dwd_lms.dwd_lms_app_visit_detail_di_p partition(part_dt)select pro_name ,pro_code ,user_name ,user_mip ,org_name ,org_code ,shortorg_name ,org_catery ,position_name ,perm_time ,is_perm ,visit_menu ,visit_table ,is_visit ,visit_time ,period_wid ,from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as w_insert_dt ,substr(period_wid,1,7) as part_dt from temp_lms.dwd_lms_app_visit_detail_di_tmp aunion all select pro_name ,pro_code ,user_name ,user_mip ,org_name ,org_code ,shortorg_name ,org_catery ,position_name ,perm_time ,is_perm ,visit_menu ,visit_table ,is_visit ,visit_time ,period_wid ,w_insert_dt ,part_dt
from dwd_lms.dwd_lms_app_visit_detail_di_p b left anti join (select org_code,pro_code,substr(period_wid,1,7) as visit_timefrom temp_lms.dwd_lms_app_visit_detail_di_tmp group by org_code,pro_code,substr(period_wid,1,7)) c on b.pro_code=c.pro_codeand b.org_code =c.org_codeand b.part_dt=c.visit_time;
2、DWS产品访问数据
2.1、产品访问数据月去重
insert overwrite table temp_lms.dws_lms_app_visit_summary_month_tmpselect pro_name ,pro_code ,user_name ,user_mip ,org_name ,org_code ,shortorg_name ,org_catery ,position_name ,perm_time ,is_perm ,is_visit ,visit_time ,from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as w_insert_dt
from (select pro_name ,pro_code ,user_name ,user_mip ,org_name ,org_code ,shortorg_name ,org_catery ,position_name ,perm_time ,is_perm ,is_visit ,substr(period_wid,1,7) as visit_time ,row_number() over(partition by pro_name,pro_code,user_mip,org_name,org_code order by period_wid) rnfrom dwd_lms.dwd_lms_app_visit_detail_di_pwhere part_dt >= substr('${START_DATE}',1,7)and part_dt <= substr('${END_DATE}',1,7)
) v
where rn > 1;
2.2、日汇总产品访问
insert overwrite table temp_lms.dws_lms_app_visit_summary_tmpselect pro_name ,pro_code ,org_name ,org_code ,shortorg_name ,org_catery ,count(distinct case when is_perm ='是' then user_mip end) as perm_total ,count(distinct case when is_visit='是' then user_mip end) as visit_total ,visit_time as part_dt ,from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as w_insert_dt from temp_lms.dws_lms_app_visit_summary_month_tmpwhere is_perm ='是'group by pro_name,pro_code,org_name,org_code,shortorg_name,org_catery,visit_time;
2.3、合并数据,将数据回写到目标表
insert overwrite table dws_lms.dws_lms_app_visit_summary_di_pselect a.pro_name ,a.pro_code ,a.org_name ,a.org_code ,a.shortorg_name ,a.org_catery ,a.perm_total ,a.visit_total ,a.part_dt ,from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as w_insert_dt
from temp_lms.dws_lms_app_visit_summary_tmp a union all select b.pro_name ,b.pro_code ,b.org_name ,b.org_code ,b.shortorg_name ,b.org_catery ,b.perm_total ,b.visit_total ,b.part_dt ,b.w_insert_dt from dws_lms.dws_lms_app_visit_summary_di_p bleft anti join (select pro_code,part_dt,org_code from temp_lms.dws_lms_app_visit_summary_tmp group by pro_code,part_dt,org_code) c on b.part_dt=c.part_dtand b.pro_code=c.pro_codeand b.org_code =c.org_code;