ISO/IEC TR 19075-9:2020
(Main)Information technology database languages — Guidance for the use of database language SQL — Part 9: Online analytic processing (OLAP) capabilities
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
Relations
Buy Standard
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 TR 19075-9:2020(E)
©
ISO/IEC 2020
---------------------- Page: 1 ----------------------
ISO/IEC TR 19075-9:2020(E)
COPYRIGHT PROTECTED DOCUMENT
© 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: +41 22 749 01 11
Email: copyright@iso.org
Website: www.iso.org
Published in Switzerland
ii © ISO/IEC 2020 – All rights reserved
---------------------- Page: 2 ----------------------
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
---------------------- Page: 3 ----------------------
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
---------------------- Page: 4 ----------------------
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
---------------------- Page: 5 ----------------------
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
---------------------- Page: 6 ----------------------
ISO/IECTR19075-9:2020(E)
Anyfeedbackorquestionsonthisdocumentshouldbedirectedtotheuser'snationalstandardsbody.A
completelistingofthesebodiescanbefoundatwww.iso.org/members.html.
©ISO/IEC2020–Allrightsreserved vii
---------------------- Page: 7 ----------------------
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
---------------------- Page: 8 ----------------------
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
---------------------- Page: 9 ----------------------
ISO/IECTR19075-9:2020(E)
2 Normativereferences
Therearenonormativereferencesinthisdocument.
2 ©ISO/IEC2020–Allrightsreserved
---------------------- Page: 10 ----------------------
ISO/IECTR19075-9:2020(E)
3 Termsanddefinitions
Notermsanddefinitionsarelistedinthisdocument.
ISOandIECmaintainterminologicaldatabasesforuseinstandardizationatthefollowingaddresses:
— ISOOnlinebrowsingplatform:availableathttp://www.iso.org/obp
— IECElectropedia:availableathttp://www.electropedia.org/
©ISO/IEC2020–Allrightsreserved 3
---------------------- Page: 11 ----------------------
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
---------------------- Page: 12 ----------------------
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
---------------------- Page: 13 ----------------------
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
---------------------- Page: 14 ----------------------
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
---------------------- Page: 15 ----------------------
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
---------------------- Page: 16 ----------------------
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
---------------------- Page: 17 ----------------------
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
---------------------- Page: 18 ----------------------
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
---------------------- Page: 19 ----------------------
ISO/IECTR19075-9:2020(E)
5.2 Windowdefinitions
5.2.3.2Nullorderingandtreatment
FeatureT611,“ElementaryOLAPoperations”alsointroducedthe“nullord
...
TECHNICAL ISO/IEC TR
REPORT 19075-9
First edition
Information technology database
languages — SQL technical reports —
Part 9:
SQL TR OLAP
PROOF/ÉPREUVE
Reference number
ISO/IEC TR 19075-9:2020(E)
©
ISO/IEC 2020
---------------------- Page: 1 ----------------------
ISO/IEC TR 19075-9:2020(E)
COPYRIGHT PROTECTED DOCUMENT
© 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: +41 22 749 01 11
Email: copyright@iso.org
Website: www.iso.org
Published in Switzerland
ii PROOF/ÉPREUVE © ISO/IEC 2020 – All rights reserved
---------------------- Page: 2 ----------------------
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
---------------------- Page: 3 ----------------------
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
---------------------- Page: 4 ----------------------
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
---------------------- Page: 5 ----------------------
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
---------------------- Page: 6 ----------------------
ISO/IECTR19075-9:2020(E)
Anyfeedbackorquestionsonthisdocumentshouldbedirectedtotheuser'snationalstandardsbody.A
completelistingofthesebodiescanbefoundatwww.iso.org/members.html.
©ISO/IEC2020–Allrightsreserved vii
---------------------- Page: 7 ----------------------
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
---------------------- Page: 8 ----------------------
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
---------------------- Page: 9 ----------------------
ISO/IECTR19075-9:2020(E)
2 Normativereferences
Therearenonormativereferencesinthisdocument.
2 ©ISO/IEC2020–Allrightsreserved
---------------------- Page: 10 ----------------------
ISO/IECTR19075-9:2020(E)
3 Termsanddefinitions
Notermsanddefinitionsarelistedinthisdocument.
ISOandIECmaintainterminologicaldatabasesforuseinstandardizationatthefollowingaddresses:
— ISOOnlinebrowsingplatform:availableathttp://www.iso.org/obp
— IECElectropedia:availableathttp://www.electropedia.org/
©ISO/IEC2020–Allrightsreserved 3
---------------------- Page: 11 ----------------------
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
---------------------- Page: 12 ----------------------
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
---------------------- Page: 13 ----------------------
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
---------------------- Page: 14 ----------------------
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
---------------------- Page: 15 ----------------------
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
---------------------- Page: 16 ----------------------
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
---------------------- Page: 17 ----------------------
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
---------------------- Page: 18 ----------------------
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
---------------------- Page: 19 ----------------------
ISO/IECTR19075-9:2020(E)
5.2 Windowdefinitions
5.2.3.2Nullorderingandtreatment
FeatureT611,“ElementaryOLAPoperations”alsointroducedthe“nullordering”optiontothesort
specificationlistofb
...
Questions, Comments and Discussion
Ask us and Technical Secretary will try to provide an answer. You can facilitate discussion about the standard in here.