Naming Window Clause in SQL Server 2022 Window / Oracle 23c Analytical Functions

One of my favorites in database development is "Analytical Functions" ( Oracle ) and "Window Functions" ( SQL Server / MariaDB).

I have written about these functions in the below mentioned posts. 

https://zahirmohideen.blogspot.com/2009/09/sql-treasures.html

https://zahirmohideen.blogspot.com/2009/10/analytics-20.html

https://zahirmohideen.blogspot.com/2009/11/analytics-20-part-ii.html

https://zahirmohideen.blogspot.com/2012/09/difference-between-percentilecont-and.html

https://zahirmohideen.blogspot.com/2013/01/book-sql-server-window-functions.html

https://zahirmohideen.blogspot.com/2018/10/windowing-clause-why-it-matters.html

In MS SQL Server 2022 , now we have the option of "labelling" or "naming" the Window.

This improved readability , especially , when the same windowing clause is referenced in the query multiple times. 

Here is the sample script to create a table and populate few records.

CREATE TABLE 

    emp

    ( 

        eid        INT ,

        lastname   VARCHAR(50),

        firstname  VARCHAR(50),

        department VARCHAR(50),

        salary     NUMERIC(18,2) ,

        hiredate   DATE 

    );

INSERT INTO 

    emp VALUES 

    ( 

        101 , 

        'Doe' , 

        'Jane', 

        'Research', 

        10000 , 

        '1971-08-16' 

    );

INSERT INTO 

    emp VALUES 

    ( 

        102 , 

        'Doe' , 

        'John', 

        'HR', 

        7500 , 

        '1975-03-09' 

    );

INSERT INTO 

    emp VALUES 

    ( 

        103 , 

        'Amin' , 

        'Al', 

        'Research', 

        10300 , 

        '1985-05-14' 

    );

INSERT INTO 

    emp VALUES 

    ( 

        105 , 

        'Kumar' , 

        'Palani', 

        'Faculty Development', 

        9175 , 

        '1985-05-14' 

    );

Here is the sample table "emp" with 4 records in it. 

Here is the sample table "emp" with 4 records in it. 

In the following example , I am naming each window clause a name and reusing that window wherever possible. 

This simple enhancement leads to readability , easier to understand. 

SELECT 

    e.* ,

    MIN(e.salary) OVER win_dep   AS Lowest_Salary_Department ,

    MIN(e.salary) OVER win_all   AS Lowest_Salary_OverAll,

    MAX(e.salary) OVER win_dep   AS Higher_Salary_Department ,

    MAX(e.salary) OVER win_all   AS Higher_Salary_OverAll,

    first_value(e.hiredate) 

                 OVER win_emp AS First_Hired_Date_Department

FROM 

    emp e 

    WINDOW 

    win_emp AS (PARTITION BY department ORDER BY hiredate ASC),

    win_dep AS (PARTITION BY department ),

    win_All AS ( PARTITION BY (1) );

This is equivalent to 

SELECT 

    e.* ,

    MIN(e.salary) OVER ( 

                    PARTITION BY 

                        department ) AS Lowest_Salary_Department ,

    MIN(e.salary) OVER ( 

                    PARTITION BY 

                        (1) ) AS Lowest_Salary_OverAll,

    MAX(e.salary) OVER ( 

                    PARTITION BY 

                        department 

                    ORDER BY 

                        hiredate ASC) AS Higher_Salary_Department ,

    MAX(e.salary) OVER ( 

                    PARTITION BY 

                        (1) ) AS Higher_Salary_OverAll,

    first_value(e.hiredate) OVER ( 

                              PARTITION BY 

                                  department 

                              ORDER BY 

                                  hiredate ASC) AS First_Hired_Date_Department

FROM 

    emp e;

Updated on 04/18/2024

By looking at the 23c new features , learn that this feature is available in Oracle 23c as well.

Here is the script ( identical to SQL Server 2023) . 

SQL> select banner from v$version ;

BANNER

--------------------------------------------------------------------------------

Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

SQL> desc emp;

 Name    Null?    Type

 ----------------------------------------- -------- ----------------------------

 EID     NUMBER(38)

 LASTNAME     VARCHAR2(50)

 FIRSTNAME     VARCHAR2(50)

 DEPARTMENT     VARCHAR2(50)

 SALARY     NUMBER(18,2)

 HIREDATE     DATE

SQL> select * from emp;

       EID LASTNAME    FIRSTNAME    DEPARTMENT    SALARY HIREDATE

---------- --------------- --------------- -------------------- --------- ---------

       101 Doe    Jane    Research 10000.00 16-AUG-71

       102 Doe    John    HR   7500.00 03-SEP-75

       103 Amin    Al    Research 10300.00 14-MAY-85

       105 Kumar    Palani    Faculty Development   9175.00 14-MAY-85

SQL> SELECT 

    e.* ,

    MIN(e.salary) OVER win_dep   AS Lowest_Salary_Department ,

    MIN(e.salary) OVER win_all   AS Lowest_Salary_OverAll,

    MAX(e.salary) OVER win_dep   AS Higher_Salary_Department ,

    MAX(e.salary) OVER win_all   AS Higher_Salary_OverAll,

    first_value(e.hiredate) 

     OVER win_emp AS First_Hired_Date_Department

FROM 

    emp e 

    WINDOW 

    win_emp AS (PARTITION BY department ORDER BY hiredate ASC),

    win_dep AS (PARTITION BY department ),

    win_All AS ( PARTITION BY (1) );  

       EID LASTNAME    FIRSTNAME    DEPARTMENT    SALARY HIREDATE  LOWEST_SALARY_DEPARTMENT LOWEST_SALARY_OVERALL HIGHER_SALARY_DEPARTMENT HIGHER_SALARY_OVERALL FIRST_HIR

---------- --------------- --------------- -------------------- --------- --------- ------------------------ --------------------- ------------------------ --------------------- ---------

       105 Kumar    Palani    Faculty Development   9175.00 14-MAY-85 9175       7500        9175     10300 14-MAY-85

       102 Doe    John    HR   7500.00 03-SEP-75 7500       7500        7500     10300 03-SEP-75

       101 Doe    Jane    Research 10000.00 16-AUG-71        10000       7500       10300     10300 16-AUG-71

       103 Amin    Al    Research 10300.00 14-MAY-85        10000       7500       10300     10300 16-AUG-71

Comments Welcome!