Math Functions

This topic describes the mathematical functions in Graph Lakehouse.

ABS: Calculates the absolute value of the specified number.
ADD: Adds two numeric values.
AVG: Calculates the average (arithmetic mean) value for a group of numbers.
BASE: Converts a number to the specified base and returns a text representation.
CEIL: Rounds up a numeric value to the nearest integer.
COS: Calculates the cosine of an angle.
EXP: Raises e to the specified power.
FACT: Calculates the factorial of the specified number.
FLOOR: Rounds down a numeric value to the nearest integer.
HAMMING_DIST: Calculates the hamming distance between two values.
HAVERSINE_DIST: Computes the haversine distance between two latitude and longitude values.
LN: Calculates the natural logarithm of a double value.
LOG: Calculates the specified base logarithm of a double value.
LOG2: Calculates the base two logarithm of a double value.
MOD: Calculates the modulo of the division between two numbers.
PI: Returns the value for PI.
POWER: Raises the specified number to the specified power.
RADIANS: Converts to radians an angle value that is in degrees.
RAND: Returns a random double value between 0 and 1.
RANDBETWEEN: Returns a random integer that falls between two specified integers.
ROUND: Rounds a numeric value to the nearest integer.
ROUNDDOWN: Rounds a numeric value down to the specified number of digits.
ROUNDUP: Rounds a numeric value up to the specified number of digits.
SIN: Calculates the sine of an angle.
SQRT: Calculates the square root of a number.
TAN: Calculates the tangent of an angle.

Typographical Conventions

The following list describes the conventions used to document function syntax:

CAPS: Although SPARQL is case-insensitive, SPARQL keywords in this section are written in uppercase for readability.
[ argument ]: Brackets indicate an optional argument or keyword.
|: Means OR. Indicates that you can use one or more of the specified options.

ABS

This function calculates the absolute value of the specified number.

Syntax

ABS(number)

Argument	Type	Description
number	numeric	The numeric value for which to calculate the absolute value.

Returns

Type	Description
number	The absolute value.

Example

The following example queries the sample Tickit data to find the absolute value of the price per ticket minus the total price paid for each of the ticket listings.

PREFIX tickit: <http://anzograph.com/tickit/>
SELECT ?listing (ABS(?priceper - ?total) AS ?absolute_value)
FROM <http://anzograph.com/tickit>
WHERE {
  ?listing tickit:priceperticket ?priceper .
  ?listing tickit:totalprice ?total .
}
ORDER BY ?listing
LIMIT 10

listing                                   | absolute_value
------------------------------------------+----------------
http://anzograph.com/tickit/listing1      |           1638
http://anzograph.com/tickit/listing10     |           2955
http://anzograph.com/tickit/listing100    |           3059
http://anzograph.com/tickit/listing1000   |            928
http://anzograph.com/tickit/listing10000  |           1350
http://anzograph.com/tickit/listing100001 |            410
http://anzograph.com/tickit/listing100002 |           5502
http://anzograph.com/tickit/listing100003 |           3146
http://anzograph.com/tickit/listing100004 |            368
http://anzograph.com/tickit/listing100006 |           6960
10 rows

ADD

This function adds two numeric values.

Syntax

ADD(value1, value2)

Argument	Type	Description
value1	numeric	The first numeric value to add.
value2	numeric	The second numeric value to add.

Returns

Type	Description
number	The result of the addition operation.

AVG

This function calculates the average (arithmetic mean) value for a group of numbers.

Syntax

AVG(number)

Argument	Type	Description
number	numeric	The numeric value for which to calculate the average.

Returns

Type	Description
number	The arithmetic mean of the input values.

Examples

The following example queries the sample Tickit data set to determine the average number of seats in the venues in each state. Since the results clause contains a non-aggregated variable (?state), a GROUP BY clause is required for grouping on ?state.

PREFIX tickit: <http://anzograph.com/tickit/>
SELECT ?state (ROUND(AVG(?seats)) AS ?avg_seats)
FROM <http://anzograph.com/tickit>
WHERE {
  ?s tickit:venuestate ?state .
  ?s tickit:venueseats ?seats .
}
GROUP BY ?state
ORDER BY ?state

state | avg_seats
------+-----------
CA    |     50309
CO    |     63285
DC    |     41888
FL    |     62603
GA    |     60620
IL    |     48244
IN    |     63000
LA    |     72000
MA    |     54342
MD    |     70229
MI    |     53391
MN    |     64035
MO    |     59217
NC    |     73298
NJ    |     80242
NY    |     48764
OH    |     56035
ON    |     50516
PA    |     53931
TN    |     68804
TX    |     56915
WA    |     57058
WI    |     57561
23 rows

The query below calculates the average total price for all of the listings in the sample Tickit data set:

PREFIX tickit: <http://anzograph.com/tickit/>
SELECT (AVG(?numtickets*?priceperticket) AS ?avg_total_price)
FROM <http://anzograph.com/tickit>
WHERE {
  ?listing tickit:priceperticket ?priceperticket . 
  ?listing tickit:numtickets ?numtickets .
}

avg_total_price
-----------------
3034.42
1 rows

BASE

This function converts a number into the specified base and returns a text representation of the calculated value.

Syntax

BASE(number, base [, min_length ])

Argument	Type	Description
number	int	The integer to convert. Valid values are 0–2^53.
base	int	The radix to convert the `number` to. Valid values are 2–36.
min_length	int	Optional argument that specifies the minimum length of the returned string. If the result is shorter than the minimum length, leading zeros are added to the result so that it reaches the minimum length. Valid values are 0–255.

Returns

Type	Description
string	The text representation of the base.

CEIL

This function rounds up a numeric value to the nearest integer if the value has a fractional part. CEILING returns the value itself if it is a whole number.

Syntax

CEIL(number)

Argument	Type	Description
number	numeric	The numeric value to round up.

Returns

Type	Description
number	The rounded up value.

COS

This function calculates the cosine of the specified angle.

Syntax

COS(angle)

Argument	Type	Description
angle	double	The angle in radians (double data type) to calculate the cosine for. If you have angle values in degrees, you can use RADIANS to convert the degrees to radians.

Returns

Type	Description
double	The cosine of the angle.

EXP

This function raises the base of the natural logarithms, e, to the specified power.

Syntax

EXP(power)

Argument	Type	Description
power	double	The number to raise e to.

Returns

Type	Description
double	E raised to the specified power.

FACT

This function calculates the factorial of the specified number.

Syntax

FACT(number)

Argument	Type	Description
number	int	The number for which to calculate the factorial.

Returns

Type	Description
int	The factorial of the input values.

FLOOR

This function rounds down a numeric value to the nearest integer if the value has a fractional part. FLOOR returns the value itself if it is a whole number.

Syntax

FLOOR(number)

Argument	Type	Description
number	numeric	The numeric value to round down.

Returns

Type	Description
number	The rounded down value.

HAMMING_DIST

This function calculates the hamming distance between two values.

Syntax

HAMMING_DIST(number1, number2)

Argument	Type	Description
number1	long	The first number.
number2	long	The second number.

Returns

Type	Description
int	The hamming distance.

HAVERSINE_DIST

This function computes the haversine distance between two latitude and longitude values and returns the distance in kilometers.

Syntax

HAVERSINE_DIST(latitude1, longitude1, latitude2, longitude2)

Argument	Type	Description
latitude1	double	The first latitude value.
longitude1	double	The first longitude value.
latitude2	double	The second latitude value.
longitude2	double	The second longitude value.

Returns

Type	Description
double	The distance in kilometers.

LN

This function calculates the natural logarithm of a double value.

Syntax

LN(number)

Argument	Type	Description
number	double	The double value for which to calculate the natural logarithm.

Returns

Type	Description
double	The natural logarithm of the input value.

LOG

This function calculates the specified base logarithm of a double value.

Syntax

LOG(number [, base ])

Argument	Type	Description
number	double	The double value for which to calculate the `base` logarithm.
base	double	An optional double value that specifies the base for the logarithm. If omitted, base e is used.

Returns

Type	Description
double	The base logarithm of the input value.

LOG2

This function calculates the base two logarithm of a double value.

Syntax

LOG2(number)

Argument	Type	Description
number	double, float	The double value for which to calculate the base 2 logarithm.

Returns

Type	Description
double, float	The base two logarithm of the input value.

Example

The example below determines the base two logarithm of the quantity of tickets sold for each ticket listing.

SELECT ?sale ?qtysold (LOG2(?qtysold) AS ?qtylog)
FROM <http://anzograph.com/tickit>
WHERE { 
  ?sale <http://anzograph.com/tickit/qtysold> ?qtysold .
}
ORDER BY ?sale
LIMIT 10

sale                                    | qtysold | qtylog
----------------------------------------+---------+---------
http://anzograph.com/tickit/sales1      |       4 |       2
http://anzograph.com/tickit/sales10     |       1 |       0
http://anzograph.com/tickit/sales100    |       2 |       1
http://anzograph.com/tickit/sales1000   |       3 | 1.58496
http://anzograph.com/tickit/sales10000  |       1 |       0
http://anzograph.com/tickit/sales100000 |       1 |       0
http://anzograph.com/tickit/sales100001 |       2 |       1
http://anzograph.com/tickit/sales100002 |       1 |       0
http://anzograph.com/tickit/sales100003 |       2 |       1
http://anzograph.com/tickit/sales100004 |       2 |       1
10 rows

MOD

This function calculates the modulo or remainder of the division between two numbers.

The calculation of negative operands depends on C++ and your underlying hardware. Graph Lakehouse uses FMOD for floating point operands and % for all other data types.

Syntax

MOD(number, divisor)

Argument	Type	Description
number	numeric	The number that is the dividend in the equation.
divisor	numeric	The number to divide the dividend by.

Returns

Type	Description
number	The modulo between the input numbers.

Example

The following example queries the sample Tickit dataset to find the modulo between the number of seats in each venue and the population of the city the venue is in.

PREFIX tickit: <http://anzograph.com/tickit/>
SELECT ?venue ?city (MOD(?pop, ?seats) AS ?modulo)
FROM <http://anzograph.com/tickit>
WHERE {
  ?s tickit:venuename ?venue .
  ?s tickit:venuecitypop ?pop .
  ?s tickit:venuecity ?city .
  ?s tickit:venueseats ?seats .
}
ORDER BY ?venue
LIMIT 10

venue                    | city          | modulo
-------------------------+---------------+--------
ARCO Arena               | Sacramento    |    638
AT&T Park                | San Francisco |  16678
Angel Stadium of Anaheim | Anaheim       |  26011
Arrowhead Stadium        | Kansas City   |  62532
Bank of America Stadium  | Charlotte     |  71742
Busch Stadium            | St. Louis     |  20109
Citizens Bank Park       | Philadelphia  |  42008
Cleveland Browns Stadium | Cleveland     |  30815
Comerica Park            | Detroit       |   3483
Coors Field              | Denver        |  45263
10 rows

PI

This function returns the value for PI.

Syntax

PI()

Returns

Type	Description
double	The PI value.

POWER

This function raises the specified number to the specified power.

Syntax

POWER(value, power)

Argument	Type	Description
value	numeric	The number to raise by the `power`.
power	numeric	The number to raise `value` by.

Returns

Type	Description
number	The result of `value` raised to the specified `power`.

Example

The following example queries the sample Tickit dataset to raise the total number of tickets sold for each event by the power of 2:

PREFIX tickit: <http://anzograph.com/tickit/>
SELECT ?event (POWER(?tickets, 2) AS ?power_total)
FROM <http://anzograph.com/tickit>
WHERE {
  SELECT ?event (sum(?qty) as ?tickets)
  WHERE {
    ?sales tickit:qtysold ?qty .
    ?sales tickit:eventid ?eventid .
    ?eventid tickit:eventname ?event .
  }
GROUP BY ?event
ORDER BY desc(?tickets)
LIMIT 10
}

event            | power_total
-----------------+-------------
Mamma Mia!       | 1.3381e+07
Spring Awakening | 9.15062e+06
The Country Girl | 8.24264e+06
Jersey Boys      | 7.73396e+06
Macbeth          | 7.46929e+06
Chicago          | 6.42622e+06
Legally Blonde   | 5.16198e+06
Spamalot         | 4.8356e+06
Rhinoceros       | 3.6481e+06
Thurgood         | 3.58724e+06
10 rows

RADIANS

This function converts to radians an angle value that is in degrees.

Syntax

RADIANS(angle)

Argument	Type	Description
angle	double	The angle value to convert to radians.

Returns

Type	Description
double	The angle in radians.

RAND

This function returns a random double value between 0 and 1, including 0 and excluding 1.

Syntax

RAND()

Returns

Type	Description
double	The random value between 0 and 1.

RANDBETWEEN

This function returns a random integer that falls between the two specified integers. The two integers are included as options to be returned.

Syntax

RANDBETWEEN(low_number, high_number)

Argument	Type	Description
low_number	int	The lowest integer in the range of values.
high_number	int	The highest integer in the range of values.

Returns

Type	Description
int	The random value between the given low and high numbers.

ROUND

This function rounds a numeric value to the nearest integer.

Syntax

ROUND(number)

Argument	Type	Description
number	numeric	The number to round to the nearest integer.

Returns

Type	Description
number	The rounded value.

ROUNDDOWN

This function rounds a numeric value down to the specified number of digits.

Syntax

ROUNDDOWN(number, num_digits)

Argument	Type	Description
number	numeric	The number to round down.
num_digits	int	An integer that specifies the number of digits to round down to.

Returns

Type	Description
number	The rounded down value.

ROUNDUP

This function rounds a numeric value up to the specified number of digits.

Syntax

ROUNDUP(number, num_digits)

Argument	Type	Description
number	numeric	The number to round up.
num_digits	int	An integer that specifies the number of digits to round up to.

Returns

Type	Description
number	The rounded up value.

SIN

This function calculates the sine of the specified angle.

Syntax

SIN(angle)

Argument	Type	Description
angle	double	The angle in radians to calculate the sine for. If you have angle values in degrees, you can use RADIANS to convert the degrees to radians.

Returns

Type	Description
double	The sine of the angle.

SQRT

This function calculates the square root of the specified number.

Syntax

SQRT(number)

Argument	Type	Description
number	numeric	The number for which to calculate the square root.

Returns

Type	Description
double	The square root of the input value.

TAN

This function calculates the tangent of the specified angle.

Syntax

TAN(angle)

Argument	Type	Description
angle	double	The angle in radians to calculate the tangent for. If you have angle values in degrees, you can use RADIANS to convert the degrees to radians.

Returns

Type	Description
double	The tangent of the angle.