ISO/IEC TR 19075-9:2020

Information technology database languages - Guidance for the use of database language SQL - Part 9: Online analytic processing (OLAP) capabilities

This document discusses the syntax and semantics for including online analytic processing (OLAP) capabilities in SQL, as defined in ISO/IEC 9075-2. It discusses the following features regarding OLAP capabilities of the SQL language: - Feature T611, "Elementary OLAP operations", - Feature T612, "Advanced OLAP operations", - Feature T614, "NTILE function", - Feature T615, "LEAD and LAG functions", - Feature T616, "Null treatment option for LEAD and LAG functions", - Feature T617, "FIRST_VALUE and LAST_VALUE functions", - Feature T618, "NTH_VALUE function", - Feature T619, "Nested window functions", - Feature T620, "WINDOW clause: GROUPS option", - Feature T621, "Enhanced numeric functions".

Langages de bases de données utilisés dans les technologies de l'information — Recommandations pour l'utilisation du langage de base de données SQL — Partie 9: Capacités de traitement analytique en ligne

General Information

• Status: Withdrawn
• Publication Date: 17-Sep-2020
• ICS: 35.060 - Languages used in information technology
• Technical Committee: ISO/IEC JTC 1/SC 32 - Data management and interchange
• Drafting Committee: ISO/IEC JTC 1/SC 32 - Data management and interchange
• Current Stage: 9599 - Withdrawal of International Standard
• Start Date: 03-Oct-2022
• Completion Date: 30-Oct-2025

Relations

• Consolidated By: ISO 25457:2023 - Oil and gas industries including lower carbon energy - Flare details for general refinery and petrochemical service
• Effective Date: 10-Sep-2022
• Revised: ISO/IEC 19075-9:2022 - Information technology - Guidance for the use of database language SQL - Part 9: Online analytic processing (OLAP) capabilities (Guide/OLAP)
• Effective Date: 06-Jun-2022

Standards Content (Sample)

TECHNICAL ISO/IEC TR
REPORT 19075-9
First edition
2020-09
Information technology database
languages — Guidance for the use of
database language SQL —
Part 9:
Online analytic processing (OLAP)
capabilities
Reference number
©
ISO/IEC 2020
© ISO/IEC 2020
All rights reserved. Unless otherwise specified, or required in the context of its implementation, no part of this publication may
be reproduced or utilized otherwise in any form or by any means, electronic or mechanical, including photocopying, or posting
on the internet or an intranet, without prior written permission. Permission can be requested from either ISO at the address
below or ISO’s member body in the country of the requester.
ISO copyright office
CP 401 • Ch. de Blandonnet 8
CH-1214 Vernier, Geneva
Phone: [phone]
Email: [e-mail]
Website: www.iso.org
Published in Switzerland
ii © ISO/IEC 2020 – All rights reserved

ISO/IECTR19075-9:2020(E)
Contents Page
Foreword.vi
Introduction.viii
1 Scope.1
2 Normativereferences.2
3 Termsanddefinitions.3
4 Example data.4
4.1 Introductiontoexampledata.4
4.2 Tablesaleshistory.4
4.3 Tablestock1.5
4.4 Tablestocks.6
4.5 Tablehomes.6
5 Windows.8
5.1 Introductiontowindows.8
5.2 Windowdefinitions.9
5.2.1 IntroductiontoWindowdefinitions.9
5.2.2 Windowpartitioning.9
5.2.3 Window ordering.10
5.2.3.1 Introductiontowindowordering.10
5.2.3.2 Nullorderingandtreatment.12
5.2.4 Windowframes.12
5.2.4.1 Introductiontowindowframes.12
5.2.4.2 Physicalwindowframes.13
5.2.4.3 Logicalwindowframes.15
5.2.4.3.1 Introductiontologicalwindowframes.15
5.2.4.3.2 RANGEwindowframes.15
5.2.4.3.3 GROUPSwindowframes.16
5.2.4.4 Windowframeexclusions.17
5.3 Explicitvsimplicitwindowdefinitions.19
5.4 Multiplewindowdefinitions.19
6 Window functions.21
6.1 Introductiontowindowfunctions.21
6.2 Rank functions.21
6.3 Distribution functions.22
6.4 Rownumberfunction.23
6.5 Windowaggregatefunctions.24
6.6 Ntilefunction.27
6.7 LEADandLAGfunctions.28
6.8 FIRST_VALUEandLAST_VALUEfunctions.30
©ISO/IEC2020–Allrightsreserved iii

ISO/IECTR19075-9:2020(E)
6.9 NTH_VALUE function.31
6.9.1 Nulltreatment.33
7 Nestedwindowfunctions.34
7.1 Introductiontonestedwindowfunctions.34
7.2 Rowmarkers.35
7.3 Offsets.36
7.4 FRAME_ROW.37
7.5 NestedROW_NUMBERfunction.38
7.6 EffectsofEXCLUDE.40
8 Enhancedaggregatefunctions.41
8.1 Introductiontoenhancedaggregatefunctions.41
8.2 Unarystatisticalaggregatefunctions.41
8.3 Binarystatisticalaggregatefunctions.41
8.4 Hypotheticalrankanddistributionaggregatefunctions.43
8.5 Inversedistributionfunctions.43
Bibliography.46
Index.47
iv ©ISO/IEC2020–Allrightsreserved

ISO/IECTR19075-9:2020(E)
Tables
Table Page
1 Tablesales_history.4
2 Table stock1.5
3 Tablestocks.6
4 Table homes.7
5 Resultofwindowclause.10
6 Resultofwindowclauseordering.11
7 Resultofphysicalwindowframe.13
8 Resultofphysicalwindowframe.14
9 Resultoflogicalwindowclause.15
10 Resultofwindowframe.16
11 Resultofwindowframeexclusion1.17
12 Resultofwindowframeexclusion2.18
13 ResultofRANKandDENSE_RANKfunction.22
14 ResultofPERCENT_RANKandCUME_DISTfunctions.23
15 ResultofROW_NUMBERfunction.24
16 Resultofaggregatefunction(SUM)ordered.25
17 Resultofaggregatefunction(SUM)unordered.25
18 Resultofaggregatefunction(AVG).26
19 Resultofaggregatefunction(NTILE).27
20 Resultofaggregatefunction(NTILE).28
21 Resultofaggregatefunction(LEAD).29
22 Resultofaggregatefunction(LAG).29
23 Resultofaggregatefunction(FIRST_VALUE).30
24 Resultofaggregatefunction(LAST_VALUE).31
25 Resultofaggregatefunction(NTH_VALUE).32
26 Resultofrowmarkers.35
27 Resultofrowmarkers(offsets).36
28 Resultofwindowframeswithrowmarkers.37
29 ResultofEXCLUDE.40
30 Resultofhypotheticalaggregatefunctions.43
31 Resultofinversedistributionfunctions.44
32 Resultofinversedistributionfunctionswithordering.44
©ISO/IEC2020–Allrightsreserved v

ISO/IECTR19075-9:2020(E)
Foreword
ISO(theInternationalOrganizationforStandardization)andIEC(theInternationalElectrotechnical
Commission)formthespecializedsystemforworldwidestandardization.Nationalbodiesthatare
membersofISOorIECparticipateinthedevelopmentofInternationalStandardsthroughtechnical
committeesestablishedbytherespectiveorganizationtodealwithparticularfieldsoftechnicalactivity.
ISOandIECtechnicalcommitteescollaborateinfieldsofmutualinterest.Otherinternationalorganizations,
governmentalandnon-governmental,inliaisonwithISOandIEC,alsotakepartinthework.
Theproceduresusedtodevelopthisdocumentandthoseintendedforitsfurthermaintenanceare
describedintheISO/IECDirectives,Part1.Inparticular,thedifferentapprovalcriterianeededforthe
differenttypesofdocumentshouldbenoted.Thisdocumentwasdraftedinaccordancewiththeeditorial
rulesoftheISO/IECDirectives,Part2(seewww.iso.org/directives).
Attentionisdrawntothepossibilitythatsomeoftheelementsofthisdocumentmaybethesubjectof
patentrights.ISOandIECshallnotbeheldresponsibleforidentifyinganyorallsuchpatentrights.Details
ofanypatentrightsidentifiedduringthedevelopmentofthedocumentwillbeintheIntroductionand/or
ontheISOlistofpatentdeclarationsreceived(seewww.iso.org/patents),ortheIEClistofpatent
declarationsreceived(seehttp://patents.iec.ch).
Anytradenameusedinthisdocumentisinformationgivenfortheconvenienceofusersanddoesnot
constituteanendorsement.
Foranexplanationofthevoluntarynatureofstandards,themeaningofISOspecifictermsandexpressions
relatedtoconformityassessment,aswellasinformationaboutISO'sadherencetotheWorldTrade
Organization(WTO)principlesintheTechnicalBarrierstoTrade(TBT)seewww.iso.org/iso/fore-
word.html.
ThisdocumentwaspreparedbyTechnicalCommitteeISO/IECJTC1,Informationtechnology,Subcom-
mitteeSC32,Datamanagementandinterchange.
ThisdocumentisintendedtobeusedinconjunctionwiththefollowingeditionsofthepartsofISO/IEC
9075:
— ISO/IEC9075-1,sixtheditionorlater
— ISO/IEC9075-2,sixtheditionorlater
— ISO/IEC9075-3,sixtheditionorlater
— ISO/IEC9075-4,seventheditionorlater
— ISO/IEC9075-9,fiftheditionorlater
— ISO/IEC9075-10,fiftheditionorlater
— ISO/IEC9075-11,fiftheditionorlater
— ISO/IEC9075-13,fiftheditionorlater
— ISO/IEC9075-14,sixtheditionorlater
— ISO/IEC9075-15,secondeditionorlater
— ISO/IEC9075-16,firsteditionorlater
AlistofallpartsintheISO/IEC19075seriescanbefoundontheISOwebsite.
vi ©ISO/IEC2020–Allrightsreserved

ISO/IECTR19075-9:2020(E)
Anyfeedbackorquestionsonthisdocumentshouldbedirectedtotheuser'snationalstandardsbody.A
completelistingofthesebodiescanbefoundatwww.iso.org/members.html.
©ISO/IEC2020–Allrightsreserved vii

ISO/IECTR19075-9:2020(E)
Introduction
Thisdocumentdiscussesthesyntaxandsemanticsforincludingonlineanalyticprocessing(OLAP)
capabilitiesinSQL,asdefinedinISO/IEC9075-2.
Theorganizationofthisdocumentisasfollows:
1) Clause1,“Scope”,specifiesthescopeofthisdocument.
2) Clause2,“Normativereferences”,identifiesstandardsthatarereferencedaspartofrequirements
bythisdocument.
3) Clause3,“Termsanddefinitions”,definesthetermsanddefinitionsusedinthisdocument.
4) Clause5,“Windows”,discussesFeatureT611,“ElementaryOLAPoperations”andFeatureT612,
“AdvancedOLAPoperations”,introducingtheconceptofawindowinanSQLquery.
5) Clause6,“Windowfunctions”,furtherdiscussesFeatureT611,“ElementaryOLAPoperations”and
FeatureT612,“AdvancedOLAPoperations”,aswellasFeatureT614,“NTILEfunction”,FeatureT615,
“LEADandLAGfunctions”,FeatureT616,“NulltreatmentoptionforLEADandLAGfunctions”,Feature
T617,“FIRST_VALUEandLAST_VALUEfunctions”,andFeatureT618,“NTH_VALUEfunction”.
6) Clause7,“Nestedwindowfunctions”,discussestheadditionalwindowfunctionalityinFeatureT619,
“Nestedwindowfunctions”.
7) Clause8,“Enhancedaggregatefunctions”,discussesFeatureT621,“Enhancednumericfunctions”
anditsintroductionofenhancedaggregatefunctionsinSQL.
viii ©ISO/IEC2020–Allrightsreserved

TECHNICAL REPORT ISO/IEC TR 19075-9:2020(E)
Information technology — Guidance for the use of database language SQL —
Part 9:
Online analytic processing (OLAP) capabilities
1 Scope
This document discusses the syntax and semantics for including online analytic processing (OLAP)
capabilities in SQL, as defined in ISO/IEC 9075-2.
It discusses the following features regarding OLAP capabilities of the SQL language:
— Feature T611, “Elementary OLAP operations”,
— Feature T612, “Advanced OLAP operations”,
— Feature T614, “NTILE function”,
— Feature T615, “LEAD and LAG functions”,
— Feature T616, “Null treatment option for LEAD and LAG functions”,
— Feature T617, “FIRST_VALUE and LAST_VALUE functions”,
— Feature T618, “NTH_VALUE function”,
— Feature T619, “Nested window functions”,
— Feature T620, “WINDOW clause: GROUPS option”,
— Feature T621, “Enhanced numeric functions”.
©ISO/IEC2020–Allrightsreserved 1

ISO/IECTR19075-9:2020(E)
2 Normativereferences
Therearenonormativereferencesinthisdocument.
2 ©ISO/IEC2020–Allrightsreserved

ISO/IECTR19075-9:2020(E)
3 Termsanddefinitions
Notermsanddefinitionsarelistedinthisdocument.
ISOandIECmaintainterminologicaldatabasesforuseinstandardizationatthefollowingaddresses:
— ISOOnlinebrowsingplatform:availableathttp://www.iso.org/obp
— IECElectropedia:availableathttp://www.electropedia.org/
©ISO/IEC2020–Allrightsreserved 3

ISO/IECTR19075-9:2020(E)
4.1 Introductiontoexampledata
4 Exampledata
4.1 Introductiontoexampledata
Theexamplesinthisdocumentarebasedonseveraltables.
Theorderinwhichtherowsofallsampletablesaredisplayedisimmaterial.
4.2 Tablesaleshistory
Subclause4.2,“Tablesaleshistory”,containsinformationonabusinessspreadoverseveralterritories
withtotalsalesaccumulatedmonthlyineachterritory.Table1,“Tablesales_history”,showssampledata
forSubclause4.2,“Tablesaleshistory”:
Table1—Tablesales_history
Territory Month Sales
East 199812 11
West 199811 12
West 199901 11
East 199811 4
East 199810 10
West 199810 8
East 199902 10
East 199901 7
West 199812 7
West 199902 6
SQLtocreateandpopulateSubclause4.2,“Tablesaleshistory”.
CREATE TABLE Sales_History
(Territory CHARACTER (10),
Month INTEGER,
Sales INTEGER)
INSERT INTO Sales_History VALUES ('East', 199812, 11)
INSERT INTO Sales_History VALUES ('West', 199811, 12)
INSERT INTO Sales_History VALUES ('West', 199901, 11)
INSERT INTO Sales_History VALUES ('East', 199811, 4)
INSERT INTO Sales_History VALUES ('East', 199810, 10)
INSERT INTO Sales_History VALUES ('West', 199810, 8)
INSERT INTO Sales_History VALUES ('East', 199902, 10)
4 ©ISO/IEC2020–Allrightsreserved

ISO/IECTR19075-9:2020(E)
4.2 Tablesaleshistory
INSERT INTO Sales_History VALUES ('East', 199901, 7)
INSERT INTO Sales_History VALUES ('West', 199812, 7)
INSERT INTO Sales_History VALUES ('West', 199902, 6)
4.3 Tablestock1
Thenextexamplesaretwovariantsofastocktablecontaininginformationonstocktransactionsfora
particularaccount.ColumnsinTable2,“Tablestock1”,includetransactionID,tradeday,andtype,as
wellastheshareamountandtickersymbol.Subclause4.4,“Tablestocks”,coversthecolumnsticker,
tradeday,andprice.
Table2—Tablestock1
Acno Tid Tradeday TType Amount Ticker
123 1 1 buy 1000 csco
123 2 1 buy 400 inpr
123 3 2 buy 2000 symc
123 4 2 buy 1200 csco
123 5 2 buy 500 inpr
123 6 4 buy 200 csco
123 7 4 buy 100 csco
123 9 5 buy 400 inpr
123 10 5 buy 200 goog
123 11 5 buy 1000 inpr
123 12 5 buy 4000 inpr
123 13 8 buy 2000 hpq
SQLtocreateandpopulateTable2,“Tablestock1”.
CREATE TABLE Stock1
(Acno INTEGER,
Tid INTEGER,
Tradeday INTEGER,
TType CHARACTER (10),
Amount INTEGER,
Ticker CHARACTER (10))
INSERT INTO Stock1 VALUES (123, 1, 1, 'buy', 1000, 'csco')
INSERT INTO Stock1 VALUES (123, 2, 1, 'buy', 400, 'inpr')
INSERT INTO Stock1 VALUES (123, 3, 2, 'buy', 2000, 'symc')
INSERT INTO Stock1 VALUES (123, 4, 2, 'buy', 1200, 'csco')
INSERT INTO Stock1 VALUES (123, 5, 2, 'buy', 500, 'inpr')
INSERT INTO Stock1 VALUES (123, 6, 4, 'buy', 200, 'csco')
©ISO/IEC2020–Allrightsreserved 5

ISO/IECTR19075-9:2020(E)
4.3 Tablestock1
INSERT INTO Stock1 VALUES (123, 7, 4, 'buy', 100, 'csco')
INSERT INTO Stock1 VALUES (123, 9, 5, 'buy', 400, 'inpr')
INSERT INTO Stock1 VALUES (123, 10, 5, 'buy', 200, 'goog')
INSERT INTO Stock1 VALUES (123, 11, 5, 'buy', 1000, 'inpr')
INSERT INTO Stock1 VALUES (123, 12, 5, 'buy', 4000, 'inpr')
INSERT INTO Stock1 VALUES (123, 13, 8, 'buy', 2000, 'hpq')
4.4 Tablestocks
Table3—Tablestocks
Ticker Tradeday Price
ZYX 1 10
ZYX 2 11
ZYX 3 12
ZYX 4 12
ZYX 5 12
ZYX 6 11
ZYX 7 12
ZYX 8 12
SQLtocreateandpopulateSubclause4.4,“Tablestocks”.
CREATE TABLE Stocks
(Ticker CHARACTER (10),
Tradeday INTEGER,
Price INTEGER)
INSERT INTO Stocks VALUES ('ZYX', 1, 10)
INSERT INTO Stocks VALUES ('ZYX', 2, 11)
INSERT INTO Stocks VALUES ('ZYX', 3, 12)
INSERT INTO Stocks VALUES ('ZYX', 4, 12)
INSERT INTO Stocks VALUES ('ZYX', 5, 12)
INSERT INTO Stocks VALUES ('ZYX', 6, 11)
INSERT INTO Stocks VALUES ('ZYX', 7, 12)
INSERT INTO Stocks VALUES ('ZYX', 8, 12)
4.5 Tablehomes
ThefinalexampleisTable4,“Tablehomes”,containingdataconcerninghousepricesandlocations.
6 ©ISO/IEC2020–Allrightsreserved

ISO/IECTR19075-9:2020(E)
4.5 Tablehomes
Table4—Tablehomes
Area Address Price
Uptown 15PeekabooSt. 456,000
Uptown 27PrimrosePath 341,000
Uptown 44ShadyLane 341,000
Uptown 23301Highway61 244,000
Uptown 34DesolationRd. 244,000
Uptown 77SunsetStrip 102,000
Downtown 72EasySt. 509,000
Downtown 29WongWay 201,000
Downtown 45DiamondLane 201,000
Downtown 76BlindAlley 201,000
Downtown 15TernPike 199,000
Downtown 444KangaRua 102,000
SQLtocreateandpopulateTable4,“Tablehomes”.
CREATE TABLE Homes
(Area CHARACTER (10),
Address CHARACTER (20),
Price INTEGER)
INSERT INTO Homes VALUES ('Uptown', '15 Peekaboo St.', 456000)
INSERT INTO Homes VALUES ('Uptown', '27 Primrose Path', 341000)
INSERT INTO Homes VALUES ('Uptown', '44 Shady Lane', 341000)
INSERT INTO Homes VALUES ('Uptown', '23301 Highway 61', 244000)
INSERT INTO Homes VALUES ('Uptown', '34 Desolation Rd.', 244000)
INSERT INTO Homes VALUES ('Uptown', '77 Sunset Strip', 102000)
INSERT INTO Homes VALUES ('Downtown', '72 Easy St.', 509000)
INSERT INTO Homes VALUES ('Downtown', '29 Wong Way', 201000)
INSERT INTO Homes VALUES ('Downtown', '45 Diamond Lane', 201000)
INSERT INTO Homes VALUES ('Downtown', '76 Blind Alley', 201000)
INSERT INTO Homes VALUES ('Downtown', '15 Tern Pike', 199000)
INSERT INTO Homes VALUES ('Downtown', '444 Kanga Rua', 102000)
©ISO/IEC2020–Allrightsreserved 7

ISO/IECTR19075-9:2020(E)
5.1 Introductiontowindows
5 Windows
5.1 Introductiontowindows
SQLinFeatureT611,“ElementaryOLAPoperations”andFeatureT612,“AdvancedOLAPoperations”of
ISO/IEC9075-2addssupportforonlineanalyticalprocessing(OLAP).Theextensionsarepartsofthe
SELECTcommand.
OLAPisconcernedwithdataaggregationacrossgroupingcriteriatogeneratevaluessuchassubtotals
andtotalsonmultiplelevels.Groupingcriteriaareoftencalleddimensions.OLAPisbasedontheconcept
ofmultipledimensionsandnavigationacrosstheaggregationlevelsaswellastheaccumulateddata.
OLAPisusedinapplicationssuchasanalyticsandreporting.Itcanbeusediniterativefashionstrying
outdifferentgroupingcriteriaanddifferentsubsetsofthedatatobeanalyzed.
Todealwiththeserequirements,thefeaturesmentionedaboveintroducea“WINDOW”facilitythatcan
presentaggregatedcontentasarollingwindow,orderedandgroupedbythespecifiedcriteria.Seethe
exampleinSubclause5.2.2,“Windowpartitioning”,Table5,“Resultofwindowclause”.
Thedatacanbetreatedlikeotherresultsets,and/orcanbeusedforfurtherprocessing.
Aquerycontainsaselectlistandatableexpression.Thetableexpressionproducesaresultset;callit
RT.TheselectlistisevaluatedbyapplyingitsexpressionstoeachrowinRT.WithouttheOLAPfeatures,
theselectlistexpressionscanonly“see”thatonecurrentrow,makingitimpossibletocomputevalues
thatrelyonvaluesfromotherrowsinRT.Suchcomputationscanbesimulatedonlybyarrangingforthe
necessaryvaluestobeincludedasadditionalcolumnsinRT,whichmaybeinconvenientorimpossible.
Theconceptofthewindowedtablealleviatesthislimitationbyaddingwindowstotheresultofthetable
expression.OnewaytothinkofawindowmightbetoimaginethatitisatransientcopyofRT,including
anindicationofthecurrentrow.(Thisisjustaconceptualdevice;animplementationneednotactually
copyRT.)Thistransientcopycanbelogicallyre-arrangedaccordingtoasortordering,apartitioning,or
both;itcanalsobelimitedtoasubsetofrows,viawindowframing.Theserearrangementsofthewindow's
rowstakeplacewithoutaffectingRTitself.Multiplewindowsmayexist,eachwithitsownindependently
appliedordering,partitioning,and/orframingspecifications.Windowsmaybedefinedeitherinthenew
windowclause,orin-lineinindividualwindowfunctionspecificationsintheselectlist.
Partitioningand/ororderingmaybeusedtocomputesuchresultsasranking,Ntiles,andotheranalytic
functions.Theframespecifieswhichrowsofapartition,relativetothecurrentrow,shouldparticipate
inthecalculationofanOLAPfunction.Throughframes,windowssupportsuchimportantOLAPcapabil-
itiesascumulativesumsandmovingaverages.
Orderinginwindowsisspecifiedwiththesamesortspecificationlistusedbycursorsandelsewherein
theSELECTstatement,andwiththesamesemantics.OrderingasenhancedfortheOLAPcapabilitiesof
SQLalsoincludesuserspecifiedcontroloftheorderingofnulls.Althoughorderingofrowsmaybenon-
deterministicwithinawindow,thesamenondeterministicorderingisusedinwindowsthathave
equivalentpartitioningandorderingclausesspecified.
8 ©ISO/IEC2020–Allrightsreserved

ISO/IECTR19075-9:2020(E)
5.2 Windowdefinitions
5.2 Windowdefinitions
5.2.1 IntroductiontoWindowdefinitions
Theisanadditionalsyntaxelementofthequeryexpressionand,ifspecified,follows
the,,,and.Aswiththeotherclauses
ofthequeryexpression,thewindowclauseappliestotheresultoftheprecedingclauses.Thewindow
clauseconsistsofacommaseparatedlistofwindowdefinitions.Eachofthesehasaname,forreference
byOLAPfunctionsintheselectlist,andawindowspecification.Forconvenience,awindowdefinition
mayalsobecodedinlineintheOLAPfunctionspecification.Clause6,“Windowfunctions”,describesthe
variousOLAPfunctionsavailableforuse,andwhichcombinationsofwindowspecificationdetailclauses
arepermittedforeach.
Thewindowspecificationmaycontainanyorallofapartitioning,ordering,andframedefinition.For
example:
WINDOW tms AS
(PARTITION BY territory -- window partitioning
ORDER BY month, sales) -- window ordering
Awindowfunctioncanrefertothedefinedwindowbyname,forinstance:
SELECT RANK() OVER tms AS the_rank, .
FROM .
WINDOW tms AS . as above .
Or,implicitlybyspecifyingthewindowdefinitiondirectlyin-line:
SELECT RANK() OVER (PARTITION BY territory
ORDER BY month, sales) AS the_rank, .
FROM .
whichhastheidenticalresult.
5.2.2 Windowpartitioning
Theoptional“windowpartitionclause”specifiesapartitioningoftheresultsetgeneratedbythepreceding
from,where,groupbyandhavingclauses.Likethegroupbyclause,thewindowpartitionclauseisa
comma-separatedlistofcolumnreferencesusedtogrouprowsforsubsequentprocessing.However,
unlikethegroupbyclause,eachinputrowtoawindowpartitioningisretainedintheresultset.This
permitstheintroductionofanalyticalfunctionsthatoperateontheindividualrowsofapartition.The
“collateclause”optionallowscharactercolumnstobepartitionedbasedonanamedcollation.Ifthere
isnowindowpartitionclause,thentheentireresultsetofthecontainingqueryconstitutesasinglepar-
tition.
Noticethatalthoughthewindowpartitionclauseissimilartothegroupbyclause,itisnotthesamething.
Thedifferenceisthatthegroupingspecifiedbyagroupbyclausecollapseseachgrouptoasinglerow
initsresultset.Thepartitioningspecifiedbyawindowpartitionclausedoesnotcollapsethepartitions
toasinglerow.Rather,thewindowpartitionofarowRisthecollectionofrowsthatarenotdistinctfrom
R,forallcolumnsenumeratedinthewindowpartitioningclause.
Example1,“windowclause”,showstheeffectofpartitioningSales_historyusingtheTerritorycolumn
inthedefinition.
©ISO/IEC2020–Allrightsreserved 9

ISO/IECTR19075-9:2020(E)
5.2 Windowdefinitions
Example1—windowclause
SELECT Territory, Month, Sales FROM Sales_history
WINDOW w_eg1 AS (PARTITION BY Territory)
— WINDOW w_eg1 identifiesthewindowdefinitionsothatitmaybereferencedinoneormoreOLAP
functionselsewhereinthequery( w_eg1 isthewindow'sname).
— PARTITION BY introducesthepartitioning.Apartitioningissimplyalistofoneormorecolumnson
whichthedataispartitioned.
Table5—Resultofwindowclause
Territory Month Sales
East 199901 7 ⎫
East 199811 4 ⎪
East 199810 10 ⎬"East"partition
East 199812 11 ⎪
East 199902 10 ⎭
West 199811 12 ⎫
West 199810 8 ⎪
West 199902 6 ⎬"West"partition
West 199901 11 ⎪
West 199812 7 ⎭
TherowsofTable5,“Resultofwindowclause”,areclusteredtoshowtheeffectofthepartitioning,but
inreality,theresultrowsmaystillappearinanysequence.Ifaresultsetisdesiredtobeorderedonone
ormorecolumns,anorderbyclausemustbeusedinthecontainingqueryexpression.
5.2.3 Windowordering
5.2.3.1Introductiontowindowordering
Thenextoptionalelementofawindowdefinitionisthe“windoworderclause”.Itconsistsofa“sort
specificationlist”thatissyntacticallythesameastheonefoundinan“orderby”clauseofthequery
expression.
Whetherinawindoworaqueryexpression,asortspecificationlistspecifiesanorderingofrows.The
differenceisthat,inaqueryexpression,theorderingdeterminesthesequenceofrowsintheresultset
ofthequeryexpression.Inawindow,theorderinghelpstodeterminethevalueoforder-dependent
OLAPfunctionssuchasranking.Anotherdifferenceisthatthequeryexpressionorderingappliestoall
resultrows,whereasawindoworderingisappliedtoeachpartitionseparately.
10 ©ISO/IEC2020–Allrightsreserved

ISO/IECTR19075-9:2020(E)
5.2 Windowdefinitions
Astherowsofeachpartitionareordered,itmayturnoutthatmultiplerowsarepeers;thatis,theyhave
thesamevaluesineachoftheelementsofthesortspecificationlistaccordingtoorderingsemantics.A
windoworderinggroupisamaximalsetofrows(perhapsjustone)inapartitionthatarepeersaccording
tothewindowordering.Someanalyticfunctions(RANK,forinstance)operateonallrowsinanordering
grouporsetoforderinggroups,whereasothers(suchasNTH_VALUE)mayoperateonindividualrows
regardlessoftheirparticipationinanorderinggroup.Theorderingofrowswithinasinglewindow
orderinggroupisimplementation-dependent,henceintroducingthepotentialfornon-determinismin
someOLAPfunctions.Distinctorderinggroupswithinapartitionareofcourseorderedaccordingtothe
sortspecificationlistofthewindoworderclause.
Ifthereisnowindoworderclause,theneachpartitioncontainsasinglewindoworderinggroupconsisting
ofalltherowsinthepartitionandallrowsarepeers.
Example2,“windowclauseordering”,showstheeffectoforderingSales_historyusingtheSalescolumn
incombinationwiththepartitioningofExample1,“windowclause”.
Example2—windowclauseordering
SELECT Territory, Month, Sales FROM Sales_history
WINDOW w_eg2 AS (PARTITION BY Territory
ORDER BY Month ASC)
— w_eg2 isthenameofthewindowdefinedbythisexample.
— Inadditiontothepartitioning, ORDER BY introducestheorderingofrowswithineachpartition.
Thesyntaxisidenticaltotheorderbyclauseofaqueryexpression,includingtheoptionalnull
orderingspecification.
Table6—Resultofwindowclauseordering
Territory Month Sales
East 199810 10 ⎫
East 199811 4 ⎪
East 199812 11 ⎬"East"partition
East 199901 7 ⎪
East 199902 10 ⎭
West 199810 8 ⎫
West 199811 12 ⎪
West 199812 7 ⎬"West"partition
West 199901 11 ⎪
West 199902 6 ⎭
Onceagain,therowsofTable6,“Resultofwindowclauseordering”,areorganizedtoshowtheeffectof
thewindowspecificationandhowtheyareoperatedonbythevariousOLAPfunctions.However,the
resultoftheapplicationofthewindowspecificationandsubsequentOLAPfunctionsdoesn'tassureany
particularresultrowsequence.Ifaresultsetisdesiredtobeorderedononeormorecolumns,anorder
byclausemustbeusedinthecontainingqueryexpression.
©ISO/IEC2020–Allrightsreserved 11

ISO/IECTR19075-9:2020(E)
5.2 Windowdefinitions
5.2.3.2Nullorderingandtreatment
FeatureT611,“ElementaryOLAPoperations”alsointroducedthe“nullordering”optiontothesort
specificationlistofboththewindoworderclauseandtheexistingorderbyclause.Withoutit,nullvalues
maybesortedaheadofallvaluedinstancesofacolumn,orafterallvaluedinstances.Whichoptionis
chosenbydefaultisimplementation-defined.Withthenullorderingoption,theusermayexplicitly
requestusingNULLSFIRSTorNULLSLASTthatnullvaluesofanorderingcolumnbesortedaheador
followingthevaluedinstances.Insomecasestheabilitytoexplicitlydefinethenullorderingisnecessary
forproducingthecorrectresultofananalyticalfunction.
Unlikeordinaryaggregatefunctions,windowfunctionsdonotnecessarilyignoreNULLvalues.The
ROW_NUMBERfunction,forinstance,isfundamentallyarowcountingfunctionoverawindowandas
suchdoesnotcareaboutNULLvalues.Somewindowfunctions,suchasLAG,LEAD,FIRST_VALUE,
LAST_VALUE,andNTH_VALUE,permitexplicitchoiceofnulltreatment:onecanignorenullsorrespect
nulls,asrequiredbythedesiredoutcome.
5.2.4 Windowframes
5.2.4.1Introductiontowindowframes
ThewindowframeofarowRinawindowpartitionisamultisetofrows,definedrelativetoRinthe
orderingoftherowsofR'spartition.Windowframesareusedtospecifymultisetsofrowsonwhichto
performwindoworaggregatefunctions,suchasSUMorAVGoroneofseveralotherOLAP-specific
functions.
Therearethreewaystospecifythescopeofawindowframe,givenacurrentrowR:rowcounting,value
offset,ororderinggroupcounting.
1) ThekeywordROWSsaystoincludethespecifiednumberofrowsbeforeorafterR.Thisdefinesa
physicalwindowframe.
2) ThekeywordRANGErequiresthatthepartitionbeorderedonasingleorderingkey,ofatypethat
canhaveavalueaddedorsubtracted(thus,numeric,datetime,orinterval);onespecifiesasortkey
valueoffset,andthewindowframeextendstoanyorderinggroupscontainingvaluesintherange
betweenthatoffsetandthesortkeyvalueinrowR.Thisdefinesalogicalwindowframe.
3) ThekeywordGROUPSrequiresthatthepartitionbeordered(possiblyonmultiplesortkeys),and
saystoincludethespecifiednumberoforderinggroupsbeforeoraftertheorderinggroupcontaining
R.Thisalsodefinesalogicalwindowframe.
Thustherearetwocomponentsinthespecificationofaframe,bothmandatory:
1) ThechoiceofthekeywordROWS,RANGE,orGROUPS,toindicatehowtheextentofthewindow
frameistobedetermined.
2) Anindicationofthestartingrowandendingrowoftheframe.Notethattheendingrowoftheframe
mayfollowtherowcurrentlybeingprocessed.Thisenablesfunctionstobecomputedover“look
ahead”data,averyvaluablepropertyforOLAPprocessing.Thewindowframeextentsyntaxmay
consistofanyofthefollowingelements:
— UNBOUNDEDPRECEDINGtorequestallrows,rangesorgroupsprecedingthecurrentrow.
— nPRECEDINGtorequestthenrows,rangesorgroupsprecedingthecurrentrow.
— nFOLLOWINGtorequestthenrows,rangesorgroupsfollowingthecurrentrow.
12 ©ISO/IEC2020–Allrightsreserved

ISO/IECTR19075-9:2020(E)
5.2 Windowdefinitions
— UNBOUNDEDFOLLOWINGtorequestallrows,rangesorgroupsfollowingthecurrentrow.
— BETWEENstartANDendtorequestallrows,rangesorgroupsbetweenthestartandenddesignations.
— startorendmayalsobeCURRENTROWtostartorendtheframewiththecurrentrow.
Iftheframespecificationwouldextendtheframepastthebeginningorendofapartition,theframewill
betruncatedtothepartitionboundary.
Ifthereisneitherawindowframeclausenorawindoworderingclause,thenthereisanimplicitwindow
frameconsistingofallrowsinthepartition.Ifthereisnowindowframeclause,butawindowordering
clauseispresent,thenthereisanimplicitwindowframewhichcontainsalltherowsofthewindow
partitionofRthatprecedeRorarepeersofRinthewindoworderingofthewindowpartitiondefined
bythewindoworderingclause.
5.2.4.2Physicalwindowframes
Aphysicalwindowframeisdefinedbycountingsomenumberofrowsfromthecurrentrow.Physical
windowframesaredefinedbythekeywordROWS.Forphysicalframingtoproduceasensibleresult
frommanywindowfunctions,therehastobeaknownandpredictablenumberofinputrowsforeach
setofvaluesofinterest.Gapsorunexpectedmultiplerowswouldthrowofftheresultormakeitnon-
deterministic.
Forinstance,supposeonewantedamovingaverageof3calendarmonthsfromthesales_historyexample
table.Onecoulddefineaframeas"ROWSBETWEEN2PRECEDINGANDCURRENTROW",butthatwill
onlyproduceameaningfulrunningaverageifthereisexactlyonesales_historyrowforeachcalendar
month.Iftherearemissingmonths,thecalendarmonthmovingaveragewillbeincorrect.
Similarly,physicalframingappliedtodatawithmultiplerowswiththesameorderingvaluemayproduce
non-deterministicresults.Rowswithinanorderinggroupareorderedinanimplementation-dependent
way,soaphysicalframethatdoesnotcovertheentireorderinggroupisnotguaranteedtoreturn
deterministicresults.
Ontheotherhand,physicalframingdoesnotplaceanyrestrictionsonwindowordering(itdoesnoteven
requirewindowordering),andmaybethesimplestwaytoframedatathathasawelldefinedprogression
ofvalues.
Examplesofphysicalwindowframesfollow.
Example3—physicalwindowframe1
SELECT Territory, Month, Sales FROM Sales_history
WINDOW w_eg3 AS (PARTITION BY Territory
ORDER BY Month ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
— w_eg3 isthewindownameofthisexample.
— Foreachrowofeachpartition,thewindowframesyntaxusesthekeyword ROWS todesignatethe
framesasphysical.Eachframeconsistsofthecurrentrowandallprecedingrowsinthepartition.
Table7—Resultofphysicalwindowframe
Territory Month Sales
East 199810 10 ⎫
©ISO/IEC2020–Allrightsreserved 13

ISO/IECTR19075-9:2020(E)
5.2 Windowdefinitions
Territory Month Sales
East 199811 4 ⎬framefor"East",199901
East 199812 11 ⎪
East 199901 7 ⎭
East 199902 10
West 199810 8 ⎫
West 199811 12 ⎭framefor"West",199811
West 199812 7
West 199901 11
West 199902 6
Example4—physicalwindowframe2
SELECT Territory, Month, Sales FROM Sales_history
WINDOW w_eg4 AS (PARTITION BY Territory
ORDER BY Month ASC
ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING)
— w_eg4 isthewindownameofthisexample.
— Foreachrowofeachpartition,thewindowframesyntaxusesthekeyword ROWS todesignatethe
framesasphysical.Eachframeconsistsofthecurrentrow,the2rowsthatprecedeitandtherow
thatfollowsit.
Table8—Resultofphysicalwindowframe
Territory Month Sales
East 199810 10 ⎫
East 199811 4 ⎬framefor"East",199812
East 199812 11 ⎪
East 199901 7 ⎭
East 199902 10
West 199810 8 ⎫
West 199811 12 ⎬framefor"West",199811
West 199812 7 ⎭
West 199901 11
West 199902 6
14 ©ISO/IEC2020–Allrightsreserved

ISO/IECTR19075-9:2020(E)
5.2 Windowdefinitions
Notethattheframefor"West",199811onlycontains3rowssincethereisonly1precedingrowinthe
partition.
5.2.4.3Logicalwindowframes
5.2.4.3.1 Introductiontologicalwindowframes
Logicalwindowframesdonotcountrows,rather,theframeisdefinedbasedonvaluesinthedata.A
logicalframingiseitherlookingforavaluerange,orcountingorderinggroups.Assuch,logicalwindow
framesrequireawindowordering.FramingbyvaluerangeusesthekeywordRANGE,whileframingby
orderinggroupsusesthekeywordGROUPS.
5.2.4.3.2 RANGEwindowframes
AlogicalwindowframedefinedbytheRANGEkeywordmayhaveonlyasinglesortkeyinitsordering
clause.Theremustbeaquantitywhichcanbeaddedtoandsubtractedfromthesortkeytodefinethe
startingorendingpointofaframe.Aframeisthendefinedbyvalueoffsetsprecedingandfollowingthe
currentrow.Thismeansthatthedatatypemustbenumeric,datetimeorinterval,andthattheoffset
mustbeanumericliteraloracompatibleintervalliteral.
ConsiderasimilarSales_historytable,withsomemonthsmissingfromthedata,andtheMonthcolumn
storedasadatevaluecontainingthefirstdayofeachmonth.Thefollowingexampledemonstratesthe
benefitsofRANGEwindowframes:
Example5—logicalrangewindowframe
SELECT Territory, Month, Sales FROM Similar_sales_history
WINDOW w_eg5 AS (PARTITION BY Territory
ORDER BY Month ASC
RANGE INTERVAL '02' MONTH PRECEDING)
— w_eg5 isthewindownameinthisexample.
— Foreachrowofeachpartition,thewindowframesyntaxusesthekeyword RANGE todesignatethe
frameaslogicaldefinedbyarangeofvaluesintheorderingcolumn(theremayonlybeoneordering
column). INTERVAL '02' MONTH istheliteralthatdefinestherange.
— TheabsenceofafollowingclausemeansthateachframewillincludetheCURRENTROW.
Table9—Resultoflogicalwindowclause
Territory Month Sales
East 1998-10-01 10 ⎫framefor"East",1998-12-01
East 1998-12-01 11 ⎭
East 1999-01-01 7
East 1999-02-01 10
©ISO/IEC2020–Allrightsreserved 15

ISO/IECTR19075-9:2020(E)
5.2 Windowdefinitions
Territory Month Sales
West 1998-10-01 8 ⎫
West 1998-11-01 12 ⎬framefor"West",1998-12-01
West 1998-12-01 7 ⎭
West 1999-02-01 6
Notethattheframefor"East",1998-12-01onlycontains2rowssincethereisnorowfor"East",1998-
11-01.
5.2.4.3.3 GROUPSwindowframes
AlogicalwindowframedefinedbytheGROUPSkeywordmayhaveanynumberofsortkeysinits
orderingclause.Likewise,thereisnolimitationonthedatatypesofthesortkeys.Aframeisdefinedby
countingsomenumberoforderinggroupsfromtheorderinggroupthatcontainsthecurrentrow.The
useofGROUPSsyntaxovercomestheweaknessesofphysicalwindowframesinthattheorderingdata
doesnothavetobedenseandtheresultsaredeterministic.Rowswithinanorderinggroupmayhavea
non-deterministicorder,butthegroupsasawholearedeterministicallyordered.Resultsofanyfunctions
executedongroupedwindowsarethenalsodeterministic.Likewise,theyovercometheweaknessesof
RANGEdefinedlogicalwindowsbypermittingmultiplesortkeyswithnotyperestrictions.
ThefollowingexampledemonstratestheuseofGROUPSwindowframes:
Example6—logicalwindowframewithGROUPS
SELECT Acno, Tid, Tradeday, TType, Amount, Ticker FROM Stock1
WINDOW w_eg6 AS (PARTITION BY Acno
ORDER BY Tradeday
GROUPS BETWEEN 2 PRECEDING AND CURRENT ROW)
— w_eg6 isthewindownameinthisexample.
— Foreachrowofeachpartition,thewindowframesyntaxusesthekeyword GROUPS todesignate
theframeaslogicaldefinedby3orderinggroups—theonecontainingthecurrentrowandthe2
precedingorderinggroups.
— Notethat,despitethe CURRENT ROW designation,rowsthatareinthesameorderinggroupasthe
currentrowbuthaveyettobeprocessed,areconsideredtobeintheframe.
Table10—Resultofwindowframe
Acno Tid Tradeday TType Amount Ticker
123 1 1 buy 1000 csco ⎫
123 2 1 buy 400 inpr ⎪
123 3 2 buy 2000 symc ⎬frameforTday2(same
frameforTid's3,4and5)
123 4 2 buy 1200 csco ⎪
16 ©ISO/IEC2020–Allrightsreserved

ISO/IECTR19075-9:2020(E)
5.2 Windowdefinitions
Acno Tid Tradeday TType Amount Ticker
123 5 2 buy 500 inpr ⎭
123 6 4 buy 200 csco ⎫
123 7 4 buy 100 csco ⎪
123 9 5 buy 400 inpr ⎪
123 10 5 buy 200 goog ⎬frameforTday8
123 11 5 buy 1000 inpr ⎪
123 12 5 buy 4000 inpr ⎪
123 13 8 buy 2000 hpq ⎭
5.2.4.4Windowframeexclusions
Syntaxalsoallowstheidentificationofrowstobespecificallyexcludedfromwindowframesinwhich
theywouldotherwiseparticipate.Optionsmaybeusedtoexcludethecurrentrow,thecurrentordering
grouporallrowsthataretiesofthecurrentrow.Thisaddsadegreeoffinetuningtothespecification
ofwindowframes.
Thefollowingexamplesshowtheeffectofwindowframeexclusionsyntax:
Example7—windowframeexclusion1
SELECT Territory, Month, Sales FROM Sales_history
WINDOW w_eg7 AS (PARTITION BY Territory
ORDER BY Month ASC
RANGE INTERVAL '02' MONTH PRECEDING
EXCLUDE CURRENT ROW)
— w_eg7 isthewindownameinthisexample,
— Thisisthesameastheearlierexampledefining w_eg5 ,withtheexceptionthatthecurrentrowis
excludedfromeachframe.
Table11—Resultofwindowframeexclusion1
Territory Month Sales
East 1998-10-01 10 --framefor"East",
East 1998-12-01 11 ⎫framefor"East",
East 1999-01-01 7 ⎭
East 1999-02-01 10
©ISO/IEC2020–Allrightsreserved 17

ISO/IECTR19075-9:2020(E)
5.2 Windowdefinitions
Territory Month Sales
West 1998-10-01 8 ⎫framefor"West",
West 1998-11-01 12 ⎭
West 1998-12-01 7
West 1999-02-01 6
Example8—windowframeexclusion2
SELECT Acno, Tid, Tradeday, TType, Amount, Ticker FROM Stock1
WINDOW w_eg8 AS (PARTITION BY acno
ORDER BY Tradeday
GROUPS BETWEEN 2 PRECEDING AND CURRENT ROW
EXCLUDE TIES)
— w_eg8 isthewindownameinthisexample.
— Thisexampleisthesameasearlierexampledefining w_eg6 withtheexceptionthatonlythecurrent
rowisincludedintheframefromtheorderinggroupthatcontainsit(Tid's4and5areomittedfrom
theframeforTid3andTid's10,11,and12areomittedfromtheframeforTid9).
Table12—Resultofwindowframeexclusion2
Acno Tid Tradeday TType Amount Ticker
123 1 1 buy 1000 csco ⎫
123 2 1 buy 400 inpr ⎬frameforTid3
123 3 2 buy 2000 symc ⎫⎭
123 4 2 buy 1200 csco ⎪
123 5 2 buy 500 inpr ⎬frameforTid9
123 6 4 buy 200 csco ⎪
123 7 4 buy 100 csco ⎪
123 9 5 buy 400 inpr ⎭
123 10 5 buy 200 goog
123 11 5 buy 1000 inpr
123 12 5 buy 4000 inpr
123 13 8 buy 2000 hpq
TheEXCLUDEGROUPoptionisusedtoexcludetheentireorderinggroupofthecurrentrowfromaframe,
andappliestoanytypeofwindowframe(ROWS,RANGE,orGROUPS).
18 ©ISO/IEC2020–Allrightsreserved

ISO/IECTR19075-9:2020(E)
5.3 Explicitvsimplicitwindowdefinitions
5.3 Explicitvsimplicitwindowdefinitions
Thewindowdefinitionsdiscussedthusfarhavebeenexplicitlydeclaredusingthewindowdefinition
clauseinthetableexpression.Analyticfunctionsmayreferenceadefinedwindowexplicitlybynameor
mayincludetheelementsofthewindowdefinitionsyntaxinthefunctionspecification.Suchwindows
areimplicitlydefinedandtheonlydifferenceintheirspecificationistheabsenceofawindowname.
Eitherformofwindowdefinitionmaybeused,althoughexplicitwindowdefinitionsareausefulfeature
whenmorethanoneanalyticfunctionistobeexecutedonthesamewindow.Ineithercase,thesemantics
offunctionexecutionarethesame.
Exampleforanexplicitwindowdefinition:
Example9—explicitwindowdefinition
SELECT Territory, Month, Sales,
SUM (Sales) OVER Win1 AS Sums
FROM Sales_history
WINDOW Win1 AS (PARTITION BY Territory ORDER BY Month)
Exampleforthesamewindowsdefinitionasanimplicitwindowdefinition:
Example10—implicitwindowdefinition
SELECT Territory, Month, Sales,
SUM (Sales) OVER (PARTITION BY Territory ORDER BY Month) AS Sums
FROM Sales_history
5.4 Multiplewindowdefinitions
Agivenquerymayhaveanynumberofwindowdefinitions,explicitand/orimplicit.Moreover,one
windowdefinitionmayreferenceanotherexplicitwindowdefinition,withtheresultingwindowcontaining
acombinationofthespecificationsofeach.Thisistrueofpurelyexplicitwindowdefinitionsorofmixtures
ofexplicitandimplicitwindowdefinitions.So,awindowfunctionmayreferenceanexplicitwindow
definition,thenfillinsomeofthewindowpropertiesastheyapplytotheparticularfunction.More
specifically,ifawindowdefinitionxreferencesanexistingwindowdefinitiony:
— xmaynotincludeawindowpartitionspecification.
— Ifyincludesawindoworderingspecification,xmaynotincludeawindoworderingspecification.
— ymaynotincludeawindowframespecification.
Thiscapabilitypermitstheeasierdefinitionofmultiplewindowsthathavesomecommonelements.For
example:
Example11—multiplewindowdefinitions
SELECT Territory, Month, Sales FROM Sales_history
WINDOW w_eg9a AS (PARTITION BY Territory),
w_eg9b AS (w_eg9a ORDER BY Month),
w_eg9c AS (w_eg9b ROWS 2 PRECEDING),
w_eg9d AS (w_eg9b GROUPS 1 PRECEDING EXCLUDE TIES)
— Anyofwindows w_eg9a , w_eg9b , w_eg9c ,and/or w_eg9d maybeusedindifferentfunctions
inthecontainingquery.
— w_egc and w_egd usethesamepartitioningandordering,butdefinedifferentwindowframes.
©ISO/IEC2020–Allrightsreserved 19

-------------
...