Debian Codegraph @

doc/sql.extensions/README.aggregate_tracking

raw
1 SQL Language Extension: Improved aggregate handling
2
3 Syntax
4 ========
5
6
7 GROUP BY
8 --------
9 SELECT ... FROM .... [GROUP BY group_by_list]
10
11 group_by_list : group_by_item [, group_by_list];
12
13 group_by_item : column_name
14 | ordinal
15 | udf
16 | group_by_function;
17
18 group_by_function : numeric_value_function
19 | string_value_function
20 | case_expression
21 ;
22
23 numeric_value_function : EXTRACT '(' timestamp_part FROM value ')';
24
25 string_value_function : SUBSTRING '(' value FROM pos_short_integer ')'
26 | SUBSTRING '(' value FROM pos_short_integer FOR nonneg_short_integer ')'
27 | KW_UPPER '(' value ')'
28 ;
29
30 (FB1.0 only allowed GROUP BY column_name and udf)
31 Ordinal references to a n-th select-item from the select-list (same as ORDER BY).
32 The group_by_item cannot reference to any aggregate-function (also not burried
33 inside a expression) from the same context.
34
35
36 HAVING
37 --------
38 The having clause only allows aggregate functions or expressions
39 that are part of the GROUP BY clause. Previously it was allowed
40 to use columns that were not part of the GROUP BY clause.
41
42
43 ORDER BY
44 --------
45 When the context is an aggregate statement then the ORDER BY
46 clause only allows valid expressions. That are aggregate
47 functions or expression part of the GROUP BY clause.
48 Previously it was allowed to use non-valid expressions.
49
50
51 Aggregate functions inside sub-selects
52 --------
53 It is now possible to use a aggregate function or expression
54 contained in the GROUP BY clause inside a sub-select.
55 (See Examples A)
56
57
58 Mixing aggregate functions from different contexts
59 --------
60 You can use aggregate functions from different contexts inside
61 a expression.
62 (See Example B)
63
64
65 Sub-selects are supported inside a aggregate function
66 --------
67 Using a singleton select expression inside a aggregate function is
68 supported.
69 (See Example C)
70
71
72 Nested aggregate functions
73 --------
74 Using a aggregate function inside a other aggregate function is
75 possible if the aggregate function inside is from a lower context.
76 (See Example C)
77
78
79 Author:
80 Arno Brinkman <[email protected]>
81
82
83 N O T E S
84 =========
85
86 - Not all expressions are currently alowed inside the GROUP BY list.
87 (concatenation for example)
88 - ORDER BY clause only accepts valid expressions (this was not for FB1.0)
89 - HAVING clause only accepts valid expressions (this was not for FB1.0)
90 - Using ordinal 'copies' the expression from the select list as does the
91 order by clause. This means when a ordinal references to a sub-select
92 the sub-select is at least executed twice.
93
94
95 Examples
96 ========
97
98
99 A)
100 SELECT
101 r.RDB$RELATION_NAME,
102 MAX(r.RDB$FIELD_POSITION),
103 (SELECT
104 r2.RDB$FIELD_NAME
105 FROM
106 RDB$RELATION_FIELDS r2
107 WHERE
108 r2.RDB$RELATION_NAME = r.RDB$RELATION_NAME and
109 r2.RDB$FIELD_POSITION = MAX(r.RDB$FIELD_POSITION))
110 FROM
111 RDB$RELATION_FIELDS r
112 GROUP BY
113 1
114
115 SELECT
116 rf.RDB$RELATION_NAME AS "Relationname",
117 (SELECT
118 r.RDB$RELATION_ID
119 FROM
120 RDB$RELATIONS r
121 WHERE
122 r.RDB$RELATION_NAME = rf.RDB$RELATION_NAME) AS "ID",
123 COUNT(*) AS "Fields"
124 FROM
125 RDB$RELATION_FIELDS rf
126 GROUP BY
127 rf.RDB$RELATION_NAME
128
129
130 B)
131 SELECT
132 r.RDB$RELATION_NAME,
133 MAX(i.RDB$STATISTICS) AS "Max1",
134 (SELECT
135 COUNT(*) || ' - ' || MAX(i.RDB$STATISTICS)
136 FROM
137 RDB$RELATION_FIELDS rf
138 WHERE
139 rf.RDB$RELATION_NAME = r.RDB$RELATION_NAME) AS "Max2"
140 FROM
141 RDB$RELATIONS r
142 JOIN RDB$INDICES i on (i.RDB$RELATION_NAME = r.RDB$RELATION_NAME)
143 GROUP BY
144 r.RDB$RELATION_NAME
145 HAVING
146 MIN(i.RDB$STATISTICS) <> MAX(i.RDB$STATISTICS)
147
148 Note! This query gives results in FB1.0, but they are WRONG!
149
150
151 C)
152 SELECT
153 r.RDB$RELATION_NAME,
154 SUM((SELECT
155 COUNT(*)
156 FROM
157 RDB$RELATION_FIELDS rf
158 WHERE
159 rf.RDB$RELATION_NAME = r.RDB$RELATION_NAME))
160 FROM
161 RDB$RELATIONS r
162 JOIN RDB$INDICES i on (i.RDB$RELATION_NAME = r.RDB$RELATION_NAME)
163 GROUP BY
164 r.RDB$RELATION_NAME
165
166