产品访问分析-CSDN博客

本文链接：https://blog.csdn.net/docsz/article/details/140379266

1、DWD产品访问明细

1.1、用户产品权限数据

--用户产品权限数据

INSERT OVERWRITE TABLE temp_lms.dm_lms_platform_usergroup_app_tmp

select 
     '仓储司南'															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,'美的_美的集团_数字化创新业务_安得智联科技公司_区域经营中心_','')
          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,'美的_美的集团_数字化创新业务_安得智联科技公司_运营本部_',''),'_.*','')
          when c.org_name like '%_区域经营中心%'									then '区域经营中心'
          when c.org_name='美的_美的集团_数字化创新业务_安得智联科技公司'						then '安得'
          else '非安得'
          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='美的_美的集团_数字化创新业务_安得智联科技公司'						then '安得'
          else '非安得'
          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
	,''																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) a
	left join platform.yunbi_usergroup b 
	  on a.usergroup_id=b.usergroup_id
    left join platform.yunbi_user d 
	  on a.user_id=d.user_id
	left join lms.logistics_auth_user_info c 
	  on d.account_name =c.user_code 
      where b.usergroup_name like '%仓储司南%'

union all

select 
	 '万象台'					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,'美的_美的集团_数字化创新业务_安得智联科技公司_区域经营中心_','')
          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,'美的_美的集团_数字化创新业务_安得智联科技公司_运营本部_',''),'_.*','')
          when c.org_name like '%_区域经营中心%'									then '区域经营中心'
          when c.org_name='美的_美的集团_数字化创新业务_安得智联科技公司'						then '安得'
          else '非安得'
          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='美的_美的集团_数字化创新业务_安得智联科技公司'						then '安得'
          else '非安得'
          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
	,''																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) a
	left join platform.yunbi_usergroup b 
	  on a.usergroup_id=b.usergroup_id
    left join platform.yunbi_user d 
	  on a.user_id=d.user_id
	left 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,'美的_美的集团_数字化创新业务_安得智联科技公司_区域经营中心_','')
          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,'美的_美的集团_数字化创新业务_安得智联科技公司_运营本部_',''),'_.*','')
          when c.org_name like '%_区域经营中心%'								then '区域经营中心'
          when c.org_name='美的_美的集团_数字化创新业务_安得智联科技公司'					then '安得'
          else '非安得'
          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='美的_美的集团_数字化创新业务_安得智联科技公司'					then '安得'
          else '非安得'
          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
	,''																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) a
	left join platform.yunbi_usergroup b 
	  on a.usergroup_id=b.usergroup_id
	left join platform.yunbi_user d 
	  on a.user_id=d.user_id
	left join lms.logistics_auth_user_info c 
	  on a.user_id =c.user_code 
      where 
		b.usergroup_name in('智慧星-干线线路管理角色','智慧星-仓网规划看板角色','智慧星-城配线路分析角色','智慧星-管报单位收入成本管理角色','智慧星-管报成本管理角色','智慧星-管报收入管理角色','智慧星-管报经营管理角色','智慧星-外部干线管理角色')
	  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,'美的_美的集团_数字化创新业务_安得智联科技公司_区域经营中心_','')
		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,'美的_美的集团_数字化创新业务_安得智联科技公司_运营本部_',''),'_.*','')
		when c.org_name like '%_区域经营中心%'								then '区域经营中心'
		when c.org_name='美的_美的集团_数字化创新业务_安得智联科技公司'					then '安得'
		else '非安得'
		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='美的_美的集团_数字化创新业务_安得智联科技公司'					then '安得'
		else '非安得'
		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
	,''																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) a
	left join platform.yunbi_usergroup b 
	  on a.usergroup_id=b.usergroup_id
	left join platform.yunbi_user d 
	  on a.user_id=d.user_id
	left join lms.logistics_auth_user_info c 
	  on a.user_id =c.user_code
      where b.usergroup_name --角色范围
		in ('智慧星-干线线路管理角色','智慧星-仓网规划看板角色','智慧星-城配线路分析角色','智慧星-管报单位收入成本管理角色','智慧星-管报成本管理角色','智慧星-管报收入管理角色','智慧星-管报经营管理角色')	--20240801	ex_shenjc	修改

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,'美的_美的集团_数字化创新业务_安得智联科技公司_区域经营中心_','')
		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,'美的_美的集团_数字化创新业务_安得智联科技公司_运营本部_',''),'_.*','')
		when ui.org_name like '%_区域经营中心%'										then '区域经营中心'
		when ui.org_name='美的_美的集团_数字化创新业务_安得智联科技公司'							then '安得'
		else '非安得'
		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='美的_美的集团_数字化创新业务_安得智联科技公司'							then '安得'
		else '非安得'
		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
	,''																as period_wid           --统计日期
	
from  
	(select role_code
	   from lms.logistics_auth_role 
	   where delete_flag=0 and enable_flag=1 
	   and role_name =  '线长岗'	--202408001	ex_shenjc	修改
	   --in ( '线长维度-管理岗','线长规则维护') 
	   group by role_code) r
inner join  
	(select role_code 
	   from lms.logistics_auth_role_application 
       --APP编码
	   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=1
	   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_code

union 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,'美的_美的集团_数字化创新业务_安得智联科技公司_区域经营中心_','')
		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,'美的_美的集团_数字化创新业务_安得智联科技公司_运营本部_',''),'_.*','')
		when ui.org_name like '%_区域经营中心%'										then '区域经营中心'
		when ui.org_name='美的_美的集团_数字化创新业务_安得智联科技公司'							then '安得'
		else '非安得'
		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='美的_美的集团_数字化创新业务_安得智联科技公司'							then '安得'
		else '非安得'
		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
	,''																	as period_wid           --统计日期
	
from  
	(select role_code
	   from lms.logistics_auth_role
	   where 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 
       --APP编码
	   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_code

union all 

select
	 '驾驶舱'               	as pro_name             --产品名称
	,'U-MCP'                as pro_code             --产品编码
	,a.user_name            as user_name            --姓名
	,a.user_code            as user_mip             -- 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
    ,role_code
    ,role_name
	,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,'美的_美的集团_数字化创新业务_安得智联科技公司_区域经营中心_','')
		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,'美的_美的集团_数字化创新业务_安得智联科技公司_运营本部_',''),'_.*','')
		when department_name like '%_区域经营中心%'									then '区域经营中心'
		when department_name='美的_美的集团_数字化创新业务_安得智联科技公司'						then '安得'
		else '非安得'
		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='美的_美的集团_数字化创新业务_安得智联科技公司'						then '安得'
		else '非安得'
		end                  as org_catery
	,main_position           as position_name
	,valid_time              as valid_time
	,period_wid              as period_wid
    ,(case 
       when (is_area+is_company+is_center+is_professional_comp+is_transaction_type+is_big_industry)=6 and (period_wid>='2024-08-08' or role_code<>'RL2022042820140') then '有效用户' --具备6个维度数据权限且不是8月8日前的客户画像角色
       when role_code='RL2022042820140' and (is_area+is_company+is_center+is_professional_comp+is_transaction_type+is_big_industry+is_customer)=7                    then '有效用户' -- 2024-08-08前的客户画像角色需具备7个维度数据权限
       when role_code in ('RL2022052725507','RL2022052725508','RL2022052725509','RL202306080813') 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','RL202408060628') and is_center=1                                                    then '有效用户' -- 时长监控+客户画像_按客户管控：权限控制只有经营中心权限
       when role_code='RL2022061729550' and (is_area+is_company+is_professional_comp+is_big_industry)=4                                                              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 '有效用户' -- 既无经营中心维度又无专司维度：投标毛利偏差、合同续签、营销个人销售达成
       when role_code='RL202408060625'                                                                                                                               then '有效用户' -- 客户画像_细分行业负责人专用：无数据权限控制
       else '缺数据权限' end) as user_type
	from dm_lms.dm_mcp_valid_user_dtl
		where 1=1 
        and delete_flag=0
        and role_name not like '%_annto' -- 剔除自建用户
        and role_code<>'RL202112153873' --剔除掉IT-安得驾驶舱
        and role_code<>'RL2022123067721' --剔除掉付款额度
        and user_code not in ('qianglei1','liangpf','zhangyuan65','xuanjm1','wangpc18','sukr') -- 剔除IT用户
        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_tmp

select 
  	 pro_name                                         --产品名称
  	,pro_code                                         --产品编码
  	,user_name                                        --姓名
  	,user_mip                                         --MIP
  	,org_name                                         --部门名称
  	,org_code                                         --部门编码
  	,shortorg_name                                    --部门简称
  	,org_catery                                       --部门分类
  	,position_name                                    --岗位
  	,perm_time              						  --授权日期
  	,is_perm                                          --是否有权限
    ,from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss')   as w_insert_dt --数据插入时间
	,period_wid           							  --统计日期
from
(
select 
	     pro_name                                         --产品名称
  	    ,pro_code                                         --产品编码
  	    ,user_name                                        --姓名
  	    ,user_mip                                         --MIP
  	    ,org_name                                         --部门名称
  	    ,org_code                                         --部门编码
  	    ,shortorg_name                                    --部门简称
  	    ,org_catery                                       --部门分类
  	    ,position_name                                    --岗位
  	    ,perm_time              						  --授权日期
  	    ,is_perm                                          --是否有权限
	    ,period_wid           							  --统计日期
from 
	(select 
	     pro_name                                         --产品名称
  	    ,pro_code                                         --产品编码
  	    ,user_name                                        --姓名
  	    ,user_mip                                         --MIP
  	    ,org_name                                         --部门名称
  	    ,org_code                                         --部门编码
  	    ,shortorg_name                                    --部门简称
  	    ,org_catery                                       --部门分类
  	    ,position_name                                    --岗位
  	    ,perm_time              						  --授权日期
  	    ,is_perm                                          --是否有权限
	    ,period_wid           							  --统计日期
	    ,row_number() over(partition by pro_code,user_mip order by perm_time) rn 
	from temp_lms.dm_lms_platform_usergroup_app_tmp
	where pro_name<>'驾驶舱'
	) t
where t.rn =1

union all 

select 
	     pro_name                                         --产品名称
  	    ,pro_code                                         --产品编码
  	    ,user_name                                        --姓名
  	    ,user_mip                                         --MIP
  	    ,org_name                                         --部门名称
  	    ,org_code                                         --部门编码
  	    ,shortorg_name                                    --部门简称
  	    ,org_catery                                       --部门分类
  	    ,position_name                                    --岗位
  	    ,perm_time              						  --授权日期
  	    ,is_perm                                          --是否有权限
	    ,period_wid           							  --统计日期
	from temp_lms.dm_lms_platform_usergroup_app_tmp
	where pro_name='驾驶舱'
) a 
;

1.3、产品访问数据

--产品访问数据


INSERT OVERWRITE TABLE temp_lms.dm_lms_platform_app_visit_tmp

select
	 mip_code    							                 as user_mip      --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   --数据插入时间

from
	platform.ads_user_qbi_visit_log
where 1=1 
	and mip_code is not null
	and portal_name = '仓储司南'
	and workspace_name = '安得'
	--and star_dt >= '2024-01-01'
    and star_dt >= '${START_DATE}'
    and star_dt <  '${END_DATE}'

union all 

select
	 mip_code    							                 as user_mip      --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   --数据插入时间

from
	platform.ads_user_qbi_visit_log
where 1=1 
	and mip_code is not null
	and portal_name like '%万象台%'
	and workspace_name = '安得'
	--and star_dt >= '2024-01-01'
    and star_dt >= '${START_DATE}'
    and star_dt <  '${END_DATE}'

union all 

select
	 mip_code    							                 as user_mip      --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   --数据插入时间

from
	platform.ads_user_qbi_visit_log 
where 1=1 
	and mip_code is not null
	and portal_name = '智慧星'
	and workspace_name = '安得'
	and report_name in (
	'整体经营达成情况_整体分析'
	, '整体经营达成情况_分公司分析'
	, '整体经营达成情况_经营中心分析'
	, '整体经营达成情况_片区分析'
	, '采购毛利_分公司分析'
	, '采购毛利_经营中心分析'
	, '采购毛利率预算_经营中心'
	, '采购毛利率预算_专司'
	, '采购毛利率预算_行业'
	, '销售预测'
	, '智慧星'
	, '整体经营达成情况'
	, '经营中心利润明细'
	, '客户经营中心利润'
	, '采购毛利'
	, '月累计采购毛利波动分析'
	, '年累计采购毛利波动分析'
	, '毛利明细'
	, '客户毛利'
	, '采购毛利率预算'
	, '收入预算'
	, '收入成本明细')
	--and star_dt >= '2024-01-01'
    and star_dt >= '${START_DATE}'
    and star_dt <  '${END_DATE}'

union all 

select
	 mip_code    							                 as user_mip      --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   --数据插入时间

from
	platform.ads_user_qbi_visit_log 
where 1=1 
	and mip_code is not null
	and portal_name='智慧星'
	and workspace_name='安得'
	and report_name in ('管理报表','干线线路明细表','城配仓配线路汇总表','城配仓配线路明细表','路由分析表','内部全链路成本','距离矩阵成本')
	--and star_dt >= '2024-01-01'
    and star_dt >= '${START_DATE}'
    and star_dt <  '${END_DATE}'

union all 

select
	 username    							                  as user_mip      --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 --数据插入时间

from
	dm.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 >='2024-01-01'
    and part_dt >= '${START_DATE}'
    and part_dt <  '${END_DATE}'

union all 

select  
     username                                                  as user_mip      --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 >='2024-01-01'
    and part_dt >= '${START_DATE}'
    and part_dt <  '${END_DATE}'

union all 


select 

     a.user_code                                               as user_mip      --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='专题' then '客户画像' 
            when (ifnull(page_L4,'')='' or page_L4='页面加载') and page_L5 like '%收入%' then '收入'
            when (ifnull(page_L4,'')='' or page_L4='页面加载') and page_L5 like '%线路%' then '线路盈利分析'
            when (ifnull(page_L4,'')='' or page_L4='页面加载') and page_L5 like '%投标%' then '投标毛利偏差'
            when (ifnull(page_L4,'')='' or page_L4='页面加载') and page_L5 like '%签单%' then '商机全流程'
            when (ifnull(page_L4,'')='' or page_L4='页面加载') and page_L5 like '资金%' then '资金周期'
            when (ifnull(page_L4,'')='' or page_L4='页面加载') and page_L5 like '%逾期应收%' then '逾期应收'
            when (ifnull(page_L4,'')='' or page_L4='页面加载') and page_L5 like '%逾期%' then '逾期订单'
            when (ifnull(page_L4,'')='' or page_L4='页面加载') and page_L5 like '%空仓%' then '销售空仓率'
            when (ifnull(page_L4,'')='' or page_L4='页面加载') and page_L5 like '%毛利%' then '经营中心利润'
            when ifnull(page_L4,'')<>'' and page_L4<>'页面加载' and ifnull(page_L5,'')<>'' then page_L4
            when ifnull(page_L4,'')<>'' or ifnull(page_L5,'')<>'' then page_L3
            else '指标汇总' end) as active_index
    from dm_lms.dm_mcp_log_trck_bsc_byte_dtl_di
    where 1=1 
      --and event_name<>'app_launch'	--20240815	ex_shenjc	修改
      and period_wid >= '${START_DATE}'
      and period_wid <  '${END_DATE}'
    group by 
	    period_wid,
        user_code,
		(case 
		   when page_L3='驾驶舱-送装' then '送装专题'
           when page_L3='专题' then '客户画像' 
           when (ifnull(page_L4,'')='' or page_L4='页面加载') and page_L5 like '%收入%' then '收入'
           when (ifnull(page_L4,'')='' or page_L4='页面加载') and page_L5 like '%线路%' then '线路盈利分析'
           when (ifnull(page_L4,'')='' or page_L4='页面加载') and page_L5 like '%投标%' then '投标毛利偏差'
           when (ifnull(page_L4,'')='' or page_L4='页面加载') and page_L5 like '%签单%' then '商机全流程'
           when (ifnull(page_L4,'')='' or page_L4='页面加载') and page_L5 like '资金%' then '资金周期'
           when (ifnull(page_L4,'')='' or page_L4='页面加载') and page_L5 like '%逾期应收%' then '逾期应收'
           when (ifnull(page_L4,'')='' or page_L4='页面加载') and page_L5 like '%逾期%' then '逾期订单'
           when (ifnull(page_L4,'')='' or page_L4='页面加载') and page_L5 like '%空仓%' then '销售空仓率'
           when (ifnull(page_L4,'')='' or page_L4='页面加载') and page_L5 like '%毛利%' then '经营中心利润'
           when ifnull(page_L4,'')<>'' and page_L4<>'页面加载' and ifnull(page_L5,'')<>'' then page_L4
           when ifnull(page_L4,'')<>'' or ifnull(page_L5,'')<>'' then page_L3
           else '指标汇总' end)
) a 
left join 
(select 
		distinct t.period_wid,t.user_code
	from(select 
			period_wid,
			user_code,	
			role_code,
			role_name,
			valid_time, 
			(case 
				when (is_area+is_company+is_center+is_professional_comp+is_transaction_type+is_big_industry)=6 and (period_wid>='2024-08-08' or role_code<>'RL2022042820140') then '有效用户' --具备6个维度数据权限且不是8月8日前的客户画像角色
				when role_code='RL2022042820140' and (is_area+is_company+is_center+is_professional_comp+is_transaction_type+is_big_industry+is_customer)=7                    then '有效用户' -- 2024-08-08前的客户画像角色需具备7个维度数据权限
				when role_code in ('RL2022052725507','RL2022052725508','RL2022052725509','RL202306080813') 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','RL202408060628') and is_center=1                                                    then '有效用户' -- 时长监控+客户画像_按客户管控：权限控制只有经营中心权限
				when role_code='RL2022061729550' and (is_area+is_company+is_professional_comp+is_big_industry)=4                                                              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 '有效用户' -- 既无经营中心维度又无专司维度：投标毛利偏差、合同续签、营销个人销售达成
				when role_code='RL202408060625'                                                                                                                               then '有效用户' -- 客户画像_细分行业负责人专用：无数据权限控制
				else '缺数据权限' end) as user_type
		from dm_lms.dm_mcp_valid_user_dtl 
		where 1=1 
			and delete_flag=0
			and role_name not like '%_annto' -- 剔除自建用户
			and role_code<>'RL202112153873' --剔除掉IT-安得驾驶舱
			-- and role_code<>'RL2022123067721' --剔除掉付款额度
			and user_code not in ('qianglei1','liangpf','zhangyuan65','xuanjm1','wangpc18','sukr') -- 剔除IT用户
			and period_wid<substr(to_date(current_timestamp()),1,10)
	)t where user_type='有效用户'
)b 
on a.period_wid=b.period_wid 
   and a.user_code=b.user_code 
   --20240815	ex_shenjc	修改
   --and b.index_name=a.active_index
where b.user_code is not null

;
;

1.4、产品访问数据明细

--产品访问数据明细

insert overwrite table temp_lms.dwd_lms_app_visit_detail_di_tmp

select
		 p.pro_name                                                                                       --产品名称
		,p.pro_code                                                                                       --产品编码
		,p.user_name                                                                                      --姓名
		,p.user_mip                                                                                       --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                                         --MIP
		,org_name                                         --部门名称
		,org_code                                         --部门编码
		,shortorg_name                                    --部门简称
		,org_catery                                       --部门分类
		,position_name                                    --岗位
		,perm_time              						  --授权日期
		,is_perm                                          --是否有权限
		,period_wid           							  --统计日期
        
    from
		temp_lms.dm_lms_platform_usergroup_app_dist_tmp
    where pro_name <> '驾驶舱' and pro_code <> 'U-MCP'
	) p

join 
    (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=1

left join 
	(select
		 user_mip
        ,pro_name                --产品名称
        ,pro_code                --产品编码
		,visit_menu              --访问菜单
		,visit_table             --访问报表
		,is_visit                --是否有访问
		,visit_time              --访问日期
    from
		temp_lms.dm_lms_platform_app_visit_dist_tmp
	where visit_time >='${START_DATE}' 
	  and visit_time <'${END_DATE}'
	) v

on  
        f.part_dt=v.visit_time
    and p.user_mip=v.user_mip 
    and p.pro_code=v.pro_code 
    and p.pro_name=v.pro_name


union all

select
		 p.pro_name                                                                                       --产品名称
		,p.pro_code                                                                                       --产品编码
		,p.user_name                                                                                      --姓名
		,p.user_mip                                                                                       --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                             --是否有访问
	    ,f.part_dt																						  --访问日期   
 	    ,from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss')   as w_insert_dt                          --数据插入时间
		,coalesce(p.period_wid,f.part_dt) 						  as period_wid							  --统计日期

from 	
	
	(select 
		 pro_name                                         --产品名称
		,pro_code                                         --产品编码
		,user_name                                        --姓名
		,user_mip                                         --MIP
		,org_name                                         --部门名称
		,org_code                                         --部门编码
		,shortorg_name                                    --部门简称
		,org_catery                                       --部门分类
		,position_name                                    --岗位
		,perm_time              						  --授权日期
		,is_perm                                          --是否有权限
		,period_wid           							  --统计日期
        
    from

	  temp_lms.dm_lms_platform_usergroup_app_dist_tmp
	where pro_name = '驾驶舱' and pro_code = 'U-MCP'
	  and period_wid >='${START_DATE}'
	  and period_wid < '${END_DATE}'
) p

full outer join  
    (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 p.period_wid=f.part_dt

left join 
	(select
		 user_mip
        ,pro_name                --产品名称
        ,pro_code                --产品编码
		,visit_menu              --访问菜单
		,visit_table             --访问报表
		,is_visit                --是否有访问
		,visit_time              --访问日期
    from
		temp_lms.dm_lms_platform_app_visit_dist_tmp
	where visit_time >='${START_DATE}' 
      and visit_time < '${END_DATE}'
	) v

on  
        f.part_dt=v.visit_time
    and p.user_mip=v.user_mip 
    and p.pro_code=v.pro_code 
    and p.pro_name=v.pro_name

;

1.5、产品访问数据明细汇总

--产品访问数据明细

insert overwrite table dwd_lms.dwd_lms_app_visit_detail_di

select 
     a.pro_name                                       --产品名称
    ,a.pro_code                                       --产品编码
    ,a.user_name                                      --姓名
    ,a.user_mip                                       --MIP
    ,a.org_name                                       --部门名称
    ,a.org_code                                       --部门编码
    ,a.shortorg_name                                  --部门简称
    ,a.org_catery                                     --部门分类
    ,a.position_name                                  --岗位
    ,a.perm_time                                      --授权日期
    ,a.is_perm                                        --是否有权限
	,a.visit_menu                                     --访问菜单
	,a.visit_table                                    --访问报表
    ,a.is_visit                                       --是否有访问
    ,a.visit_time                                     --访问日期
	,a.period_wid           						  --统计日期
	,from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss')                as w_insert_dt     --数据更新时间

from temp_lms.dwd_lms_app_visit_detail_di_tmp a
where a.pro_name is not null

union all 

select 
     b.pro_name                                       --产品名称
    ,b.pro_code                                       --产品编码
    ,b.user_name                                      --姓名
    ,b.user_mip                                       --MIP
    ,b.org_name                                       --部门名称
    ,b.org_code                                       --部门编码
    ,b.shortorg_name                                  --部门简称
    ,b.org_catery                                     --部门分类
    ,b.position_name                                  --岗位
    ,b.perm_time                                      --授权日期
    ,b.is_perm                                        --是否有权限
	,b.visit_menu                                     --访问菜单
	,b.visit_table                                    --访问报表
    ,b.is_visit                                       --是否有访问
    ,b.visit_time                                     --访问日期
	,b.period_wid           						  --统计日期
	,b.w_insert_dt									  --数据更新时间
from 
	dwd_lms.dwd_lms_app_visit_detail_di b 
left anti join 
(select period_wid from temp_lms.dwd_lms_app_visit_detail_di_tmp group by period_wid) c 
on b.period_wid=c.period_wid
--(select substr(period_wid,1,7) as period_wid,pro_code from temp_lms.dwd_lms_app_visit_detail_di_tmp 
--group by substr(period_wid,1,7),pro_code) c 
--on  substr(b.period_wid,1,7)=c.period_wid
--and b.pro_code=c.pro_code

;

2、DWS产品访问数据

2.1、日汇总产品访问

--月汇总产品权限和访问量指标计算

insert overwrite table temp_lms.dws_lms_app_visit_summary_tmp

select 
	   a.pro_name --产品名称
      ,a.pro_code --产品编码
      ,a.org_name --部门名称
      ,a.org_code --部门编码
      ,a.shortorg_name --部门简称
      ,a.org_catery -- 部门分类
      ,count(distinct(case when a.is_perm_cnt = 1  then a.user_mip else null end)) as perm_total --权限人数
      ,count(distinct(case when a.is_visit_cnt = 1 then a.user_mip else null end)) as visit_total --访问人数
      ,a.visit_time as part_dt --日期(月)
      ,from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as w_insert_dt --数据更新时间
from 
(
      select * 
		from(
			select 
				substr(period_wid,1,7) as visit_time 
                ,pro_name
                ,pro_code
                ,user_name
                ,user_mip  
                ,org_name
                ,org_code
                ,shortorg_name
                ,org_catery
                ,max(case when is_perm ='是'   then 1 when is_visit = '是' and is_perm ='否' then 1 else 0 end) over(partition by pro_code,user_mip) as is_perm_cnt    
                ,max(case when is_visit = '是' then 1 else 0 end) over(partition by pro_code,user_mip) as is_visit_cnt
                ,row_number() over(partition by pro_code,user_mip,substr(period_wid,1,7) order by period_wid desc) as rn
			from 
				dwd_lms.dwd_lms_app_visit_detail_di 
			where substr(period_wid,1,7)=substr('${START_DATE}',1,7) 
			--and pro_name='仓储司南'
		)a where a.rn = 1
	) a 
    
--判断本月有权限的用户最后一天是否有权限（权限失效）
left join (
			select 
				substr(visit_time,1,7) as visit_time
               ,pro_code
               ,user_mip
			from (
				select 
					 period_wid as visit_time 
					,pro_name
					,pro_code
					,user_name
					,user_mip
					,is_perm
					,max(period_wid) over(partition by pro_code,user_mip) as mip_max_visit_time
					,max(period_wid) over(partition by pro_code) as pro_max_visit_time
				from 
					dwd_lms.dwd_lms_app_visit_detail_di
				where substr(period_wid,1,7)=substr('${START_DATE}',1,7)
				) a 
			where 
					visit_time = mip_max_visit_time 
				and mip_max_visit_time < pro_max_visit_time
			--and pro_name='仓储司南' 
			group by 
				 substr(visit_time,1,7)
				,pro_code
				,user_mip
		) b 
on 		b.visit_time = a.visit_time 
	and b.pro_code = a.pro_code 
	and b.user_mip = a.user_mip

where 
	b.user_mip is null 
	or (b.user_mip is not null and a.is_visit_cnt = 1) -- 修改：剔除当月权限失效且当月无访问的用户的数据
group by a.pro_name
        ,a.pro_code
        ,a.org_name
        ,a.org_code
        ,a.shortorg_name
        ,a.org_catery
        ,a.visit_time

;

2.2、合并数据，将数据回写到目标表

--合并数据，将数据回写到目标表

insert overwrite table dws_lms.dws_lms_app_visit_summary_di

select 
   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 b
left anti join (select part_dt 
				from 
					temp_lms.dws_lms_app_visit_summary_tmp 
				group by part_dt) c 
on b.part_dt=c.part_dt

;