图1：
图2：
图3：
问题：Doris-sql语句加过滤条件判断不能准确出结果。
具体情况：
图1：图1是没加pk_group 条件查询的全量数据 ，里面是有符合条件的pk_group='-1' 的数据。
图2：图1的原有的查询基础加查询条件and pk_group='-1'会显示empty set 。
图3：把pk_group 替换其中一个具体值如图3 结果是这个对应的和-1对应的数据。
目前其他类似sql语句也会出现该现象。这些语句在问题出现前正常可执行，条件 pk_group=’-1’正常可筛选，目前推测是doris集群方面问题。
Doris相关信息：
Doris 版本：doris-1.2.1-rc01-Unknown
Doris 集群：be-3节点 fe-2节点
Doris 语法使用：CTE-with语法、grouping sets
Doris 源表：复合主键的unique model
需要的帮助：
如何排查并解决该问题。
doris源表建表语句：
-- 源表建表语句
create table if not exists DWM.`dwm_psndoc_practice_detail` (
`person_no_sub` varchar(20) null comment 'xxxx主键',
`partition_yearmon` char(7) NOT NULL DEFAULT "9999-99" COMMENT '月度周期快照标识',
`practice_name` varchar(128) null comment 'xxxx名称',
`practice_grade_pk` varchar(128) null comment 'xxxx等级主键',
`practice_grade_name` varchar(128) null comment 'xxxx等级名称',
`practice_grade_code` varchar(128) null comment 'xxxx等级编码',
`glbdef3` varchar(128) null comment 'glbdef3主键',
`glbdef3_name` varchar(128) null comment 'glbdef3名称',
`glbdef3_code` varchar(128) null comment 'glbdef3编码',
`glbdef4` varchar(128) null comment 'glbdef4主键',
`glbdef4_name` varchar(128) null comment 'glbdef4名称',
`glbdef4_code` varchar(128) null comment 'glbdef4编码',
`glbdef5` varchar(128) null comment 'glbdef5主键',
`glbdef5_name` varchar(128) null comment 'glbdef5名称',
`glbdef5_code` varchar(128) null comment 'glbdef5编码',
`last_flag` varchar(10) null comment '是否最新记录',
`person_no` varchar(20) null comment 'xxxx主键',
`recordnum` decimal(27, 9) null comment '记录序号',
`glbdef6` varchar(128) null comment 'glbdef6主键',
`glbdef6_name` varchar(128) null comment 'glbdef6名称',
`glbdef6_code` varchar(128) null comment 'glbdef6编码',
`glbdef7` varchar(128) null comment 'glbdef7主键',
`glbdef7_name` varchar(128) null comment 'glbdef7名称',
`glbdef7_code` varchar(128) null comment 'glbdef7编码',
`glbdef8` varchar(128) null comment 'glbdef8主键',
`glbdef8_name` varchar(128) null comment 'glbdef8名称',
`glbdef8_code` varchar(128) null comment 'glbdef8编码',
`rn` int null comment '记录排序',
`pk_org` varchar(255) NULL COMMENT 'xxxx主键',
`org_code` varchar(255) NULL COMMENT 'xxxx编码',
`org_name` varchar(255) NULL COMMENT 'xxxx名称',
`org_shortname` varchar(255) NULL COMMENT 'xxxx名称缩写',
`org_pk_fatherorg` varchar(255) NULL COMMENT 'xxxx父类主键',
`org_father_code` varchar(255) NULL COMMENT 'xxxx父类编码',
`org_father_name` varchar(255) NULL COMMENT 'xxxx父类名称',
`pk_province` varchar(255) NULL COMMENT 'xxxx主键',
`province_code` varchar(255) NULL COMMENT 'xxxx编码',
`province_name` varchar(255) NULL COMMENT 'xxxx名称',
`pk_group` varchar(255) NULL COMMENT 'xxxxxxxx主键',
`group_code` varchar(255) NULL COMMENT '所属xxxxxxxx编码',
`group_name` varchar(255) NULL COMMENT '所属xxxxxxxx名称',
`group_no` int(11) NULL COMMENT 'xxxx序号',
`org_type2` varchar(255) NULL COMMENT 'xxxx单位标识',
`org_type2_name` varchar(255) NULL COMMENT 'xxxx单位标识名称',
`pk_corp` varchar(50) NULL COMMENT '所属xxxx主键',
`corp_code` varchar(255) NULL COMMENT '所属xxxxxxxx编码',
`corp_name` varchar(255) NULL COMMENT '所属xxxxxxxx名称',
`industry_code` varchar(255) NULL COMMENT '行业类别编码',
`industry_name` varchar(255) NULL COMMENT '行业类别名称',
`is_power_service` varchar(20) NULL COMMENT '是否zzzz',
`work_farm_flag` varchar(255) NULL DEFAULT "~" COMMENT 'xxxx标识',
`work_use_type` varchar(255) NULL DEFAULT "~" COMMENT 'xxxx类型',
`work_use_name` varchar(255) NULL DEFAULT "~" COMMENT 'xxxx类型名称',
`pk_post` varchar(255) NULL DEFAULT "~" COMMENT 'xxxx主键',
`post_code` varchar(255) NULL DEFAULT "~" COMMENT 'xxxx编码',
`post_name` varchar(255) NULL DEFAULT "~" COMMENT 'xxxx名称',
`pk_post_series` varchar(255) NULL DEFAULT "~" COMMENT 'xxxx序列主键',
`post_series_code` varchar(255) NULL DEFAULT "~" COMMENT 'xxxx序列名称',
`post_series_name` varchar(255) NULL DEFAULT "~" COMMENT 'xxxx序列名称',
`pk_father_post_series` varchar(255) NULL DEFAULT "~" COMMENT 'xxxx序列父级编码',
`post_series_father_code` varchar(255) NULL DEFAULT "~" COMMENT 'xxxx序列父级编码',
`post_series_father_name` varchar(255) NULL DEFAULT "~" COMMENT 'xxxx序列父级名称',
`etl_create_time` datetime default '0000-01-01 00:00:00' COMMENT '时间创建时间'
) engine=olap
unique key(person_no_sub,partition_yearmon)
comment 'dwmxxxxxxxx明细数据'
distributed by hash(person_no_sub,partition_yearmon) buckets 10
properties (
"replication_allocation" = "tag.location.default: 3",
"in_memory" = "false",
"storage_format" = "v2",
"disable_auto_compaction" = "false"
);
sql加工执行过程
-- 筛选的sql语句
select * from (
-- 生成xxxx等级排序字段，用于筛选最高等级xxxxxxxx
with map_practice_grade_rn as (
select
dppda1.*
,case
when dppda1.work_use_type='yyyyyyyy6' and (dppda1.work_farm_flag='N' or dppda1.work_farm_flag is null ) then 'xxxx0'
when dppda1.work_use_type='yyyyyyyy6' and dppda1.work_farm_flag='Y' then 'xxxx1'
when dppda1.work_use_type='yyyyyyyy3' then 'xxxx2'
when dppda1.work_use_type='yyyyyyyy5' then 'xxxx3'
when dppda1.work_use_type='yyyyyyyy9' then 'xxxx4'
when dppda1.work_use_type='yyyyyyyy7' then 'xxxx5'
when dppda1.work_use_type='yyyyyyyy8' then 'xxxx6'when dppda1.work_use_type is null then '-1' else '' end as work_use_name_tmp
-- 'xxxx类型名称'
,cast(ifnull(replace(dppda1.glbdef8_code,'DJ',''),999) as integer) as grade_rn
from DWM.`dwm_psndoc_practice_detail` dppda1
where dppda1.glbdef6_code like 'ZY01%'
),
-- 筛选xxxx最高等级xxxxxxxx
gen_practice_grade_rn as (
select
mpgra2.*
,row_number() over(partition by mpgra2.person_no,mpgra2.glbdef6_name order by mpgra2.grade_rn asc ) as record_rn
from map_practice_grade_rn mpgra2
),
-- 筛选xxxx最高等级xxxxxxxx
filter_practice_grade_high as (
select
gpgra3.*
from gen_practice_grade_rn gpgra3
where record_rn =1
),
-- 计算xxxx最高等级xxxxxxxx分布，只计算xxxx种类，不区分xxxx种类下的等级
cacl_practice_grade_high as (
select
ifnull(fpgha4.pk_province,'-1') as pk_province , -- xxxx主键
ifnull(fpgha4.province_code ,'-1') as province_code , -- xxxx编码
ifnull(fpgha4.province_name ,'-1') as province_name, -- xxxx名称
ifnull(fpgha4.partition_yearmon,'-1') as partition_yearmon,
ifnull(fpgha4.pk_group,'-1') as pk_group,
ifnull(fpgha4.group_code,'-1') as group_code,
ifnull(fpgha4.group_name,'-1') as group_name,
ifnull(fpgha4.group_no,'-1') as group_no,
ifnull(fpgha4.pk_corp,'-1') as pk_corp,
ifnull(fpgha4.corp_code,'-1') as corp_code,
ifnull(fpgha4.corp_name,'-1') as corp_name,
ifnull(fpgha4.org_pk_fatherorg ,'-1') as org_pk_fatherorg,
ifnull(fpgha4.org_father_code ,'-1') as org_father_code ,
ifnull(fpgha4.org_father_name ,'-1') as org_father_name,
ifnull(fpgha4.pk_org,'-1') as pk_org,
ifnull(fpgha4.org_code,'-1') as org_code,
ifnull(fpgha4.org_name,'-1') as org_name,
ifnull(fpgha4.org_type2,'-1') as org_type2,
ifnull(fpgha4.org_type2_name,'-1') as org_type2_name,
ifnull(fpgha4.is_power_service,'-1') as is_power_service,
ifnull(fpgha4.work_use_type ,'-1') as work_use_type,
ifnull(fpgha4.work_use_name_tmp ,'-1') as work_use_name,
fpgha4.glbdef6 ,
fpgha4.glbdef6_name ,
fpgha4.glbdef6_code ,
count(1) as certificate_cnt -- 数据总量
from filter_practice_grade_high fpgha4
group by
grouping sets(
(
-- xxxx级-xxxx类型-xxxx类别聚合
fpgha4.pk_province,
fpgha4.province_code,
fpgha4.province_name,
fpgha4.partition_yearmon,
fpgha4.pk_group,
fpgha4.group_code,
fpgha4.group_name,
fpgha4.group_no,
fpgha4.pk_corp,
fpgha4.corp_code,
fpgha4.corp_name,
fpgha4.org_pk_fatherorg,
fpgha4.org_father_code,
fpgha4.org_father_name,
fpgha4.pk_org,
fpgha4.org_code,
fpgha4.org_name,
fpgha4.org_type2,
fpgha4.org_type2_name,
fpgha4.is_power_service,
fpgha4.work_use_type,
fpgha4.work_use_name_tmp,
fpgha4.glbdef6,
fpgha4.glbdef6_name,
fpgha4.glbdef6_code),
(
-- xxxx父级-xxxx类型-xxxx类别聚合
fpgha4.pk_province,
fpgha4.province_code,
fpgha4.province_name,
fpgha4.partition_yearmon,
fpgha4.pk_group,
fpgha4.group_code,
fpgha4.group_name,
fpgha4.group_no,
fpgha4.pk_corp,
fpgha4.corp_code,
fpgha4.corp_name,
fpgha4.org_pk_fatherorg,
fpgha4.org_father_code,
fpgha4.org_father_name,
fpgha4.org_type2,
fpgha4.org_type2_name,
fpgha4.is_power_service,
fpgha4.work_use_type,
fpgha4.work_use_name_tmp,
fpgha4.glbdef6,
fpgha4.glbdef6_name,
fpgha4.glbdef6_code),
(
-- xxxxxxxx级-xxxx类型-xxxx类别聚合
fpgha4.pk_province,
fpgha4.province_code,
fpgha4.province_name,
fpgha4.partition_yearmon,
fpgha4.pk_group,
fpgha4.group_code,
fpgha4.group_name,
fpgha4.group_no,
fpgha4.pk_corp,
fpgha4.corp_code,
fpgha4.corp_name,
fpgha4.org_type2,
fpgha4.org_type2_name,
fpgha4.is_power_service,
fpgha4.work_use_type,
fpgha4.work_use_name_tmp,
fpgha4.glbdef6,
fpgha4.glbdef6_name,
fpgha4.glbdef6_code),
(
-- xxxx级-xxxx类型-xxxx类别聚合
fpgha4.pk_province,
fpgha4.province_code,
fpgha4.province_name,
fpgha4.partition_yearmon,
fpgha4.pk_group,
fpgha4.group_code,
fpgha4.group_name,
fpgha4.group_no,
fpgha4.org_type2,
fpgha4.org_type2_name,
fpgha4.is_power_service,
fpgha4.work_use_type,
fpgha4.work_use_name_tmp,
fpgha4.glbdef6,
fpgha4.glbdef6_name,
fpgha4.glbdef6_code),
(
-- xxxx级-xxxx类型-xxxx类别聚合
fpgha4.pk_province,
fpgha4.province_code,
fpgha4.province_name,
fpgha4.partition_yearmon,
fpgha4.org_type2,
fpgha4.org_type2_name,
fpgha4.is_power_service,
fpgha4.work_use_type,
fpgha4.work_use_name_tmp,
fpgha4.glbdef6,
fpgha4.glbdef6_name,
fpgha4.glbdef6_code),
(
-- xxxx级-xxxx类型-xxxx类别聚合-不区分xxxx
fpgha4.pk_province,
fpgha4.province_code,
fpgha4.province_name,
fpgha4.partition_yearmon,
fpgha4.pk_group,
fpgha4.group_code,
fpgha4.group_name,
fpgha4.group_no,
fpgha4.pk_corp,
fpgha4.corp_code,
fpgha4.corp_name,
fpgha4.org_pk_fatherorg,
fpgha4.org_father_code,
fpgha4.org_father_name,
fpgha4.pk_org,
fpgha4.org_code,
fpgha4.org_name,
fpgha4.is_power_service,
fpgha4.work_use_type,
fpgha4.work_use_name_tmp,
fpgha4.glbdef6,
fpgha4.glbdef6_name,
fpgha4.glbdef6_code),
(
-- xxxx父级-xxxx类型-xxxx类别聚合-不区分xxxx
fpgha4.pk_province,
fpgha4.province_code,
fpgha4.province_name,
fpgha4.partition_yearmon,
fpgha4.pk_group,
fpgha4.group_code,
fpgha4.group_name,
fpgha4.group_no,
fpgha4.pk_corp,
fpgha4.corp_code,
fpgha4.corp_name,
fpgha4.org_pk_fatherorg,
fpgha4.org_father_code,
fpgha4.org_father_name,
fpgha4.is_power_service,
fpgha4.work_use_type,
fpgha4.work_use_name_tmp,
fpgha4.glbdef6,
fpgha4.glbdef6_name,
fpgha4.glbdef6_code),
(
-- xxxxxxxx级-xxxx类型-xxxx类别聚合-不区分xxxx
fpgha4.pk_province,
fpgha4.province_code,
fpgha4.province_name,
fpgha4.partition_yearmon,
fpgha4.pk_group,
fpgha4.group_code,
fpgha4.group_name,
fpgha4.group_no,
fpgha4.pk_corp,
fpgha4.corp_code,
fpgha4.corp_name,
fpgha4.is_power_service,
fpgha4.work_use_type,
fpgha4.work_use_name_tmp,
fpgha4.glbdef6,
fpgha4.glbdef6_name,
fpgha4.glbdef6_code),
(
-- xxxx级-xxxx类型-xxxx类别聚合-不区分xxxx
fpgha4.pk_province,
fpgha4.province_code,
fpgha4.province_name,
fpgha4.partition_yearmon,
fpgha4.pk_group,
fpgha4.group_code,
fpgha4.group_name,
fpgha4.group_no,
fpgha4.is_power_service,
fpgha4.work_use_type,
fpgha4.work_use_name_tmp,
fpgha4.glbdef6,
fpgha4.glbdef6_name,
fpgha4.glbdef6_code),
(
-- xxxx级-xxxx类型-xxxx类别聚合-不区分xxxx
fpgha4.pk_province,
fpgha4.province_code,
fpgha4.province_name,
fpgha4.partition_yearmon,
fpgha4.is_power_service,
fpgha4.work_use_type,
fpgha4.work_use_name_tmp,
fpgha4.glbdef6,
fpgha4.glbdef6_name,
fpgha4.glbdef6_code),
(
-- xxxx级-只区分xxxxxxxx
fpgha4.pk_province,
fpgha4.province_code,
fpgha4.province_name,
fpgha4.partition_yearmon,
fpgha4.pk_group,
fpgha4.group_code,
fpgha4.group_name,
fpgha4.group_no,
fpgha4.pk_corp,
fpgha4.corp_code,
fpgha4.corp_name,
fpgha4.org_pk_fatherorg,
fpgha4.org_father_code,
fpgha4.org_father_name,
fpgha4.pk_org,
fpgha4.org_code,
fpgha4.org_name,
fpgha4.org_type2,
fpgha4.org_type2_name,
fpgha4.glbdef6,
fpgha4.glbdef6_name,
fpgha4.glbdef6_code),
(
-- xxxx父级-只区分xxxxxxxx
fpgha4.pk_province,
fpgha4.province_code,
fpgha4.province_name,
fpgha4.partition_yearmon,
fpgha4.pk_group,
fpgha4.group_code,
fpgha4.group_name,
fpgha4.group_no,
fpgha4.pk_corp,
fpgha4.corp_code,
fpgha4.corp_name,
fpgha4.org_pk_fatherorg,
fpgha4.org_father_code,
fpgha4.org_father_name,
fpgha4.org_type2,
fpgha4.org_type2_name,
fpgha4.glbdef6,
fpgha4.glbdef6_name,
fpgha4.glbdef6_code),
(
-- xxxxxxxx级-只区分xxxxxxxx
fpgha4.pk_province,
fpgha4.province_code,
fpgha4.province_name,
fpgha4.partition_yearmon,
fpgha4.pk_group,
fpgha4.group_code,
fpgha4.group_name,
fpgha4.group_no,
fpgha4.pk_corp,
fpgha4.corp_code,
fpgha4.corp_name,
fpgha4.org_type2,
fpgha4.org_type2_name,
fpgha4.glbdef6,
fpgha4.glbdef6_name,
fpgha4.glbdef6_code),
(
-- xxxx级-只区分xxxxxxxx
fpgha4.pk_province,
fpgha4.province_code,
fpgha4.province_name,
fpgha4.partition_yearmon,
fpgha4.pk_group,
fpgha4.group_code,
fpgha4.group_name,
fpgha4.group_no,
fpgha4.org_type2,
fpgha4.org_type2_name,
fpgha4.glbdef6,
fpgha4.glbdef6_name,
fpgha4.glbdef6_code),
(
-- xxxx级-只区分xxxxxxxx
fpgha4.pk_province,
fpgha4.province_code,
fpgha4.province_name,
fpgha4.partition_yearmon,
fpgha4.org_type2,
fpgha4.org_type2_name,
fpgha4.glbdef6,
fpgha4.glbdef6_name,
fpgha4.glbdef6_code),
(
-- xxxx级-只区分zzzz
fpgha4.pk_province,
fpgha4.province_code,
fpgha4.province_name,
fpgha4.partition_yearmon,
fpgha4.pk_group,
fpgha4.group_code,
fpgha4.group_name,
fpgha4.group_no,
fpgha4.pk_corp,
fpgha4.corp_code,
fpgha4.corp_name,
fpgha4.org_pk_fatherorg,
fpgha4.org_father_code,
fpgha4.org_father_name,
fpgha4.pk_org,
fpgha4.org_code,
fpgha4.org_name,
fpgha4.is_power_service,
fpgha4.glbdef6,
fpgha4.glbdef6_name,
fpgha4.glbdef6_code),
(
-- xxxx父级-只区分zzzz
fpgha4.pk_province,
fpgha4.province_code,
fpgha4.province_name,
fpgha4.partition_yearmon,
fpgha4.pk_group,
fpgha4.group_code,
fpgha4.group_name,
fpgha4.group_no,
fpgha4.pk_corp,
fpgha4.corp_code,
fpgha4.corp_name,
fpgha4.org_pk_fatherorg,
fpgha4.org_father_code,
fpgha4.org_father_name,
fpgha4.is_power_service,
fpgha4.glbdef6,
fpgha4.glbdef6_name,
fpgha4.glbdef6_code),
(
-- xxxxxxxx级-只区分zzzz
fpgha4.pk_province,
fpgha4.province_code,
fpgha4.province_name,
fpgha4.partition_yearmon,
fpgha4.pk_group,
fpgha4.group_code,
fpgha4.group_name,
fpgha4.group_no,
fpgha4.pk_corp,
fpgha4.corp_code,
fpgha4.corp_name,
fpgha4.is_power_service,
fpgha4.glbdef6,
fpgha4.glbdef6_name,
fpgha4.glbdef6_code),
(
-- xxxx级-只区分zzzz
fpgha4.pk_province,
fpgha4.province_code,
fpgha4.province_name,
fpgha4.partition_yearmon,
fpgha4.pk_group,
fpgha4.group_code,
fpgha4.group_name,
fpgha4.group_no,
fpgha4.is_power_service,
fpgha4.glbdef6,
fpgha4.glbdef6_name,
fpgha4.glbdef6_code),
(
-- xxxx级-只区分zzzz
fpgha4.pk_province,
fpgha4.province_code,
fpgha4.province_name,
fpgha4.partition_yearmon,
fpgha4.is_power_service,
fpgha4.glbdef6,
fpgha4.glbdef6_name,
fpgha4.glbdef6_code
),
(
-- xxxx级-只区分xxxx类型
fpgha4.pk_province,
fpgha4.province_code,
fpgha4.province_name,
fpgha4.partition_yearmon,
fpgha4.pk_group,
fpgha4.group_code,
fpgha4.group_name,
fpgha4.group_no,
fpgha4.pk_corp,
fpgha4.corp_code,
fpgha4.corp_name,
fpgha4.org_pk_fatherorg,
fpgha4.org_father_code,
fpgha4.org_father_name,
fpgha4.pk_org,
fpgha4.org_code,
fpgha4.org_name,
fpgha4.work_use_type,
fpgha4.work_use_name_tmp,
fpgha4.glbdef6,
fpgha4.glbdef6_name,
fpgha4.glbdef6_code),
(
-- xxxx父级-只区分xxxx类型
fpgha4.pk_province,
fpgha4.province_code,
fpgha4.province_name,
fpgha4.partition_yearmon,
fpgha4.pk_group,
fpgha4.group_code,
fpgha4.group_name,
fpgha4.group_no,
fpgha4.pk_corp,
fpgha4.corp_code,
fpgha4.corp_name,
fpgha4.org_pk_fatherorg,
fpgha4.org_father_code,
fpgha4.org_father_name,
fpgha4.work_use_type,
fpgha4.work_use_name_tmp,
fpgha4.glbdef6,
fpgha4.glbdef6_name,
fpgha4.glbdef6_code),
(
-- xxxxxxxx级-只区分xxxx类型
fpgha4.pk_province,
fpgha4.province_code,
fpgha4.province_name,
fpgha4.partition_yearmon,
fpgha4.pk_group,
fpgha4.group_code,
fpgha4.group_name,
fpgha4.group_no,
fpgha4.pk_corp,
fpgha4.corp_code,
fpgha4.corp_name,
fpgha4.work_use_type,
fpgha4.work_use_name_tmp,
fpgha4.glbdef6,
fpgha4.glbdef6_name,
fpgha4.glbdef6_code),
(
-- xxxx级-只区分xxxx类型
fpgha4.pk_province,
fpgha4.province_code,
fpgha4.province_name,
fpgha4.partition_yearmon,
fpgha4.pk_group,
fpgha4.group_code,
fpgha4.group_name,
fpgha4.group_no,
fpgha4.work_use_type,
fpgha4.work_use_name_tmp,
fpgha4.glbdef6,
fpgha4.glbdef6_name,
fpgha4.glbdef6_code),
(
-- xxxx级-只区分xxxx类型
fpgha4.pk_province,
fpgha4.province_code,
fpgha4.province_name,
fpgha4.partition_yearmon,
fpgha4.work_use_type,
fpgha4.work_use_name_tmp,
fpgha4.glbdef6,
fpgha4.glbdef6_name,
fpgha4.glbdef6_code),
(
-- xxxx级-只区分xxxx层级，不区分xxxx类型、xxxxxxxx、zzzz
fpgha4.pk_province,
fpgha4.province_code,
fpgha4.province_name,
fpgha4.partition_yearmon,
fpgha4.pk_group,
fpgha4.group_code,
fpgha4.group_name,
fpgha4.group_no,
fpgha4.pk_corp,
fpgha4.corp_code,
fpgha4.corp_name,
fpgha4.org_pk_fatherorg,
fpgha4.org_father_code,
fpgha4.org_father_name,
fpgha4.pk_org,
fpgha4.org_code,
fpgha4.org_name ,
fpgha4.glbdef6,
fpgha4.glbdef6_name,
fpgha4.glbdef6_code),
(
-- xxxx父级-只区分xxxx层级，不区分xxxx类型、xxxxxxxx、zzzz
fpgha4.pk_province,
fpgha4.province_code,
fpgha4.province_name,
fpgha4.partition_yearmon,
fpgha4.pk_group,
fpgha4.group_code,
fpgha4.group_name,
fpgha4.group_no,
fpgha4.pk_corp,
fpgha4.corp_code,
fpgha4.corp_name,
fpgha4.org_pk_fatherorg,
fpgha4.org_father_code,
fpgha4.org_father_name,
fpgha4.glbdef6,
fpgha4.glbdef6_name,
fpgha4.glbdef6_code),
(
-- xxxxxxxx级-只区分xxxx层级，不区分xxxx类型、xxxxxxxx、zzzz
fpgha4.pk_province,
fpgha4.province_code,
fpgha4.province_name,
fpgha4.partition_yearmon,
fpgha4.pk_group,
fpgha4.group_code,
fpgha4.group_name,
fpgha4.group_no,
fpgha4.pk_corp,
fpgha4.corp_code,
fpgha4.corp_name,
fpgha4.glbdef6,
fpgha4.glbdef6_name,
fpgha4.glbdef6_code),
(
-- xxxx级-只区分xxxx层级，不区分xxxx类型、xxxxxxxx、zzzz
fpgha4.pk_province,
fpgha4.province_code,
fpgha4.province_name,
fpgha4.partition_yearmon,
fpgha4.pk_group,
fpgha4.group_code,
fpgha4.group_name,
fpgha4.group_no,
fpgha4.glbdef6,
fpgha4.glbdef6_name,
fpgha4.glbdef6_code),
(
-- xxxx级-只区分xxxx层级，不区分xxxx类型、xxxxxxxx、zzzz
fpgha4.pk_province,
fpgha4.province_code,
fpgha4.province_name,
fpgha4.partition_yearmon,
fpgha4.glbdef6,
fpgha4.glbdef6_name,
fpgha4.glbdef6_code)
)
),
-- 计算xxxx最高等级xxxxxxxxtopn分布
cacl_practice_grade_high_topn as (
select
cpgha5.*
,row_number() over(
partition by
cpgha5.pk_province,
cpgha5.province_code,
cpgha5.province_name,
cpgha5.partition_yearmon,
cpgha5.pk_group,
cpgha5.group_code,
cpgha5.group_name,
cpgha5.group_no,
cpgha5.pk_corp,
cpgha5.corp_code,
cpgha5.corp_name,
cpgha5.org_pk_fatherorg,
cpgha5.org_father_code,
cpgha5.org_father_name,
cpgha5.pk_org,
cpgha5.org_code,
cpgha5.org_name,
cpgha5.org_type2,
cpgha5.org_type2_name,
cpgha5.is_power_service,
cpgha5.work_use_type,
cpgha5.work_use_name
order by cpgha5.certificate_cnt desc ) as group_rn
from cacl_practice_grade_high cpgha5
),
get_practice_grade_high_top6_null as (
select
cpghta6.pk_province,
cpghta6.province_code,
cpghta6.province_name,
cpghta6.partition_yearmon,
cpghta6.pk_group,
cpghta6.group_code,
cpghta6.group_name,
cpghta6.group_no,
cpghta6.pk_corp,
cpghta6.corp_code,
cpghta6.corp_name,
cpghta6.org_pk_fatherorg,
cpghta6.org_father_code,
cpghta6.org_father_name,
cpghta6.pk_org,
cpghta6.org_code,
cpghta6.org_name,
cpghta6.org_type2,
cpghta6.org_type2_name,
cpghta6.is_power_service,
cpghta6.work_use_type,
cpghta6.work_use_name,
cpghta6.glbdef6,
cpghta6.glbdef6_name,
cpghta6.glbdef6_code,
cpghta6.certificate_cnt, -- 数据总量
8 as group_rn
from cacl_practice_grade_high_topn cpghta6
where 1=1
and cpghta6.glbdef6 is null
),
get_practice_grade_high_top6_front as (
select cpghta6.*
from cacl_practice_grade_high_topn cpghta6
where 1=1
and cpghta6.group_rn < 7
and cpghta6.glbdef6 is not null
),
get_practice_grade_high_top6_back as (
select
cpghta6.pk_province,
cpghta6.province_code,
cpghta6.province_name,
cpghta6.partition_yearmon,
cpghta6.pk_group,
cpghta6.group_code,
cpghta6.group_name,
cpghta6.group_no,
cpghta6.pk_corp,
cpghta6.corp_code,
cpghta6.corp_name,
cpghta6.org_pk_fatherorg,
cpghta6.org_father_code,
cpghta6.org_father_name,
cpghta6.pk_org,
cpghta6.org_code,
cpghta6.org_name,
cpghta6.org_type2,
cpghta6.org_type2_name,
cpghta6.is_power_service,
cpghta6.work_use_type,
cpghta6.work_use_name,
'other' as glbdef6,
'其他' as glbdef6_name,
'-1' as glbdef6_code,
sum(certificate_cnt) as certificate_cnt, -- 数据总量
7 as group_rn
from cacl_practice_grade_high_topn cpghta6
where 1= 1
and cpghta6.group_rn >= 7
and cpghta6.glbdef6 is not null
group by
cpghta6.pk_province,
cpghta6.province_code,
cpghta6.province_name,
cpghta6.partition_yearmon,
cpghta6.pk_group,
cpghta6.group_code,
cpghta6.group_name,
cpghta6.group_no,
cpghta6.pk_corp,
cpghta6.corp_code,
cpghta6.corp_name,
cpghta6.org_pk_fatherorg,
cpghta6.org_father_code,
cpghta6.org_father_name,
cpghta6.pk_org,
cpghta6.org_code,
cpghta6.org_name,
cpghta6.org_type2,
cpghta6.org_type2_name,
cpghta6.is_power_service,
cpghta6.work_use_type,
cpghta6.work_use_name
),
-- 结果集合并
union_practice_grade_high_topn as
(
select gpghtna7.*
from get_practice_grade_high_top6_null gpghtna7
union all
select gpghtfa7.*
from get_practice_grade_high_top6_front gpghtfa7
union all
select gpghtba7.*
from get_practice_grade_high_top6_back gpghtba7
)
select
length(pk_group),
upghta8.pk_province,
upghta8.partition_yearmon,
upghta8.pk_group,
upghta8.group_code,
upghta8.pk_corp,
upghta8.glbdef6_name,
upghta8.certificate_cnt,
now() as etl_create_time -- '时间创建时间'
--from union_practice_grade_high_topn upghta8
from cacl_practice_grade_high upghta8
)all_result
where
pk_province = 'yyyyyyyy4'
and partition_yearmon='2024-09'
and pk_group='-1';
