1xquery version "3.0"; 2 3(: 4 : Copyright 2006-2009 The FLWOR Foundation. 5 : 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 9 : 10 : http://www.apache.org/licenses/LICENSE-2.0 11 : 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. 17:) 18 19(:~ 20 : Extensive math library. 21 : 22 : @author Daniel Turcanu, Dan Muresan 23 : @project XDM/atomic 24 :) 25module namespace math = "http://www.zorba-xquery.com/modules/math"; 26 27(:~ 28 : W3C Math namespace URI. 29:) 30declare namespace W3Cmath = "http://www.w3.org/2005/xpath-functions/math"; 31 32declare namespace ver = "http://www.zorba-xquery.com/options/versioning"; 33declare option ver:module-version "2.0"; 34 35(:~ 36 : Errors namespace URI. 37:) 38declare variable $math:errNS as xs:string := "http://www.zorba-xquery.com/modules/math"; 39 40(:~ 41 : xs:QName with namespace URI="http://www.zorba-xquery.com/modules/math" and local name "math:Value" 42:) 43declare variable $math:errValue as xs:QName := fn:QName($math:errNS, "math:Value"); 44 45(:~ 46 : xs:QName with namespace URI="http://www.zorba-xquery.com/modules/math" and local name "math:Num" 47:) 48declare variable $math:errNum as xs:QName := fn:QName($math:errNS, "math:Num"); 49 50(:~ 51 : xs:QName with namespace URI="http://www.zorba-xquery.com/modules/math" and local name "math:Div0" 52:) 53declare variable $math:errDiv0 as xs:QName := fn:QName($math:errNS, "math:Div0"); 54 55(:~ 56 : xs:QName with namespace URI="http://www.zorba-xquery.com/modules/math" and local name "math:NA" 57:) 58declare variable $math:errNA as xs:QName := fn:QName($math:errNS, "math:NA"); 59 60 61(:~ 62 : Returns the hyperbolic cosine of x. 63 : If the result it too large, INF is returned. 64 : 65 : @param $arg must be smaller than 7.104760e+002 66 : @return cosh(arg) 67 :) 68declare function math:cosh ($arg as xs:double) as xs:double external; 69 70(:~ 71 : Inverse hyperbolic cosine. 72 : 73 : @param $arg the arg 74 : @return the result of acosh(arg) 75 :) 76declare function math:acosh ($arg as xs:double) as xs:double external; 77 78(:~ 79 : Function performing the modulo operation between the two arguments. 80 : 81 : @param $x the x 82 : @param $y the y 83 : @return The remainder of x/y. 84 :) 85declare function math:fmod ($x as xs:double, $y as xs:double) as xs:double external; 86 87(:~ 88 : Returns the argument split as mantissa and exponent. 89 : The recombining formula is (mantissa * 2^exponent). 90 : 91 : @param $arg the double to be split. 92 : @return A sequence of two doubles (mantissa, exponent) 93 :) 94declare function math:frexp ($arg as xs:double) as xs:double+ external; 95 96(:~ 97 : Computes a real number from the mantissa and exponent. 98 : The formula is (x * 2^i). 99 : 100 : @param $x the mantissa 101 : @param $i the exponent 102 : @return the computed real number 103 :) 104declare function math:ldexp ($x as xs:double, $i as xs:integer) as xs:double external; 105 106(:~ 107 : Splits a floating-point value into fractional and integer parts. 108 : Both the fraction and integer keep the original sign of the value. 109 : 110 : @param $arg the double to be split. 111 : @return A sequence of two doubles (fraction, integer) 112 :) 113declare function math:modf ($arg as xs:double) as xs:double+ external; 114 115(:~ 116 : Calculate hyperbolic sine. 117 : 118 : @param $arg the arg 119 : @return the result of sinh(arg) 120 :) 121declare function math:sinh ($arg as xs:double) as xs:double external; 122 123(:~ 124 : Inverse hyperbolic sine of the number. 125 : 126 : @param $arg the arg 127 : @return the result of asinh(arg) 128 :) 129declare function math:asinh($arg as xs:double) as xs:double external; 130 131(:~ 132 : Calculate the hyperbolic tangent. 133 : 134 : @param $arg the arg 135 : @return the result of tanh(arg) 136 :) 137declare function math:tanh($arg as xs:double) as xs:double external; 138 139(:~ 140 : Calculate the hyperbolic tangent. 141 : 142 : @param $arg must be in range -1 ... +1 (exclusive) 143 : @return the result of atanh(arg) 144 :) 145declare function math:atanh($arg as xs:double) as xs:double external; 146 147(:~ 148 : Convert angle from degrees to radians. <br/> 149 : The parameter is first converted to value range of (-360, 360). 150 : 151 : @param $deg angle in degrees 152 : @return value in radians (-2PI, 2PI) 153 :) 154declare function math:deg-to-rad($deg as xs:double) as xs:double 155{ 156 ($deg mod 360) * 2 * W3Cmath:pi() div 360 157}; 158 159(:~ 160 : Convert angle from radians to degrees. <br/> 161 : 162 : @param $rad value in radians 163 : @return value in degrees (-360, 360) 164 :) 165declare function math:rad-to-deg($rad as xs:double) as xs:double 166{ 167 ($rad * 360 div 2 div W3Cmath:pi()) mod 360 168}; 169 170(:~ 171 : Checks if the double value is positive or negative infinite. 172 : 173 : @param $arg the double to be checked 174 : @return boolean true if argument is pos INF or neg INF 175 :) 176declare function math:is_inf($arg as xs:double) as xs:boolean external; 177 178(:~ 179 : Checks if the double value is Not a Number (NaN). 180 : 181 : @param $arg the arg 182 : @return boolean true if the double is NaN 183 :) 184declare function math:is_nan($arg as xs:double) as xs:boolean external; 185 186 187 188(:functions borrowed from excel module :) 189(:Excel math functions:) 190 191(:~ 192 : Borrowed from excel module.<br/> 193 : Checks if the xs:anyAtomicType argument is actually a numeric type 194 : or can be converted to numeric. 195 : 196 : @param $value Parameter to be checked. 197 : @return true if the value can be casted to numeric. 198 :) 199declare function math:is-a-number($value as xs:anyAtomicType) as xs:boolean 200{ 201 fn:string(fn:number($value)) ne 'NaN' 202}; 203 204(:~ 205 : Borrowed from excel module.<br/> 206 : Cast the xs:anyAtomicType to a numeric type. 207 : If the value is already of a numeric type then nothing is changed. 208 : Otherwise the value is casted to the numeric type that is most appropriate. 209 : 210 : @param $number The parameter can be a number, string, boolean value. 211 : @return The casted value. 212 : @error math:errValue if the value cannot be casted to numeric type. 213 :) 214declare function math:cast-as-numeric($number as xs:anyAtomicType) as xs:anyAtomicType 215{ 216 typeswitch ($number) 217 case xs:double return $number 218 case xs:decimal return $number 219 case xs:double return $number 220 case xs:float return $number 221 default return 222 if ($number castable as xs:integer) then 223 xs:integer($number) 224 else if ($number castable as xs:decimal) then 225 xs:decimal($number) 226 else if ($number castable as xs:double) then 227 xs:double($number) 228 else 229 fn:error($math:errValue, "Provided value is not a number", $number) 230}; 231 232(:~ 233 : Borrowed from excel module.<br/> 234 : Returns number rounded up, away from zero, to the nearest multiple of significance. 235 : Significance must have the same sign as number. 236 : Number and significance must be of a numeric type or castable to numeric. 237 : Significance must not be zero. 238 : 239 : @see http://office.microsoft.com/en-us/excel/HP052090071033.aspx 240 : @param $number The value you want to round. 241 : @param $significance The multiple to which you want to round. 242 : @return The rounded value. 243 : @error math:errNum if significance is zero or it doesn't have the same sign as number. 244 : @example test/rbkt/Queries/zorba/math/from_excel/excel_ceiling1.xq 245 : @example test/rbkt/Queries/zorba/math/from_excel/excel_ceiling2.xq 246 : @example test/rbkt/Queries/zorba/math/from_excel/excel_ceiling3.xq 247 : @example test/rbkt/Queries/zorba/math/from_excel/excel_ceiling4.xq 248 : @example test/rbkt/Queries/zorba/math/from_excel/excel_ceiling5.xq 249 : @example test/rbkt/Queries/zorba/math/from_excel/excel_ceiling6.xq 250 : @example test/rbkt/Queries/zorba/math/from_excel/excel_ceiling7.xq 251 :) 252declare function math:ceiling( 253 $number as xs:double, 254 $significance as xs:double) as xs:double 255{ 256 if ($significance eq 0) then 257 fn:error($math:errNum, "Ceiling function does not accept significance 0") 258 else if ($number * $significance ge 0) then 259 fn:ceiling($number div $significance) * $significance 260 else 261 fn:error($math:errNum, "Ceiling function: both arguments must have the same sign") 262}; 263 264(:~ 265 : Borrowed from excel module.<br/> 266 : Returns number rounded up to the nearest even integer. 267 : Regardless of the sign of number, a value is rounded up when adjusted away from zero. 268 : 269 : @see http://office.microsoft.com/en-us/excel/HP052090801033.aspx 270 : @param $number The value to round. 271 : @return The rounded value casted as numeric type. 272 : @example test/rbkt/Queries/zorba/math/from_excel/excel_even1.xq 273 : @example test/rbkt/Queries/zorba/math/from_excel/excel_even2.xq 274 : @example test/rbkt/Queries/zorba/math/from_excel/excel_even3.xq 275 : @example test/rbkt/Queries/zorba/math/from_excel/excel_even4.xq 276 : @example test/rbkt/Queries/zorba/math/from_excel/excel_even5.xq 277 : @example test/rbkt/Queries/zorba/math/from_excel/excel_even6.xq 278 :) 279declare function math:even($number as xs:double) as xs:integer 280{ 281 let $num := $number 282 return 283 if ($num = 0) then 284 0 285 else 286 let $intnum := xs:integer(math:ceiling($num, math:sign($num))) 287 return 288 if ($intnum mod 2 ne 0) then 289 if ($intnum gt 0) then 290 $intnum + 1 291 else 292 $intnum - 1 293 else 294 $intnum 295}; 296 297(:~ 298 : Borrowed from excel module.<br/> 299 : Function for computing factorial. 300 : This function should not be used outside this module. 301 : This recursive function computes: number * fact(number-1) 302 : 303 : @param $intnum A positive integer. 304 : @return The factorial of intnum. 305:) 306declare %private function math:fact-integer($intnum as xs:integer) as xs:integer 307{ 308 if ($intnum = 1) then 309 1 310 else 311 $intnum * math:fact-integer($intnum - 1) 312}; 313 314(:~ 315 : Borrowed from excel module.<br/> 316 : Returns the factorial of a number. 317 : 318 : @see http://office.microsoft.com/en-us/excel/HP052090841033.aspx 319 : @param $number The non-negative number you want the factorial of. 320 : @return Returns the factorial of a number. The factorial of a number is equal to 1*2*3*...* number. 321 : @error math:errNum if the number is smaller than zero 322 : @example test/rbkt/Queries/zorba/math/from_excel/excel_fact1.xq 323 : @example test/rbkt/Queries/zorba/math/from_excel/excel_fact2.xq 324 : @example test/rbkt/Queries/zorba/math/from_excel/excel_fact3.xq 325 : @example test/rbkt/Queries/zorba/math/from_excel/excel_fact4.xq 326 : @example test/rbkt/Queries/zorba/math/from_excel/excel_fact5.xq 327:) 328declare function math:fact($number as xs:integer) as xs:integer 329{ 330 let $num := $number return 331 if ($num eq 0) then 332 1 333 else 334 if ($num lt 0) then 335 fn:error($math:errNum, "Fact function does not accept numbers smaller than zero") 336 else 337 math:fact-integer(xs:integer($num)) 338}; 339 340(:~ 341 : Borrowed from excel module.<br/> 342 : Rounds number down, toward zero, to the nearest multiple of significance. 343 : Significance must have the same sign as number. 344 : 345 : @see http://office.microsoft.com/en-us/excel/HP052090941033.aspx 346 : @param $number The value you want to round. 347 : @param $significance The multiple to which you want to round. 348 : @return The rounded value as numeric type. 349 : @error math:errNum if significance is zero or it doesn't have the same sign as number. 350 : @example test/rbkt/Queries/zorba/math/from_excel/excel_floor1.xq 351 : @example test/rbkt/Queries/zorba/math/from_excel/excel_floor2.xq 352 : @example test/rbkt/Queries/zorba/math/from_excel/excel_floor3.xq 353 : @example test/rbkt/Queries/zorba/math/from_excel/excel_floor4.xq 354 : @example test/rbkt/Queries/zorba/math/from_excel/excel_floor5.xq 355:) 356declare function math:floor( 357 $number as xs:double, 358 $significance as xs:double) as xs:double 359{ 360 let $num := $number 361 let $sig := $significance 362 return 363 if ($sig eq 0) then 364 fn:error($math:errNum, "Floor function does not accept significance 0") 365 else if ($num * $sig ge 0) then 366 fn:floor($num div $sig) * $sig 367 else 368 fn:error($math:errNum, "Floor function: both arguments must have the same sign") 369}; 370 371(:~ 372 : Borrowed from excel module.<br/> 373 : Rounds a number down to the nearest integer. 374 : Positive numbers are rounded toward zero, negative numbers are rounded away from zero. 375 : 376 : @see http://office.microsoft.com/en-us/excel/HP052091421033.aspx 377 : @param $number The value to be rounded. 378 : @return The rounded integer. 379 : @example test/rbkt/Queries/zorba/math/from_excel/excel_int1.xq 380 : @example test/rbkt/Queries/zorba/math/from_excel/excel_int2.xq 381 : @example test/rbkt/Queries/zorba/math/from_excel/excel_int3.xq 382 : @example test/rbkt/Queries/zorba/math/from_excel/excel_int4.xq 383:) 384declare function math:int($number as xs:double) as xs:integer 385{ 386 xs:integer(fn:floor($number)) 387}; 388 389(:~ 390 : Borrowed from excel module.<br/> 391 : Returns the remainder after number is divided by divisor. 392 : The result has the same sign as divisor. 393 : 394 : @see http://office.microsoft.com/en-us/excel/HP052091821033.aspx 395 : @param $number The number for which you want to find the remainder. 396 : @param $divisor The number by which you want to divide number. 397 : This cannot be zero. 398 : @return The remainder from division as numeric type. 399 : @error math:errDiv0 if divisor is zero after casting to numeric. 400 : @example test/rbkt/Queries/zorba/math/from_excel/excel_mod1.xq 401 : @example test/rbkt/Queries/zorba/math/from_excel/excel_mod2.xq 402 : @example test/rbkt/Queries/zorba/math/from_excel/excel_mod3.xq 403 : @example test/rbkt/Queries/zorba/math/from_excel/excel_mod4.xq 404 :) 405declare function math:mod( 406 $number as xs:double, 407 $divisor as xs:double) as xs:double 408{ 409 let $num := $number 410 let $div := $divisor return 411 if ($div eq 0) then 412 fn:error($math:errDiv0, "Mod operator: divide by 0") 413 else 414 let $result := $num mod $div 415 return 416 if ($result * $div lt 0) then 417 -$result 418 else 419 $result 420}; 421 422(:~ 423 : Borrowed from excel module.<br/> 424 : Returns number rounded up to the nearest odd integer, away from zero. 425 : 426 : @see http://office.microsoft.com/en-us/excel/HP052092031033.aspx 427 : @param $number The value to round. 428 : @return The odd integer. 429 : @example test/rbkt/Queries/zorba/math/from_excel/excel_odd1.xq 430 : @example test/rbkt/Queries/zorba/math/from_excel/excel_odd2.xq 431 : @example test/rbkt/Queries/zorba/math/from_excel/excel_odd3.xq 432 : @example test/rbkt/Queries/zorba/math/from_excel/excel_odd4.xq 433 : @example test/rbkt/Queries/zorba/math/from_excel/excel_odd5.xq 434 : @example test/rbkt/Queries/zorba/math/from_excel/excel_odd6.xq 435 :) 436declare function math:odd($number as xs:double) as xs:integer 437{ 438 let $num := $number return 439 if ($num eq 0) then 440 1 441 else 442 let $intnum := xs:integer(math:ceiling($num, math:sign($num))) 443 return 444 if ($intnum mod 2 eq 0) then 445 if ($intnum ge 0) then 446 ($intnum + 1) cast as xs:integer 447 else 448 ($intnum - 1) cast as xs:integer 449 else 450 $intnum cast as xs:integer 451}; 452 453(:~ 454 : Borrowed from excel module.<br/> 455 : Function for product. 456 : This function should not be used outside this module. 457 : Multiplies all numbers in the sequence. 458 : 459 : @param $numbers The list of arguments to be casted to numeric and multiplied. 460 : @return The multiplication result as numeric type. 461 :) 462declare %private function math:product-internal($numbers as xs:double*) as xs:double 463{ 464 if (fn:empty($numbers)) then 465 1 466 else 467 let $x := $numbers[1] 468 return 469 $x * math:product-internal(fn:subsequence($numbers, 2)) 470}; 471 472(:~ 473 : Borrowed from excel module.<br/> 474 : Multiplies all the numbers given as arguments and returns the product. 475 : 476 : @see http://office.microsoft.com/en-us/excel/HP052092231033.aspx 477 : @param $numbers The sequence of arguments convertible to numeric types. 478 : The sequence can be of any length. 479 : @return The multiplication result as numeric type. 480 : @example test/rbkt/Queries/zorba/math/from_excel/excel_product1.xq 481 : @example test/rbkt/Queries/zorba/math/from_excel/excel_product2.xq 482 : @example test/rbkt/Queries/zorba/math/from_excel/excel_product3.xq 483 : @example test/rbkt/Queries/zorba/math/from_excel/excel_product4.xq 484 : @example test/rbkt/Queries/zorba/math/from_excel/excel_product5.xq 485 :) 486declare function math:product($numbers as xs:double*) as xs:double 487{ 488 if (fn:empty($numbers)) then 489 0 490 else 491 math:product-internal($numbers) 492}; 493 494(:~ 495 : Borrowed from excel module.<br/> 496 : Returns the integer portion of a division. 497 : 498 : @see http://office.microsoft.com/en-us/excel/HP052092271033.aspx 499 : @param $numerator The divider. 500 : @param $denominator The divisor. It cannot be zero. 501 : @return The result value as numeric type. 502 : @error math:errDiv0 if denominator casted as numeric type has value zero. 503 : @example test/rbkt/Queries/zorba/math/from_excel/excel_quotient1.xq 504 : @example test/rbkt/Queries/zorba/math/from_excel/excel_quotient2.xq 505 : @example test/rbkt/Queries/zorba/math/from_excel/excel_quotient3.xq 506 : @example test/rbkt/Queries/zorba/math/from_excel/excel_quotient4.xq 507 :) 508declare function math:quotient( 509 $numerator as xs:double, 510 $denominator as xs:double) as xs:integer 511{ 512 let $numer := $numerator 513 let $denom := $denominator 514 return 515 if ($denom eq 0) then 516 fn:error($math:errDiv0, "Quotient function: divide by 0") 517 else 518 xs:integer($numer div $denom) 519}; 520 521(:~ 522 : Borrowed from excel module.<br/> 523 : Rounds a number to a specified number of digits. 524 : If precision is greater than 0 (zero), then number is rounded 525 : to the specified number of decimal places. 526 : If num_digits is 0, then number is rounded to the nearest integer. 527 : If num_digits is less than 0, then number is rounded to the left of the decimal point. 528 : The 0.5 is rounded away from zero. 529 : 530 : @see http://office.microsoft.com/en-us/excel/HP052092391033.aspx 531 : @param $number The number to round. 532 : @param $precision The number of decimal places to keep. 533 : @return The rounded number as numeric type. 534 : @example test/rbkt/Queries/zorba/math/from_excel/excel_round1.xq 535 : @example test/rbkt/Queries/zorba/math/from_excel/excel_round2.xq 536 : @example test/rbkt/Queries/zorba/math/from_excel/excel_round3.xq 537 : @example test/rbkt/Queries/zorba/math/from_excel/excel_round4.xq 538 :) 539declare function math:round( 540 $number as xs:double, 541 $precision as xs:integer) as xs:double 542{ 543 let $num := $number 544 return 545 if ($precision ge 0) then 546 let $exp_prec := W3Cmath:pow(10, $precision) 547 return 548 if ($num ge 0) then 549 fn:floor($num * $exp_prec + 0.5) div $exp_prec 550 else 551 -fn:floor(-$num * $exp_prec + 0.5) div $exp_prec 552 else 553 let $exp_prec := W3Cmath:pow(10, -$precision) 554 return 555 if ($num ge 0) then 556 fn:floor($num div $exp_prec + 0.5) * $exp_prec 557 else 558 -fn:floor(-$num div $exp_prec + 0.5) * $exp_prec 559}; 560 561(:~ 562 : Borrowed from excel module.<br/> 563 : Rounds a number down, toward zero. 564 : If num_digits is greater than 0 (zero), then number is rounded down 565 : to the specified number of decimal places. 566 : If num_digits is 0, then number is rounded down to the nearest integer. 567 : If num_digits is less than 0, then number is rounded down to the left of the decimal point. 568 : 569 : @see http://office.microsoft.com/en-us/excel/HP052092411033.aspx 570 : @param $number The number to round 571 : @param $precision The number of decimal places to keep. 572 : @return the truncated number toward zero, as numeric type. 573 : @example test/rbkt/Queries/zorba/math/from_excel/excel_rounddown1.xq 574 : @example test/rbkt/Queries/zorba/math/from_excel/excel_rounddown2.xq 575 : @example test/rbkt/Queries/zorba/math/from_excel/excel_rounddown3.xq 576 : @example test/rbkt/Queries/zorba/math/from_excel/excel_rounddown4.xq 577 : @example test/rbkt/Queries/zorba/math/from_excel/excel_rounddown5.xq 578 :) 579declare function math:rounddown( 580 $number as xs:double, 581 $precision as xs:integer) as xs:double 582{ 583 let $num := $number 584 return 585 if ($precision ge 0) then 586 let $exp_prec := W3Cmath:pow(10, $precision) 587 return 588 if ($num ge 0) then 589 fn:floor($num * $exp_prec) div $exp_prec 590 else 591 -fn:floor(-$num * $exp_prec) div $exp_prec 592 else 593 let $exp_prec := W3Cmath:pow(10, -$precision) 594 return 595 if ($num ge 0) then 596 fn:floor($num div $exp_prec) * $exp_prec 597 else 598 -fn:floor(-$num div $exp_prec) * $exp_prec 599}; 600 601(:~ 602 : Borrowed from excel module.<br/> 603 : Rounds a number up, away from 0 (zero). 604 : If num_digits is greater than 0 (zero), then number is rounded down 605 : to the specified number of decimal places. 606 : If num_digits is 0, then number is rounded down to the nearest integer. 607 : If num_digits is less than 0, then number is rounded down to the left of the decimal point. 608 : 609 : @see http://office.microsoft.com/en-us/excel/HP052092421033.aspx 610 : @param $number The number to round 611 : @param $precision The number of decimal places to keep. 612 : @return The truncated number away from zero, as numeric type. 613 : @example test/rbkt/Queries/zorba/math/from_excel/excel_roundup1.xq 614 : @example test/rbkt/Queries/zorba/math/from_excel/excel_roundup2.xq 615 : @example test/rbkt/Queries/zorba/math/from_excel/excel_roundup3.xq 616 : @example test/rbkt/Queries/zorba/math/from_excel/excel_roundup4.xq 617 : @example test/rbkt/Queries/zorba/math/from_excel/excel_roundup5.xq 618 :) 619declare function math:roundup( 620 $number as xs:double, 621 $precision as xs:integer) as xs:double 622{ 623 let $num := $number 624 return 625 if ($precision ge 0) then 626 let $exp_prec := W3Cmath:pow(10, $precision) 627 return 628 if ($num ge 0) then 629 fn:ceiling($num * $exp_prec) div $exp_prec 630 else 631 -fn:ceiling(-$num * $exp_prec) div $exp_prec 632 else 633 let $exp_prec := W3Cmath:pow(10, -$precision) 634 return 635 if ($num ge 0) then 636 fn:ceiling($num div $exp_prec) * $exp_prec 637 else 638 -fn:ceiling(-$num div $exp_prec) * $exp_prec 639}; 640 641(:~ 642 : Borrowed from excel module.<br/> 643 : Determines the sign of a number. 644 : Returns 1 if the number is positive, zero (0) if the number is 0, 645 : and -1 if the number is negative. 646 : 647 : @see http://office.microsoft.com/en-us/excel/HP052092551033.aspx 648 : @param $number The argument 649 : @return The sign as (-1, 0, 1). 650 : @example test/rbkt/Queries/zorba/math/from_excel/excel_sign1.xq 651 : @example test/rbkt/Queries/zorba/math/from_excel/excel_sign2.xq 652 : @example test/rbkt/Queries/zorba/math/from_excel/excel_sign3.xq 653 :) 654declare function math:sign($number as xs:double) as xs:integer 655{ 656 let $num := $number 657 return 658 if ($num eq 0) then 659 0 660 else if ($num gt 0) then 661 1 662 else 663 -1 664 }; 665 666(:~ 667 : Borrowed from excel module.<br/> 668 : Truncates a number to an integer by removing the fractional part of the number. 669 : 670 : @see http://office.microsoft.com/en-us/excel/HP052093241033.aspx 671 : @param $number The argument . 672 : @return The integer value. 673 : @example test/rbkt/Queries/zorba/math/from_excel/excel_trunc1.xq 674 : @example test/rbkt/Queries/zorba/math/from_excel/excel_trunc2.xq 675 :) 676declare function math:trunc($number as xs:double ) as xs:integer 677{ 678 xs:integer($number) 679}; 680 681(:~ 682 : Borrowed from excel module.<br/> 683 : Truncates a number down to precision. 684 : This behaves exactly like rounddown. 685 : 686 : @see http://office.microsoft.com/en-us/excel/HP052093241033.aspx 687 : @param $number The argument castable to numeric type. 688 : @param $precision The number of decimal places to keep . 689 : @return The integer value. 690 : @example test/rbkt/Queries/zorba/math/from_excel/excel_trunc3.xq 691 :) 692declare function math:trunc( 693 $number as xs:double, 694 $precision as xs:integer) as xs:double 695{ 696 math:rounddown($number, $precision) 697}; 698 699(:~ 700 : Borrowed from excel module.<br/> 701 : Helper function.<br/> 702 : Sorts a sequence of numbers or arguments castable to numeric. 703 : It first casts all arguments to numeric and then sorts ascending. 704 : 705 : @param $numbers The sequence of arguments castable to numeric. 706 : @return The sorted sequence as numeric types. 707 :) 708declare function math:sort-numbers($numbers as xs:double*) as xs:double* 709{ 710 let $sorted-numbers := 711 ( 712 for $number in $numbers 713 let $num := $number 714 order by $num 715 return $num 716 ) 717 return $sorted-numbers 718}; 719 720(:~ 721 : Borrowed from excel module.<br/> 722 : Returns the double factorial of a number. 723 : Computes the double factorial of n as n(n-2)(n-4)... 724 : 725 : @see http://office.microsoft.com/en-us/excel/HP052090851033.aspx 726 : @param $number The positive integer value. 727 : @return The result as integer. 728 : @error math:errNum if the number is negative. 729 : @example test/rbkt/Queries/zorba/math/from_excel/excel_factdouble1.xq 730 : @example test/rbkt/Queries/zorba/math/from_excel/excel_factdouble2.xq 731 :) 732declare function math:factdouble($number as xs:integer) as xs:integer 733{ 734 if ($number lt 0) then 735 fn:error($math:errNum, "Factdouble function: number should be greater than zero or equal") 736 else if ($number eq 1) then 737 1 738 else if ($number eq 2) then 739 2 740 else 741 $number * math:factdouble($number - 2) 742}; 743 744(:~ 745 : Borrowed from excel module.<br/> 746 : Function for computing GCD. 747 : This function should not be used outside this module. 748 : It calculates the minimum value from a sequence of positive integers, 749 : not taking into account the zero value. 750 : 751 : @param $numbers The sequence of positive integers. 752 : @return The minimum value. If the sequence contains only zero values, then zero is returned. 753 :) 754declare %private function math:min-without-zero($numbers as xs:integer+) as xs:integer 755{ 756 if (fn:count($numbers) eq 1) then 757 $numbers[1] 758 else 759 let $min-other := math:min-without-zero(fn:subsequence($numbers, 2)) 760 return 761 if ($numbers[1] eq 0) then 762 $min-other 763 else if ($min-other eq 0) then 764 $numbers[1] 765 else if ($numbers[1] lt $min-other) then 766 $numbers[1] 767 else 768 $min-other 769}; 770 771(:~ 772 : Borrowed from excel module.<br/> 773 : Function for computing GCD. 774 : This function should not be used outside this module. 775 : Checks if all integer numbers from a sequence divide exactly to a divider. 776 : 777 : @param $numbers The positive integers. 778 : @param $divider The divider to be tried. 779 : @return true if the numbers divide exactly. 780:) 781declare %private function math:try-exact-divide( 782 $numbers as xs:integer*, 783 $divider as xs:integer) as xs:boolean 784{ 785 if (fn:empty($numbers)) then 786 fn:true() 787 else 788 if ($numbers[1] mod $divider ne 0) then 789 fn:false() 790 else 791 math:try-exact-divide(fn:subsequence($numbers, 2), $divider) 792}; 793 794(:~ 795 : Borrowed from excel module.<br/> 796 : Function for computing GCD. 797 : This function should not be used outside this module. 798 : This function iterates through possible divisors and checks if the sequence 799 : divides exactly to any of those. It starts from the minimum value from the 800 : sequence and searches downwards. 801 : 802 : @param $numbers The sequence of positive integers. 803 : @param $min-nonzero The minimum value of numbers sequence, excluding the zero value. 804 : @param $iteration Which iteration is it. It starts from 1 and continues 805 : to min-nonzero/2. 806 : @return The greatest common divisor if found, or 1 if not found. 807 :) 808declare %private function math:iterate-all-gcd( 809 $numbers as xs:integer*, 810 $min-nonzero as xs:integer, 811 $iteration as xs:integer) as xs:integer 812{ 813 if ($min-nonzero mod $iteration eq 0) then 814 if (math:try-exact-divide($numbers, $min-nonzero idiv $iteration)) then 815 $min-nonzero idiv $iteration 816 else 817 math:iterate-all-gcd($numbers, $min-nonzero, $iteration + 1) 818 else 819 if ($iteration > $min-nonzero idiv 2) then 820 1 821 else 822 math:iterate-all-gcd($numbers, $min-nonzero, $iteration + 1) 823}; 824 825(:~ 826 : Borrowed from excel module.<br/> 827 : Returns the greatest common divisor GCD of a sequence of integers. 828 : The sequence can have one or more positive integers. 829 : 830 : @see http://office.microsoft.com/en-us/excel/HP052091041033.aspx 831 : @param $numbers The sequence of positive integers. 832 : @return The GCD as integer. 833 : @error math:errNum if any number is smaller than zero. 834 : @example test/rbkt/Queries/zorba/math/from_excel/excel_gcd1.xq 835 : @example test/rbkt/Queries/zorba/math/from_excel/excel_gcd2.xq 836 : @example test/rbkt/Queries/zorba/math/from_excel/excel_gcd3.xq 837 : @example test/rbkt/Queries/zorba/math/from_excel/excel_gcd4.xq 838 : @example test/rbkt/Queries/zorba/math/from_excel/excel_gcd5.xq 839 : @example test/rbkt/Queries/zorba/math/from_excel/excel_gcd6.xq 840 : @example test/rbkt/Queries/zorba/math/from_excel/excel_gcd7.xq 841 : @example test/rbkt/Queries/zorba/math/from_excel/excel_gcd8.xq 842 :) 843declare function math:gcd($numbers as xs:integer+) as xs:integer 844{ 845 if (fn:count($numbers) = 1) then 846 $numbers[1] 847 else 848 let $minval := math:min-without-zero($numbers) 849 return 850 if ($minval lt 0) then 851 fn:error($math:errNum, "gcd function: numbers should be greater than zero or equal") 852 else if ($minval eq 0) then 853 0 854 else 855 math:iterate-all-gcd($numbers, $minval, 1) 856}; 857 858(:~ 859 : Borrowed from excel module.<br/> 860 : Returns the least common multiple of integers.<br/> 861 : LCM for two numbers is computed by multiplying them and dividing with GCD. <br/> 862 : The function is applied recursively replacing the first two numbers in the sequence with their LCM. 863 : 864 : @see http://office.microsoft.com/en-us/excel/HP052091521033.aspx 865 : @param $numbers The sequence of one or more positive integers. 866 : @return The LCM as integer. 867 : @error math:errNum if any number is smaller than zero. 868 : @example test/rbkt/Queries/zorba/math/from_excel/excel_lcm1.xq 869 : @example test/rbkt/Queries/zorba/math/from_excel/excel_lcm2.xq 870 : @example test/rbkt/Queries/zorba/math/from_excel/excel_lcm3.xq 871 : @example test/rbkt/Queries/zorba/math/from_excel/excel_lcm4.xq 872 : @example test/rbkt/Queries/zorba/math/from_excel/excel_lcm5.xq 873 :) 874declare function math:lcm($numbers as xs:integer+) as xs:integer 875{ 876 if(count($numbers) eq 1) then 877 $numbers[1] 878 else 879 if(count($numbers) eq 2) then 880 let $product := math:product(fn:distinct-values($numbers)) 881 return 882 if ($product eq 0) then 883 0 884 else 885 $product idiv math:gcd($numbers) 886 else 887 math:lcm((math:lcm(($numbers[1], $numbers[2])), subsequence($numbers, 3))) 888 889}; 890 891(:~ 892 : Borrowed from excel module.<br/> 893 : Returns a number rounded to the desired multiple. 894 : MROUND rounds up, away from zero, if the remainder of dividing number by multiple 895 : is greater than or equal to half the value of multiple. 896 : MROUND is computed through math:floor function. 897 : 898 : @see http://office.microsoft.com/en-us/excel/HP052091851033.aspx 899 : @param $number The value to round, 900 : @param $multiple The multiple to which you want to round number. 901 : @return The rounded number up to the desired multiple. 902 : @example test/rbkt/Queries/zorba/math/from_excel/excel_mround1.xq 903 : @example test/rbkt/Queries/zorba/math/from_excel/excel_mround2.xq 904 : @example test/rbkt/Queries/zorba/math/from_excel/excel_mround3.xq 905 :) 906declare function math:mround( 907 $number as xs:decimal, 908 $multiple as xs:double) as xs:double 909{ 910 let $num := $number 911 let $mul := $multiple 912 let $floor := math:floor($num, $mul) return 913 if ($num ge 0) then 914 if (($num - $floor) ge (($mul div (2 + 1e-12)))) then 915 $floor + $mul 916 else 917 $floor 918 else 919 if ((-$num + $floor) ge (-$mul div (2 + 1e-12))) then 920 $floor + $mul 921 else 922 $floor 923}; 924 925(:~ 926 : Borrowed from excel module.<br/> 927 : Converts an Arabic numeral to roman, as text. 928 : Only the classic format is supported (out of all formats Excel requires).<br/> 929 : M is the largest digit, it represents 1000. 930 : Numbers bigger than 2000 will be represented by a sequence of "M".<br/> 931 : D = 500, C = 100, L = 50, X = 10, V = 5, I = 1. 932 : 933 : @see http://office.microsoft.com/en-us/excel/HP052092381033.aspx 934 : @param $number A positive integer. 935 : @return The roman string representation. 936 : @error math:errNum if the input integer is negative 937 : @example test/rbkt/Queries/zorba/math/from_excel/excel_roman1.xq 938 : @example test/rbkt/Queries/zorba/math/from_excel/excel_roman2.xq 939 : @example test/rbkt/Queries/zorba/math/from_excel/excel_roman3.xq 940 :) 941declare function math:roman($number as xs:integer) as xs:string 942{ 943 if ($number lt 0) then 944 fn:error($math:errNum, "Roman function: number should be greater than zero or equal") 945 else if ($number ge 1000) then 946 fn:concat("M", math:roman($number - 1000)) 947 else if ($number ge 900) then 948 fn:concat("CM", math:roman($number - 900)) 949 else if ($number ge 800) then 950 fn:concat("DCCC", math:roman($number - 800)) 951 else if ($number ge 700) then 952 fn:concat("DCC", math:roman($number - 700)) 953 else if ($number ge 600) then 954 fn:concat("DC", math:roman($number - 600)) 955 else if ($number ge 500) then 956 fn:concat("D", math:roman($number - 500)) 957 else if ($number ge 400) then 958 fn:concat("CD", math:roman($number - 400)) 959 else if ($number ge 300) then 960 fn:concat("CCC", math:roman($number - 300)) 961 else if ($number ge 200) then 962 fn:concat("CC", math:roman($number - 200)) 963 else if ($number ge 100) then 964 fn:concat("C", math:roman($number - 100)) 965 else if ($number ge 90) then 966 fn:concat("XC", math:roman($number - 90)) 967 else if ($number ge 80) then 968 fn:concat("LXXX", math:roman($number - 80)) 969 else if ($number ge 70) then 970 fn:concat("LXX", math:roman($number - 70)) 971 else if ($number ge 60) then 972 fn:concat("LX", math:roman($number - 60)) 973 else if ($number ge 50) then 974 fn:concat("L", math:roman($number - 50)) 975 else if ($number ge 40) then 976 fn:concat("XL", math:roman($number - 40)) 977 else if ($number ge 30) then 978 fn:concat("XXX", math:roman($number - 30)) 979 else if ($number ge 20) then 980 fn:concat("XX", math:roman($number - 20)) 981 else if ($number ge 10) then 982 fn:concat("X", math:roman($number - 10)) 983 else if ($number eq 9) then 984 "IX" 985 else if ($number eq 8) then 986 "VIII" 987 else if ($number eq 7) then 988 "VII" 989 else if ($number eq 6) then 990 "VI" 991 else if ($number eq 5) then 992 "V" 993 else if ($number eq 4) then 994 "IV" 995 else if ($number eq 3) then 996 "III" 997 else if ($number eq 2) then 998 "II" 999 else if ($number eq 1) then 1000 "I" 1001 else 1002 "" 1003}; 1004 1005(:~ 1006 : Borrowed from excel module.<br/> 1007 : Multiplies the elements on the same position in each sequence 1008 : and sums up the results. 1009 : 1010 : @see http://office.microsoft.com/en-us/excel/HP052092931033.aspx 1011 : @param $array1 the sequences of numbers 1012 : @param $array2 the sequences of numbers 1013 : @return the sum of products 1014 : @example test/rbkt/Queries/zorba/math/from_excel/excel_sumproduct2.xq 1015:) 1016 declare function math:sumproduct( $array1 as xs:double*, 1017 $array2 as xs:double* ) as xs:double 1018 { 1019 if( fn:empty($array1) or 1020 fn:empty($array2)) 1021 then 1022 0 1023 else 1024 $array1[1] * $array2[1] + math:sumproduct( fn:subsequence($array1,2), 1025 fn:subsequence($array2,2)) 1026 }; 1027 1028(:~ 1029 : Borrowed from excel module.<br/> 1030 : Returns the sum of the squares of the arguments. 1031 : It uses the sumproduct function. 1032 : 1033 : @see http://office.microsoft.com/en-us/excel/HP052092951033.aspx 1034 : @param $numbers the sequence of one or more numbers 1035 : @return the sum of squared values, as numeric type 1036 : @example test/rbkt/Queries/zorba/math/from_excel/excel_sumsq1.xq 1037:) 1038 declare function math:sumsq( $numbers as xs:double+) as xs:double 1039 { 1040 math:sumproduct($numbers, $numbers) 1041 }; 1042 1043 1044 1045(:Excel statistical functions :) 1046 1047(:~ 1048 : Borrowed from excel module.<br/> 1049 : Returns the median of the given numbers. 1050 : The median is the number in the middle of a set of numbers. 1051 : Half the numbers have values that are greater than the median, 1052 : and half the numbers have values that are less than the median. 1053 : 1054 : 1055 : @see http://office.microsoft.com/en-us/excel/HP052091741033.aspx 1056 : @param $numbers the sequence of numbers, of any length 1057 : @return for odd count of numbers return the number in the middle of the sorted sequence. 1058 : For even count of numbers return the average of the two numbers in the middle. 1059 : @example test/rbkt/Queries/zorba/math/from_excel/excel_median1.xq 1060 : @example test/rbkt/Queries/zorba/math/from_excel/excel_median2.xq 1061:) 1062declare function math:median( $numbers as xs:double* ) as xs:double 1063{ 1064 let $number_count := fn:count( $numbers ) 1065 let $sorted_numbers := math:sort-numbers( $numbers ) return 1066 if ($number_count mod 2 != 0) then 1067 $sorted_numbers[$number_count idiv 2 + 1] 1068 else 1069 if ($number_count = 0) then 1070 0 1071 else 1072 ($sorted_numbers[$number_count idiv 2] + $sorted_numbers[$number_count idiv 2 + 1] ) div 2 1073}; 1074 1075(:~ 1076 : Borrowed from excel module.<br/> 1077 : Returns the most frequently occurring, or repetitive, value in a sequence. 1078 : 1079 : @see http://office.microsoft.com/en-us/excel/HP052091831033.aspx 1080 : @param $numbers the sequence of numbers, of any length 1081 : @return The most occuring number 1082 : @error math:errNA if there are no duplicate numbers 1083 : @example test/rbkt/Queries/zorba/math/from_excel/excel_mode1.xq 1084 : @example test/rbkt/Queries/zorba/math/from_excel/excel_mode2.xq 1085 : @example test/rbkt/Queries/zorba/math/from_excel/excel_mode3.xq 1086:) 1087declare function math:mode( $numbers as xs:double* ) as xs:double 1088{ 1089 if ( fn:empty($numbers)) then 1090 fn:error($math:errNA, "Mode function: empty sequence") 1091 else 1092 let $result := 1093 ( for $n_at in fn:distinct-values($numbers) 1094 let $n := $n_at 1095 let $count := fn:count( (for $d in $numbers where $d eq $n return $d) ) 1096 where $count > 1 1097 order by $count descending 1098 return $n 1099 ) return 1100 if (fn:empty($result)) then 1101 fn:error($math:errNA, "Mode function: no duplicate elements") 1102 else 1103 $result[1] 1104}; 1105 1106(:~ 1107 : Borrowed from excel module.<br/> 1108 : Returns the k-th percentile of values in a sequence. 1109 : If k is not a multiple of 1/(n - 1), 1110 : PERCENTILE interpolates to determine the value at the k-th percentile. 1111 : The function is computed by (max-min)*k + min 1112 : 1113 : @see http://office.microsoft.com/en-us/excel/HP052092111033.aspx 1114 : @param $numbers the sequence of numbers, of any length 1115 : @param $k_at the percentile, with value between 0 .. 1 inclusive 1116 : @return The computed percentile 1117 : @error math:errNum if percentile is not between 0 .. 1 1118 : @example test/rbkt/Queries/zorba/math/from_excel/excel_percentile1.xq 1119 : @example test/rbkt/Queries/zorba/math/from_excel/excel_percentile2.xq 1120 : @example test/rbkt/Queries/zorba/math/from_excel/excel_percentile3.xq 1121:) 1122declare function math:percentile( $numbers as xs:double*, $k_at as xs:double) as xs:double 1123{ 1124 let $k := $k_at return 1125 if ($k < 0 or $k > 1) then 1126 fn:error($math:errNum, "Percentile function: k must be a value between 0 and 1 inclusive") 1127 else 1128 let $max := fn:max($numbers) 1129 let $min := fn:min($numbers) return 1130 ($max - $min) * $k + $min 1131}; 1132 1133 1134 1135(:~ 1136 : Borrowed from excel module.<br/> 1137 : Function for AVEDEV. 1138 : This function should not be used outside this module. 1139 : Computes formula sum(abs(x - average)) for every x in $numbers 1140 : 1141 : @param $numbers The sequence of numbers. 1142 : Sequence can be of any length. 1143 : @param $average The average of all numbers, computed with function AVERAGE. 1144 : @return The result of the formula. 1145 :) 1146declare %private function math:sum-deviations( 1147 $numbers as xs:double*, 1148 $average as xs:double) as xs:double 1149{ 1150 if (fn:empty($numbers)) then 1151 0 1152 else 1153 fn:abs($numbers[1] - $average) + math:sum-deviations(fn:subsequence($numbers, 2), $average) 1154}; 1155 1156(:~ 1157 : Borrowed from excel module.<br/> 1158 : Returns the average of the absolute deviations of data points from their mean. 1159 : The formula is sum(abs(x - average_x))/n, where n is the count of x in the sequence. 1160 : 1161 : @see http://office.microsoft.com/en-us/excel/HP052089931033.aspx 1162 : @param $numbers the sequence of numbers. 1163 : Sequence can be of any length from 1 up. 1164 : @return The formula result 1165 : @example test/rbkt/Queries/zorba/math/from_excel/excel_avedev1.xq 1166:) 1167declare function math:avedev($numbers as xs:double+) as xs:double 1168{ 1169 let $average := fn:avg($numbers) return 1170 math:sum-deviations($numbers, $average) div fn:count($numbers) 1171}; 1172 1173(:~ 1174 : Borrowed from excel module.<br/> 1175 : Returns the k-th largest value in a data set. 1176 : If n is the number of data points in a range, 1177 : then LARGE(array,1) returns the largest value, 1178 : and LARGE(array,n) returns the smallest value. 1179 : 1180 : @see http://office.microsoft.com/en-us/excel/HP052091511033.aspx 1181 : @param $numbers the sequence of numbers 1182 : The sequence can be of any length, from 1 up. 1183 : @param $k the position of largest value, with value from 1 to count of values 1184 : @return The k-th largest value as numeric type 1185 : @error math:errNum if the sequence is empty or k is not a value between 1 1186 : and the size of the sequence 1187 : @example test/rbkt/Queries/zorba/math/from_excel/excel_large1.xq 1188 : @example test/rbkt/Queries/zorba/math/from_excel/excel_large2.xq 1189 : @example test/rbkt/Queries/zorba/math/from_excel/excel_large3.xq 1190:) 1191declare function math:large($numbers as xs:double+, $k as xs:integer) as xs:double 1192{ 1193 if (fn:empty($numbers)) then 1194 fn:error($math:errNum, "Large function: value list must not be empty") 1195 else if ($k > fn:count($numbers) or $k le 0) then 1196 fn:error($math:errNum, "Large function: k must be between 1 and the count of numbers ", $k) 1197 else 1198 let $ordered_numbers := 1199 (for $n in $numbers 1200 let $nn := $n 1201 order by $nn descending 1202 return $nn 1203 ) return 1204 $ordered_numbers[$k] 1205}; 1206 1207(:~ 1208 : Borrowed from excel module.<br/> 1209 : Returns the rank of a number in a list of numbers. 1210 : The rank of a number is its size relative to other values in a list. 1211 : (If you were to sort the list, the rank of the number would be its position.) 1212 : RANK gives duplicate numbers the same rank. 1213 : 1214 : @see http://office.microsoft.com/en-us/excel/HP052092311033.aspx 1215 : @param $x The number whose rank you want to find. 1216 : @param $numbers The sequence of numbers. 1217 : The sequence can be of any length. 1218 : @param $order_ascending <dl>A boolean having the meaning: 1219 : <dt>false</dt><dd>then rank the number as if the sequence was sorted in descending order.</dd> 1220 : <dt>true</dt> <dd>then rank the number as if the sequence was sorted in ascending order.</dd></dl> 1221 : @return The rank of $x. 1222 : @example test/rbkt/Queries/zorba/math/from_excel/excel_rank1.xq 1223 : @example test/rbkt/Queries/zorba/math/from_excel/excel_rank2.xq 1224 : @example test/rbkt/Queries/zorba/math/from_excel/excel_rank4.xq 1225 :) 1226declare function math:rank( 1227 $x as xs:double, 1228 $numbers as xs:double*, 1229 $order_ascending as xs:boolean) as xs:double 1230{ 1231 let $ordered_numbers := 1232 if ($order_ascending) then ( 1233 for $n in $numbers 1234 let $nn := $n 1235 order by $nn ascending 1236 return $nn 1237 ) else ( 1238 for $n in $numbers 1239 let $nn := $n 1240 order by $nn descending 1241 return $nn 1242 ) 1243 let $xnum := $x 1244 let $rank := 1245 ( 1246 for $i at $pos in $ordered_numbers 1247 where $xnum = $i or $order_ascending and $xnum < $i 1248 or fn:not($order_ascending) and $xnum > $i 1249 return 1250 if ($xnum = $i) then 1251 $pos 1252 else if ($pos = 1) then 1253 0 1254 else 1255 ($pos - 1) + ($xnum - $ordered_numbers[$pos - 1]) div ($ordered_numbers[$pos] - $ordered_numbers[$pos - 1]) 1256 ) 1257 return 1258 if (fn:empty($rank)) then 1259 fn:count($numbers) 1260 else 1261 $rank[1] 1262}; 1263 1264(:~ 1265 : Borrowed from excel module.<br/> 1266 : This RANK function is same as the above, only that $order_ascending is set by default to false. 1267 : 1268 : @see http://office.microsoft.com/en-us/excel/HP052092311033.aspx 1269 : @param $x The number whose rank you want to find. 1270 : @param $numbers the sequence of numbers. 1271 : The sequence can be of any length. 1272 : @return The rank of $x. 1273 : @example test/rbkt/Queries/zorba/math/from_excel/excel_rank3.xq 1274 : @example test/rbkt/Queries/zorba/math/from_excel/excel_rank5.xq 1275:) 1276declare function math:rank( 1277 $x as xs:double, 1278 $numbers as xs:double*) as xs:double 1279{ 1280 math:rank($x, $numbers, fn:false()) 1281}; 1282 1283(:~ 1284 : Borrowed from excel module.<br/> 1285 : Returns the rank of a value in a data set as a percentage of the data set. 1286 : If x does not match one of the values in array, 1287 : PERCENTRANK interpolates to return the correct percentage rank. <br/> 1288 : The formula is uses: (RANK - 1) / (size - 1) . 1289 : 1290 : @see http://office.microsoft.com/en-us/excel/HP052092121033.aspx 1291 : @param $numbers the sequence of numbers. 1292 : The sequence can be of any length, from 1 up. 1293 : @param $x is the value for which you want to know the rank 1294 : @return The percentage of rank. 1295 : @error math:errNum if the sequence is zero length 1296 : @example test/rbkt/Queries/zorba/math/from_excel/excel_percentrank1.xq 1297 : @example test/rbkt/Queries/zorba/math/from_excel/excel_percentrank2.xq 1298 : @example test/rbkt/Queries/zorba/math/from_excel/excel_percentrank3.xq 1299 : @example test/rbkt/Queries/zorba/math/from_excel/excel_percentrank4.xq 1300 : @example test/rbkt/Queries/zorba/math/from_excel/excel_percentrank5.xq 1301:) 1302declare function math:percentrank($numbers as xs:double*, $x as xs:double) as xs:double 1303{ 1304 if (fn:empty($numbers)) then 1305 fn:error($math:errNum, "Percentrank function: value list must not be empty") 1306 else 1307 let $rank := math:rank($x, $numbers, fn:true()) return 1308 if ($rank = 0) then 1309 0 1310 else 1311 ($rank - 1) div (fn:count($numbers) - 1) 1312}; 1313 1314(:~ 1315 : Borrowed from excel module.<br/> 1316 : Returns the quartile of a data set. 1317 : 1318 : @see http://office.microsoft.com/en-us/excel/HP052092261033.aspx 1319 : @param $numbers sequence of numbers. 1320 : The sequence can be of any length, from 1 up. 1321 : @param $quart <dl>one of the values 0, 1, 2, 3, 4 with meaning: 1322 : <dt>0</dt> <dd> compute minimum value</dd> 1323 : <dt>1</dt> <dd> compute first quartile (25th percentile)</dd> 1324 : <dt>2</dt> <dd> compute median value (50th percentile)</dd> 1325 : <dt>3</dt> <dd> compute third quartile (75th percentile)</dd> 1326 : <dt>4</dt> <dd> compute maximum value</dd></dl> 1327 : @return the computed quartile, as numeric type 1328 : @error math:errNum if the sequence is zero length or $quart is not one of the values 0,1,3,4 1329 : @example test/rbkt/Queries/zorba/math/from_excel/excel_quartile1.xq 1330 : @example test/rbkt/Queries/zorba/math/from_excel/excel_quartile2.xq 1331 : @example test/rbkt/Queries/zorba/math/from_excel/excel_quartile3.xq 1332 : @example test/rbkt/Queries/zorba/math/from_excel/excel_quartile4.xq 1333 : @example test/rbkt/Queries/zorba/math/from_excel/excel_quartile5.xq 1334:) 1335declare function math:quartile($numbers as xs:double*, $quart as xs:integer) as xs:double 1336{ 1337 if (fn:empty($numbers)) then 1338 fn:error($math:errNum, "Quartile function: value list must not be empty") 1339 else 1340 if ($quart = 0) then 1341 fn:min($numbers) 1342 else 1343 if ($quart = 1) then 1344 let $r := (fn:count($numbers) + 3) div 4 1345 let $rint := xs:integer($r) 1346 let $rrem := $r - $rint 1347 let $sorted_numbers := math:sort-numbers( $numbers ) return 1348 ($numbers[$rint + 1] - $numbers[$rint]) * $rrem + $numbers[$rint] 1349 else 1350 if ($quart = 2) then 1351 math:median($numbers) 1352 else 1353 if ($quart = 3) then 1354 let $r := (3 * fn:count($numbers) + 1) div 4 1355 let $rint := xs:integer($r) 1356 let $rrem := $r - $rint 1357 let $sorted_numbers := math:sort-numbers( $numbers ) return 1358 ($numbers[$rint + 1] - $numbers[$rint]) * $rrem + $numbers[$rint] 1359 else 1360 if ($quart = 4) then 1361 fn:max($numbers) 1362 else 1363 fn:error($math:errNum, "Quartile function: quart should be between 0 and 4 :", $quart) 1364}; 1365 1366(:~ 1367 : Borrowed from excel module.<br/> 1368 : This function computes the k-th smallest value in a data set. 1369 : Use this function to return values with a particular relative standing in a data set. 1370 : If n is the number of data points in array, SMALL(array,1) equals the smallest value, 1371 : and SMALL(array,n) equals the largest value. 1372 : 1373 : @see http://office.microsoft.com/en-us/excel/HP052092661033.aspx 1374 : @param $numbers A sequence of numbers. 1375 : The sequence can be of any length, from 1 up. 1376 : @param $k The position (from the smallest) in the sequence of data to return. 1377 : Must have value between 1 and size of sequence. 1378 : @return The k-th smallest value of $numbers. 1379 : @error math:errNum if the sequence is zero length or $k is not a value 1380 : between 1 and the size of sequence. 1381 : @example test/rbkt/Queries/zorba/math/from_excel/excel_small1.xq 1382 : @example test/rbkt/Queries/zorba/math/from_excel/excel_small2.xq 1383:) 1384declare function math:small($numbers as xs:double*, $k as xs:integer) as xs:double 1385{ 1386 if (fn:empty($numbers)) then 1387 fn:error($math:errNum, "Small function: value list must not be empty") 1388 else if ($k gt fn:count($numbers) or $k le 0) then 1389 fn:error($math:errNum, "Small function: k must be between 1 and the count of numbers ", $k) 1390 else 1391 let $ordered_numbers := ( 1392 for $n in $numbers 1393 let $nn := $n 1394 order by $nn ascending 1395 return $nn 1396 ) 1397 return 1398 $ordered_numbers[$k] 1399}; 1400 1401 1402(:~ 1403 : Borrowed from excel module.<br/> 1404 : Function for VAR, VARA, VARP, VARPA and SLOPE. 1405 : This function should not be used outside this module. 1406 : It computes formula sum((x - average_x)^2) for all x in $numbers. 1407 : 1408 : @param $numbers the sequence of numbers. 1409 : The sequence can be of any length. 1410 : @param $average The precomputed average over the sequence. 1411 : @return The result as numeric type. 1412 :) 1413declare %private function math:sumsq-deviations($numbers as xs:double*, $average as xs:double) as xs:double 1414{ 1415 if (fn:empty($numbers)) then 1416 0 1417 else 1418 let $val := $numbers[1] - $average 1419 return 1420 $val * $val + math:sumsq-deviations(fn:subsequence($numbers, 2), $average) 1421}; 1422 1423(:~ 1424 : Borrowed from excel module.<br/> 1425 : Estimates variance based on a sample.<br/> 1426 : The formula is sum(x - average_x)^2 / (n - 1).<br/> 1427 : average_x is computed with AVERAGE function.<br/> 1428 : n is the count of numbers from the sequence, excluding empty values. 1429 : 1430 : @see http://office.microsoft.com/en-us/excel/HP052093301033.aspx 1431 : @param $numbers the sequence of numbers. 1432 : The sequence can be of any length, from 1 up. 1433 : @return The variance, as numeric type 1434 : @example test/rbkt/Queries/zorba/math/from_excel/excel_var1.xq 1435 :) 1436declare function math:var($numbers as xs:double+) as xs:double 1437{ 1438 let $average := fn:avg($numbers) 1439 return 1440 math:sumsq-deviations($numbers, $average) div (fn:count($numbers) - 1) 1441}; 1442 1443(:~ 1444 : Borrowed from excel module.<br/> 1445 : Estimates variance based on a sample.<br/> 1446 : The formula is sum(x - average_x)^2 / (n - 1).<br/> 1447 : average_x is computed with AVERAGE function.<br/> 1448 : n is the size of sequence, including empty values.<br/> 1449 : 1450 : @see http://office.microsoft.com/en-us/excel/HP052093311033.aspx 1451 : @param $numbers the sequence of numbers. 1452 : The sequence can be of any length, from 1 up. 1453 : @return The variance, as numeric type 1454 : @example test/rbkt/Queries/zorba/math/from_excel/excel_vara1.xq 1455:) 1456declare function math:vara($numbers as xs:double+) as xs:double 1457{ 1458 let $average := fn:avg($numbers) return 1459 math:sumsq-deviations($numbers, $average) div (fn:count($numbers) - 1) 1460}; 1461 1462(:~ 1463 : Borrowed from excel module.<br/> 1464 : Calculates variance based on the entire population.<br/> 1465 : The formula is sum(x - average_x)^2 / n.<br/> 1466 : average_x is computed with AVERAGE function.<br/> 1467 : n is the count of numbers from the sequence, excluding empty values.<br/> 1468 : 1469 : @see http://office.microsoft.com/en-us/excel/HP052093321033.aspx 1470 : @param $numbers the sequence of numbers. 1471 : The sequence can be of any length, from 1 up. 1472 : @return The variance, as numeric type 1473 : @example test/rbkt/Queries/zorba/math/from_excel/excel_varp1.xq 1474:) 1475declare function math:varp($numbers as xs:double+) as xs:double 1476{ 1477 let $average := fn:avg($numbers) return 1478 math:sumsq-deviations($numbers, $average) div fn:count($numbers) 1479}; 1480 1481(:~ 1482 : Borrowed from excel module.<br/> 1483 : Calculates variance based on the entire population.<br/> 1484 : The formula is sum(x - average_x)^2 / n.<br/> 1485 : average_x is computed with AVERAGE function.<br/> 1486 : n is the size of sequence, including empty values.<br/> 1487 : 1488 : @see http://office.microsoft.com/en-us/excel/HP052093321033.aspx 1489 : @param $numbers the sequence of numbers. 1490 : The sequence can be of any length, from 1 up. 1491 : @return The variance, as numeric type 1492 : @example test/rbkt/Queries/zorba/math/from_excel/excel_varpa1.xq 1493:) 1494declare function math:varpa($numbers as xs:double+) as xs:double 1495{ 1496 let $average := fn:avg($numbers) return 1497 math:sumsq-deviations($numbers, $average) div fn:count($numbers) 1498}; 1499 1500(:~ 1501 : Borrowed from excel module.<br/> 1502 : Function for PROB function. 1503 : This function should not be used outside this module. 1504 : Computes the sum over a sequence of numbers. 1505 : Checks if the values are between 0 and 1. 1506 : 1507 : @param $prob_range The sequence of probabilities. 1508 : @return The sum of probabilities. This should be 1. 1509 : @error math:errNum if any probability is not between 0 and 1. 1510 : @error math:errValue if any parameter is not castable to numeric. 1511:) 1512declare %private function math:sum-prob($prob_range as xs:double*) as xs:double 1513{ 1514 if (fn:empty($prob_range)) then 1515 0 1516 else 1517 let $prob_num := $prob_range[1] 1518 return 1519 if ($prob_num < 0 or $prob_num > 1) then 1520 fn:error($math:errNum, "Prob function: prob values should be between 0 and 1 ", $prob_num) 1521 else 1522 $prob_num + math:sum-prob(fn:subsequence($prob_range, 2)) 1523}; 1524 1525(:~ 1526 : Borrowed from excel module.<br/> 1527 : Function for PROB function. 1528 : This function should not be used outside this module. 1529 : Checks the prob range and x range if they have the same number of values. 1530 : Adds all probabilities corresponding to values between range_lower_limit and upper_limit. 1531 : 1532 : @param $x_range The sequence of x values. 1533 : @param $prob_range The sequence of probabilities associated to x values. 1534 : @param $range_lower_limit The lower limit of the range to compute the probability. 1535 : @param $upper_limit The upper limit of the range to compute the probability. 1536 : @return The sum of probabilities. 1537 : @error $math:errNum if x_range and prob_range do not have the same number of values. 1538 :) 1539declare %private function math:sum-prob-x( 1540 $x_range as xs:double*, 1541 $prob_range as xs:double*, 1542 $range_lower_limit as xs:double, 1543 $upper_limit as xs:double) as xs:double 1544{ 1545 if (fn:empty($x_range) and fn:not(fn:empty($prob_range))) then 1546 fn:error($math:errNum, "Prob function: x range and prob range should have the same number of elements") 1547 else if (fn:empty($prob_range) and fn:not(fn:empty($x_range))) then 1548 fn:error($math:errNum, "Prob function: x range and prob range should have the same number of elements") 1549 else if (fn:empty($prob_range) and fn:empty($x_range)) then 1550 0 1551 else 1552 let $x := $x_range[1] 1553 let $this_prob := 1554 if ($x ge $range_lower_limit and $x le $upper_limit) then 1555 $prob_range[1] 1556 else 1557 0 1558 return 1559 $this_prob + math:sum-prob-x( 1560 fn:subsequence($x_range, 2), 1561 fn:subsequence($prob_range, 2), 1562 $range_lower_limit, 1563 $upper_limit) 1564}; 1565 1566(:~ 1567 : Borrowed from excel module.<br/> 1568 : Returns the probability that values in a range are between two limits. 1569 : 1570 : @see http://office.microsoft.com/en-us/excel/HP052092221033.aspx 1571 : @param $x_range is the range of numeric values of x with which there are associated probabilities. 1572 : This does not need to be ordered. 1573 : @param $prob_range is a set of probabilities associated with values in x_range. 1574 : @param $range_lower_limit is the lower bound on the value for which you want a probability. 1575 : @param $upper_limit is the upper bound on the value for which you want a probability. 1576 : @return The probability of the entire range 1577 : @error math:errNum if any probability is not between 0 and 1 1578 : @error math:errNum if the sum of probabilities is not equal to 1 1579 : @error math:errNum if x_range and prob_range do not have the same number of values 1580 : @example test/rbkt/Queries/zorba/math/from_excel/excel_prob2.xq 1581:) 1582declare function math:prob($x_range as xs:double+, 1583 $prob_range as xs:double+, 1584 $range_lower_limit as xs:double, 1585 $upper_limit as xs:double) as xs:double 1586{ 1587 let $prob_sum := math:sum-prob($prob_range) return 1588 if ($prob_sum != 1) then 1589 fn:error($math:errNum, "Prob function: prob sum should equal 1") 1590 else 1591 math:sum-prob-x($x_range, $prob_range, 1592 $range_lower_limit, 1593 $upper_limit) 1594}; 1595 1596(:~ 1597 : Borrowed from excel module.<br/> 1598 : This is the same as above, only that upper_limit is not specified. 1599 : The probability is computed only for range_lower_limit. 1600 : 1601 : @see http://office.microsoft.com/en-us/excel/HP052092221033.aspx 1602 : @param $x_range is the range of numeric values of x with which there are associated probabilities. 1603 : This does not need to be ordered. 1604 : @param $prob_range is a set of probabilities associated with values in x_range. 1605 : @param $range_lower_limit is the value for which you want a probability. 1606 : @return The probability of the range_lower_limit value 1607 : @error math:errNum if any probability is not between 0 and 1 1608 : @error math:errNum if the sum of probabilities is not equal to 1 1609 : @error math:errNum if x_range and prob_range do not have the same number of values 1610 : @example test/rbkt/Queries/zorba/math/from_excel/excel_prob1.xq 1611 :) 1612declare function math:prob($x_range as xs:double+, 1613 $prob_range as xs:double+, 1614 $range_lower_limit as xs:double) as xs:double 1615{ 1616 math:prob($x_range, $prob_range, $range_lower_limit, $range_lower_limit) 1617}; 1618 1619(:~ 1620 : Borrowed from excel module.<br/> 1621 : Function for SLOPE function. 1622 : This function should not be used outside this module. 1623 : It computes the formula:<br/> 1624 : sum((x - average_x)(y - average_y)) <br/> 1625 : where average_x and average_y are computed with AVERAGE function. 1626 : 1627 : @param $x_numbers The sequence of x numbers. 1628 : @param $x_average The precomputed AVERAGE over the x_numbers. 1629 : @param $y_numbers The sequence of y numbers. 1630 : @param $y_average The precomputed AVERAGE over the y_numbers. 1631 : @return The formula result, as numeric type. 1632 : @error math:errNA if there are different numbers of x's and y's. 1633 :) 1634declare %private function math:sum-x-y-deviations( 1635 $x_numbers as xs:double*, 1636 $x_average as xs:double, 1637 $y_numbers as xs:double*, 1638 $y_average as xs:double) as xs:double 1639{ 1640 if (fn:empty($x_numbers) and fn:not(fn:empty($y_numbers))) then 1641 fn:error($math:errNA, "Slope function: different number of x's and y's") 1642 else if (fn:empty($y_numbers) and fn:not(fn:empty($x_numbers))) then 1643 fn:error($math:errNA, "Slope function: different number of x's and y's") 1644 else if (fn:empty($x_numbers) and fn:empty($y_numbers)) then 1645 0 1646 else 1647 ($x_numbers[1] - $x_average) * 1648 ($y_numbers[1] - $y_average) + 1649 math:sum-x-y-deviations( 1650 fn:subsequence($x_numbers, 2),$x_average, 1651 fn:subsequence($y_numbers, 2),$y_average) 1652}; 1653 1654(:~ 1655 : Borrowed from excel module.<br/> 1656 : Returns the slope of the linear regression line through data points in known_y's and known_x's. 1657 : The slope is the vertical distance divided by the horizontal distance between 1658 : any two points on the line, which is the rate of change along the regression line. 1659 : It computes the formula:<br/> 1660 : sum((x - average_x)(y - average_y)) / sum((x - average_x)^2) <br/> 1661 : where average_x and average_y are computed with AVERAGE function. 1662 : 1663 : @see http://office.microsoft.com/en-us/excel/HP052092641033.aspx 1664 : @param $known_y the sequence of y numbers. 1665 : The sequence can be of any length, from 1 up. 1666 : @param $known_x the sequence of x numbers. 1667 : The sequence can be of any length, from 1 up. 1668 : @return The slope value, as numeric type 1669 : @error math:errNA if there are different numbers of x's and y's or if the sequence is empty 1670 : @error math:errDiv0 if all x's are equal 1671 : @example test/rbkt/Queries/zorba/math/from_excel/excel_slope1.xq 1672:) 1673declare function math:slope($known_y as xs:double+, 1674 $known_x as xs:double+) as xs:double 1675{ 1676 if (fn:empty($known_y) or fn:empty($known_x)) then 1677 fn:error($math:errNA, "Slope function: known_x and known_y cannot be empty sequences") 1678 else 1679 let $x_average := fn:avg($known_x) 1680 let $y_average := fn:avg($known_y) 1681 let $xsq_dev := math:sumsq-deviations($known_x, $x_average) return 1682 if ($xsq_dev = 0) then 1683 fn:error($math:errDiv0, "Slope function: all x's are equal") 1684 else 1685 let $x_y_dev := math:sum-x-y-deviations($known_x, $x_average, $known_y, $y_average) return 1686 $x_y_dev div $xsq_dev 1687}; 1688 1689(:~ 1690 : Borrowed from excel module.<br/> 1691 : Returns a normalized value from a distribution characterized by mean and standard_dev.<br/> 1692 : The formula is (x - mean) / standard_dev . 1693 : 1694 : @see http://office.microsoft.com/en-us/excel/HP052092731033.aspx 1695 : @param $x is the value you want to normalize 1696 : @param $mean is the arithmetic mean of the distribution. 1697 : @param $standard_dev is the standard deviation of the distribution. 1698 : @return The normalized x, as numeric type 1699 : @error math:errNum if standard_dev is a value smaller than zero or equal 1700 : @example test/rbkt/Queries/zorba/math/from_excel/excel_standardize1.xq 1701:) 1702declare function math:standardize($x as xs:double, 1703 $mean as xs:double, 1704 $standard_dev as xs:double) as xs:double 1705{ 1706 if ($standard_dev le 0) then 1707 fn:error($math:errNum, "Standardize function: standard_dev must be positive ", $standard_dev) 1708 else 1709 ($x - $mean) div $standard_dev 1710}; 1711 1712 1713(:~ 1714 : Borrowed from excel module.<br/> 1715 : Estimates standard deviation based on a sample. 1716 : The standard deviation is a measure of how widely values are dispersed 1717 : from the average value (the mean). 1718 : It is computed with formula: 1719 : sqrt( sum((x-average_x)^2) / (n-1) ) = sqrt ( VAR(numbers) ) 1720 : 1721 : @see http://office.microsoft.com/en-us/excel/HP052092771033.aspx 1722 : @param $numbers the sequence of numbers 1723 : The sequence can be of any length, from 1 up. 1724 : @return the standard deviation, as numeric type 1725 : @example test/rbkt/Queries/zorba/math/from_excel/excel_stdev1.xq 1726:) 1727declare function math:stdev($numbers as xs:double+) as xs:double 1728{ 1729 W3Cmath:sqrt(math:var($numbers)) 1730}; 1731 1732(:~ 1733 : Borrowed from excel module.<br/> 1734 : Estimates standard deviation based on a sample. 1735 : The standard deviation is a measure of how widely values are dispersed 1736 : from the average value (the mean). 1737 : It is computed with formula: 1738 : sqrt( sum((x-average_x)^2) / (n-1) ) = sqrt ( VARA(numbers) ) 1739 : 1740 : @see http://office.microsoft.com/en-us/excel/HP052092791033.aspx 1741 : @param $numbers the sequence of numbers. 1742 : The sequence can be of any length, from 1 up. 1743 : @return the standard deviation, as numeric type 1744 : @example test/rbkt/Queries/zorba/math/from_excel/excel_stdeva1.xq 1745:) 1746declare function math:stdeva($numbers as xs:double+) as xs:double 1747{ 1748 W3Cmath:sqrt(math:vara($numbers)) 1749}; 1750 1751(:~ 1752 : Borrowed from excel module.<br/> 1753 : Calculates standard deviation based on the entire population given as arguments. 1754 : The standard deviation is a measure of how widely values are dispersed from 1755 : the average value (the mean). 1756 : It is computed with formula: 1757 : sqrt( sum((x-average_x)^2) / n ) = sqrt ( VARP(numbers) ) 1758 : 1759 : @see http://office.microsoft.com/en-us/excel/HP052092811033.aspx 1760 : @param $numbers the sequence of numbers or values castable to numeric 1761 : The sequence can be of any length, from 1 up. 1762 : @return the standard deviation, as numeric type 1763 : @example test/rbkt/Queries/zorba/math/from_excel/excel_stdevp1.xq 1764:) 1765declare function math:stdevp($numbers as xs:double+) as xs:double 1766{ 1767 W3Cmath:sqrt(math:varp($numbers)) 1768}; 1769 1770(:~ 1771 : Borrowed from excel module.<br/> 1772 : Calculates standard deviation based on the entire population given as arguments. 1773 : The standard deviation is a measure of how widely values are dispersed from 1774 : the average value (the mean). 1775 : It is computed with formula: 1776 : sqrt( sum((x-average_x)^2) / n ) = sqrt ( VARPA(numbers) ) 1777 : 1778 : @see http://office.microsoft.com/en-us/excel/HP052092831033.aspx 1779 : @param $numbers the sequence of numbers or values castable to numeric 1780 : The sequence can be of any length, from 1 up. 1781 : @return the standard deviation, as numeric type 1782 : @example test/rbkt/Queries/zorba/math/from_excel/excel_stdevpa1.xq 1783:) 1784declare function math:stdevpa($numbers as xs:double+) as xs:double 1785{ 1786 W3Cmath:sqrt(math:varpa($numbers)) 1787}; 1788 1789(:~ 1790 : Borrowed from excel module.<br/> 1791 : Returns a subtotal in a sequence of numbers. 1792 : The function applied is given by $function_num. 1793 : 1794 : @see http://office.microsoft.com/en-us/excel/HP052092881033.aspx 1795 : @param $function_num <dl>defines the function to be applied on sequence values. 1796 : The possible values are: 1797 : <dt>1 or 101</dt> <dd> AVERAGE</dd> 1798 : <dt>2 or 102</dt> <dd> COUNT</dd> 1799 : <dt>3 or 103</dt> <dd> COUNTA</dd> 1800 : <dt>4 or 104</dt> <dd> MAX</dd> 1801 : <dt>5 or 105</dt> <dd> MIN</dd> 1802 : <dt>6 or 106</dt> <dd> PRODUCT</dd> 1803 : <dt>7 or 107</dt> <dd> STDEV</dd> 1804 : <dt>8 or 108</dt> <dd> STDEVP</dd> 1805 : <dt>9 or 109</dt> <dd> SUM</dd> 1806 : <dt>10 or 110</dt> <dd> VAR</dd> 1807 : <dt>11 or 111</dt> <dd> VARP</dd></dl> 1808 : 1809 : In this implementation there is no difference between x and 10x.<br/> 1810 : @param $numbers the sequence of numbers. 1811 : The sequence can be of any length. 1812 : @return The function result, as numeric type 1813 : @error * depends on the function called 1814 : @error math:errNum if $function_num is not a value between 1 .. 11 or 101 .. 111 1815 : @example test/rbkt/Queries/zorba/math/from_excel/excel_subtotal1.xq 1816 : @example test/rbkt/Queries/zorba/math/from_excel/excel_subtotal2.xq 1817 : @example test/rbkt/Queries/zorba/math/from_excel/excel_subtotal3.xq 1818 : @example test/rbkt/Queries/zorba/math/from_excel/excel_subtotal4.xq 1819 : @example test/rbkt/Queries/zorba/math/from_excel/excel_subtotal5.xq 1820 : @example test/rbkt/Queries/zorba/math/from_excel/excel_subtotal6.xq 1821 : @example test/rbkt/Queries/zorba/math/from_excel/excel_subtotal7.xq 1822 : @example test/rbkt/Queries/zorba/math/from_excel/excel_subtotal8.xq 1823 : @example test/rbkt/Queries/zorba/math/from_excel/excel_subtotal9.xq 1824 : @example test/rbkt/Queries/zorba/math/from_excel/excel_subtotal10.xq 1825 : @example test/rbkt/Queries/zorba/math/from_excel/excel_subtotal11.xq 1826:) 1827declare function math:subtotal($function_num as xs:integer, $numbers as xs:double*) as xs:double 1828{ 1829 if ($function_num = 1 or $function_num = 101) then 1830 fn:avg($numbers) 1831 else 1832 if ($function_num = 2 or $function_num = 102) then 1833 fn:count($numbers) 1834 else 1835 if ($function_num = 3 or $function_num = 103) then 1836 fn:count($numbers) 1837 else 1838 if ($function_num = 4 or $function_num = 104) then 1839 fn:max($numbers) 1840 else 1841 if ($function_num = 5 or $function_num = 105) then 1842 fn:min($numbers) 1843 else 1844 if ($function_num = 6 or $function_num = 106) then 1845 math:product($numbers) 1846 else 1847 if ($function_num = 7 or $function_num = 107) then 1848 math:stdev($numbers) 1849 else 1850 if ($function_num = 8 or $function_num = 108) then 1851 math:stdevp($numbers) 1852 else 1853 if ($function_num = 9 or $function_num = 109) then 1854 fn:sum($numbers) 1855 else 1856 if ($function_num = 10 or $function_num = 110) then 1857 math:var($numbers) 1858 else 1859 if ($function_num = 11 or $function_num = 111) then 1860 math:varp($numbers) 1861 else 1862 fn:error($math:errNum, "Subtotal function: function_num should be between 1 and 11 or 101 and 111") 1863}; 1864