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
Published
Publication Date
17-Sep-2020
Current Stage
9092 - International Standard to be revised
Start Date
30-Jun-2021
Completion Date
30-Jun-2021
Ref Project

Buy Standard

Technical report
ISO/IEC TR 19075-9:2020 - Information technology database languages -- Guidance for the use of database language SQL
English language
48 pages
sale 15% off
Preview
sale 15% off
Preview
Draft
ISO/IEC PRF TR 19075-9 - Information technology database languages -- SQL technical reports
English language
48 pages
sale 15% off
Preview
sale 15% off
Preview

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.