ISO/IEC 9075-2:1999/Amd 1:2001

Information technology — Database languages — SQL — Part 2: Foundation (SQL/Foundation) — Amendment 1: On-Line Analytical Processing (SQL/OLAP)

Technologies de l'information — Langages de base de données — SQL — Partie 2: Fondations (SQL/Fondations) — Amendement 1: Traitement analytique en ligne (SQL/OLAP)

General Information

• Status: Withdrawn
• Publication Date: 21-Mar-2001
• Withdrawal Date: 21-Mar-2001
• ICS: 35.060 - Languages used in information technology
• Technical Committee: ISO/IEC JTC 1/SC 32 - Data management and interchange
• Drafting Committee: ISO/IEC JTC 1/SC 32/WG 3 - Database language
• Current Stage: 9599 - Withdrawal of International Standard
• Completion Date: 15-Dec-2003

Relations

• Parent: ISO/IEC 9075-2:1999/Amd 1:2001/Cor 1:2003 - Information technology — Database languages — SQL — Part 2: Foundation (SQL/Foundation) — Amendment 1: On-Line Analytical Processing (SQL/OLAP) — Technical Corrigendum 1
• Effective Date: 15-Apr-2008
• Amends: ISO/IEC 9075-2:1999 - Information technology — Database languages — SQL — Part 2: Foundation (SQL/Foundation)
• Effective Date: 06-Jun-2022
• Child: ISO/IEC 9075-2:1999 - Information technology — Database languages — SQL — Part 2: Foundation (SQL/Foundation)
• Effective Date: 15-Apr-2008
• Corrected By: ISO/IEC 9075-2:1999/Amd 1:2001/Cor 1:2003 - Information technology — Database languages — SQL — Part 2: Foundation (SQL/Foundation) — Amendment 1: On-Line Analytical Processing (SQL/OLAP) — Technical Corrigendum 1
• Effective Date: 06-Jun-2022

Standards Content (Sample)

ISO/IEC
INTERNATIONAL
9075-1
STANDARD
9075-2
9075-5
First edition
1999-12-01
AMENDMENT 1
2001-03-15
Information technology — Database
languages — SQL —
Part 1:
Framework (SQL/Framework)
Part 2:
Foundation (SQL/Foundation)
Part 5:
Host Language Bindings (SQL/Bindings)
AMENDMENT 1: On-Line Analytical
Processing (SQL/OLAP)
Technologies de l'information — Langages de base de données — SQL —
Partie 1: Charpente (SQL/Charpente)
Partie 2: Fondations (SQL/Fondations)
Partie 5: Liants de langage d'hôte (SQL/Liants)
AMENDEMENT 1: Traitement analytique en ligne (SQL/OLAP)
Reference number
ISO/IEC 9075 (parts 1, 2 and 5):1999/Amd.1:2001(E)
©
ISO/IEC 2001

---------------------- Page: 1 ----------------------
ISO/IEC 9075 (parts 1, 2 and 5):1999/Amd.1:2001(E)
PDF disclaimer
This PDF file may contain embedded typefaces. In accordance with Adobe's licensing policy, this file may be printed or viewed but shall not
be edited unless the typefaces which are embedded are licensed to and installed on the computer performing the editing. In downloading this
file, parties accept therein the responsibility of not infringing Adobe's licensing policy. The ISO Central Secretariat accepts no liability in this
area.
Adobe is a trademark of Adobe Systems Incorporated.
Details of the software products used to create this PDF file can be found in the General Info relative to the file; the PDF-creation parameters
were optimized for printing. Every care has been taken to ensure that the file is suitable for use by ISO member bodies. In the unlikely event
that a problem relating to it is found, please inform the Central Secretariat at the address given below.
© ISO/IEC 2001
All rights reserved. Unless otherwise specified, no part of this publication may be reproduced or utilized in any form or by any means, electronic
or mechanical, including photocopying and microfilm, without permission in writing from either ISO at the address below or ISO's member body
in the country of the requester.
ISO copyright office
Case postale 56 � CH-1211 Geneva 20
Tel. + 41 22 749 01 11
Fax + 41 22 749 09 47
E-mail [e-mail]
Web www.iso.ch
Printed in Switzerland
ii © ISO/IEC 2001 – All rights reserved

---------------------- Page: 2 ----------------------
ISO/IEC 9075 (parts 1, 2 and 5):1999/Amd.1:2001(E)
Contents Page
Foreword . vii
Introduction . . . . viii
1 Scope . 1
2 Normative references. 3
3 Definitions, notations, and conventions . 5
3.1 Definitions . 5
3.2 Notation. 5
3.3 Conventions . 5
3.3.1 Use of terms. 5
3.3.1.1 Syntactic containment . . . 5
3.3.2 Relationships to other parts of ISO/IEC 9075 . 5
3.3.2.1 Clause, Subclause, and Table relationships . . . 5
4 Concepts. 9
4.1 Numbers . 9
4.1.1 Operations involving numbers . 9
4.2 Tables . . . 10
4.2.1 Windowed tables . . 10
4.3 Data analysis operations (involving tables) . 11
4.3.1 Group functions . . . 11
4.3.2 Window functions . . 12
4.3.3 Aggregate functions . 13
5 Lexical elements . 17
5.1 and . 17
5.2 Names and identifiers . 19
6 Scalar expressions . 21
6.1 . . . 21
6.2 . . . . 23
6.3 . 27
6.4 . 31
© ISO/IEC 2001 – All rights reserved Contents iii

---------------------- Page: 3 ----------------------
ISO/IEC 9075 (parts 1, 2 and 5):1999/Amd1:2001(E)
7 Query expressions . 33
7.1 . 33
7.2 . . . . 34
7.3 . . . 35
7.4 . . . 36
7.5 . . 37
7.6 . 47
8 Additional common elements . 51
8.1 . 51
8.2 . 61
9 Schema definition and manipulation. 63
9.1 . 63
9.2 . 64
10 SQL-client modules . 65
10.1 Calls to an . 65
11 Data manipulation . 67
11.1 . . . 67
11.2 . 68 12 Dynamic SQL. 69 12.1 . 69 13 Information Schema. 71 13.1 Definition of SQL built-in functions . 71 14 Status codes. 73 14.1 SQLSTATE. 73 15 Conformance . 75 15.1 General conformance requirements . 75 Annex A SQL conformance summary . 77 Annex B Implementation-defined elements . 81 Annex C Implementation-dependent elements . 85 Annex D SQL feature and package taxonomy. 87 Annex E SQL Packages. 89 E.1 OLAP . . . 89 Index . . 91 iv On-Line Analytical Processing (SQL/OLAP) © ISO/IEC 2001 – All rights reserved ---------------------- Page: 4 ---------------------- ISO/IEC 9075 (parts 1, 2 and 5):1999/Amd.1:2001(E) FIGURES Figure Page 1 Illustration of WIDTH_BUCKET Semantics . 9 © ISO/IEC 2001 – All rights reserved Contents v ---------------------- Page: 5 ---------------------- ISO/IEC 9075 (parts 1, 2 and 5):1999/Amd.1:2001(E) TABLES Tables Page 1 Clause, Subclause, and Table relationships . 5 2 SQLSTATE class and subclass values . . . 73 3 Implied feature relationships . . 75 4 SQL/OLAP feature taxonomy for features outside Core SQL . . 87 5 SQL Packages . . . . 89 vi On-Line Analytical Processing (SQL/OLAP) © ISO/IEC 2001 – All rights reserved ---------------------- Page: 6 ---------------------- ISO/IEC 9075 (parts 1, 2 and 5):1999/Amd.1:2001(E) Foreword ISO (the International Organization for Standardization) and IEC (the International Electrotechni- cal 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. International Standards are drafted in accordance with the rules given in the ISO/IEC Directives, Part 3. In the field of information technology, ISO and IEC have established a joint technical committee, ISO/IEC JTC 1. Draft International Standards adopted by the joint technical committee are circu- lated to national bodies for voting. Publication as an International Standard requires approval by at least 75% of the national bodies casting a vote. Attention is drawn to the possibility that some of the elements of this Amendment may be the subject of patent rights. ISO and IEC shall not be held responsible for identifying any or all such patent rights. Amendment 1 to parts 1, 2 and 5 of ISO/IEC 9075:1999 was prepared by Joint Technical Committee ISO/IEC JTC 1, Information technology, Subcommittee SC 32, Data management and interchange. © ISO/IEC 2001 – All rights reserved Foreword vii ---------------------- Page: 7 ---------------------- ISO/IEC 9075 (parts 1, 2 and 5):1999/Amd.1:2001(E) Introduction The organization of this Amendment is as follows: 1) Clause 1, ‘‘Scope’’, specifies the scope of this Amendment. 2) Clause 2, ‘‘Normative references’’, identifies additional standards that, through reference in this Amendment, constitute provisions of this Amendment. 3) Clause 3, ‘‘Definitions, notations, and conventions’’, defines the notations and conventions used in this Amendment. 4) Clause 4, ‘‘Concepts’’, presents concepts used in the definition of On-Line Analytical Processing facilities. 5) Clause 5, ‘‘Lexical elements’’, defines a number of lexical elements used in the definition of On-Line Analytical Processing facilities. 6) Clause 6, ‘‘Scalar expressions’’, defines a number of scalar expressions used in the definition of On-Line Analytical Processing facilities. 7) Clause 7, ‘‘Query expressions’’, defines the elements of the language that produce rows and tables of data as used in On-Line Analytical Processing facilities. 8) Clause 8, ‘‘Additional common elements’’, defines additional common elements used in the definition of On-Line Analytical Processing facilities. 9) Clause 9, ‘‘Schema definition and manipulation’’, defines the schema definition and manipulation statements associated with the definition of On-Line Analytical Processing facilities. 10) Clause 10, ‘‘SQL-client modules’’, defines SQL-client modules and externally-invoked procedures. 11) Clause 11, ‘‘Data manipulation’’, defines data manipulation operations associated with On-Line Analytical Processing facilities. 12) Clause 12, ‘‘Dynamic SQL’’, defines the SQL dynamic statements. 13) Clause 13, ‘‘Information Schema’’, defines viewed tables that contain schema information related to On-Line Analytical Processing facilities. 14) Clause 14, ‘‘Status codes’’, defines SQLSTATE values related to On-Line Analytical Processing facilities. 15) Clause 15, ‘‘Conformance’’, defines the criteria for conformance to this Amendment. 16) Annex A, ‘‘SQL conformance summary’’, is an informative Annex. It summarizes the confor- mance requirements of the SQL language. 17) Annex B, ‘‘Implementation-defined elements’’, is an informative Annex. It lists those features for which the body of this Amendment states that the syntax, the meaning, the returned results, the effect on SQL-data and/or schemas, or any other behavior is partly or wholly implementation-defined. viii On-Line Analytical Processing (SQL/OLAP) © ISO/IEC 2001 – All rights reserved ---------------------- Page: 8 ---------------------- ISO/IEC 9075 (parts 1, 2 and 5):1999/Amd.1:2001(E) 18) Annex C, ‘‘Implementation-dependent elements’’, is an informative Annex. It lists those features for which the body of this Amendment states that the syntax, the meaning, the returned results, the effect on SQL-data and/or schemas, or any other behavior is partly or wholly implementation-dependent. 19) Annex D, ‘‘SQL feature and package taxonomy’’, is an informative Annex. It identifies features of the SQL language specified in this Amendment by a numeric identifier and a short descriptive name. This taxonomy is used to specify conformance to Core SQL and may be used to develop other profiles involving the SQL language. 20) Annex E, ‘‘SQL Packages’’, is an informative Annex. It specifies a package of SQL language features. In the text of this Amendment, Clauses begin a new odd-numbered page, and in Clause 5, ‘‘Lexical elements’’, through Clause 15, ‘‘Conformance’’, Subclauses begin a new page. Any resulting blank space is not significant. © ISO/IEC 2001 – All rights reserved Introduction ix ---------------------- Page: 9 ---------------------- ISO/IEC 9075-1:1999/Amd.1:2001(E) ISO/IEC 9075-2:1999/Amd.1:2001(E) ISO/IEC 9075-5:1999/Amd.1:2001(E) Information technology — Database languages — SQL — Part 1: Framework (SQL/Framework) Part 2: Foundation (SQL/Foundation) Part 5: Host Language Bindings (SQL/Bindings) AMENDMENT 1: On-Line Analytical Processing (SQL/OLAP) 1 Scope This Amendment specifies the syntax and semantics of database language facilities that support on-line analytical processing. The database language facilities that support on-line analytical processing include: — Rank functions. — Distribution functions. — Inverse distribution functions (percentiles). — Hypothetical set functions. — Cumulative and other forms of moving aggregates. — Variance, standard deviation, covariance, correlation, and linear regression functions. This amendment also incidentally defines several new numeric functions. NOTE 1 – The context for this Amendment is described by the Reference Model of Data Management (ISO/IEC 10032:1993). © ISO/IEC 2001 – All rights reserved Scope 1 ---------------------- Page: 10 ---------------------- ISO/IEC 9075 (parts 1, 2 and 5):1999/Amd.1:2001(E) (Blank page) 2 On-Line Analytical Processing (SQL/OLAP) © ISO/IEC 2001 – All rights reserved ---------------------- Page: 11 ---------------------- ISO/IEC 9075 (parts 1, 2 and 5):1999/Amd.1:2001(E) 2 Normative references The following normative documents contain provisions which, through reference in this text, constitute provisions of this Amendment. For dated references, subsequent amendments to, or revisions of, any of these publications do not apply. However, parties to agreements based on this Amendment are encouraged to investigate the possibility of applying the most recent editions of the normative documents indicated below. For undated references, the latest edition of the normative document referred to applies. Members of ISO and IEC maintain registers of currently valid International Standards. ISO/IEC 9075-1:1999, Information technology — Database languages — SQL — Part 1: Frame- work (SQL/Framework). ISO/IEC 9075-2:1999, Information technology — Database languages — SQL — Part 2: Founda- tion (SQL/Foundation). ISO/IEC 9075-5:1999, Information technology — Database languages — SQL — Part 5: Host Language Bindings (SQL/Bindings). © ISO/IEC 2001 – All rights reserved Normative references 3 ---------------------- Page: 12 ---------------------- ISO/IEC 9075 (parts 1, 2 and 5):1999/Amd.1:2001(E) (Blank page) 4 On-Line Analytical Processing (SQL/OLAP) © ISO/IEC 2001 – All rights reserved ---------------------- Page: 13 ---------------------- ISO/IEC 9075 (parts 1, 2 and 5):1999/Amd.1:2001(E) 3 Definitions, notations, and conventions 3.1 Definitions Insert this paragraph For the purposes of this Amendment, the definitions given in ISO/IEC 9075-1 and ISO/IEC 9075-2 apply. 3.2 Notation Insert this paragraph The syntax notation used in this Amendment is an extended version of BNF ("Backus Normal Form" or "Backus Naur Form"). This version of BNF is fully described in Sub- clause 3.2, "Notation", of ISO/IEC 9075-1. 3.3 Conventions Insert this paragraph Except as otherwise specified in this Amendment the conventions used in this Amendment are identical to those described in ISO/IEC 9075-1 and ISO/IEC 9075-2. 3.3.1 Use of terms 3.3.1.1 Syntactic containment Replace 2nd paragraph A1 directly contains B1 if A1 contains B1 without an intervening , , or that is not an . 3.3.2 Relationships to other parts of ISO/IEC 9075 3.3.2.1 Clause, Subclause, and Table relationships Table 1—Clause, Subclause, and Table relationships Corresponding Clause, Sub- Clause, Subclause, or Table in clause, or Table from another Part containing corre- this part of ISO/IEC 9075 part spondence Clause 1, ‘‘Scope’’ Clause 1, "Scope" ISO/IEC 9075-2 Clause 2, ‘‘Normative references’’ Clause 2, "Normative references" ISO/IEC 9075-2 Clause 3, ‘‘Definitions, notations, Clause 3, "Definitions, notations, ISO/IEC 9075-2 and conventions’’ and conventions" Subclause 3.1, ‘‘Definitions’’ Subclause 3.1, "Definitions" ISO/IEC 9075-2 Subclause 3.2, ‘‘Notation’’ Subclause 3.2, "Notation" ISO/IEC 9075-2 © ISO/IEC 2001 – All rights reserved Definitions, notations, and conventions 5 ---------------------- Page: 14 ---------------------- ISO/IEC 9075 (parts 1, 2 and 5):1999/Amd.1:2001(E) 3.3 Conventions Table 1—Clause, Subclause, and Table relationships (Cont.) Corresponding Clause, Sub- Clause, Subclause, or Table in clause, or Table from another Part containing corre- this part of ISO/IEC 9075 part spondence Subclause 3.3, ‘‘Conventions’’ Subclause 3.3, "Conventions" ISO/IEC 9075-2 Subclause 3.3.1, ‘‘Use of terms’’ Subclause 3.3.1, "Use of terms" ISO/IEC 9075-2 Subclause 3.3.1.1, ‘‘Syntactic con- Subclause 3.3.1.1, "Syntactic con- ISO/IEC 9075-2 tainment’’ tainment" Subclause 3.3.2, ‘‘Relationships to (none) (none) other parts of ISO/IEC 9075’’ Subclause 3.3.2.1, ‘‘Clause, Sub- (none) (none) clause, and Table relationships’’ Clause 4, ‘‘Concepts’’ Clause 4, "Concepts" ISO/IEC 9075-2 Subclause 4.1, ‘‘Numbers’’ Subclause 4.5, "Numbers" ISO/IEC 9075-2 Subclause 4.1.1, ‘‘Operations involv- Subclause 4.5.2, "Operations involv- ISO/IEC 9075-2 ing numbers’’ ing numbers" Subclause 4.2, ‘‘Tables’’ Subclause 4.16, "Tables" ISO/IEC 9075-2 Subclause 4.2.1, ‘‘Windowed tables’’ (none) (none) Subclause 4.3, ‘‘Data analysis opera- (none) (none) tions (involving tables)’’ Subclause 4.3.1, ‘‘Group functions’’ (none) (none) Subclause 4.3.2, ‘‘Window functions’’ (none) (none) Subclause 4.3.3, ‘‘Aggregate func- (none) (none) tions’’ Clause 5, ‘‘Lexical elements’’ Clause 5, "Lexical elements" ISO/IEC 9075-2 Subclause 5.1, ‘‘ and and rator>’’ rator>" Subclause 5.2, ‘‘Names and identi- Subclause 5.4, "Names and identi- ISO/IEC 9075-2 fiers’’ fiers" Clause 6, ‘‘Scalar expressions’’ Clause 6, "Scalar expressions" ISO/IEC 9075-2 Subclause 6.1, ‘‘ cation>’’ specification>" Subclause 6.2, ‘‘ function>’’ function>" Subclause 6.3, ‘‘’’ (none) (none) Subclause 6.4, ‘‘’’ Subclause 6.23, " sion>" Clause 7, ‘‘Query expressions’’ Clause 7, "Query expressions" ISO/IEC 9075-2 Subclause 7.1, ‘‘

’’ Subclause 7.4, "" ISO/IEC 9075-2
Subclause 7.2, ‘‘’’ Subclause 7.7, "" ISO/IEC 9075-2
6 On-Line Analytical Processing (SQL/OLAP) © ISO/IEC 2001 – All rights reserved

---------------------- Page: 15 ----------------------
ISO/IEC 9075 (parts 1, 2 and 5):1999/Amd.1:2001(E)
3.3 Conventions
Table 1—Clause, Subclause, and Table relationships (Cont.)
Corresponding Clause, Sub-
Clause, Subclause, or Table in clause, or Table from another Part containing corre-
this part of ISO/IEC 9075 part spondence
Subclause 7.3, ‘‘’’ Subclause 7.8, "" ISO/IEC 9075-2
Subclause 7.4, ‘‘’’ Subclause 7.10, "" ISO/IEC 9075-2
Subclause 7.5, ‘‘’’ (none) (none)
Subclause 7.6, ‘‘ tion>’’ tion>"
Clause 8, ‘‘Additional common Clause 10, "Additional common ISO/IEC 9075-2
elements’’ elements"
Subclause 8.1, ‘‘ tion>’’
Subclause 8.2, ‘‘ list>’’
Clause 9, ‘‘Schema definition and Clause 11, "Schema definition and ISO/IEC 9075-2
manipulation’’ manipulation"
Subclause 9.1, ‘‘ straint definition>’’ straint definition>"
Subclause 9.2, ‘‘ ordering statement>’’ defined ordering statement>"
Clause 10, ‘‘SQL-client modules’’ Clause 13, "SQL-client modules" ISO/IEC 9075-2
Subclause 10.1, ‘‘Calls to an Subclause 13.4, "Calls to an ISO/IEC 9075-2
’’ "
Clause 11, ‘‘Data manipulation’’ Clause 14, "Data manipulation" ISO/IEC 9075-2
Subclause 11.1, ‘‘’’ Subclause 14.1, "" ISO/IEC 9075-2
Subclause 11.2, ‘‘ single row>’’ single row>" Clause 12, ‘‘Dynamic SQL’’ Clause 15, "Dynamic SQL" ISO/IEC 9075-5 Subclause 12.1, ‘‘ ment>’’ ment>" Clause 13, ‘‘Information Schema’’ Clause 20, "Information Schema" ISO/IEC 9075-2 Subclause 13.1, ‘‘Definition of SQL Subclause 20.70, "Definition of SQL ISO/IEC 9075-2 built-in functions’’ built-in functions" Clause 14, ‘‘Status codes’’ Clause 22, "Status codes" ISO/IEC 9075-2 Subclause 14.1, ‘‘SQLSTATE’’ Subclause 22.1, "SQLSTATE" ISO/IEC 9075-2 Clause 15, ‘‘Conformance’’ Clause 23, "Conformance" ISO/IEC 9075-2 Subclause 15.1, ‘‘General confor- Subclause 23.1, "General confor- ISO/IEC 9075-2 mance requirements’’ mance requirements" Annex A, ‘‘SQL conformance sum- Annex A, "SQL Conformance Sum- ISO/IEC 9075-2 mary’’ mary" © ISO/IEC 2001 – All rights reserved Definitions, notations, and conventions 7 ---------------------- Page: 16 ---------------------- ISO/IEC 9075 (parts 1, 2 and 5):1999/Amd.1:2001(E) 3.3 Conventions Table 1—Clause, Subclause, and Table relationships (Cont.) Corresponding Clause, Sub- Clause, Subclause, or Table in clause, or Table from another Part containing corre- this part of ISO/IEC 9075 part spondence Annex B, ‘‘Implementation-defined Annex B, "Implementation-defined ISO/IEC 9075-2 elements’’ elements" Annex C, ‘‘Implementation- Annex C, "Implementation- ISO/IEC 9075-2 dependent elements’’ dependent elements" Annex D, ‘‘SQL feature and package Annex F, "SQL feature and package ISO/IEC 9075-2 taxonomy’’ taxonomy" Annex E, ‘‘SQL Packages’’ Annex B, "SQL Packages" ISO/IEC 9075-1 Subclause E.1, ‘‘OLAP’’ (none) (none) Figure 1, ‘‘Illustration of WIDTH_ (none) (none) BUCKET Semantics’’ Table 1, ‘‘Clause, Subclause, and (none) (none) Table relationships’’ Table 2, ‘‘SQLSTATE class and Table 27, "SQLSTATE class and ISO/IEC 9075-2 subclass values’’ subclass values" Table 3, ‘‘Implied feature relation- Table 30, "Implied feature relation- ISO/IEC 9075-2 ships’’ ships" Table 4, ‘‘SQL/OLAP feature tax- Table 32, "SQL/Foundation feature ISO/IEC 9075-2 onomy for features outside Core taxonomy for features outside Core SQL’’ SQL" Table 5, ‘‘SQL Packages’’ Table 2, "SQL Packages" ISO/IEC 9075-1 8 On-Line Analytical Processing (SQL/OLAP) © ISO/IEC 2001 – All rights reserved ---------------------- Page: 17 ---------------------- ISO/IEC 9075 (parts 1, 2 and 5):1999/Amd.1:2001(E) 4 Concepts 4.1 Numbers 4.1.1 Operations involving numbers Insert this paragraph The following are also functions that return numbers: — computes the natural logarithm of its argument. — computes the exponential function, that is, e, (the base of natural loga- rithms) raised to the power equal to its argument. — raises its first argument to the power of its second argument. — computes the square root of its argument. — <floor function> computes the greatest integer less than or equal to its argument. — computes the least integer greater than or equal to its argument. — is a function of four arguments, returning an integer between 0 (zero) and the value of the final argument plus 1 (one), by assigning the first argument to an equi-width partitioning of the range of numbers between the second and third arguments. Values outside the range between the second and third arguments are assigned to either 0 (zero) or the value of the final argument plus 1 (one). NOTE 2 – The semantics of are illustrated in Figure 1, ‘‘Illustration of WIDTH_ BUCKET Semantics’’. Figure 1—Illustration of WIDTH_BUCKET Semantics width_bucket(WBO,WBB1,WBB2,WBC) ����� � � � � � � ��� ������� ���� ���� ��� © ISO/IEC 2001 – All rights reserved Concepts 9 ---------------------- Page: 18 ---------------------- ISO/IEC 9075 (parts 1, 2 and 5):1999/Amd.1:2001(E) 4.2 Tables 4.2 Tables 4.2.1 Windowed tables A windowed table is a table together with one or more windows. A window is a transient data structure associated with a . A window is defined explicitly by a inition> or implicitly by an . Implicitly defined windows have an
implementation-dependent window name. A window is used to specify window partitions and
window frames, which are multisets of rows used in the definition of s.
Every window defines a window partitioning of the rows of the . The window
partitioning is specified by a list of columns. Window partitioning is similar to forming groups of
a grouped table. However, unlike grouped tables, each row is retained in the result of the expression>. The window partition of a row R is the multiset of rows R2 that are not distinct from
R, for all columns enumerated in the window partitioning clause. The window partitioning clause is
optional; if omitted, there is a single window partition consisting of all the rows in the result.
If a is grouped and also has a window, then there is a syntactic transformation
that segregates the grouping into a , so that the window partitions consist of rows of
the rather than groups of rows.
A window may define a window ordering of rows within each window partition defined by the
window. The window ordering of rows within window partitions is specified by a list of expression>s, followed by ASC (for ascending order) or DESC (for descending order). In addition,
NULLS FIRST or NULLS LAST may be specified, to indicate whether a null value should appear
before or after all non-null values in the ordered sequence of each .
Optionally, a window may define a window frame for each row R. A window frame is always defined
relative to the current row. A window frame is specified by up to four syntactic elements:
— The choice of RANGE, to indicate a logical definition of the window frame by offsetting forward
or backward from the current row by an increment or decrement to the sort key; or ROWS, to
indicate a physical definition of the window frame, by counting rows forward or backward from
the current row.
— A starting row, which may be the first row of the window partition of R, the current row, or
some row determined by a logical or physical offset from the current row.
— An ending row, which may be the last row of the window partition of R, the current row, or some
row determined by a logical or physical offset from the current row.
— A , indicating whether to exclude the current row and/or its peers (if
not already excluded by being prior to the starting row or after the ending row).
A window is described by a window structure descriptor, including:
— The window name.
— Optionally, the ordering window name—that is, the name of another window, called the ordering
window, that is used to define the partitioning and ordering of the present window.
— The window partitioning clause—that is, a , if any is specified in
either the present or in the window descriptor of the ordering window.
— The window ordering clause—that is, a , if any is specified in either the
present or in the window descriptor of the ordering window.
10 On-Line Analytical Processing (SQL/OLAP) © ISO/IEC 2001 – All rights reserved

---------------------- Page: 19 ----------------------
ISO/IEC 9075 (parts 1, 2 and 5):1999/Amd.1:2001(E)
4.2 Tables
— The window framing clause—that is, a , if any.
In general, two s are computed independently, each one performing its own sort
of its data, even if they use the same data and the same ...