Prototype Rectification for Few-Shot Learning

创新奇智CTO张发恩团队研发的少样本学习方法,通过减小类内偏差和跨类偏差,显著提升了少样本分类的准确性。此方法在miniImageNet和tieredImageNet数据集上取得了最优结果,论文被ECCV2020接收为Oral。

有关少样本学习(Few-shot Learning)的研究论文《Prototype Rectification for Few-Shot Learning》被全球计算机视觉顶会ECCV 2020接收为Oral论文(入选率仅2%)。

ECCV全称为European Conference on Computer Vision(欧洲计算机视觉国际会议),与ICCV和CVPR合称为全球计算机视觉三大顶级会议,每两年举办一次。据大会官方介绍,本届会议共收到5025份有效投稿,共接收1361篇,录取率为27%,其中1361篇接收论文里面,有104篇Oral以及161篇 Spotlight,分别占比2%和5%,堪称史上最难ECCV。

创新奇智CTO张发恩表示:“当前的深度学习技术对数据具有极大依赖性,如何减小数据依赖,利用较少的数据取得理想的识别效果成为当下亟待突破的技术难点。少样本学习旨在从已有类别的数据中学习先验知识,然后利用极少的标注数据完成对新类别的识别,打破了样本数据量的制约,在传统制造业等样本普遍缺失的领域具有实用价值,有助于推动AI落地。”

创新奇智本次发表的论文指出,少样本学习的瓶颈在于数据稀缺引起的偏差,主要包括类内偏差和跨类偏差,并提出相应方法有针对性地减小两项偏差,该思路经过严格的理论证明其合理性,并通过大量实验证明了方法的有效性,在少样本学习通用的数据集中达到了最优的结果。

以下为论文解读:

 

(论文初版arXiv地址为:https://arxiv.org/abs/1911.10713 。 后续Camera Ready版本将于近日更新,补充了更多数据集上的表现。

1、概述

少样本学习(Few-shot learning)旨在从大量有标注数据的类别中学习到普遍的规律,利用学习到的知识,能够使用少量的有标注数据(如一张或五张)完成对新类别的识别。原型网络是少样本学习中一类非常有效的方法,其针对不同的类别提取出对应的类原型,然后根据样本与类原型之间的距离进行分类。由于新类别的样本数量极少,原型网络所计算出的类原型存在一定的偏差。本文指出了制约原型网络效果的两个关键因素:类内偏差和跨类偏差,并且提出利用伪标签的方法减小类内偏差,利用特征偏移的方法减小跨类偏差,进一步通过理论分析指出原型网络准确率的理论下界,证明伪标签的方法可以提高理论下界从而提高整体准确率,最后,实验结果表明,本文的方法在miniImageNet和tieredImageNet上达到了最高水平的结果。

主要贡献

本文指出了原型网络在少样本学习中的两项制约因素:类内偏差(intra-class bias)及跨类偏差(cross-class bias)。本文利用伪标签和特征偏移,进行原型校正和减小偏差,简单有效地提高了少样本分类的表现。本文分析了理论下界与样本数量之间的关系,从而验证了所提方法的合理性,并给出了偏移量的推导过程。本文所提出的方法在通用的少样本数据集miniImageNet和tieredImageNet上达到了最优的结果。

2、方法

 

2.1 基于余弦相似度的原型网络(CSPN)

本文利用基于余弦相似度的原型网络(Cosine Similarity Based Prototypical Network,CSPN)得到少样本类别(few-shot class)的基础类原型。

首先在基础类别(base class)上训练特征提取器和余弦分类器,余弦分类器定义如下:

 

其中Fθ是特征提取器,W 为可学习权重,τ为温度参数。在模型训练阶段使用如下损失函数:

 

预训练结束后,使用下式得到few-shot class的基础类原型:

 

2.2 原型校正中的偏差消除(Bias Diminishing for Prototype Rectification)

在样本较少的情况下,比如K=1或K=5,计算所得基础类原型与理想的类原型之间存在一定偏差,减小偏差可以提高类原型的表征能力,从而提高分类准确率,本文指出如下两种偏差以及对应的减小偏差的方法。

类内偏差(intra-class bias)

 

即真实类原型(第一项)和使用少量样本计算的类原型(第二项)之间的偏差。真实的类原型往往是不可得的,可得的是利用少量有标注的数据集(support set)计算得到的类原型,为了减小这两项之间的偏差,本文提出利用无标注的数据集(query set)重新计算类原型。首先通过计算query set中的样本与基础类原型之间的余弦相似度获得query sample的伪标签,然后将top-z confident的query sample加入support set中,并根据下式重新计算,得到修正后的类原型P'n:

 

跨类偏差(cross-class bias)

 

即整个有标注数据集support set和无标注数据集query set之间的偏差。为了修正跨类偏差,本文在无标注数据中加入偏移量ξ, ξ的具体计算方式如下:

2.3 理论推导Theoretical Analysis

本文通过以下理论推导,解释了上述方法的合理性以及该方法是如何提高少样本分类表现的。

理论下界

在原型网络中,假设最终的准确率与类原型和样本间余弦相似度之间呈正相关,即最终优化目标可以表示为:

 

再结合使用一阶近似、柯西施瓦茨不等式等方法进行推导验证,可以得到模型的理论下界:

 

可以看出模型最终效果和样本数之间呈正相关,因此可以通过引入无标签样本来提高模型理论下界,即增大T 可以提高模型表现。

有关偏移量ξ的推导过程详见论文,在这里就不敷述了。

3、实验

3.1实验结果

本文在少样本学习的两个公开数据集 (miniImageNet, tieredImageNet) 上进行了实验,与其他方法相比,本文提出的BD-CSPN在1-shot及5-shot的设置下均达到了最佳效果。

 

3.2消融实验

本文通过消融实验进一步验证了模型每一部分的有效性。

表中从上到下分别为:不使用原型修正的方法,单独使用跨类偏差修正,单独使用类内偏差修正以及同时使用类内偏差和跨类偏差修正的结果,可以看到减小偏差对最终结果有明显的提升。

 

本文进一步分析了原型校正中无标注样本的样本数量对于最终结果的影响,如图2(a-b)所示,随着无标注样本的增多最终效果有持续的提升,图2(c)中虚线为通过计算所得到的理论下界,可以看到本文的实验结果和理论相符。

 

本文采用不同的网络结构作为特征提取器,验证了原型校正的方法在不同网络结构下均有明显提升。

总结

本文提出一种简单有效的少样本学习方法,通过减小类内偏差和跨类偏差进行原型校正,从而显著提高少样本分类结果,并且给出理论推导证明本文所提方法可以提高理论下界,最终通过实验表明本方法在通用数据集中达到了最优结果,论文被ECCV 2020 接收为Oral。本文中提出的算法已经在创新奇智的实际场景中落地应用,可以从海量非结构化数据中根据极少数种子数据(1张至5张)挖掘出所需要的同类数据,可以极大的提升数据收集速度和准确率,降低成本。

数据表 OA-PATROL-INFO 字段 ID TITLE SIGNDATE EXT1 数据表 OA-PATROL-DEPT-INFO 字段 ID PATROLINFOID BEINSPECTEDID 数据表 OA-PATROL-RECTIFICATION-INFO 字段 ID PATROLINFOID BEINSPECTEDID LINKNAME EXT1 EXT2 EXT3 EXT4 EXT5 EXT6 EXT7 EXT8 EXT9 EXT10 EXT11 EXT12 EXT13 EXT14 EXT15 EXT16 EXT17 EXT18 EXT19 写一条sql语句查询OA-PATROL-INFO左连接OA-PATROL-DEPT-INFO(连接条件:OA-PATROL-INFO.ID = OA-PATROL-DEPT-INFO.PATROLINFOID)左连接OA-PATROL-RECTIFICATION-INFO(连接条件:OA-PATROL-DEPT-INFO.BEINSPECTEDID = OA-PATROL-RECTIFICATION-INFO.BEINSPECTEDID)中OA-PATROL-RECTIFICATION-INFO.LINKNAME = '4' 时OA-PATROL-RECTIFICATION-INFO.BEINSPECTEDID的数量、OA-PATROL-RECTIFICATION-INFO.EXT3的数量、OA-PATROL-RECTIFICATION-INFO.EXT4的数量、OA-PATROL-RECTIFICATION-INFO.EXT14 = '已完成' 的数量、OA-PATROL-RECTIFICATION-INFO.EXT14 = '未完成' 的数量、OA-PATROL-RECTIFICATION-INFO.EXT7的求和、OA-PATROL-RECTIFICATION-INFO.EXT8的求和、OA-PATROL-RECTIFICATION-INFO.EXT9的求和,OA-PATROL-RECTIFICATION-INFO.LINKNAME = '6' 时(相同的OA-PATROL-RECTIFICATION-INFO.BEINSPECTEDID和OA-PATROL-RECTIFICATION-INFO.PATROLINFOID取EXT17转化为数字后最大的一组)OA-PATROL-RECTIFICATION-INFO.EXT14全部为'已完成'的OA-PATROL-RECTIFICATION-INFO.BEINSPECTEDID数量 、OA-PATROL-RECTIFICATION-INFO.EXT14存在'未完成'的OA-PATROL-RECTIFICATION-INFO.BEINSPECTEDID数量、OA-PATROL-RECTIFICATION-INFO.EXT14存在'未完成'的OA-PATROL-RECTIFICATION-INFO.EXT3的数量、OA-PATROL-RECTIFICATION-INFO.EXT14存在'未完成'的OA-PATROL-RECTIFICATION-INFO.BEINSPECTEDID占全部的比例、OA-PATROL-RECTIFICATION-INFO.EXT14存在'未完成'的OA-PATROL-RECTIFICATION-INFO.EXT3占全部的比例
最新发布
06-05
<think>我们面对一个复杂的多表联合查询需求,需要分步骤构建SQL语句。根据描述,我们需要连接三个表,并根据不同条件进行统计和计算。由于需求包含多个条件分支,我们可以考虑使用多个子查询或条件聚合(CASEWHEN)来实现。步骤:1.表连接:先左连接OA-PATROL-INFO(记为A)和OA-PATROL-DEPT-INFO(记为B)条件为A.ID=B.PATROLINFOID;然后将结果再左连接OA-PATROL-RECTIFICATION-INFO(记为C)条件为B.BEINSPECTEDID=C.BEINSPECTEDID。2.对于LINKNAME='4'的情况:-统计BEINSPECTEDID、EXT3、EXT4的数量(注意:这里可能是去重统计?需求没有明确,但根据后续操作,可能是指去重计数)-统计EXT14为'已完成'和'未完成'的数量(这里应该是按行统计?还是按BEINSPECTEDID去重?需求没有明确,但根据上下文,可能是按每个BEINSPECTEDID的状态行数)-对EXT7、EXT8、EXT9分别求和(同样,按行求和?)3.对于LINKNAME='6'的情况,需要做更复杂的处理:-先筛选出LINKNAME='6'的记录,然后对于同一个BEINSPECTEDID和PATROLINFOID,只取EXT17最大的一组记录(即每组保留一条,使用窗口函数或子查询)。-然后,对这样的记录进行统计:a.EXT14全部为'已完成'的BEINSPECTEDID数量(注意:这里按BEINSPECTEDID去重计数,并且要求该BEINSPECTEDID对应的所有记录中EXT14都是'已完成')b.EXT14存在'未完成'的BEINSPECTEDID数量(同样按BEINSPECTEDID去重计数,并且只要有一条是'未完成'就算)c.EXT3的数量(可能是去重数量,也可能是所有记录数?这里根据需求,应该是统计该组记录中EXT3的数量?但描述不够清晰,我们暂时理解为每个BEINSPECTEDID对应的一条记录(取EXT17最大的那一条)的EXT3,然后进行计数。但后面又说计算占总数的比例,这个总数是指什么?需求描述中说是占总数的比例,总数可能是所有BEINSPECTEDID的数量?)-然后计算比例:注意,这里要求分别计算存在'未完成'的BEINSPECTEDID数量占总BEINSPECTEDID数量(即满足LINKNAME='6'并且按条件分组后的BEINSPECTEDID总数)的比例,以及EXT3的数量占总数的比例?这里需求描述有些模糊,我们将总数理解为满足LINKNAME='6'并且按条件分组后的BEINSPECTEDID总数(即上面a和b的总和,因为每个BEINSPECTEDID要么全部完成,要么存在未完成,所以总数就是a+b)。但是注意,EXT3的计数可能不是BEINSPECTEDID的数量,而是记录数(因为我们每组取一条,所以一条记录对应一个EXT3,那么EXT3的数量其实就是我们处理的记录数,也就是BEINSPECTEDID的数量(因为按BEINSPECTEDID分组取一条))。因此,比例就是:存在未完成的BEINSPECTEDID数量/总数(a+b),以及EXT3的数量(也就是记录数,这里等于a+b)占总数的比例(那就是100%?)。这显然不对。重新理解需求:当LINKNAME='6'时,我们得到一组记录(每个BEINSPECTEDID和PATROLINFOID取EXT17最大的一条),然后:-对BEINSPECTEDID进行统计:全部完成的个数(记为completed_count),存在未完成的个数(记为uncompleted_count)-同时,还要统计这些记录中的EXT3的数量?这里可能是指这些记录中EXT3列非空的记录数?或者就是记录数(因为每组一条,记录数等于BEINSPECTEDID的数量)?然后计算:uncompleted_count/(completed_count+uncompleted_count)作为第一个比例(未完成占比)。以及EXT3的数量?这里EXT3的数量是指什么?需求说“EXT3的数量”,然后又要求计算其占总数的比例。总数是什么?如果是所有BEINSPECTEDID的数量,那么EXT3的数量应该是记录数(也就是completed_count+uncompleted_count),那么比例就是1?这显然不对。另一种理解:在LINKNAME='6'的情况下,我们取EXT17最大的一条记录,然后统计这条记录中的EXT3字段的值(可能是数值型)?但需求说的是“数量”,因此可能是计数?或者是指该条记录中EXT3有值(非空)?我们无法确定。由于需求描述存在歧义,我们只能基于假设来构建。为了推进工作,我们做如下假设:对于LINKNAME='4'的部分:-统计BEINSPECTEDID的数量:这里应该是去重计数,即COUNT(DISTINCTBEINSPECTEDID)-EXT3、EXT4的数量:可能是去重计数?也可能是有多少条记录就有多少个?需求未明确。但考虑到与BEINSPECTEDID并列,可能是去重计数?但是后续对EXT3又计算比例,所以我们需要明确。由于需求不明确,我们将按照去重计数处理。-统计EXT14为'已完成'的数量:即当EXT14='已完成'时,计数(按记录计数,不按去重)?因为同一个BEINSPECTEDID可能有多个记录,所以这里可能是COUNT(CASEWHENEXT14='已完成'THEN1END)。同样,未完成的也是类似。-求和:EXT7,EXT8,EXT9是数值型字段,直接SUM(EXT7)等。对于LINKNAME='6'的部分:-先过滤LINKNAME='6',然后按BEINSPECTEDID,PATROLINFOID分组,取EXT17最大的一条(可以使用ROW_NUMBER()窗口函数)。-然后,我们得到这样的一批记录。接着,对这批记录进行统计:全部为'已完成'的BEINSPECTEDID数量:注意,这里不是按记录计数,而是按BEINSPECTEDID去重,并且要求该BEINSPECTEDID在之前取出的唯一一条记录上的EXT14为'已完成'?但是注意,我们取EXT17最大的一条记录,那么每个BEINSPECTEDID只有一条记录。所以这里可以直接根据该条记录的EXT14来判断。存在'未完成':即该条记录的EXT14为'未完成'?但是需求说“存在'未完成'的BEINSPECTEDID”,但这里每个BEINSPECTEDID只有一条记录,所以该条记录是'未完成'就算。因此,统计的是每个BEINSPECTEDID对应的那条记录的EXT14是否是'未完成'。那么,统计全部完成的数量:COUNT(CASEWHENEXT14='已完成'THENBEINSPECTEDIDEND)但是要注意去重?因为每个BEINSPECTEDID只出现一次,所以直接计数即可。存在未完成的数量:COUNT(CASEWHENEXT14='未完成'THENBEINSPECTEDIDEND)EXT3的数量:这里我们理解是统计这些记录(每组一条的记录)中,EXT3字段非空的数量?即COUNT(EXT3)(注意,如果是非空计数,那么使用COUNT(EXT3))然后计算两个比例:1.存在未完成的BEINSPECTEDID数量占总BEINSPECTEDID数量(即所有取出来的记录对应的BEINSPECTEDID的数量)的比例:uncompleted_count/total_count2.EXT3的数量同样占总BEINSPECTEDID数量(即total_count)的比例?但是EXT3的数量可能不是BEINSPECTEDID的数量,而是这些记录中EXT3非空的数量。所以比例就是:非空EXT3的记录数/总记录数(即COUNT(EXT3)/COUNT(*))但是需求原文:“分别计算EXT14存在'未完成'的BEINSPECTEDID和EXT3占总数的比例”,这里两个比例:比例1:存在未完成的BEINSPECTEDID数量/总数(即总BEINSPECTEDID数)比例2:EXT3的数量/总数(这个总数也是指总BEINSPECTEDID数?还是其他?需求没有明确说明。根据上文,我们假设总数都是指总BEINSPECTEDID数量(也就是总记录数,因为每个BEINSPECTEDID对应一条记录))。因此,比例1就是:存在未完成的数量/总记录数。比例2:EXT3(非空)的数量/总记录数。注意:在LINKNAME='6'的情况下,统计的BEINSPECTEDID都是去重的(每个BEINSPECTEDID只出现一次),所以总记录数就是总BEINSPECTEDID数。由于需求复杂,我们将整个查询拆分为两个主要部分,然后可能分别查询,也可以合并为一个查询。考虑到两个部分都是基于相同的连接表,我们可以分别处理然后通过UNION或者分别展示。但是需求并没有说明两个部分是分开统计还是合并在一起。所以我们可以分别用两个查询来写,但也可以在一个查询中同时计算。但是注意,这两个部分是基于两个不同的条件:LINKNAME='4'和LINKNAME='6'。所以我们可以分别针对这两个条件进行统计。然而,在同一个查询中同时处理LINKNAME='4'和LINKNAME='6'可能会因为数据量大而影响性能,而且逻辑复杂。我们可以分开写两个查询,但根据需求描述,似乎需要一条SQL语句?因此我们尝试在一个查询中完成。步骤:1.先完成表连接。2.然后分别处理两个条件。使用条件聚合,但是注意两个条件的过滤条件不同,而且对于LINKNAME='6'的部分还需要先分组取最大值,因此我们需要分别构造子查询。由于LINKNAME='6'的部分需要先按照BEINSPECTEDID,PATROLINFOID分组取EXT17最大的一条记录,我们可以先对这部分数据单独处理,然后再与主查询进行连接或者作为子查询。具体方案:方案1:分别计算两个条件的统计结果,然后使用两个子查询分别统计,最后放在一行中(或者两行)。这里我们使用两个子查询,然后交叉连接(因为没有关联键)形成一行(如果结果需要一行展示)或者分别输出两行(如果是两个统计部分)。方案2:在同一个查询中使用条件聚合,但LINKNAME='6'的部分需要先进行额外处理(分组取最大),因此我们可以先构建一个公共表表达式(CTE),将连接后的数据分成两个部分:一部分是LINKNAME='4'的数据,另一部分是经过处理(分组取最大EXT17)的LINKNAME='6'的数据,然后分别聚合。但注意,这两个部分的数据是相互独立的,所以我们可以分别处理然后UNIONALL,但是聚合结果的形式不同(需要统计的字段不同),所以可能不太适合UNION。因此,我们采用方案1:在一个查询中分别计算两个部分,然后通过CROSSJOIN(或者没有连接)放在一行中(用多个列同时展示)。但是这样会使得查询结果只有一行,且列非常多。但是,需求中并没有说明输出格式,我们按照两个部分分开输出两行?也没有具体说明。为了避免混淆,我们输出两行:第一行是LINKNAME='4'的统计结果,第二行是LINKNAME='6'的统计结果。这样我们就可以用不同的列表示不同的统计项。具体步骤:1.构建一个连接表的CTE(CommonTableExpression),记为base。2.对于LINKNAME='4'的部分,直接从base中筛选LINKNAME='4',然后进行聚合。3.对于LINKNAME='6'的部分,我们需要先对base表中LINKNAME='6'的数据进行分组(BEINSPECTEDID,PATROLINFOID)取EXT17最大的一条(使用ROW_NUMBER),然后在这个结果集上再进行聚合。然后,我们将两个部分的结果进行UNIONALL,但两个部分的输出列不同,所以我们需要对齐列,不足的列置为NULL。但这样会增加复杂度。或者我们分别计算两个部分,然后使用两个子查询,将结果放在同一行中(作为不同列)。这样输出只有一行,两个部分的结果作为不同的列。但是这样要求两个部分都只返回一行(即统计值都是标量)。由于需求没有明确,我们选择分两行输出,每行代表一个部分,然后每行有各自的统计列。这样我们可以使用多个列,对于不相关的列置NULL。但是这样需要保证两个部分的列数一致。但两个部分的统计项不同,列数可能不一致,所以我们把两个部分各自的统计项都列出来,然后将另一部分置为NULL。这样输出列的设计:第一部分(LINKNAME='4')的统计结果包含以下字段:-stat_type:标记为'LINKNAME=4'-count_be_inspected_id:BEINSPECTEDID数量-count_ext3:EXT3数量-count_ext4:EXT4数量-count_ext14_completed:EXT14为'已完成'的数量-count_ext14_uncompleted:EXT14为'未完成'的数量-sum_ext7:EXT7求和-sum_ext8:EXT8求和-sum_ext9:EXT9求和对于第二部分需要的列,这里全部置NULL(我们暂时不输出,因为这一行只展示第一部分)第二部分(LINKNAME='6')的统计结果包含:-stat_type:标记为'LINKNAME=6'-completed_count:EXT14全部为'已完成'的BEINSPECTEDID数量(注意,这里因为每条记录代表一个BEINSPECTEDID,所以直接计数完成即可)-uncompleted_count:EXT14存在'未完成'的BEINSPECTEDID数量(这里我们按该条记录的EXT14为未完成计数?注意,我们只取了一条记录,所以每个BEINSPECTEDID的状态就是这条记录的状态。因此,我们可以直接按这条记录的EXT14进行判断)-count_ext3:EXT3非空的数量(COUNT(EXT3))-uncompleted_ratio:未完成的BEINSPECTEDID比例(即uncompleted_count除以总记录数)-ext3_ratio:EXT3的数量占记录总数的比例(即count_ext3除以总记录数)对于第一部分需要的列,全部置为NULL。但是这样输出列不同,我们可以分别输出两个结果集,但是用户要求一条SQL语句,所以我们可以使用UNIONALL,并且将两个结果集使用不同列,并用NULL补全对方需要的列。但必须保证两个SELECT语句输出的列数量相同且类型兼容。因此,我们设计输出列如下(共11列):stat_type,count_be_inspected_id,count_ext3,count_ext4,count_ext14_completed,count_ext14_uncompleted,sum_ext7,sum_ext8,sum_ext9,completed_count,uncompleted_count,count_ext3_for_linkname6,uncompleted_ratio,ext3_ratio这样,第一部分的查询中,前9列有值,后5列为NULL;第二部分的查询中,前8列为NULL(除了stat_type),后5列有值(但注意第二部分的count_ext3_for_linkname6不是前面的count_ext3,所以我们单独命名)。但是这样列太多,而且部分列的含义在两种情况下不同,可能造成混淆。所以我们分别输出两个统计结果,并在结果中用stat_type区分。然而,由于UNIONALL要求列数相同,所以我们只能使用尽可能多的列,把两个部分的结果都囊括进去。我们定义以下列(15列):stat_type,--字符串,标记是哪个部分count_be_inspected_id,count_ext3,count_ext4,count_ext14_completed,count_ext14_uncompleted,sum_ext7,sum_ext8,sum_ext9,completed_count,uncompleted_count,count_ext3_for_linkname6,total_count_for_linkname6,uncompleted_ratio,ext3_ratio解释:total_count_for_linkname6是第二部分的记录总数(即用于计算比例的分母),虽然可以通过completed_count+uncompleted_count得到,但为了清晰,我们也单独计算。这样,对于第一部分:除了stat_type外,只有前8列有值(count_be_inspected_id到sum_ext9),其余列(从completed_count到ext3_ratio)均为NULL。对于第二部分:stat_type有值,completed_count,uncompleted_count,count_ext3_for_linkname6,total_count_for_linkname6有值,uncompleted_ratio=uncompleted_count/total_count_for_linkname6,ext3_ratio=count_ext3_for_linkname6/total_count_for_linkname6其他列(即前8列)为NULL。现在,我们分别构建两个部分的子查询:第一部分(LINKNAME='4'):从base表(连接后的表)中选出LINKNAME='4'的数据,然后计算:count_be_inspected_id=COUNT(DISTINCTBEINSPECTEDID)count_ext3=COUNT(DISTINCTEXT3)count_ext4=COUNT(DISTINCTEXT4)count_ext14_completed=COUNT(CASEWHENEXT14='已完成'THEN1END)count_ext14_uncompleted=COUNT(CASEWHENEXT14='未完成'THEN1END)sum_ext7=SUM(EXT7)sum_ext8=SUM(EXT8)sum_ext9=SUM(EXT9)注意:这里是否使用DISTINCT要根据需求。由于需求没有明确,我们假设需要去重。但EXT14的计数不需要去重(按行统计)。第二部分(LINKNAME='6'):先处理base表中LINKNAME='6'的数据,按照(BEINSPECTEDID,PATROLINFOID)分组,每组取EXT17最大的一条记录(如果EXT17相同,则取任意一条)。我们可以使用ROW_NUMBER()窗口函数。然后基于这个结果集,进行统计:total_count_for_linkname6=COUNT(BEINSPECTEDID)--总记录数(也即总BEINSPECTEDID数)completed_count=COUNT(CASEWHENEXT14='已完成'THENBEINSPECTEDIDEND)--注意,这里每个BEINSPECTEDID只出现一次,所以COUNT(条件)即可,不需要去重uncompleted_count=COUNT(CASEWHENEXT14='未完成'THENBEINSPECTEDIDEND)count_ext3_for_linkname6=COUNT(EXT3)--非空计数uncompleted_ratio=uncompleted_count/total_count_for_linkname6--注意:使用小数表示,可能需要用浮点数ext3_ratio=count_ext3_for_linkname6/total_count_for_linkname6但是,注意:在第二部分中,我们得到的是一个临时结果集(每个BEINSPECTEDID只出现一次,所以我们直接使用聚合函数即可)。由于在SQL中除法可能整除,为了避免截断,可以显式乘以1.0转换成浮点数,或者使用CAST。下面我们开始编写SQL:注意:表名和字段名用方括号或双引号,但这里为了通用,我们使用反引号(如果有需要)。但根据用户提供的信息,表名是大写字母和横杠,在SQL中通常需要用双引号或方括号。这里我们假设数据库支持使用双引号,例如:"OA-PATROL-INFO"。步骤:1.构建基本连接表(CTE)2.对第二部分的数据单独处理(使用ROW_NUMBER)形成子查询3.分别进行两个部分的聚合注意:在连接表中,可能有多个表有同一个字段名(如BEINSPECTEDID),我们需要明确指定来自哪个表。根据连接条件,OA-PATROL-DEPT-INFO(B表)中有BEINSPECTEDID,而OA-PATROL-RECTIFICATION-INFO(C表)也有BEINSPECTEDID,我们使用的是B.BEINSPECTEDID=C.BEINSPECTEDID。所以后续使用BEINSPECTEDID时,我们可以用B表的,也可以用C表的(在左连接时,如果C表没有连接上,则C.BEINSPECTEDID为NULL,但B.BEINSPECTEDID还有值)。但在LINKNAME='4'和'6'时,都需要使用C.LINKNAME(因为条件在C表上),所以我们使用C.BEINSPECTEDID(但注意,如果连接不上,C.BEINSPECTEDID为NULL,那么LINKNAME也是NULL。因此,我们的连接条件要确保在LINKNAME='4'或'6'时,C表必须存在(所以严格来说,我们只关心C表存在且LINKNAME符合的情况)。但需求是左连接,所以C表可能不存在匹配的行。所以我们在筛选LINKNAME='4'或'6'时,也隐含着筛选了C表存在匹配的行。因此,在连接表中,我们使用C表上的字段时,如果是NULL,那么LINKNAME就不会为'4'或'6',所以不会出现在我们的统计中。下面开始编写CTE:由于需求复杂,我们使用多个CTE来提高可读性。注意:由于数据量较大,这样写可能会影响性能,但为了清晰,我们先按逻辑编写。具体SQL语句:WITHbaseAS(SELECTA.*,--实际使用时,应该只选取需要的字段,避免性能问题。但这里为了演示,我们使用*,实际开发中要明确字段B.BEINSPECTEDIDASB_BEINSPECTEDID,B.PATROLINFOIDASB_PATROLINFOID,...,C.BEINSPECTEDIDASC_BEINSPECTEDID,C.LINKNAME,C.EXT3,C.EXT4,C.EXT14,C.EXT7,C.EXT8,C.EXT9,C.EXT17FROM"OA-PATROL-INFO"ALEFTJOIN"OA-PATROL-DEPT-INFO"BONA.ID=B.PATROLINFOIDLEFTJOIN"OA-PATROL-RECTIFICATION-INFO"CONB.BEINSPECTEDID=C.BEINSPECTEDID),--第一部分:直接筛选LINKNAME='4'的数据part4AS(SELECTCOUNT(DISTINCTC_BEINSPECTEDID)AScount_be_inspected_id,COUNT(DISTINCTEXT3)AScount_ext3,COUNT(DISTINCTEXT4)AScount_ext4,COUNT(CASEWHENEXT14='已完成'THEN1END)AScount_ext14_completed,COUNT(CASEWHENEXT14='未完成'THEN1END)AScount_ext14_uncompleted,SUM(EXT7)ASsum_ext7,SUM(EXT8)ASsum_ext8,SUM(EXT9)ASsum_ext9FROMbaseWHERELINKNAME='4'),--第二部分:处理LINKNAME='6',先分组取EXT17最大的一条记录part6_prepAS(SELECT*,ROW_NUMBER()OVER(PARTITIONBYB_BEINSPECTEDID,B_PATROLINFOIDORDERBYEXT17DESC)ASrnFROMbaseWHERELINKNAME='6'),part6_dataAS(SELECT*FROMpart6_prepWHERErn=1--每个分组取EXT17最大的一条),part6AS(SELECTCOUNT(C_BEINSPECTEDID)AStotal_count,COUNT(CASEWHENEXT14='已完成'THENC_BEINSPECTEDIDEND)AScompleted_count,COUNT(CASEWHENEXT14='未完成'THENC_BEINSPECTEDIDEND)ASuncompleted_count,COUNT(EXT3)AScount_ext3_for_linkname6,--计算比例COUNT(CASEWHENEXT14='未完成'THENC_BEINSPECTEDIDEND)*1.0/COUNT(C_BEINSPECTEDID)ASuncompleted_ratio,COUNT(EXT3)*1.0/COUNT(C_BEINSPECTEDID)ASext3_ratioFROMpart6_data),--合并两个部分的结果--我们为每个部分单独构造一行resultAS(SELECT'LINKNAME=4'ASstat_type,count_be_inspected_id,count_ext3,count_ext4,count_ext14_completed,count_ext14_uncompleted,sum_ext7,sum_ext8,sum_ext9,NULLAScompleted_count,NULLASuncompleted_count,NULLAScount_ext3_for_linkname6,NULLAStotal_count,NULLASuncompleted_ratio,NULLASext3_ratioFROMpart4UNIONALLSELECT'LINKNAME=6'ASstat_type,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,completed_count,uncompleted_count,count_ext3_for_linkname6,total_count,uncompleted_ratio,ext3_ratioFROMpart6)SELECT*FROMresult;注意:如果某个部分没有数据,那么该部分返回0条记录?我们的part4和part6子查询没有数据时,会返回一行(count为0)?聚合函数在没有数据时返回NULL(或0?如果字段为数字,则为0)?在part4中,所有聚合函数在没有数据时返回0(因为COUNT返回0,SUM返回NULL,但我们可以用COALESCE(SUM(EXT7),0)来避免NULL)?所以,对于part4和part6,我们可能需要用COALESCE确保没有数据时返回0。因此,在part4中:将所有的COUNT和SUM都加上COALESCE,这样没有数据时就不会是NULL。例如:COALESCE(COUNT(DISTINCTC_BEINSPECTEDID),0)AScount_be_inspected_id,但COUNT在没有行的时候返回0,所以不需要。但是SUM需要:COALESCE(SUM(EXT7),0)另外,在part6中,total_count在没有记录时为0(COUNT(C_BEINSPECTEDID)),其他类似。但是,如果没有数据时,part4会返回一行(0值),part6也会返回一行(0值)?所以我们最后会有两行。但是,如果两个部分都没有数据,那么我们就会有两行(每行都是0值)?或者part4和part6都是0行?我们需要确保即使没有数据,part4和part6也要返回一行,因此可以使用:在part4中,如果没有数据,我们也要返回一行0值。可以使用LEFTJOIN一个虚拟行,或者通过UNION一个空行,但不需要,因为COUNT和SUM在没有行的时候会返回0或NULL(需要COALESCE)。因此,在part4中,我们只需确保即使FROM没有数据,我们也能返回0。使用聚合函数时,如果没有数据,COUNT返回0,其他聚合函数返回NULL。我们调整part4:part4AS(SELECTCOALESCE(COUNT(DISTINCTC_BEINSPECTEDID),0)AScount_be_inspected_id,COALESCE(COUNT(DISTINCTEXT3),0)AScount_ext3,COALESCE(COUNT(DISTINCTEXT4),0)AScount_ext4,COALESCE(COUNT(CASEWHENEXT14='已完成'THEN1END),0)AScount_ext14_completed,COALESCE(COUNT(CASEWHENEXT14='未完成'THEN1END),0)AScount_ext14_uncompleted,COALESCE(SUM(EXT7),0)ASsum_ext7,COALESCE(SUM(EXT8),0)ASsum_ext8,COALESCE(SUM(EXT9),0)ASsum_ext9FROMbaseWHERELINKNAME='4'),但是,COUNT函数在有数据的情况下不会返回NULL,没有数据时返回0,所以只有SUM需要这样处理?而COUNT不需要。但上面语句中,COUNT不会返回NULL,只有SUM会返回NULL,所以只需要对SUM使用COALESCE即可。调整:将COUNT相关的聚合去掉COALESCE(改为COUNT本身),只对SUM使用COALESCE。同理,在part6中,除了比率,其他字段如果为0时,比率应该是0/0(即NULL)。因此,我们可能需要用CASEWHEN来避免除数为0的情况。修改part6的比率计算:CASEWHENCOUNT(C_BEINSPECTEDID)>0THENCOUNT(CASEWHENEXT14='未完成'THENC_BEINSPECTEDIDEND)*1.0/COUNT(C_BEINSPECTEDID)ELSENULLENDASuncompleted_ratio,其他比率类似。由于需求复杂,并且我们不知道数据库类型(不同的数据库系统对数据类型和除数为0的处理可能不同),我们做了以上处理。最终,我们将以上SQL语句整理为一条完整的SQL。注意,实际应用中可能需要调整字段和表别名,确保每个字段的来源表正确。由于用户没有提供具体字段对应的表,我们在base中使用了*,这可能会引发列名冲突(如BEINSPECTEDID在B表和C表中都有),因此我们给它们起了别名(B_BEINSPECTEDID和C_BEINSPECTEDID)。但后续计算中,我们使用的是C_BEINSPECTEDID(因为条件是C.LINKNAME,所以使用C表字段)。在连接后,如果C表无匹配,则C_BEINSPECTEDID为NULL,但LINKNAME也为NULL,所以不会进入筛选。在LINKNAME='4'或'6'的情况下,C_BEINSPECTEDID一定不为空,因为C表匹配到了。以上SQL在复杂场景下可能需要进一步优化性能,但功能上满足需求。考虑到SQL长度,我们将其完整写出,并适当注释。由于在文本编辑器中,我们可能会遇到数据库系统保留字的问题,所以建议在表名和字段名上加双引号(或方括号)。以下是完整SQL语句:</think> 以下是根据需求编写的复杂SQL语句,结合了多表连接、条件聚合、窗口函数和比例计算: ```sql WITH JoinedData AS ( SELECT info.ID, dept.BEINSPECTEDID, rect.LINKNAME, rect.EXT3, rect.EXT4, rect.EXT7, rect.EXT8, rect.EXT9, rect.EXT14, rect.EXT17 FROM [OA-PATROL-INFO] info LEFT JOIN [OA-PATROL-DEPT-INFO] dept ON info.ID = dept.PATROLINFOID LEFT JOIN [OA-PATROL-RECTIFICATION-INFO] rect ON dept.BEINSPECTEDID = rect.BEINSPECTEDID ), Link4Data AS ( SELECT COUNT(DISTINCT BEINSPECTEDID) AS CountBeInspected, COUNT(DISTINCT EXT3) AS CountExt3, COUNT(DISTINCT EXT4) AS CountExt4, COUNT(CASE WHEN EXT14 = '已完成' THEN 1 END) AS CompletedCount, COUNT(CASE WHEN EXT14 = '未完成' THEN 1 END) AS UncompletedCount, SUM(EXT7) AS SumExt7, SUM(EXT8) AS SumExt8, SUM(EXT9) AS SumExt9 FROM JoinedData WHERE LINKNAME = '4' ), Link6Filtered AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY BEINSPECTEDID, ID ORDER BY EXT17 DESC ) AS RowNum FROM JoinedData WHERE LINKNAME = '6' ), Link6Data AS ( SELECT COUNT(DISTINCT BEINSPECTEDID) AS TotalBeInspected, COUNT(DISTINCT CASE WHEN EXISTS ( SELECT 1 FROM JoinedData j WHERE j.BEINSPECTEDID = Link6Filtered.BEINSPECTEDID AND j.EXT14 = '未完成' ) THEN BEINSPECTEDID END ) AS AnyUncompletedBeInspected, COUNT(DISTINCT CASE WHEN NOT EXISTS ( SELECT 1 FROM JoinedData j WHERE j.BEINSPECTEDID = Link6Filtered.BEINSPECTEDID AND j.EXT14 <> '已完成' ) THEN BEINSPECTEDID END ) AS AllCompletedBeInspected, COUNT(EXT3) AS CountExt3 FROM Link6Filtered WHERE RowNum = 1 ) SELECT 'LINKNAME=4' AS Condition, CountBeInspected, CountExt3, CountExt4, CompletedCount, UncompletedCount, SumExt7, SumExt8, SumExt9, NULL AS AnyUncompletedBeInspected, NULL AS AllCompletedBeInspected, NULL AS AnyUncompletedRatio, NULL AS Ext3Ratio FROM Link4Data UNION ALL SELECT 'LINKNAME=6' AS Condition, TotalBeInspected, NULL, NULL, NULL, NULL, NULL, NULL, NULL, AnyUncompletedBeInspected, AllCompletedBeInspected, ROUND(AnyUncompletedBeInspected * 1.0 / NULLIF(TotalBeInspected, 0), 4) AS AnyUncompletedRatio, ROUND(CountExt3 * 1.0 / NULLIF(TotalBeInspected, 0), 4) AS Ext3Ratio FROM Link6Data; ``` ### 关键步骤说明: 1. **基础连接 (JoinedData CTE)**: - 三个表左连接:`OA-PATROL-INFO` → `OA-PATROL-DEPT-INFO` → `OA-PATROL-RECTIFICATION-INFO` - 使用字段:`ID`、`BEINSPECTEDID`、`LINKNAME`、以及各EXT字段 2. **LINKNAME=4处理 (Link4Data CTE)**: ```sql COUNT(DISTINCT CASE WHEN EXT14='已完成' THEN 1 END) AS CompletedCount ``` - 统计`BEINSPECTEDID`、`EXT3`、`EXT4`的**去重计数** - 按`EXT14`状态分类计数 - 对`EXT7/8/9`进行`SUM`求和 3. **LINKNAME=6处理**: - **数据过滤 (Link6Filtered CTE)**: ```sql ROW_NUMBER() OVER (PARTITION BY BEINSPECTEDID, ID ORDER BY EXT17 DESC) ``` - 分组取`EXT17`最大值的记录 - **统计计算 (Link6Data CTE)**: ```sql COUNT(DISTINCT CASE WHEN NOT EXISTS (...) THEN BEINSPECTEDID END) ``` - 使用子查询判断状态完成情况 - 计算全部完成/存在未完成的`BEINSPECTEDID`数量 - 计算`EXT3`计数和占比: ```sql ROUND(CountExt3 * 1.0 / NULLIF(TotalBeInspected, 0), 4) ``` 4. **结果合并 (UNION ALL)**: - 两个条件的结果统一结构输出 - 使用`NULLIF`防止除零错误 - 比例计算保留4位小数 ### 注意事项: 1. 使用`DISTINCT COUNT`确保统计项不重复计数 2. 窗口函数`ROW_NUMBER()`确保每组取唯一记录 3. 子查询`EXISTS/NOT EXISTS`精确判断状态 4. `NULLIF(TotalBeInspected, 0)`避免除零错误 5. `ROUND(value * 1.0 / total, 4)`计算精确比例
评论 1
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值