欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

每日学习心得:SQL查询表的行列转换/小计/统计(with rollup,with cube,pivot解析)

程序员文章站 2023-10-30 23:17:10
每日学习心得:sql查询表的行列转换/小计/统计(with rollup,with cube,pivot解析)   1.    sql查询表的行列...

每日学习心得:sql查询表的行列转换/小计/统计(with rollup,with cube,pivot解析)

 

1.    sql查询表的行列转换/小计/统计(with  rollup,with cube,pivot解析)

在实际的项目开发中有很多项目都会有报表模块,今天就通过一个小的sql查询统计来讲解一下实际开发中比较常用的行列转换/小计/统计等报表统计相关的常用知识点。

 

题目如下:

 

 查询sales 和stores表,得出1993年每个store每季度销售数量及小计和总计,查询出的结果如下

 

                       每日学习心得:SQL查询表的行列转换/小计/统计(with rollup,with cube,pivot解析)


其中sales表的数据结构如下:

 

每日学习心得:SQL查询表的行列转换/小计/统计(with rollup,with cube,pivot解析)

 

其中stores表的数据结构如下:

 

每日学习心得:SQL查询表的行列转换/小计/统计(with rollup,with cube,pivot解析)

 

1.1 普通方法(容易理解)

初看题目,第一感觉是竖表转横表,首先想到的是使用case when,

 

所以

 

第一步操作如下:

 

[csharp] 

select st.stor_name,sum(sa.qty) as total,  

       (case when datepart(qq,sa.ord_date)=1 then sum(sa.qty) else 0 end) as qtr1,  

       (case when datepart(qq,sa.ord_date)=2 then sum(sa.qty) else 0 end) as qtr2,  

      (case when datepart(qq,sa.ord_date)=3 then sum(sa.qty) else 0 end) as qtr3,  

       (case when datepart(qq,sa.ord_date)=4 then sum(sa.qty) else 0 end) as qtr4  

       from stores st left join sales sa  

       on st.stor_id=sa.stor_id  

       where datepart(yy,sa.ord_date)=1993  

       group by st.stor_name,sa.ord_date  

 

 

检索出结果如下:

 

每日学习心得:SQL查询表的行列转换/小计/统计(with rollup,with cube,pivot解析)

 

这个时候由检索的结果可知,其中部分商店的统计信息没有合并统计,原因在于分组的时候我们是按商店名和日期分组的,

 

第二步操作,将第一步检索的信息,再次按店名分组统计,sql语句如下:

 

[csharp] 

select a.stor_name as stor_name ,sum(a.total) as total,sum(a.qtr1) as qtr1,  

       sum(a.qtr2) as qtr2,sum(a.qtr3) as qtr3,sum(a.qtr4) as qtr4  

       from  

       (  

       --按时间和stor_name分组统计出对应的stor一年的销售明细  

       select st.stor_name,sum(sa.qty) as total,  

       (case when datepart(qq,sa.ord_date)=1 then sum(sa.qty) else 0 end) as qtr1,  

       (case when datepart(qq,sa.ord_date)=2 then sum(sa.qty) else 0 end) as qtr2,  

       (case when datepart(qq,sa.ord_date)=3 then sum(sa.qty) else 0 end) as qtr3,  

       (case when datepart(qq,sa.ord_date)=4 then sum(sa.qty) else 0 end) as qtr4  

       from stores st left join sales sa  

       on st.stor_id=sa.stor_id  

       where datepart(yy,sa.ord_date)=1993  

       group by st.stor_name,sa.ord_date) as a  

group by a.stor_name  

 

 

统计结果如下:

 

每日学习心得:SQL查询表的行列转换/小计/统计(with rollup,with cube,pivot解析)

 

这个时候已经很接近标准答案了,但是还有一个统计行需要统计列出

 

第三步,将第二步统计的结果再和总计的结果union一下就可以实现标准的结果

 

--对每个stor一年的销售明细进行汇总,之后按stor名分组

 

[csharp] 

select a.stor_name as stor_name ,sum(a.total) as total,sum(a.qtr1) as qtr1,  

       sum(a.qtr2) as qtr2,sum(a.qtr3) as qtr3,sum(a.qtr4) as qtr4  

       from  

       (  

       --按时间和stor_name分组统计出对应的stor一年的销售明细  

       select st.stor_name,sum(sa.qty) as total,  

       (case when datepart(qq,sa.ord_date)=1 then sum(sa.qty) else 0 end) as qtr1,  

       (case when datepart(qq,sa.ord_date)=2 then sum(sa.qty) else 0 end) as qtr2,  

       (case when datepart(qq,sa.ord_date)=3 then sum(sa.qty) else 0 end) as qtr3,  

       (case when datepart(qq,sa.ord_date)=4 then sum(sa.qty) else 0 end) as qtr4  

       from stores st left join sales sa  

       on st.stor_id=sa.stor_id  

       where datepart(yy,sa.ord_date)=1993  

       group by st.stor_name,sa.ord_date) as a  

group by a.stor_name  

union  

--汇总统计信息  

select 'total',sum(total),sum(qtr1),sum(qtr2),sum(qtr3),sum(qtr4) from  

    (  

    --每个store一年的销售明细  

      select a.stor_name as stor_name ,sum(a.total) as total,sum(a.qtr1) as qtr1,  

       sum(a.qtr2) as qtr2,sum(a.qtr3) as qtr3,sum(a.qtr4) as qtr4  

       from  

       (  

       select st.stor_name,sum(sa.qty) as total,  

       (case when datepart(qq,sa.ord_date)=1 then sum(sa.qty) else 0 end) as qtr1,  

       (case when datepart(qq,sa.ord_date)=2 then sum(sa.qty) else 0 end) as qtr2,  

       (case when datepart(qq,sa.ord_date)=3 then sum(sa.qty) else 0 end) as qtr3,  

       (case when datepart(qq,sa.ord_date)=4 then sum(sa.qty) else 0 end) as qtr4  

       from stores st left join sales sa  

       on st.stor_id=sa.stor_id  

       where datepart(yy,sa.ord_date)=1993  

       group by st.stor_name,sa.ord_date) as a  

group by a.stor_name  

) as b  

 

 

执行之后就可以得出我们想要的结果。

 

总结一下解题的整个思路,首先看题目要求求出每个店铺每年,每季度的销售统计,同时最后还要有总计行,统计全年/每个季度的销售总额。

 

接着通过case when语句查询出每个商店每年每季度的销售总统计,因为是按商店名和时间分组的,所以在查询出大体的数据结构之后,还需要再对结果进行按商店分组统计,这样就统计出了符合答案要求的数据,最后在将统计出的结果与以结果为基础的再次统计union一下就得出了最终的答案。看起来很复杂的一个查询,只要把思路理清之后一步一步实现就很容易了。

 

虽然我们经过查询实现了题目的要求,但是再让我们回过头来看看我们的查询语句,数据少的时候这样查询还没什么问题,但是如果数据量过大就会有很严重的性能问题,同时,这样的sql查询语句过于庞大,有木有可以优化的方案呢?答案是肯定的。下面就给大家讲一下优化的查询解决方案。

 

1.2 with rollup  + case when count

首先我们的查询思路还是一下的,先用case when语句构建出大体的查询框架,唯一不同的是在group by 之后我们多了with rollup语句。代码如下:

 

[csharp] 

select isnull(stor_name,'total') as stor_name,sum(qty) as total,  

         sum(case when datepart(qq,ord_date)=1 then qty else 0 end) as qtr1,  

         sum(case when datepart(qq,ord_date)=2 then qty else 0 end) as qtr2,  

         sum(case when datepart(qq,ord_date)=3 then qty else 0 end) as qtr3,  

         sum(case when datepart(qq,ord_date)=4 then qty else 0 end) as qtr4  

from stores t inner join sales s on s.stor_id = t.stor_id  

where year(s.ord_date) = '1993'  

group by stor_name with rollup  

 

 

在group by 之后加上with rollup,我们执行一下查询语句,就会发现马上出现了我们想要的结果,这是为什么呢?

 

在生成包含小计和合计的报表时,rollup 运算符很有用。group by子句允许一个将额外行添加到简略输出端 with rollup 修饰符。这些行代表高层(或高聚集)简略操作。rollup 因而允许你在多层分析的角度回答有关问询的问题。或者你可以使用 rollup, 它能用一个问询提供双层分析。将一个 with rollup修饰符添加到group by 语句,使询问产生另一行结果,也就是在上例中采用rollup之后,在按stor_name分组之后,还能检索出本组类的整体聚合信息。

 

如果有多重分组列的情况时,rollup产生的效果更加复杂。这时,每次在除了最后一个分类列之外的任何列出现一个 “break” (值的改变) ,则问讯会产生一个高聚集累计行。

 

1.3 with cube  +  povit

上例中我们讲了使用with rullup来实现统计分组,那么还木有比with rollup 更加简便的查询呢?答案是肯定的。

 

首先我们想按照商店和时间分组统计出每家商店每年/季度的销售情况,这个时候我们需要借助于with cube语句。代码如下:

 

[csharp] 

select isnull(t.stor_name, 'total') as 'stor_name',  

                      isnull(datepart(qq, ord_date),0) as 'qtr', sum(qty) as 'qty'  

         from sales s  

         join stores t on s.stor_id = t.stor_id  

         where year(s.ord_date) = 1993  

         group by datepart(qq, ord_date), t.stor_name with cube  

 

 

执行结果如下:

 

每日学习心得:SQL查询表的行列转换/小计/统计(with rollup,with cube,pivot解析)

 

with cube语句跟with rollup语句作用很相像,它们的区别在于with cube 生成的结果集显示了所选列中值的所有组合的聚合,而with rollup 生成的结果集显示了所选列中值的某一层次结构的聚合

 

第二步,我们将原始数据经过第一步的查询之后转换成了个标准的竖表,下边要做的就是如何将这个竖表转换成横表,我们在上边都是用case when的语法来实现这种表的横竖转换,这里我们换一种方式来实现。这里我们用povit方法来实现。代码如下:

 

[csharp] 

select stor_name, isnull([0],0) as 'total',  

            isnull([1],0) as 'qtr1',isnull([2],0) as 'qtr2',  

            isnull([3],0) as 'qtr3', isnull([4],0) as 'qtr4'  

from  

(  

         select isnull(t.stor_name, 'total') as 'stor_name',  

                     isnull(datepart(qq, ord_date),0) as 'qtr', sum(qty) as 'qty'  

         from sales s  

         join stores t on s.stor_id = t.stor_id  

         where year(s.ord_date) = 1993  

         group by datepart(qq, ord_date), t.stor_name with cube  

) as tmp  

pivot  

(  

         sum(qty) for qtr in ([0], [1], [2], [3], [4])  

) as pvt  

 

 

上边代码示例中高亮部分即为使用pivot进行表的横竖转换的关键代码。

 

pivot用于行转列,在sql server 2000可以用聚合函数配合case语句实现,

 

pivot的一般语法是:pivot(聚合函数(列) for 列 in (…) )as p

 

这跟我们上边示例中使用的高亮标注的部分的方法是一样的

 

 

 

总结:

 

     通过这样一个简单的查询,引出了今天要讲的表的行列转换(case when 和 pivot两种方法),表数据的统计(with rollup 和with cube方法),这也就达到了总结的目的。重要的不是讲这些方法怎么怎么用,主要是讲求解决问题的一个思路,以及在解决问题后对性能及效率的优化,希望可以对大家有些帮助。