oracle 窗口子句的分析功能与方法

ffvjumwh 于 2022-11-22 发布在 Oracle

关注(0)|答案(2)|浏览(135)

我正在使用Oracle和SQL Developer。我已经下载了HR模式，需要使用它进行一些查询。现在我正在使用表Employees。作为一个用户**，我需要查看其工资与相应部门中所有后来雇用的同事的平均工资之间差距最大的雇员**。这看起来很有趣，但确实很复杂。我阅读了一些文档并尝试过，例如LEAD（），可同时存取数据表的多个数据列：

SELECT
    employee_id,
    first_name
    || ' '
    || last_name,
    department_id,
    salary,
    hire_date,
    LEAD(hire_date)
    OVER(PARTITION BY department_id
         ORDER BY
             hire_date DESC
    ) AS Prev_hiredate
FROM
    employees
ORDER BY
    department_id,
    hire_date;

这表明了部门中每个人的雇用日期和后来被雇用的人。我也试着用窗口子句来理解它的概念：

SELECT
    employee_id,
    first_name
    || ' '
    || last_name,
    department_id,
    hire_date,
    salary,
    AVG(salary)
    OVER(PARTITION BY department_id
         ORDER BY
             hire_date ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
    ) AS avg_sal
FROM
    employees
ORDER BY
    department_id,
    hire_date;

此查询的结果为：

但是，这并不是我所需要的。我需要通过添加gap（salary-avr_sal）列来减少结果，其中差距最大，每个部门接收一个雇员。结果应该是这样的：例如，我们有60个部门。2我们有5个雇员，按雇用日期排序。3第一个雇员薪金为4800，第二个为9000，第三个为4800，第四个为4200，第五个为6000。4如果我们进行计算：4800 -（（9000+4800+4200+6000）/4）= -1200，9000-（（4800+4200+6000）/3）=4000，4800 -（（4200+6000）/2）= -300，4200 - 6000=-1800部门中最后一个人的差距最大：6000 - 0 = 6000.让我们看看20个部门.我们有两个人在那里：第一个有工资13000，第二- 6000。计算：13000 - 6000 = 7000，6000 - 0 = 6000。第一个人员的差距最大。因此，对于部门20，结果应为薪金为13000的人员，对于部门60，结果应为薪金为6000的人员，依此类推。应如何查看我的查询以获得适当的结果（我需要的是粗体标记的，我还想查看差距最大的列，可以使用解析函数获得不同的解决方案，但必须包括窗口子句）？

oracle

来源：https://stackoverflow.com/questions/74424476/analytic-functions-and-means-of-window-clause

2条答案

按热度按时间

ifmq2ha21#

只需修改 avg 的 rows 子句，就可以获得 * * 早于 * * 当前雇员雇用的雇员的平均薪金：

AVG(salary) OVER(
        PARTITION BY department_id
        ORDER BY hire_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS avg_salary

中的每一个
1 PRECEDING 子句告诉数据库不要在窗口中包含当前行 .
如果您要查找与平均值差距最大的员工，我们只需 order by 结果集：

SELECT e.*,
    AVG(salary) OVER(
        PARTITION BY department_id
        ORDER BY hire_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS avg_salary
FROM employees e
ORDER BY ABS(salary - avg_salary) DESC;

格式
最后，如果您想要每个部门的最高 “ 离群值工资 ” ，则至少还需要一个层次。最简单的表达方式可能是使用 ROW_NUMBER() ，根据每个部门中员工的工资与平均工资的差距来排列员工的等级，然后使用 WITH TIES 撷取每个群组的所有最高数据列：

SELECT *
FROM (
    SELECT e.*,
        AVG(salary) OVER(
            PARTITION BY department_id
            ORDER BY hire_date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS avg_salary
    FROM employees e
) e
ORDER BY ROW_NUMBER() OVER(
    PARTITION BY department_id 
    ORDER BY ABS(salary - avg_salary) DESC
)
FETCH FIRST ROW WITH TIES

格式

赞(0）回复(0）举报 2022-11-22

gzszwxb42#

也许这就是你要找的。
示例数据：

WITH
    emp (ID, EMP_NAME, HIRE_DATE, SALARY, DEPT) AS 
        (
            Select 601, 'HILLER',   To_Date('23-JAN-82', 'dd-MON-yy'),  4800,  60 From Dual Union All
            Select 602, 'MILLER',   To_Date('23-FEB-82', 'dd-MON-yy'),  9000,  60 From Dual Union All
            Select 603, 'SMITH',    To_Date('23-MAR-82', 'dd-MON-yy'),  4800,  60 From Dual Union All
            Select 604, 'FORD',     To_Date('23-APR-82', 'dd-MON-yy'),  4200,  60 From Dual Union All
            Select 605, 'KING',   To_Date('23-MAY-82', 'dd-MON-yy'),    6000,  60 From Dual Union All
            Select 201, 'SCOT',   To_Date('23-MAR-82', 'dd-MON-yy'), 13000,  20 From Dual Union All
            Select 202, 'JONES',    To_Date('23-AUG-82', 'dd-MON-yy'),  6000,  20 From Dual 
        ),

中的每一个
创建具有多个分析函数和窗口子句的 CTE 命名网格。它们并非都是必需的，但下面生成的数据集显示了包含所有组件的逻辑。

grid AS
        (
          Select 
              g.*, Max(GAP) OVER(PARTITION BY DEPT) "DEPT_MAX_GAP"
          From
              (
                  Select  
                      ROWNUM "RN",
                      Sum(1) OVER(Partition By DEPT Order By DEPT, HIRE_DATE, ID ROWS BETWEEN Unbounded Preceding And Current Row) "RN_DEPT",
                      ID, EMP_NAME, HIRE_DATE, DEPT, SALARY,
                      --
                      Nvl(Sum(SALARY) OVER(Partition By DEPT Order By DEPT, HIRE_DATE, ID ROWS BETWEEN 1 Following And Unbounded Following), 0) "SUM_SAL_LATER",
                      Nvl(Sum(1) OVER(Partition By DEPT Order By DEPT, HIRE_DATE, ID ROWS BETWEEN 1 Following And Unbounded Following), 0) "COUNT_EMP_LATER",
                      --
                      Nvl(Sum(SALARY) OVER(Partition By DEPT Order By DEPT, HIRE_DATE, ID ROWS BETWEEN 1 Following And Unbounded Following) /
                      Sum(1) OVER(Partition By DEPT Order By DEPT, HIRE_DATE, ID ROWS BETWEEN 1 Following And Unbounded Following), 0) "AVG_LATER",
                      --
                      SALARY - 
                      Nvl((
                      Sum(SALARY) OVER(Partition By DEPT Order By DEPT, HIRE_DATE, ID ROWS BETWEEN 1 Following And Unbounded Following) /
                      Sum(1) OVER(Partition By DEPT Order By DEPT, HIRE_DATE, ID ROWS BETWEEN 1 Following And Unbounded Following)
                      ), 0)  "GAP"
                  from 
                      emp
                  Order By
                      DEPT, HIRE_DATE, ID
              ) g
          Order By
              RN
        )

格式
CTE 网格结果数据集：
| 注册号码|RN _ 部门|识别码|员工姓名|聘用日期|部门|薪金|SAL _ LATER 总和|稍后计数|平均 _ 稍后|间隙|部门 _ 最大 _ 间隙|
| - -| - -| - -| - -| - -| - -| - -| - -| - -| - -| - -| - -|
| 一个|一个|六零一|希勒|一九八二年一月二十三日|六十|小行星 4800|小行星 24000|四个|六千|-1200 年|六千|
| 2 个|2 个|六零二|米勒|一九八二年二月二十三日|六十|九千|一万五千|三个|五千|四千|六千|
| 三个|三个|六百零三|史密斯|一九八二年三月二十三日|六十|小行星 4800|小行星 10200| 2 个|小行星 5100|零下 300 度|六千|
| 四个|四个|小行星 604|福特|一九八二年四月二十三日|六十|小行星 4200|六千|一个|六千|-1800 年|六千|
| 五个|五个|六百零五|国王|一九八二年五月二十三日|六十|六千|第 0 页|第 0 页|第 0 页|六千|六千|
| 六个|一个|二百零一|斯科特|一九八二年三月二十三日|二十个|小行星 13000|六千|一个|六千|七千|七千|
| 七个|2 个|二百零二|琼斯|一九八二年八月二十三日|二十个|六千|第 0 页|第 0 页|第 0 页|六千|七千|
主 SQL

SELECT
    g.ID, g.EMP_NAME, g.HIRE_DATE, g.DEPT, g.SALARY, g.GAP
FROM
    grid g
WHERE
    g.GAP = g.DEPT_MAX_GAP
Order By 
    RN

格式
结果为：
| 识别码|员工姓名|聘用日期|部门|薪金|间隙|
| - -| - -| - -| - -| - -| - -|
| 六百零五|国王|一九八二年五月二十三日|六十|六千|六千|
| 二百零一|斯科特|一九八二年三月二十三日|二十个|小行星 13000|七千|

- 如果没有 CTE ，并且排除了所有不必要的列，则如下所示： * *

SELECT    ID, EMP_NAME, HIRE_DATE, DEPT, SALARY, GAP
FROM      
    (
        ( Select g.*, Max(GAP) OVER(PARTITION BY DEPT) "DEPT_MAX_GAP"
          From(   Select  
                      ID, EMP_NAME, HIRE_DATE, DEPT, SALARY,
                      SALARY - 
                      Nvl(( Sum(SALARY) OVER(Partition By DEPT Order By DEPT, HIRE_DATE, ID ROWS BETWEEN 1 Following And Unbounded Following) /
                            Sum(1) OVER(Partition By DEPT Order By DEPT, HIRE_DATE, ID ROWS BETWEEN 1 Following And Unbounded Following)
                          ), 0)  "GAP"
                  From emp
                  Order By DEPT, HIRE_DATE, ID
              ) g
        )    
    )
WHERE     GAP = DEPT_MAX_GAP
Order By  DEPT, HIRE_DATE, ID

格式
看起来这就是你所需要的。
此致

赞(0）回复(0）举报 2022-11-22

我来回答

oracle 窗口子句的分析功能与方法

2条答案

相关问题

热门标签

最新问答