案例背景

        最近读到一个审计案例《审计观察 | 大数据分析在建筑央企招标采购审计中的应用实践》，主要内容就是在汇集供应商管理、招标方案制定、投标、评标和定标全流程管理数据，分包招标系统与人力资源系统数据的基础上，运用大数据分析思维，使用SQL语句，将各类数据源表中的身份信息进行关联、对比、汇总等操作，输出围标串标等疑点线索。

        该案例针对的检查场景是审计比较常见的场景，数据分析规则也比较简洁，对学习和应用SQL进行数据分析具有很好的参考价值。

        本文使用SQL语句，根据案例的分析逻辑，对分析模型进行重现，过程中修正了文字描述中的一些字段名称错误。

        为便于代码具有通用性，易于学习和重现，采用MySQL库进行分析，SQL语句也遵循MySQL的语法规则。

    关于SQL语言的基础知识和入门案例，参见人民邮电出版社《数字化审计实务指南》“第九章 数据分析语言 / 第一节 结构化查询语言（SQL）”。

数据源表

       数字化审计的第一步就是分析数据采集和清洗，根据案例描述，有如下数据源表：

• 分包商联系人信息表：包括单位名称、法人姓名、法人电话、法人身份证号，现场负责人姓名、现场负责人电话、现场负责人身份证号等字段。

• 分包采购信息表：包括招标方案名称、采购方式、招标金额、中标金额、采购组织单位、参与供应商名称、是否中标等字段

• 员工信息表：包括员工姓名、所在部门、身份证号、联系方式、性别，家属姓名、关系、性别、联系方式等字段

SQL模型重现

利益输送疑点分析

案例中的SQL数据分析模型编写逻辑如下：

分析模型SQL重现如下：

-- STEP 1 查询验证
select *
from 分包商联系人信息表 as a,员工信息表 as b
where a.现场负责人身份证号=b.身份证号

--STEP 2 生成临时表
create table 临时表tb1
as
    select a.单位名称 as 分包商名称,a.现场负责人姓名,a.现场负责人身份证号,b.员工姓名,b.身份证号,b.所在部门
    from 分包商联系人信息表 as a,员工信息表 as b
    where a.现场负责人身份证号=b.身份证号

--STEP 3 查询验证
select *
from 分包采购信息表 as a,临时表tb1 as b
where a.参与供应商名称=b.分包商名称
and a.是否中标='是'

--STEP 3 生成结果表
create table 利益输送疑点表
as
    select a.招标方案名称 as 采购方案名称,a.招标金额,a.中标金额,b.分包商名称,b.现场负责人姓名,b.现场负责人身份证号,b.员工姓名,b.身份证号 as 员工身份证号,b.所在部门 as 员工所在部门
    from 分包采购信息表 as a,临时表tb1 as b
    where a.参与供应商名称=b.分包商名称
    and a.是否中标='是'

围标串标疑点分析

案例中的SQL数据分析模型编写逻辑如下：

分析模型SQL重现如下：

-- STEP 1 建立分包采购信息及参与分包商联系人信息
-- 未来避免和上一段语句的临时表名冲突 改为 临时表tb2
create table 临时表tb2
as
    select a.招标方案名称 as 采购方案名称,a.招标金额,a.中标金额, b.单位名称 as 分包商名称,b.现场负责人姓名,b.现场负责人身份证号
    from 分包采购信息表 as a,分包商联系人信息表 as b
    where a.参与供应商名称=b.单位名称
    and a.是否中标='是'

-- STEP 2 分组统计分包商现场负责人身份证号 同一方案中多次出现同一现场负责人
select a.采购方案名称,count(a.现场负责人身份证号) as 出现次数
from 
(
    select a.采购方案名称,a.现场负责人身份证号
    from 临时表tb2 as a
    group by a.采购方案名称,a.现场负责人身份证号
) as b
group by a.采购方案名称
having count(a.现场负责人身份证号)>1

--STEP 3 提取疑点明细数据
select *
from 临时表tb2 as a,
(
    select a.采购方案名称,count(a.现场负责人身份证号) as 出现次数
    from 
    (
        select a.采购方案名称,a.现场负责人身份证号
        from 临时表tb2 as a
        group by a.采购方案名称,a.现场负责人身份证号
    ) as b
    group by a.采购方案名称
    having count(a.现场负责人身份证号)>1
) as c
where a.采购方案名称=c.采购方案名称
order by a.采购方案名称,a.现场负责人身份证号