当前位置：首页 > news >正文

Oracle练习（2）

news 来源：原创 2024/5/5 1:58:13

有如下三张表：

销售表：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','201601','上海','A',1000);
INSERT INTO SALE_FACT VALUES('C00001','201601','上海','B',5000);
INSERT INTO SALE_FACT VALUES('C00001','201601','上海','C',300);
INSERT INTO SALE_FACT VALUES('C00001','201601','上海','D',800);
INSERT INTO SALE_FACT VALUES('C00004','201601','北京','E',600);
INSERT INTO SALE_FACT VALUES('C00004','201602','长春','F',300);
INSERT INTO SALE_FACT VALUES('C00006','201603','沈阳','G',9000);
INSERT INTO SALE_FACT VALUES('C00007','201604','哈尔滨','H',800);
INSERT INTO SALE_FACT VALUES('C00008','201605','沈阳','I',2200);
INSERT INTO SALE_FACT VALUES('C00008','201606','大连','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','201601','A',5);
INSERT INTO VISIT_FACT VALUES('C00001','201601','B',6);
INSERT INTO VISIT_FACT VALUES('C00001','201601','C',9);
INSERT INTO VISIT_FACT VALUES('C00001','201601','D',22);
INSERT INTO VISIT_FACT VALUES('C00004','201601','E',33);
INSERT INTO VISIT_FACT VALUES('C00004','201602','F',44);
INSERT INTO VISIT_FACT VALUES('C00006','201603','G',100);
INSERT INTO VISIT_FACT VALUES('C00007','201604','H',6);
INSERT INTO VISIT_FACT VALUES('C00008','201605','I',9);
INSERT INTO VISIT_FACT VALUES('C00008','201606','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;

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

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

用distinct选出year_month,对应代码：

SELECT DISTINCT e.year_month FROM SALE_FACT e  ORDER BY 1

接下来的重点是怎么让每个人有每个月的总销售额呢？这里主要用到了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条：

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

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

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

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

相信你，能行的！！！

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

直接来代码吧：

 --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;