sql两个数字之差取最接近的_从零学DAX/Sql/Python030203SQL数据分类汇总续篇

   点击 “ 数字化审计 ”，可以关注哦！

开篇啰嗦话

上篇文章 ，为你介绍了SQL语句中select 的group by参数进行分类统计，group by在审计数据分析过程中是必然绕不开的。因为，你必然离不开各种汇总计算，例如，不同产品的采购额、销售额，不同年度的不同客户的合同额，等等，其中，提到了with cube 和with rollup两个参数，今天继续为你补充。

从零学DAX/Sql/Python之SQL篇文章索引
1	SQL系列-01-数据库及SQL Server基本操作
2	SQL系列-02-SQL Server基本介绍及SSMS
3	SQL系列-03-SQL与Python集成编辑工具ADS
4	SQL系列-04-Select语法、别名、概览数据
5	SQL系列-05-SQL数据分类汇总
6

上表中前3篇文章，对数据库及其操作、操作数据库的工具SSMS和Azure Data Studio 进行了介绍，如果你要跟着学习SQL，请务必阅读！ 微软的SQLServer官方教程提供了T-SQL语法详细介绍：

https://docs.microsoft.com/zh-cn/sql/t-sql/language-reference?view=sql-server-ver15

再谈group by 参数with rollup/cube

上篇文章中为你介绍了group by 及其参数with rollup/cube的基本用法。在实际工作中，我们往往需要添加“小计”和“总计”，如下图所示。

上图可以看出：

(1)group by 年度,公司名称：仅能汇总不同年度、不同公司的聚合值；

(2)group by 年度,公司名称 with rollup：还能汇总每个年度全部公司的聚合值，及所有年度全部公司的聚合值。而无法汇总每个公司全部年度的聚合值(这个得用group by 公司名称,年度 with rollup)

(3)group by 年度,公司名称 with cube：在with rollup的基础上还能汇总每个公司全部年度。也就是说，

group by 年度,公司名称 with cube=

group by 年度,公司名称 with rollup  +

group by 公司名称,年度 with rollup

但是，在上篇文章中，并未显示小计和合计，在小计和合计的地方均显示为null-空值。

这是如何做到的呢？这就需要为你介绍sql中的一个条件判断CASE WHEN语句：

条件判断语句CASE When Then else end CASE WHEN 条件判断语句语法格式 ：

case  when 条件1  then 结果1

         when 条件2  then 结果2

         ......

         when 条件n  then 结果n

         else 结果m

end

表达的含义是：当满足条件1时，返回结果1，...，如果最终没有匹配的条件，就返回结果m。

其详细语法及介绍可参见微软官网：

https://docs.microsoft.com/zh-cn/sql/t-sql/language-elements/case-transact-sql?view=sql-server-ver15

下面显示了不同招标方式，并且将“目录采购自动匹配”、“公开招标”、"邀请招标"三种招标方式归为“招标”、其余招标方式均归为“未招标”，sql代码如下：

SELECT distinct [招标方式]
  FROM [samples].[dbo].[采购明细]
GO
---case when 根据不同的条件，返回不同的结果
SELECT distinct [招标方式],
'招标分类'=case when a.招标方式 ='目录采购自动匹配' then '招标' 
               when a.招标方式 ='公开招标' then '招标'
               when a.招标方式 ='邀请招标' then '招标'
               else '未招标'
          end         
FROM [samples].[dbo].[采购明细] a

sql代码及其结果如下图所示。

下面是审计确定应招未招的一个假定定义。其SQL语句为：

---应招未招：招标方式不是目录采购自动匹配、公开招标、邀请招标，且金额大于1万
SELECT a.订单号,a.招标方式,a.金额,
'审计认定的招标类型'=case when a.招标方式 ='目录采购自动匹配' then '应招已招' 
               when a.招标方式 ='公开招标' then '应招已招'
               when a.招标方式 ='邀请招标' then '应招已招'
               else 
                   (case when a.金额>10000 then '应招未招' 
                     else '可不招' end)
          end
FROM [samples].[dbo].[采购明细] a

SQL语句运行结果如下图，然后只需筛选出“应招未招”的采购订单。

Tips!  当然，实际工作中判定应招未招的条件会更加复杂，这就涉及到更加复杂的逻辑运算，后面会继续补充！

再谈group by with rollup/cube中的小计与合计

掌握了上述case when 语句后，就可以对group by with rollup/cube的结果中的null值，显示小计和合计字样。

SQL 代码如下所示 ：

---对group by 进行with rollup后的结果，增加小计和合计
---年度列如果是空值null，则年度列所在的单位格显示总计
---年度列不是空值null,并且公司名称列是空值null,则单元格显示小计
select 
case when cgmx.年度 is null then '总计' else cgmx.年度 end '年度',
case when cgmx.年度 is not null  and cgmx.公司名称 is null then '小计'
         else cgmx.公司名称 end '公司名称',
count(cgmx.订单号) as 订单数
,sum(cgmx.金额) as 采购金额 from [samples].[dbo].[采购明细] cgmx
group by cgmx.年度,cgmx.公司名称  with rollup

SQL语句运行结果如下图

动态显示group by的结果

在审计工作中，我们不仅需要按照不同维度，运用group by来查看各种统计数据，而且往往会需要以图形的形式进行展示，以增加感官认识。

但是，传统的图型展示只能是静态的，例如，在excel中，如果需要显示不同年度的所有公司采购额和采购数量的构成，通常的做法，只能一年做一个饼图。如果需要显示不同公司的所有年度的采购额和采购数量变化趋势，通常的做法，只能一个公司做一个直线图。

但是，python中的pyechart包给出了一个新奇的解决方案-运用时间线的概念，可以完美的解决这个动态需要。

下图显示了2016年三家公司采购额和采购量构成情况，当你滑动底部时间线，你会可以看到2017、2018年的构成情况。

一条线、一个页面同时承载了不同年度的显示！

下面给出动画效果图

怎么样，你是否心动了！

这要感谢pyecharts的作者之一chenjiandongx，他给与我很多的解惑。https://github.com/chenjiandongx。

这就是python的强大之处，如前我多次提到：我学习和研究python的最主要原因不是用它来清洗、处理数据，这些用excel和sql基本可以搞定，而是用它来解决excel、sql往往很难解决的问题，这个就是其中之一！

关于上述效果的代码我会在下下篇文章中予以介绍，它属于python的范畴。

下一篇我会介绍python包安装及管理的技巧，这是很多初学者最容易忽视也是最令人抓狂的问题。有了这个铺垫后，再为你介绍这个动画的代码！

随着轻轻的风轻轻地飘

历经的路都会刻骨铭心

如果你觉得有用，记得点击右下角“在看”按钮，并写下宝贵的意见，也可在公众号下扫码加群。

欲见详情，静候下文

原创文章欢迎转载！

请注明:本文首发于

"数字化审计"公众号