mysql 判断是否为子集的方法步骤

一、问题

故事起源于一个查询错漏率的报表:有两个查询结果,分别是报告已经添加的项目和报告应该添加的项目,求报告无遗漏率

何为无遗漏?即,应该添加的项目已经被全部添加

报告无遗漏率也就是无遗漏报告数占报告总数的比率

这里以两个报告示例(分别是已全部添加和有遗漏的报告)

首先,查出第一个结果——报告应该添加的项目

select 
     r.id as 报告id,m.project_id 应添加项目
from 
  report r 
  inner join application a on r.app_id=a.id
  inner join application_sample s on a.id=s.app_id
  right join application_sample_item si on s.id=si.sample_id       
  right join set_project_mapping m on si.set_id=m.set_id
where r.id in ('44930','44927')
order by r.id,m.project_id;

然后,再查出第二个结果——报告已经添加的项目

select r.id as 报告id,i.project_id as 已添加项目 
from report r 
right join report_item i on r.id=i.report_id
where r.id in ('44930','44927');

以上就是我们要比较的结果集,不难看出报告44927是无遗漏的,而44930虽然项目数量一致,但实际是多添加了项目758,缺少了项目112,是有遗漏的报告

二、解决方案

从问题看,显然是一个判断是否为子集的问题。可以分别遍历已添加的项目和应该添加的项目,如果应该添加的项目在已添加的项目中都能匹配上,即代表应该添加的项目是已添加的项目子集,也就是无遗漏。

通过循环遍历比较确实可以解决这个问题,但是sql中出现笛卡儿积的交叉连接往往意味着开销巨大,查询速度慢,那么有没有办法避免这一问题呢?

方案一:

借助于函数 find_in_set和group_concat, 首先认识下两个函数

find_in_set(str,strlist)

  • str: 需要查询的字符串
  • strlist: 参数以英文”,”分隔,如 (1,2,6,8,10,22)

find_in_set 函数返回了需要查询的字符串在目标字符串的位置

group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc  ] [separator ‘分隔符’] )

group_concat()函数可以将多条记录的同一字段的值,拼接成一条记录返回。默认以英文‘,’分割

但是,group_concat()默认长度为1024

所以,如果需要拼接的长度超过1024将会导致截取不全,需要修改长度

set global group_concat_max_len=102400;
set session group_concat_max_len=102400;

 从上述两个函数介绍中,我们发现find_in_set和group_concat都以英文‘,’分割(加粗标识)

所以,我们可以用group_concat将已添加项目的项目连接为一个字符串,然后再用find_in_set逐一查询应添加项目是否都存在于字符串

1、修改问题中描述中的sql,用group_concat将已添加项目的项目连接为一个字符串

select r.id,group_concat(i.project_id order by i.project_id,'') as 已添加项目列表 
from report r 
left join report_item i on r.id=i.report_id
where r.id in ('44930','44927')
group by r.id;

2、用find_in_set逐一查询应添加项目是否都存在于字符串

select q.id,find_in_set(w.应添加项目列表,q.已添加项目列表) as 是否遗漏
   from 
   (
   -- 报告已经添加的项目 
      select r.id,group_concat(i.project_id order by i.project_id,'') as 已添加项目列表 
      from report r 
      left join report_item i on r.id=i.report_id
      where r.id in ('44930','44927')
      group by r.id
   )q,
   (
   -- 报告应该添加的项目 
      select 
         r.id,s.app_id,m.project_id 应添加项目列表
      from 
         report r 
         inner join application a on r.app_id=a.id
         inner join application_sample s on a.id=s.app_id
         inner join application_sample_item si on s.id=si.sample_id       
         inner join set_project_mapping m on si.set_id=m.set_id
      where r.id in ('44930','44927')
      order by r.id,m.project_id
   )w
   where q.id=w.id;

3、过滤掉有遗漏的报告

 select q.id,case when find_in_set(w.应添加项目列表,q.已添加项目列表)>0 then 1 else 0 end as 是否遗漏
   from 
   (
   -- 报告已经添加的项目 
      select r.id,group_concat(i.project_id order by i.project_id,'') as 已添加项目列表 
      from report r 
      left join report_item i on r.id=i.report_id
      where r.id in ('44930','44927')
      group by r.id
   )q,
   (
   -- 报告应该添加的项目 
      select 
         r.id,s.app_id,m.project_id 应添加项目列表
      from 
         report r 
         inner join application a on r.app_id=a.id
         inner join application_sample s on a.id=s.app_id
         inner join application_sample_item si on s.id=si.sample_id       
         inner join set_project_mapping m on si.set_id=m.set_id
      where r.id in ('44930','44927')
      order by r.id,m.project_id
   )w
   where q.id=w.id
   group by q.id
   having count(`是否遗漏`)=sum(`是否遗漏`);

4、我们的最终目标是求无遗漏率

 select count(x.id) 无遗漏报告数,y.total 报告总数, concat(format(count(x.id)/y.total*100,2),'%') as 项目无遗漏率 from 
(
  select q.id,case when find_in_set(w.应添加项目列表,q.已添加项目列表)>0 then 1 else 0 end as 是否遗漏
   from 
   (
   -- 报告已经添加的项目 
      select r.id,group_concat(i.project_id order by i.project_id,'') as 已添加项目列表 
      from report r 
      left join report_item i on r.id=i.report_id
      where r.id in ('44930','44927')
      group by r.id
   )q,
   (
   -- 报告应该添加的项目 
      select 
         r.id,s.app_id,m.project_id 应添加项目列表
       from 
         report r 
         inner join application a on r.app_id=a.id
         inner join application_sample s on a.id=s.app_id
         inner join application_sample_item si on s.id=si.sample_id       
         inner join set_project_mapping m on si.set_id=m.set_id
       where r.id in ('44930','44927')
    order by r.id,m.project_id
   )w
   where q.id=w.id
   group by q.id
   having count(`是否遗漏`)=sum(`是否遗漏`)
 )x,
 (
    -- 总报告数
    select count(e.nums) as total from
    (
      select count(r.id) as nums from report r 
      where r.id in ('44930','44927')
      group by r.id
    )e    
 )y 
 ;

方案二:

上述方案一虽然避免了逐行遍历对比,但本质上还是对项目的逐一对比,那么有没有什么方式可以不用对比呢?

答案当然是有的。我们可以根据统计数量判断是否完全包含。

1、使用union all 将已添加项目与应添加项目联表,不去重

 (
 -- 应该添加的项目
select 
  r.id,m.project_id
from 
   report r 
inner join application a on r.app_id=a.id
inner join application_sample s on a.id=s.app_id
inner join application_sample_item si on s.id=si.sample_id       
inner join set_project_mapping m on si.set_id=m.set_id
where r.id in ('44930','44927')
order by r.id,m.project_id
)
union all
(
 -- 已经添加的项目
select r.id,i.project_id from report r,report_item i 
where r.id = i.report_id and r.id in ('44930','44927')
group by r.app_id,i.project_id
 )

从结果可以看出,项目同一个报告下有重复的项目,分别代表了应该添加和已经添加的项目

2、根据联表结果,统计报告重合的项目数量

# 应该添加与已经添加的项目重叠数量
select tt.id,count(*) count from 
(
   select t.id,t.project_id,count(*) from 
   (
      (
        -- 应该添加的项目
        select 
          r.id,m.project_id
        from 
          report r 
          inner join application a on r.app_id=a.id
          inner join application_sample s on a.id=s.app_id
          inner join application_sample_item si on s.id=si.sample_id       
          inner join set_project_mapping m on si.set_id=m.set_id
        where r.id in ('44930','44927')
        order by r.id,m.project_id
      )
      union all
      (
        -- 已经添加的项目
        select r.id,i.project_id from report r,report_item i 
        where r.id = i.report_id and r.id in ('44930','44927')
        group by r.app_id,i.project_id
      )
      
   ) t
   group by t.id,t.project_id
   having count(*) >1 
) tt group by tt.id 

3、将第二步的数量与应该添加的数量作比较,如果相等,则代表无遗漏

select bb.id,aa.count 已添加,bb.count 需添加,
    case when aa.count/bb.count=1 then 1
    else 0
    end as '是否遗漏' 
from 
(
# 应该添加与已经添加的项目重叠数量
select tt.id,count(*) count from 
(
   select t.id,t.project_id,count(*) from 
   (
      (
        -- 应该添加的项目
        select 
          r.id,m.project_id
        from 
          report r 
          inner join application a on r.app_id=a.id
          inner join application_sample s on a.id=s.app_id
          inner join application_sample_item si on s.id=si.sample_id       
          inner join set_project_mapping m on si.set_id=m.set_id
        where r.id in ('44930','44927')
        order by r.id,m.project_id
      )
      union all
      (
        -- 已经添加的项目
        select r.id,i.project_id from report r,report_item i 
        where r.id = i.report_id and r.id in ('44930','44927')
        group by r.app_id,i.project_id
      )
      
   ) t
   group by t.id,t.project_id
   having count(*) >1 
) tt group by tt.id 
) aa right join
(
  -- 应该添加的项目数量
  select 
    r.id,s.app_id,count(m.project_id) count
  from 
    report r 
    inner join application a on r.app_id=a.id
    inner join application_sample s on a.id=s.app_id
    inner join application_sample_item si on s.id=si.sample_id       
    inner join set_project_mapping m on si.set_id=m.set_id
  where r.id in ('44930','44927')
  group by r.id
  order by r.id,m.project_id
) bb on aa.id = bb.id 
order by aa.id

4、求出无遗漏率

select 
    sum(asr.`是否遗漏`) as 无遗漏数,count(asr.id) as 总数,concat(format(sum(asr.`是否遗漏`)/count(asr.id)*100,5),'%') as 报告无遗漏率
from 
(
  select bb.id,aa.count 已添加,bb.count 需添加,
      case when aa.count/bb.count=1 then 1
      else 0
      end as '是否遗漏' 
  from 
  (
  # 应该添加与已经添加的项目重叠数量
  select tt.id,count(*) count from 
  (
     select t.id,t.project_id,count(*) from 
     (
        (
          -- 应该添加的项目
          select 
            r.id,m.project_id
          from 
            report r 
            inner join application a on r.app_id=a.id
            inner join application_sample s on a.id=s.app_id
            inner join application_sample_item si on s.id=si.sample_id       
            inner join set_project_mapping m on si.set_id=m.set_id
          where r.id in ('44930','44927')
          order by r.id,m.project_id
        )
        union all
        (
          -- 已经添加的项目
          select r.id,i.project_id from report r,report_item i 
          where r.id = i.report_id and r.id in ('44930','44927')
          group by r.app_id,i.project_id
        )
        
     ) t
     group by t.id,t.project_id
     having count(*) >1 
  ) tt group by tt.id 
  ) aa right join
  (
    -- 应该添加的项目数量
    select 
      r.id,s.app_id,count(m.project_id) count
    from 
      report r 
      inner join application a on r.app_id=a.id
      inner join application_sample s on a.id=s.app_id
      inner join application_sample_item si on s.id=si.sample_id       
      inner join set_project_mapping m on si.set_id=m.set_id
    where r.id in ('44930','44927')
    group by r.id
    order by r.id,m.project_id
  ) bb on aa.id = bb.id 
  order by aa.id
) asr;

到此这篇关于mysql 判断是否为子集的方法步骤的文章就介绍到这了,更多相关mysql 判断是否子集内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com!

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐