4
: Copyright 2006-2009 The FLWOR Foundation.
6
: Licensed under the Apache License, Version 2.0 (the "License");
7
: you may not use this file except in compliance with the License.
8
: You may obtain a copy of the License at
10
: http://www.apache.org/licenses/LICENSE-2.0
12
: Unless required by applicable law or agreed to in writing, software
13
: distributed under the License is distributed on an "AS IS" BASIS,
14
: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
15
: See the License for the specific language governing permissions and
16
: limitations under the License.
20
: This module implements some Excel 2003 statistical functions
21
: that cannot be implemented with standard XQuery functions.
22
: It uses Zorba specific functions.
24
: @Author Daniel Turcanu
25
: @See http://office.microsoft.com/en-us/excel/CH062528311033.aspx
28
module namespace excel = "http://www.zorba-xquery.com/modules/excel/statistical-zorba" ;
30
import module namespace
31
excel-math="http://www.zorba-xquery.com/modules/excel/math";
33
import module namespace
34
excel-statistical="http://www.zorba-xquery.com/modules/excel/statistical";
36
import module namespace
37
excel-err="http://www.zorba-xquery.com/modules/excel/errors";
40
: W3C Math namespace URI.
42
declare namespace math="http://www.w3.org/2005/xpath-functions/math";
44
declare namespace ver = "http://www.zorba-xquery.com/options/versioning";
45
declare option ver:module-version "1.0";
49
: Estimates standard deviation based on a sample.
50
: The standard deviation is a measure of how widely values are dispersed
51
: from the average value (the mean).
52
: It is computed with formula:
53
: sqrt( sum((x-average_x)^2) / (n-1) ) = sqrt ( VAR(numbers) )
55
: @see http://office.microsoft.com/en-us/excel/HP052092771033.aspx
56
: @param $numbers the sequence of numbers or values castable to numeric
57
: The sequence can be of any length, from 1 up.
58
: @return the standard deviation, as numeric type
59
: @error the errors returned by VAR function
60
: @example test/Queries/excel/statistical/stdev1.xq
62
declare function excel:stdev($numbers as xs:anyAtomicType+) as xs:anyAtomicType
64
math:sqrt(excel-statistical:var($numbers))
68
: Estimates standard deviation based on a sample.
69
: The standard deviation is a measure of how widely values are dispersed
70
: from the average value (the mean).
71
: It is computed with formula:
72
: sqrt( sum((x-average_x)^2) / (n-1) ) = sqrt ( VARA(numbers) )
74
: @see http://office.microsoft.com/en-us/excel/HP052092791033.aspx
75
: @param $numbers the sequence of numbers or values castable to numeric
76
: The sequence can be of any length, from 1 up.
77
: @return the standard deviation, as numeric type
78
: @error the errors returned by VARA function
79
: @example test/Queries/excel/statistical/stdeva1.xq
81
declare function excel:stdeva($numbers as xs:anyAtomicType+) as xs:anyAtomicType
83
math:sqrt(excel-statistical:vara($numbers))
87
: Calculates standard deviation based on the entire population given as arguments.
88
: The standard deviation is a measure of how widely values are dispersed from
89
: the average value (the mean).
90
: It is computed with formula:
91
: sqrt( sum((x-average_x)^2) / n ) = sqrt ( VARP(numbers) )
93
: @see http://office.microsoft.com/en-us/excel/HP052092811033.aspx
94
: @param $numbers the sequence of numbers or values castable to numeric
95
: The sequence can be of any length, from 1 up.
96
: @return the standard deviation, as numeric type
97
: @error the errors returned by VARP function
98
: @example test/Queries/excel/statistical/stdevp1.xq
100
declare function excel:stdevp($numbers as xs:anyAtomicType+) as xs:anyAtomicType
102
math:sqrt(excel-statistical:varp($numbers))
106
: Calculates standard deviation based on the entire population given as arguments.
107
: The standard deviation is a measure of how widely values are dispersed from
108
: the average value (the mean).
109
: It is computed with formula:
110
: sqrt( sum((x-average_x)^2) / n ) = sqrt ( VARPA(numbers) )
112
: @see http://office.microsoft.com/en-us/excel/HP052092831033.aspx
113
: @param $numbers the sequence of numbers or values castable to numeric
114
: The sequence can be of any length, from 1 up.
115
: @return the standard deviation, as numeric type
116
: @error the errors returned by VARPA function
117
: @example test/Queries/excel/statistical/stdevpa1.xq
119
declare function excel:stdevpa($numbers as xs:anyAtomicType+) as xs:anyAtomicType
121
math:sqrt(excel-statistical:varpa($numbers))
125
: Moved from math module.
126
: Returns a subtotal in a sequence of numbers.
127
: The function applied is given by $function_num.
129
: @see http://office.microsoft.com/en-us/excel/HP052092881033.aspx
130
: @param $function_num <dl>defines the function to be applied on sequence values.
131
: The possible values are:
132
: <dt>1 or 101</dt> <dd> AVERAGE</dd>
133
: <dt>2 or 102</dt> <dd> COUNT</dd>
134
: <dt>3 or 103</dt> <dd> COUNTA</dd>
135
: <dt>4 or 104</dt> <dd> MAX</dd>
136
: <dt>5 or 105</dt> <dd> MIN</dd>
137
: <dt>6 or 106</dt> <dd> PRODUCT</dd>
138
: <dt>7 or 107</dt> <dd> STDEV</dd>
139
: <dt>8 or 108</dt> <dd> STDEVP</dd>
140
: <dt>9 or 109</dt> <dd> SUM</dd>
141
: <dt>10 or 110</dt> <dd> VAR</dd>
142
: <dt>11 or 111</dt> <dd> VARP</dd></dl>
144
: In this implementation there is no difference between x and 10x.<br/>
145
: @param $numbers the sequence of numbers or values castable to numeric.
146
: The sequence can be of any length.
147
: @return The function result, as numeric type
148
: @error ** depends on the function called
149
: @error XQP0021(errNum) if $function_num is not a value between 1 .. 11 or 101 .. 111
150
: @example test/Queries/excel/statistical/priority1/subtotal1.xq
151
: @example test/Queries/excel/statistical/priority1/subtotal2.xq
152
: @example test/Queries/excel/statistical/priority1/subtotal3.xq
153
: @example test/Queries/excel/statistical/priority1/subtotal4.xq
154
: @example test/Queries/excel/statistical/priority1/subtotal5.xq
155
: @example test/Queries/excel/statistical/priority1/subtotal6.xq
156
: @example test/Queries/excel/statistical/priority1/subtotal7.xq
157
: @example test/Queries/excel/statistical/priority1/subtotal8.xq
158
: @example test/Queries/excel/statistical/priority1/subtotal9.xq
159
: @example test/Queries/excel/statistical/priority1/subtotal10.xq
160
: @example test/Queries/excel/statistical/priority1/subtotal11.xq
162
declare function excel:subtotal($function_num as xs:integer, $numbers as xs:anyAtomicType*) as xs:anyAtomicType
164
if ($function_num = 1 or $function_num = 101) then
165
excel-statistical:average($numbers)
167
if ($function_num = 2 or $function_num = 102) then
168
excel-statistical:count($numbers)
170
if ($function_num = 3 or $function_num = 103) then
171
excel-statistical:counta($numbers)
173
if ($function_num = 4 or $function_num = 104) then
174
excel-statistical:max($numbers)
176
if ($function_num = 5 or $function_num = 105) then
177
excel-statistical:min($numbers)
179
if ($function_num = 6 or $function_num = 106) then
180
excel-math:product($numbers)
182
if ($function_num = 7 or $function_num = 107) then
183
excel:stdev($numbers)
185
if ($function_num = 8 or $function_num = 108) then
186
excel:stdevp($numbers)
188
if ($function_num = 9 or $function_num = 109) then
189
excel-math:sum($numbers)
191
if ($function_num = 10 or $function_num = 110) then
192
excel-statistical:var($numbers)
194
if ($function_num = 11 or $function_num = 111) then
195
excel-statistical:varp($numbers)
197
fn:error($excel-err:errNum, "Subtotal function: function_num should be between 1 and 11 or 101 and 111")