Oracle练习（2）

有如下三张表：

销售表：SALE_FACT

工号	年月	城市	客户	销售额
C00001	201601	上海	A	1000
C00001	201601	上海	B	5000
C00001	201601	上海	C	300
C00001	201601	上海	D	800
C00004	201601	北京	E	600
C00004	201602	长春	F	300
C00006	201603	沈阳	G	9000
C00007	201604	哈尔滨	H	800
C00008	201605	沈阳	I	2200
C00008	201606	大连	J	1200

员工表：DIM_EMP

工号	姓名
C00001	张三
C00002	李四
C00003	王五
C00004	赵六
C00005	林七
C00006	钱八
C00007	宋十
C00008	李白
C00009	陆游
C00010	王林

拜访表：VISIT_FACT

工号	年月	客户	拜访次数
C00001	201601	A	5
C00001	201601	B	6
C00001	201601	C	9
C00001	201601	D	22
C00004	201601	E	33
C00004	201602	F	44
C00006	201603	G	100
C00007	201604	H	6
C00008	201605	I	9
C00008	201606	J	8

问题如下：

1.查出每个员工每个月的总销售额

2.查出每个员工每个月的总拜访次数

3.查出每个员工每个城市的总销售额

4.查出全年销售额最大的员工

5.列出全年销售额从大到小排序员工姓名及其全年销售额

6.列出每个员工当年的销售额及其当年总的拜访次数

7.查出拜访次数最多的员工的全年销售额

下面我们先进行前期表和数据的准备

1.创建销售事实表：

CREATE TABLE SALE_FACT(

     EMPNO NVARCHAR2(10)

    ,YEAR_MONTH CHAR(6)

    ,CITY VARCHAR(10)

    ,CLIENT VARCHAR(10)

    ,SALES NUMBER);

2.往销售事实表中插入数据：

--往销售事实表中插入数据

INSERT INTO SALE_FACT VALUES('C00001','','上海','A',1000);

INSERT INTO SALE_FACT VALUES('C00001','','上海','B',5000);

INSERT INTO SALE_FACT VALUES('C00001','','上海','C',300);

INSERT INTO SALE_FACT VALUES('C00001','','上海','D',800);

INSERT INTO SALE_FACT VALUES('C00004','','北京','E',600);

INSERT INTO SALE_FACT VALUES('C00004','','长春','F',300);

INSERT INTO SALE_FACT VALUES('C00006','','沈阳','G',9000);

INSERT INTO SALE_FACT VALUES('C00007','','哈尔滨','H',800);

INSERT INTO SALE_FACT VALUES('C00008','','沈阳','I',2200);

INSERT INTO SALE_FACT VALUES('C00008','','大连','J',1200);

3.创建员工维度表：

CREATE TABLE DIM_EMP(

  EMPNO NVARCHAR2(10)

 ,EMPNAME NVARCHAR2(10));

4.往员工维度表插入数据：

--往员工维度表插入数据

INSERT INTO DIM_EMP VALUES('C00001','张三');

INSERT INTO DIM_EMP VALUES('C00002','李四');

INSERT INTO DIM_EMP VALUES('C00003','王五');

INSERT INTO DIM_EMP VALUES('C00004','赵六');

INSERT INTO DIM_EMP VALUES('C00005','林七');

INSERT INTO DIM_EMP VALUES('C00006','钱八');

INSERT INTO DIM_EMP VALUES('C00007','宋十');

INSERT INTO DIM_EMP VALUES('C00008','李白');

INSERT INTO DIM_EMP VALUES('C00009','陆游');

INSERT INTO DIM_EMP VALUES('C00010','王林');

5.创建员工拜访事实表：

CREATE TABLE VISIT_FACT(

  EMPNO NVARCHAR2(10)

 ,YEAR_MONTH CHAR(6)

 ,CLIENT NVARCHAR2(10)

 ,VISIT_COUNT NUMBER);

6.往拜访事实表插入数据：

--往拜访事实表插入数据

INSERT INTO VISIT_FACT VALUES('C00001','','A',5);

INSERT INTO VISIT_FACT VALUES('C00001','','B',6);

INSERT INTO VISIT_FACT VALUES('C00001','','C',9);

INSERT INTO VISIT_FACT VALUES('C00001','','D',22);

INSERT INTO VISIT_FACT VALUES('C00004','','E',33);

INSERT INTO VISIT_FACT VALUES('C00004','','F',44);

INSERT INTO VISIT_FACT VALUES('C00006','','G',100);

INSERT INTO VISIT_FACT VALUES('C00007','','H',6);

INSERT INTO VISIT_FACT VALUES('C00008','','I',9);

INSERT INTO VISIT_FACT VALUES('C00008','','J',8);

查出每个员工每个月的总销售额

这里特别注意，每个员工，每个月，想想一共会有几条数据？？比如说有10个员工，一年12个月，求每个员工每个月的总销售额，会有几条数据呢？

当然是10*12=120条了，而这里我们考虑1至6月，而销售事实表中有些员工根本没有销售数据（如：C00002 、C00003 等），也有些员工不是每个月都有销售数据（如：C00001只有1月有销售数据，其他月份没有销售数据），换句话说，就是：数据不全，不能保证每个员工每个月都有销售额，所以为了查出的数据完整，我们需要补全这些数据并且没有就按0来算。

首先我们要做的是把所有员工都拉到销售表中来，对应的就是和员工表join,同时对每个员工相同月份的销售额进行求和，对应代码如下：

SELECT

  t2.empname

 ,t1.year_month

 ,SUM(NVL(t1.sales,0))

 FROM SALE_FACT t1

 RIGHT JOIN DIM_EMP t2

 ON t1.empno=t2.empno

 GROUP BY  t2.empnamE ,t1.year_month

 ORDER BY 1;

Oracle练习（2）-LMLPHP

　　这里所有员工都被拉进来了，并且李白和赵六有两个月的销售额，其他人只有一个月或者一个月也没有。

然后再对每个员工每个月进行补充数据，我们先要选出所有的日期，这里我们从销售表中选出日期，一般情况下各个公司会有一个日期维度表，这里我们

用distinct选出year_month,对应代码：

SELECT DISTINCT e.year_month FROM SALE_FACT e  ORDER BY 1

Oracle练习（2）-LMLPHP

接下来的重点是怎么让每个人有每个月的总销售额呢？这里主要用到了partition by方法，

好了，前期日期表和所有员工部分月的总销售额表都准备好了，开始吧：

WITH all_emp_part_date AS(

     SELECT

     t2.empname

     ,t1.year_month

     ,SUM(NVL(t1.sales,0)) sumsal

     FROM SALE_FACT t1

     RIGHT JOIN DIM_EMP t2

     ON t1.empno=t2.empno

     GROUP BY  t2.empnamE ,t1.year_month

     ORDER BY 1),

    temp_date AS(

     SELECT  DISTINCT e.year_month FROM SALE_FACT e  ORDER BY 1)

   SELECT

      t1.empname

     ,t2.year_month

     ,NVL(t1.sumsal,0) as mounthSales

    FROM all_emp_part_date t1

   partition by (t1.empname)   --对日期进行稠化

   right join temp_date t2

   on t1.year_month=t2.year_month;

注意：这里的重点是对数据实现补充，或者说稠化数据吧，详细方法可以看看数据稠化

最终的结果，一共是60条：

Oracle练习（2）-LMLPHP

好了，这样每个员工，每个月的总销售额以及可以完整的显示了，那么

每个员工，每个城市，每个月的总销售额呢？

你肯定知道的是：返回结果数=员工数*城市数*月数

每个员工，每个城市，每个客户，每个月的总销售额呢？

相信你，能行的！！！

好了，下一个题目，查出每个员工每个月的总拜访次数，是不是和第一题差不多呢？

直接来代码吧：

 --2.查出每个员工每个月的总拜访次数

WITH all_emp_part_date AS(

     SELECT

      t2.empname

     ,t1.year_month

     ,SUM(NVL(t1.visit_count,0)) sumvis

     FROM VISIT_FACT t1

     RIGHT JOIN DIM_EMP t2

     ON t1.empno=t2.empno

     GROUP BY  t2.empname ,t1.year_month

     ORDER BY 1),

    temp_date AS(

     SELECT  DISTINCT e.year_month FROM SALE_FACT e  ORDER BY 1)

   SELECT

      t1.empname

     ,t2.year_month

     ,NVL(t1.sumvis,0) as mounthVisit

    FROM all_emp_part_date t1

   partition by (t1.empname)

   right join temp_date t2

   on t1.year_month=t2.year_month;

第三题，查出每个员工每个城市的总销售额，依旧如此，把月份替换城市，代码如下：

--查出每个员工每个城市的总销售额

WITH all_emp_part_city AS(

     SELECT

     t2.empname

     ,t1.city

     ,SUM(NVL(t1.sales,0)) sumsal

     FROM SALE_FACT t1

     RIGHT JOIN DIM_EMP t2

     ON t1.empno=t2.empno

     GROUP BY  t2.empname,t1.city

     ORDER BY 1),

     temp_city AS(

          SELECT DISTINCT t1.city FROM SALE_FACT t1)

     SELECT

       t1.empname

      ,t2.city

      ,NVL(t1.sumsal,0)

      FROM all_emp_part_city t1

     PARTITION BY (t1.empname)

     RIGHT JOIN temp_city t2

     ON t1.city=t2.city;

第四题：查出全年销售额最大的员工

这里是查出全年销售额，先sum一下，在求出最大的值以及员工

 WITH RANK_SAL AS

   ( SELECT t2.empname,NVL(SUM(t1.sales),0) as sumsal FROM SALE_FACT t1

    right join  DIM_EMP t2

    ON t1.empno=t2.empno

    GROUP BY t2.empname)

    SELECT * FROM RANK_SAL t1 WHERE t1.sumsal = (select max(t1.sumsal) from RANK_SAL t1);

第五题：列出全年销售额从大到小排序员工姓名及其全年销售额

--5.列出全年销售额从大到小排序员工姓名及其全年销售额

 SELECT t2.empname,NVL(SUM(t1.sales),0) sumsal FROM SALE_FACT t1

    right join  DIM_EMP t2

    ON t1.empno=t2.empno

    GROUP BY t2.empname order by sumsal desc;

带排名

SELECT

     t2.empname

     ,NVL(SUM(t1.sales),0) as sumsal

     ,dense_rank() over(order by NVL(SUM(t1.sales),0) desc )

     FROM SALE_FACT t1

    right join  DIM_EMP t2

    ON t1.empno=t2.empno

    GROUP BY t2.empname;

第六题：列出每个员工当年的销售额及其当年总的拜访次数

 WITH SALACCOUNT AS

  (SELECT

    t1.empname

   ,t1.empno

   ,sum(t2.sales) salsum

   FROM DIM_EMP t1

   LEFT JOIN SALE_FACT t2

   on t1.empno=t2.empno

   group by t1.empname,t1.empno)

   SELECT t1.empname,nvl(t1.salsum,0) , nvl(sum(t2.visit_count),0)  FROM  SALACCOUNT t1

   left join VISIT_FACT t2

   on t2.empno=t1.empno group by t1.empname,t1.salsum;