Terdata 基础 第四课（常用统计方式）

1.总计与小计

  1.1.with by 进行小计

        特点：为明细表创建分类统计，不去除明细，而是在明细后增加一列，by后的字段表示按此类别排序统计，同时可允许多个聚合同时使用。

        SELECT last_name AS NAME
       ,salary_amount AS SALARY
       ,department_number AS DEPT
       FROM employee
       WITH SUM (salary) BY DEPT;

 

返回的报表如下所示：
NAME SALARY DEPT
Stein 29450.00 301
Kanieski 29250.00 301
--------------
Sum(SALARY)58700.00
Johnson 36300.00 401
Trader 37850.00 401
--------------
Sum(SALARY)74150.00
Villegas 49700.00 403
Ryan 31200.00 403
--------------
Sum(SALARY) 80900.00

 

   1.1.1.多聚合计算

    SELECT last_name AS NAME
   ,salary_amount AS SALARY
   ,department_number AS DEPT
    FROM employee
    WITH SUM (salary) (TITLE `Dept Total')
   ,AVG (salary) (TITLE `Dept Avg ') BY DEPT;

 

NAME SALARY DEPT
Stein 29450.00 301
Kanieski 29250.00 301
--------------
Dept Total 58700.00
Dept Avg 29350.00
Johnson 36300.00 401
Trader 37850.00 401
--------------
Dept Total 74150.00
Dept Avg 37075.00
Villegas 49700.00 403
Ryan 31200.00 403
--------------
Dept Total 80900.00
Dept Avg 40450.00

 

   1.1.2.多层小计

       特点：越往后层次越高，类似排在前面的with by是小计，排在后面的是总计，以此类推

SELECT department_number AS Dept
,job_code AS Job
,employee_number AS Emp
,salary_amount AS Sal
FROM employee
WHERE department_number IN (401, 501)
WITH SUM(salary_amount) (TITLE 'Job Total') BY Job
WITH SUM(salary_amount) (TITLE 'DEPT TOTAL') BY Dept;

 

 

返回结果如下：
Dept Job Emp Sal
401 411100 1003 37850.00
------------
Job Total 37850.00
401 412101 1004 36300.00
. 159 .
401 412101 1001 25525.00
401 412101 1010 46000.00
------------
Job Total 107825.00
401 412102 1013 24500.00
401 412102 1022 32300.00
------------
Job Total 56800.00
401 413201 1002 43100.00
------------
Job Total 43100.00
------------
DEPT TOTAL 245575.00

 

    1.1.3.with语句产生最后的总计

SELECT employee_number
,salary_amount
FROM employee
WHERE department_number = 301
WITH SUM(salary_amount) (TITLE 'GRAND TOTAL')
ORDER BY employee_number;

 

返回结果如下：
employee_number salary_amount
1006 29450.00
1008 29250.00
1019 57700.00
-------------
GRAND TOTAL 116400.00

 

    1.1.4.DISTINCT修饰语

SELECT employee_number
,department_number
,manager_employee_number AS manager
FROM employee
WHERE employee_number BETWEEN 1003 AND 1008
WITH COUNT (DISTINCT manager) (TITLE 'TOTAL MANAGERS');

 

则结果为：
employee_number department_number manager
1006 301 1019
1006 301 1019
1005 403 801
1003 401 801
1007 403 1005
1008 301 1019
1004 401 1003
TOTAL MANAGERS 4
 

 

    1.1.5.WITH BY, WITH和ORDER BY的联合使用
假设要显示所有雇员的姓及其工资，按部门进行小计，得出最后总计，并要
求按姓排序，则语句为：
SELECT last_name AS NAME
,salary_amount AS SALARY
,department_number AS DEPT
FROM employee
WITH SUM (SALARY) BY DEPT
WITH SUM (SALARY) (TITLE 'GRAND TOTAL')
ORDER BY NAME;

 

结果如下：
NAME SALARY DEPT
Kanieski 29250.00 301
Stein 29450.00 301
-------------
Sum (SALARY) 58700.00
Johnson 36300.00 401
Trader 37850.00 401
-------------
Sum (SALARY) 74150.00
. 163 .
Ryan 31200.00 403
Villegas 49700.00 403
-------------
Sum (SALARY) 80900.00
-------------
GRAND TOTAL 213750.00

 

 

     1.1.6.WITH和GROUP BY的联合使用，利用WITH和GROUP BY可以建立最后的总计

列出每个部门的工资总计和平均值，最后列出整个公司的工资总额和平均
值，则语句如下：
SELECT department_number (TITLE 'dept_no')
,SUM (salary_amount)
,AVG (salary_amount)
FROM employee
GROUP BY department_number
WITH SUM (salary_amount) (TITLE 'GRAND TOTAL')
,AVG (salary_amount) (TITLE '')
ORDER BY department_number;
 
结果如下：
dept_no SUM (salary_amount) AVG (salary_amount)
301 58700.00 29350.00
401 74150.00 37075.00
403 80900.00 40450.00
--------------------- ----------------------
. 164 .
GRAND TOTAL 213750.00 35635.00

 

      1.1.7.WITH BY和WITH总结
WITH BY WITH都是Teradata扩展的特性，ANSI SQL标准不支持。
WITH BY的基本格式为：
格式：WITH 汇总列表 BY 分类列表ASC/DESC
 
特点如下：
! 为详细数据列表建立总计和小计行
! 汇总列表可以多于一个字段
! 分类列表可以多于一个字段
! 分类列表的列已经隐含了ORDER BY功能
! WITH. . . BY后的相应字段决定了主要的排序键
! 一个 SQL语句可以有多个WITH BY子句
! 第一个 WITH BY排序层次最低
! ORDER BY用来指定其它附加的子排序，指在某个字段内的进一步排序，
如部门内再按薪资高低来进一步排序。
 
WITH的基本格式为：
格式：WITH 汇总列表
 
WITH的特点如下：
! 提供最终或总体的总计
! 汇总列表可以多于一个字段