ISO/IEC 19075-9:2022
(Main)Information technology — Guidance for the use of database language SQL — Part 9: Online analytic processing (OLAP) capabilities (Guide/OLAP)
Information technology — Guidance for the use of database language SQL — Part 9: Online analytic processing (OLAP) capabilities (Guide/OLAP)
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 (OLAP), (Guide/OLAP)
General Information
Relations
Buy Standard
Standards Content (Sample)
INTERNATIONAL ISO/IEC
STANDARD 19075-9
First edition
2022-10
Information technology — Guidance
for the use of database language
SQL —
Part 9:
Online analytic processing (OLAP)
capabilities (Guide/OLAP)
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 (OLAP), (Guide/
OLAP)
Reference number
ISO/IEC 19075-9:2022(E)
© ISO/IEC 2022
---------------------- Page: 1 ----------------------
ISO/IEC 19075-9:2022(E)
COPYRIGHT PROTECTED DOCUMENT
© ISO/IEC 2022
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 2022 – All rights reserved
---------------------- Page: 2 ----------------------
ISO/IEC 19075-9:2022(E)
Contents Page
Foreword. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . vii
Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix
1 Scope.1
2 Normativereferences.2
3 Termsanddefinitions.3
4 Example data.4
4.1 Introduction to example data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
4.2 Table sales history. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
4.3 Table stock1. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
4.4 Table stocks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
4.5 Table homes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
5 Windows.8
5.1 Introduction to windows. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
5.2 Window definitions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
5.2.1 Introduction to window definitions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
5.2.2 Window partitioning. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
5.2.3 Window ordering. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
5.2.3.1 Introduction to window ordering. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
5.2.3.2 Null ordering and treatment. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
5.2.4 Window frames. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
5.2.4.1 Introduction to window frames. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
5.2.4.2 Physical window frames. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
5.2.4.3 Logical window frames. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
5.2.4.3.1 Introduction to logical window frames. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
5.2.4.3.2 RANGE window frames. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
5.2.4.3.3 GROUPS window frames. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
5.2.4.4 Window frame exclusions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
5.3 Explicit vs. implicit window definitions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
5.4 Multiple window definitions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
6 Window functions.20
6.1 Introduction to window functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
6.2 Rank functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
6.3 Distribution functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
6.4 Row number function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
6.5 Window aggregate functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
6.6 Ntile function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
6.7 LEAD and LAG functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
6.8 FIRST_VALUE and LAST_VALUE functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
© ISO/IEC 2022 – All rights reserved iii
---------------------- Page: 3 ----------------------
ISO/IEC 19075-9:2022(E)
6.9 NTH_VALUE function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
6.10 Null treatment. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
7 Nestedwindowfunctions.33
7.1 Introduction to nested window functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
7.2 Row markers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
7.3 Offsets. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
7.4 FRAME_ROW. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
7.5 Nested ROW_NUMBER function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
7.6 Effects of EXCLUDE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
8 Enhancedaggregatefunctions.40
8.1 Introduction to enhanced aggregate functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
8.2 Unary statistical aggregate functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
8.3 Binary statistical aggregate functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
8.4 Hypothetical rank and distribution aggregate functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
8.5 Inverse distribution functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Bibliography.47
Index.48
iv © ISO/IEC 2022 – All rights reserved
---------------------- Page: 4 ----------------------
ISO/IEC 19075-9:2022(E)
Tables
Table Page
1 Table sales_history. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
2 Table stock1. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
3 Table stocks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
4 Table homes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
5 Result of window clause. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
6 Result of window clause ordering. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
7 Result of physical window frame, UNBOUNDED PRECEDING to CURRENT ROW. . . . . . . . . . . . . . . . . . . . . . 13
8 Result of physical window frame, 2 PRECEDING to 1 FOLLOWING. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
9 Result of logical window frame with RANGE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
10 Result of logical window frame with GROUPS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
11 Result of window frame exclusion 1. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
12 Result of window frame exclusion 2. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
13 Result of RANK and DENSE_RANK function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
14 Result of PERCENT_RANK and CUME_DIST functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
15 Result of ROW_NUMBER function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
16 Result of aggregate function (SUM) ordered. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
17 Result of aggregate function (SUM) unordered. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
18 Result of aggregate function (AVG). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
19 Result of aggregate function (NTILE) with partitioned query. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
20 Result of aggregate function (NTILE) in non-partitioned query. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
21 Result of aggregate function (LEAD). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
22 Result of aggregate function (LAG). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
23 Result of aggregate function (FIRST_VALUE). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
24 Result of aggregate function (LAST_VALUE). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
25 Result of aggregate function (NTH_VALUE). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
26 Result of row markers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
27 Result of row markers (offsets). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
28 Result of window frames with row markers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
29 Result of EXCLUDE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
30 Result of hypothetical aggregate functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
31 Result of inverse distribution functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
32 Result of inverse distribution functions with ordering. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
© ISO/IEC 2022 – All rights reserved v
---------------------- Page: 5 ----------------------
ISO/IEC 19075-9:2022(E)
Examples
Example Page
1 Window clause. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
2 Window clause ordering. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
3 Physical window frame, UNBOUNDED PRECEDING to CURRENT ROW. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
4 Physical window frame, 2 PRECEDING to 1 FOLLOWING. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
5 Logical window frame with RANGE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
6 Logical window frame with GROUPS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
7 Window frame exclusion 1. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
8 Window frame exclusion 2. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
9 Explicit window definition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
10 Implicit window definition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
11 Multiple window definitions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
12 Rank functions with explicit window. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
13 Rank functions with implicit window. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
14 Distribution functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
15 Row number function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
16 Window aggregate function (SUM) ordered. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
17 Window aggregate function (SUM) unordered. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
18 Window aggregate moving average. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
19 NTILE in partitioned query. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
20 NTILE in non-partitioned query. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
21 LEAD function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
22 LAG function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
23 FIRST_VALUE function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
24 LAST_VALUE function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
25 NTH_VALUE function usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
26 Equivalent NTH_VALUE function usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
27 Null treatment with LEAD function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
28 Null treatment with FIRST_VALUE function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
29 Null treatment with LAST_VALUE function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
30 Q1: CASE expression in a window query. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
31 Q2: Complex join. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
32 Q3: VALUE_OF function usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
33 Q4: frame_row and current_row in value_of function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
34 Weight function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
35 Hypothetical aggregate function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
36 Inverse distribution function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
37 Inverse distribution function ordered. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
vi © ISO/IEC 2022 – All rights reserved
---------------------- Page: 6 ----------------------
ISO/IEC 19075-9:2022(E)
Foreword
ISO (the International Organization for Standardization) and IEC (the International Electrotechnical
Commission) form the specialized system for worldwide standardization. National bodies that are
members of ISO or IEC participate in the development of International Standards through technical
committees established by the respective organization to deal with particular fields of technical activity.
ISO and IEC technical committees collaborate in fields of mutual interest. Other international
organizations, governmental and non-governmental, in liaison with ISO and IEC, also take part in the
work.
The procedures used to develop this document and those intended for its further maintenance
are described in the ISO/IEC Directives, Part 1. In particular, the different approval criteria needed
for the different types of document should be noted. This document was drafted in accordance
with the editorial rules of the ISO/IEC Directives, Part 2 (see www.iso.org/directives or
www.iec.ch/members_experts/refdocs).
Attention is drawn to the possibility that some of the elements of this document may be the subject of
patent rights. ISO and IEC shall not be held responsible for identifying any or all such patent rights. Details
of any patent rights identified during the development of the document will be in the Introduction and/or
on the ISO list of patent declarations received (see www.iso.org/patents) or the IEC list of patent
declarations received (see patents.iec.ch).
Any trade name used in this document is information given for the convenience of users and does not
constitute an endorsement.
For an explanation of the voluntary nature of standards, the meaning of ISO specific terms
and expressions related to conformity assessment, as well as information about ISO's adherence to
the World Trade Organization (WTO) principles in the Technical Barriers to Trade (TBT) see
www.iso.org/iso/foreword.html. In the IEC, see www.iec.ch/understanding-standards.
This document was prepared by Joint Technical Committee ISO/IEC JTC 1, Information technology,
Subcommittee SC 32, Data management and interchange.
This first edition of ISO/IEC 19075-9 cancels and replaces ISO/IEC TR 19075-9:2020.
This document is intended to be used in conjunction with the following editions of the parts of the
ISO/IEC 9075 series:
— ISO/IEC 9075-1, sixth edition or later;
— ISO/IEC 9075-2, sixth edition or later;
— ISO/IEC 9075-3, sixth edition or later;
— ISO/IEC 9075-4, seventh edition or later;
— ISO/IEC 9075-9, fifth edition or later;
— ISO/IEC 9075-10, fifth edition or later;
— ISO/IEC 9075-11, fifth edition or later;
— ISO/IEC 9075-13, fifth edition or later;
© ISO/IEC 2022 – All rights reserved vii
---------------------- Page: 7 ----------------------
ISO/IEC 19075-9:2022(E)
— ISO/IEC 9075-14, sixth edition or later;
— ISO/IEC 9075-15, second edition or later;
— ISO/IEC 9075-16, first edition or later.
A list of all parts in the ISO/IEC 19075 series can be found on the ISO and IEC websites.
Any feedback or questions on this document should be directed to the user’s national standards body. A
complete listing of these bodies can be found at www.iso.org/members.html and www.iec.ch/national-
committees.
viii © ISO/IEC 2022 – All rights reserved
---------------------- Page: 8 ----------------------
ISO/IEC 19075-9:2022(E)
Introduction
This document discusses the syntax and semantics for including online analytic processing (OLAP) cap-
abilities in SQL, as defined in ISO/IEC 9075-2.
The organization of this document is as follows:
1) Clause 1, “Scope”, specifies the scope of this document.
2) Clause 2, “Normative references”, identifies standards that are referenced as part of requirements
by this document.
3) Clause 3, “Terms and definitions”, defines the terms and definitions used in this document.
4) Clause 5, “Windows”, discusses Feature T611, “Elementary OLAP operations” and Feature T612,
“Advanced OLAP operations”, introducing the concept of a window in an SQL query.
5) Clause 6, “Window functions”, further discusses Feature T611, “Elementary OLAP operations” and
Feature T612, “Advanced OLAP operations”, as well as 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”, and Feature T618, “NTH_VALUE function”.
6) Clause 7, “Nested window functions”, discusses the additional window functionality in Feature
T619, “Nested window functions”.
7) Clause 8, “Enhanced aggregate functions”, discusses Feature T621, “Enhanced numeric functions”
and its introduction of enhanced aggregate functions in SQL.
© ISO/IEC 2022 – All rights reserved ix
---------------------- Page: 9 ----------------------
INTERNATIONAL STANDARD ISO/IEC 19075-9:2022(E)
Informationtechnology—GuidancefortheuseofdatabaselanguageSQL—
Part 9:
Onlineanalyticprocessing(OLAP)capabilities(Guide/OLAP)
1 Scope
This document discusses the syntax and semantics for including online analytic processing (OLAP) cap-
abilities 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/IEC 2022 – All rights reserved 1
---------------------- Page: 10 ----------------------
ISO/IEC 19075-9:2022(E)
2 Normativereferences
There are no normative references in this document.
2 © ISO/IEC 2022 – All rights reserved
---------------------- Page: 11 ----------------------
ISO/IEC 19075-9:2022(E)
3 Termsanddefinitions
No terms and definitions are listed in this document.
ISO and IEC maintain terminological databases for use in standardization at the following addresses:
— IEC Electropedia: available athttp://www.electropedia.org/
— ISO Online browsing platform: available athttp://www.iso.org/obp
© ISO/IEC 2022 – All rights reserved 3
---------------------- Page: 12 ----------------------
ISO/IEC 19075-9:2022(E)
4 Example data
4.1 Introduction to example data
The examples in this document are based on several tables.
The order in which the rows of all sample tables are displayed is immaterial.
4.2 Table sales history
Table 1, “Table sales_history”, contains information on a business spread over several territories with
total sales accumulated monthly in each territory. Table 1, “Table sales_history”, shows sample data for
Subclause 4.2, “Table sales history”:
Table 1 — Table sales_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
SQL to create and populate Subclause 4.2, “Table sales history”.
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)
INSERT INTO Sales_History VALUES ('East', 199901, 7)
4 © ISO/IEC 2022 – All rights reserved
---------------------- Page: 13 ----------------------
ISO/IEC 19075-9:2022(E)
4.2 Table sales history
INSERT INTO Sales_History VALUES ('West', 199812, 7)
INSERT INTO Sales_History VALUES ('West', 199902, 6)
4.3 Tablestock1
The next examples are two variants of a stock table containing information on stock transactions for a
particular account. Columns in Table 2, “Table stock1”, include transaction ID, trade day, and type, as
well as the share amount and ticker symbol. Subclause 4.4, “Table stocks”, covers the columns ticker,
tradeday, and price.
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
SQL to create and populate Table 2, “Table stock1”.
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')
INSERT INTO Stock1 VALUES (123, 7, 4, 'buy', 100, 'csco')
I
...
INTERNATIONAL ISO/IEC
STANDARD 19075-9
First edition
Information technology — Guidance
for the use of database language
SQL —
Part 9:
Online analytic processing (OLAP)
capabilities (Guide/OLAP)
PROOF/ÉPREUVE
Reference number
ISO/IEC 19075-9:2022(E)
© ISO/IEC 2022
---------------------- Page: 1 ----------------------
ISO/IEC 19075-9:2022(E)
COPYRIGHT PROTECTED DOCUMENT
© ISO/IEC 2022
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 2022 – All rights reserved
---------------------- Page: 2 ----------------------
ISO/IEC 19075-9:2022(E)
Contents Page
Foreword. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . vii
Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix
1 Scope.1
2 Normativereferences.2
3 Termsanddefinitions.3
4 Example data.4
4.1 Introduction to example data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
4.2 Table sales history. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
4.3 Table stock1. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
4.4 Table stocks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
4.5 Table homes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
5 Windows.8
5.1 Introduction to windows. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
5.2 Window definitions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
5.2.1 Introduction to window definitions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
5.2.2 Window partitioning. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
5.2.3 Window ordering. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
5.2.3.1 Introduction to window ordering. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
5.2.3.2 Null ordering and treatment. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
5.2.4 Window frames. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
5.2.4.1 Introduction to window frames. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
5.2.4.2 Physical window frames. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
5.2.4.3 Logical window frames. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
5.2.4.3.1 Introduction to logical window frames. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
5.2.4.3.2 RANGE window frames. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
5.2.4.3.3 GROUPS window frames. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
5.2.4.4 Window frame exclusions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
5.3 Explicit vs. implicit window definitions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
5.4 Multiple window definitions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
6 Window functions.20
6.1 Introduction to window functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
6.2 Rank functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
6.3 Distribution functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
6.4 Row number function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
6.5 Window aggregate functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
6.6 Ntile function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
6.7 LEAD and LAG functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
6.8 FIRST_VALUE and LAST_VALUE functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
© ISO/IEC 2022 – All rights reserved iii
---------------------- Page: 3 ----------------------
ISO/IEC 19075-9:2022(E)
6.9 NTH_VALUE function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
6.10 Null treatment. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
7 Nestedwindowfunctions.33
7.1 Introduction to nested window functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
7.2 Row markers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
7.3 Offsets. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
7.4 FRAME_ROW. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
7.5 Nested ROW_NUMBER function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
7.6 Effects of EXCLUDE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
8 Enhancedaggregatefunctions.40
8.1 Introduction to enhanced aggregate functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
8.2 Unary statistical aggregate functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
8.3 Binary statistical aggregate functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
8.4 Hypothetical rank and distribution aggregate functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
8.5 Inverse distribution functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Bibliography.47
Index.48
iv © ISO/IEC 2022 – All rights reserved
---------------------- Page: 4 ----------------------
ISO/IEC 19075-9:2022(E)
Tables
Table Page
1 Table sales_history. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
2 Table stock1. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
3 Table stocks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
4 Table homes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
5 Result of window clause. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
6 Result of window clause ordering. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
7 Result of physical window frame, UNBOUNDED PRECEDING to CURRENT ROW. . . . . . . . . . . . . . . . . . . . . . 13
8 Result of physical window frame, 2 PRECEDING to 1 FOLLOWING. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
9 Result of logical window frame with RANGE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
10 Result of logical window frame with GROUPS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
11 Result of window frame exclusion 1. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
12 Result of window frame exclusion 2. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
13 Result of RANK and DENSE_RANK function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
14 Result of PERCENT_RANK and CUME_DIST functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
15 Result of ROW_NUMBER function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
16 Result of aggregate function (SUM) ordered. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
17 Result of aggregate function (SUM) unordered. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
18 Result of aggregate function (AVG). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
19 Result of aggregate function (NTILE) with partitioned query. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
20 Result of aggregate function (NTILE) in non-partitioned query. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
21 Result of aggregate function (LEAD). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
22 Result of aggregate function (LAG). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
23 Result of aggregate function (FIRST_VALUE). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
24 Result of aggregate function (LAST_VALUE). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
25 Result of aggregate function (NTH_VALUE). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
26 Result of row markers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
27 Result of row markers (offsets). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
28 Result of window frames with row markers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
29 Result of EXCLUDE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
30 Result of hypothetical aggregate functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
31 Result of inverse distribution functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
32 Result of inverse distribution functions with ordering. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
© ISO/IEC 2022 – All rights reserved v
---------------------- Page: 5 ----------------------
ISO/IEC 19075-9:2022(E)
Examples
Example Page
1 Window clause. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
2 Window clause ordering. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
3 Physical window frame, UNBOUNDED PRECEDING to CURRENT ROW. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
4 Physical window frame, 2 PRECEDING to 1 FOLLOWING. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
5 Logical window frame with RANGE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
6 Logical window frame with GROUPS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
7 Window frame exclusion 1. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
8 Window frame exclusion 2. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
9 Explicit window definition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
10 Implicit window definition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
11 Multiple window definitions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
12 Rank functions with explicit window. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
13 Rank functions with implicit window. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
14 Distribution functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
15 Row number function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
16 Window aggregate function (SUM) ordered. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
17 Window aggregate function (SUM) unordered. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
18 Window aggregate moving average. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
19 NTILE in partitioned query. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
20 NTILE in non-partitioned query. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
21 LEAD function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
22 LAG function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
23 FIRST_VALUE function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
24 LAST_VALUE function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
25 NTH_VALUE function usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
26 Equivalent NTH_VALUE function usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
27 Null treatment with LEAD function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
28 Null treatment with FIRST_VALUE function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
29 Null treatment with LAST_VALUE function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
30 Q1: CASE expression in a window query. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
31 Q2: Complex join. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
32 Q3: VALUE_OF function usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
33 Q4: frame_row and current_row in value_of function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
34 Weight function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
35 Hypothetical aggregate function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
36 Inverse distribution function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
37 Inverse distribution function ordered. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
vi © ISO/IEC 2022 – All rights reserved
---------------------- Page: 6 ----------------------
ISO/IEC 19075-9:2022(E)
Foreword
ISO (the International Organization for Standardization) and IEC (the International Electrotechnical
Commission) form the specialized system for worldwide standardization. National bodies that are
members of ISO or IEC participate in the development of International Standards through technical
committees established by the respective organization to deal with particular fields of technical activity.
ISO and IEC technical committees collaborate in fields of mutual interest. Other international
organizations, governmental and non-governmental, in liaison with ISO and IEC, also take part in the
work.
The procedures used to develop this document and those intended for its further maintenance
are described in the ISO/IEC Directives, Part 1. In particular, the different approval criteria needed
for the different types of document should be noted. This document was drafted in accordance
with the editorial rules of the ISO/IEC Directives, Part 2 (see www.iso.org/directives or
www.iec.ch/members_experts/refdocs).
Attention is drawn to the possibility that some of the elements of this document may be the subject of
patent rights. ISO and IEC shall not be held responsible for identifying any or all such patent rights. Details
of any patent rights identified during the development of the document will be in the Introduction and/or
on the ISO list of patent declarations received (see www.iso.org/patents) or the IEC list of patent
declarations received (see patents.iec.ch).
Any trade name used in this document is information given for the convenience of users and does not
constitute an endorsement.
For an explanation of the voluntary nature of standards, the meaning of ISO specific terms
and expressions related to conformity assessment, as well as information about ISO's adherence to
the World Trade Organization (WTO) principles in the Technical Barriers to Trade (TBT) see
www.iso.org/iso/foreword.html. In the IEC, see www.iec.ch/understanding-standards.
This document was prepared by Joint Technical Committee ISO/IEC JTC 1, Information technology,
Subcommittee SC 32, Data management and interchange.
This first edition of ISO/IEC 19075-9 cancels and replaces ISO/IEC TR 19075-9:2020.
This document is intended to be used in conjunction with the following editions of the parts of the
ISO/IEC 9075 series:
— ISO/IEC 9075-1, sixth edition or later;
— ISO/IEC 9075-2, sixth edition or later;
— ISO/IEC 9075-3, sixth edition or later;
— ISO/IEC 9075-4, seventh edition or later;
— ISO/IEC 9075-9, fifth edition or later;
— ISO/IEC 9075-10, fifth edition or later;
— ISO/IEC 9075-11, fifth edition or later;
— ISO/IEC 9075-13, fifth edition or later;
© ISO/IEC 2022 – All rights reserved vii
---------------------- Page: 7 ----------------------
ISO/IEC 19075-9:2022(E)
— ISO/IEC 9075-14, sixth edition or later;
— ISO/IEC 9075-15, second edition or later;
— ISO/IEC 9075-16, first edition or later.
A list of all parts in the ISO/IEC 19075 series can be found on the ISO and IEC websites.
Any feedback or questions on this document should be directed to the user’s national standards body. A
complete listing of these bodies can be found at www.iso.org/members.html and www.iec.ch/national-
committees.
viii © ISO/IEC 2022 – All rights reserved
---------------------- Page: 8 ----------------------
ISO/IEC 19075-9:2022(E)
Introduction
This document discusses the syntax and semantics for including online analytic processing (OLAP) cap-
abilities in SQL, as defined in ISO/IEC 9075-2.
The organization of this document is as follows:
1) Clause 1, “Scope”, specifies the scope of this document.
2) Clause 2, “Normative references”, identifies standards that are referenced as part of requirements
by this document.
3) Clause 3, “Terms and definitions”, defines the terms and definitions used in this document.
4) Clause 5, “Windows”, discusses Feature T611, “Elementary OLAP operations” and Feature T612,
“Advanced OLAP operations”, introducing the concept of a window in an SQL query.
5) Clause 6, “Window functions”, further discusses Feature T611, “Elementary OLAP operations” and
Feature T612, “Advanced OLAP operations”, as well as 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”, and Feature T618, “NTH_VALUE function”.
6) Clause 7, “Nested window functions”, discusses the additional window functionality in Feature
T619, “Nested window functions”.
7) Clause 8, “Enhanced aggregate functions”, discusses Feature T621, “Enhanced numeric functions”
and its introduction of enhanced aggregate functions in SQL.
© ISO/IEC 2022 – All rights reserved ix
---------------------- Page: 9 ----------------------
INTERNATIONAL STANDARD ISO/IEC 19075-9:2022(E)
Informationtechnology—GuidancefortheuseofdatabaselanguageSQL—
Part 9:
Onlineanalyticprocessing(OLAP)capabilities(Guide/OLAP)
1 Scope
This document discusses the syntax and semantics for including online analytic processing (OLAP) cap-
abilities 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/IEC 2022 – All rights reserved 1
---------------------- Page: 10 ----------------------
ISO/IEC 19075-9:2022(E)
2 Normativereferences
There are no normative references in this document.
2 © ISO/IEC 2022 – All rights reserved
---------------------- Page: 11 ----------------------
ISO/IEC 19075-9:2022(E)
3 Termsanddefinitions
No terms and definitions are listed in this document.
ISO and IEC maintain terminological databases for use in standardization at the following addresses:
— IEC Electropedia: available athttp://www.electropedia.org/
— ISO Online browsing platform: available athttp://www.iso.org/obp
© ISO/IEC 2022 – All rights reserved 3
---------------------- Page: 12 ----------------------
ISO/IEC 19075-9:2022(E)
4 Example data
4.1 Introduction to example data
The examples in this document are based on several tables.
The order in which the rows of all sample tables are displayed is immaterial.
4.2 Table sales history
Table 1, “Table sales_history”, contains information on a business spread over several territories with
total sales accumulated monthly in each territory. Table 1, “Table sales_history”, shows sample data for
Subclause 4.2, “Table sales history”:
Table 1 — Table sales_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
SQL to create and populate Subclause 4.2, “Table sales history”.
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)
INSERT INTO Sales_History VALUES ('East', 199901, 7)
4 © ISO/IEC 2022 – All rights reserved
---------------------- Page: 13 ----------------------
ISO/IEC 19075-9:2022(E)
4.2 Table sales history
INSERT INTO Sales_History VALUES ('West', 199812, 7)
INSERT INTO Sales_History VALUES ('West', 199902, 6)
4.3 Tablestock1
The next examples are two variants of a stock table containing information on stock transactions for a
particular account. Columns in Table 2, “Table stock1”, include transaction ID, trade day, and type, as
well as the share amount and ticker symbol. Subclause 4.4, “Table stocks”, covers the columns ticker,
tradeday, and price.
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
SQL to create and populate Table 2, “Table stock1”.
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')
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,
...
Questions, Comments and Discussion
Ask us and Technical Secretary will try to provide an answer. You can facilitate discussion about the standard in here.