Doorda Knowledge Base

Doorda Host Supported Functions and Operators

Doorda Host is built on Google Big Query. You can access the data directly and run standard SQL queries on the data via a web interface or any application which has a Big Query connector. If you want to utilise the web interface the following Functions and Operators are supported. Source documentation and information on Query Structures can be found on the Google support site here.

Supported functions and operators

Most SELECT statement clauses support functions. Fields referenced in a function don't need to be listed in any SELECT clause. Therefore, the following query is valid, even though the clicks field is not displayed directly:

 
#legacySQL
SELECT country, SUM(clicks) FROM table GROUP BY country;
Aggregate functions
AVG() Returns the average of the values for a group of rows ...
BIT_AND() Returns the result of a bitwise AND operation ...
BIT_OR() Returns the result of a bitwise OR operation ...
BIT_XOR() Returns the result of a bitwise XOR operation ...
CORR() Returns the Pearson correlation coefficient of a set of number pairs.
COUNT() Returns the total number of values ...
COUNT([DISTINCT]) Returns the total number of non-NULL values ...
COVAR_POP() Computes the population covariance of the values ...
COVAR_SAMP() Computes the sample covariance of the values ...
EXACT_COUNT_DISTINCT() Returns the exact number of non-NULL, distinct values for the specified field.
FIRST() Returns the first sequential value in the scope of the function.
GROUP_CONCAT() Concatenates multiple strings into a single string ...
GROUP_CONCAT_UNQUOTED() Concatenates multiple strings into a single string ... will not add double quotes ...
LAST() Returns the last sequential value ...
MAX() Returns the maximum value ...
MIN() Returns the minimum value ...
NEST() Aggregates all values in the current aggregation scope into a repeated field.
NTH() Returns the nth sequential value ...
QUANTILES() Computes approximate minimum, maximum, and quantiles ...
STDDEV() Returns the standard deviation ...
STDDEV_POP() Computes the population standard deviation ...
STDDEV_SAMP() Computes the sample standard deviation ...
SUM() Returns the sum total of the values ...
TOP() ... COUNT(*) Returns the top max_records records by frequency.
UNIQUE() Returns the set of unique, non-NULL values ...
VARIANCE() Computes the variance of the values ...
VAR_POP() Computes the population variance of the values ...
VAR_SAMP() Computes the sample variance of the values ...
Arithmetic operators
+ Addition
- Subtraction
* Multiplication
/ Division
% Modulo
Bitwise functions
& Bitwise AND
| Bitwise OR
^ Bitwise XOR
<< Bitwise shift left
>> Bitwise shift right
~ Bitwise NOT
BIT_COUNT() Returns the number of bits ...
Casting functions
BOOLEAN() Cast to boolean.
BYTES() Cast to bytes.
CAST(expr AS type) Converts expr into a variable of type type.
FLOAT() Cast to double.
HEX_STRING() Cast to hexadecimal string.
INTEGER() Cast to integer.
STRING() Cast to string.
Comparison functions
expr1 = expr2 Returns true if the expressions are equal.
expr1 != expr2
expr1 <> expr2
Returns true if the expressions are not equal.
expr1 > expr2 Returns true if expr1 is greater than expr2.
expr1 < expr2 Returns true if expr1 is less than expr2.
expr1 >= expr2 Returns true if expr1 is greater than or equal to expr2.
expr1 <= expr2 Returns true if expr1 is less than or equal to expr2.
expr1 BETWEEN expr2 AND expr3 Returns true if the value of expr1 is between expr2 and expr3, inclusive.
expr IS NULL Returns true if expr is NULL.
expr IN() Returns true if expr matches expr1expr2, or any value in the parentheses.
COALESCE() Returns the first argument that isn't NULL.
GREATEST() Returns the largest numeric_expr parameter.
IFNULL() If argument is not null, returns the argument.
IS_INF() Returns true if positive or negative infinity.
IS_NAN() Returns true if argument is NaN.
IS_EXPLICITLY_DEFINED() deprecated: Use expr IS NOT NULL instead.
LEAST() Returns the smallest argument numeric_expr parameter.
NVL() If expr is not null, returns expr, otherwise returns null_default.
Date and time functions
CURRENT_DATE() Returns current date in the format %Y-%m-%d.
CURRENT_TIME() Returns the server's current time in the format %H:%M:%S.
CURRENT_TIMESTAMP() Returns the server's current time in the format %Y-%m-%d %H:%M:%S.
DATE() Returns the date in the format %Y-%m-%d.
DATE_ADD() Adds the specified interval to a TIMESTAMP data type.
DATEDIFF() Returns the number of days between two TIMESTAMP data types.
DAY() Returns the day of the month as an integer between 1 and 31.
DAYOFWEEK() Returns the day of the week as an integer between 1 (Sunday) and 7 (Saturday).
DAYOFYEAR() Returns the day of the year as an integer between 1 and 366.
FORMAT_UTC_USEC() Returns a UNIX timestamp in the format YYYY-MM-DD HH:MM:SS.uuuuuu.
HOUR() Returns the hour of a TIMESTAMP as an integer between 0 and 23.
MINUTE() Returns the minutes of a TIMESTAMP as an integer between 0 and 59.
MONTH() Returns the month of a TIMESTAMP as an integer between 1 and 12.
MSEC_TO_TIMESTAMP() Converts a UNIX timestamp in milliseconds to a TIMESTAMP.
NOW() Returns the current UNIX timestamp in microseconds.
PARSE_UTC_USEC() Converts a date string to a UNIX timestamp in microseconds.
QUARTER() Returns the quarter of the year of a TIMESTAMP as an integer between 1 and 4.
SEC_TO_TIMESTAMP() Converts a UNIX timestamp in seconds to a TIMESTAMP.
SECOND() Returns the seconds of a TIMESTAMP as an integer between 0 and 59.
STRFTIME_UTC_USEC() Returns a date string in the format date_format_str.
TIME() Returns a TIMESTAMP in the format %H:%M:%S.
TIMESTAMP() Convert a date string to a TIMESTAMP.
TIMESTAMP_TO_MSEC() Converts a TIMESTAMP to a UNIX timestamp in milliseconds.
TIMESTAMP_TO_SEC() Converts a TIMESTAMP to a UNIX timestamp in seconds.
TIMESTAMP_TO_USEC() Converts a TIMESTAMP to a UNIX timestamp in microseconds.
USEC_TO_TIMESTAMP() Converts a UNIX timestamp in microseconds to a TIMESTAMP.
UTC_USEC_TO_DAY() Shifts a UNIX timestamp in microseconds to the beginning of the day it occurs in.
UTC_USEC_TO_HOUR() Shifts a UNIX timestamp in microseconds to the beginning of the hour it occurs in.
UTC_USEC_TO_MONTH() Shifts a UNIX timestamp in microseconds to the beginning of the month it occurs in.
UTC_USEC_TO_WEEK() Returns a UNIX timestamp in microseconds that represents a day in the week.
UTC_USEC_TO_YEAR() Returns a UNIX timestamp in microseconds that represents the year.
WEEK() Returns the week of a TIMESTAMP as an integer between 1 and 53.
YEAR() Returns the year of a TIMESTAMP.
IP functions
FORMAT_IP() Converts 32 least significant bits of integer_value to human-readable IPv4 address string.
PARSE_IP() Converts a string representing IPv4 address to unsigned integer value.
FORMAT_PACKED_IP() Returns a human-readable IP address in the form 10.1.5.23 or 2620:0:1009:1:216:36ff:feef:3f.
PARSE_PACKED_IP() Returns an IP address in BYTES.
JSON functions
JSON_EXTRACT() Selects a value according to the JSONPath expression and returns a JSON string.
JSON_EXTRACT_SCALAR() Selects a value according to the JSONPath expression and returns a JSON scalar.
Logical operators
expr AND expr Returns true if both expressions are true.
expr OR expr Returns true if one or both expressions are true.
NOT expr Returns true if the expression is false.
Mathematical functions
ABS() Returns the absolute value of the argument.
ACOS() Returns the arc cosine of the argument.
ACOSH() Returns the arc hyperbolic cosine of the argument.
ASIN() Returns the arc sine of the argument.
ASINH() Returns the arc hyperbolic sine of the argument.
ATAN() Returns the arc tangent of the argument.
ATANH() Returns the arc hyperbolic tangent of the argument.
ATAN2() Returns the arc tangent of the two arguments.
CEIL() Rounds the argument up to the nearest whole number and returns the rounded value.
COS() Returns the cosine of the argument.
COSH() Returns the hyperbolic cosine of the argument.
DEGREES() Converts from radians to degrees.
EXP() Returns e to the power of the argument.
FLOOR() Rounds the argument down to the nearest whole number.
LN()
LOG()
Returns the natural logarithm of the argument.
LOG2() Returns the Base-2 logarithm of the argument.
LOG10() Returns the Base-10 logarithm of the argument.
PI() Returns the constant π.
POW() Returns first argument to the power of the second argument.
RADIANS() Converts from degrees to radians.
RAND() Returns a random float value in the range 0.0 <= value < 1.0.
ROUND() Rounds the argument either up or down to the nearest whole number.
SIN() Returns the sine of the argument.
SINH() Returns the hyperbolic sine of the argument.
SQRT() Returns the square root of the expression.
TAN() Returns the tangent of the argument.
TANH() Returns the hyperbolic tangent of the argument.
Regular expression functions
REGEXP_MATCH() Returns true if the argument matches the regular expression.
REGEXP_EXTRACT() Returns the portion of the argument that matches the capturing group within the regular expression.
REGEXP_REPLACE() Replaces a substring that matches a regular expression.
String functions
CONCAT() Returns the concatenation of two or more strings, or NULL if any of the values are NULL.
expr CONTAINS 'str' Returns true if expr contains the specified string argument.
INSTR() Returns the one-based index of the first occurrence of a string.
LEFT() Returns the leftmost characters of a string.
LENGTH() Returns the length of the string.
LOWER() Returns the original string with all characters in lower case.
LPAD() Inserts characters to the left of a string.
LTRIM() Removes characters from the left side of a string.
REPLACE() Replaces all occurrences of a substring.
RIGHT() Returns the rightmost characters of a string.
RPAD() Inserts characters to the right side of a string.
RTRIM() Removes trailing characters from the right side of a string.
SPLIT() Splits a string into repeated substrings.
SUBSTR() Returns a substring ...
UPPER() Returns the original string with all characters in upper case.
Table wildcard functions
TABLE_DATE_RANGE() Queries multiple daily tables that span a date range.
TABLE_DATE_RANGE_STRICT() Queries multiple daily tables that span a date range, with no missing dates.
TABLE_QUERY() Queries tables whose names match a specified predicate.
URL functions
HOST() Given a URL, returns the host name as a string.
DOMAIN() Given a URL, returns the domain as a string.
TLD() Given a URL, returns the top level domain plus any country domain in the URL.
Window functions
AVG()
COUNT(*)
COUNT([DISTINCT])
MAX()
MIN()
STDDEV()
SUM()
The same operation as the corresponding Aggregate functions, but are computed over a window defined by the OVER clause.
CUME_DIST() Returns a double that indicates the cumulative distribution of a value in a group of values ...
DENSE_RANK() Returns the integer rank of a value in a group of values.
FIRST_VALUE() Returns the first value of the specified field in the window.
LAG() Enables you to read data from a previous row within a window.
LAST_VALUE() Returns the last value of the specified field in the window.
LEAD() Enables you to read data from a following row within a window.
NTH_VALUE() Returns the value of <expr> at position <n> of the window frame ...
NTILE() Divides the window into the specified number of buckets.
PERCENT_RANK() Returns the rank of the current row, relative to the other rows in the partition.
PERCENTILE_CONT() Returns an interpolated value that would map to the percentile argument with respect to the window ...
PERCENTILE_DISC() Returns the value nearest the percentile of the argument over the window.
RANK() Returns the integer rank of a value in a group of values.
RATIO_TO_REPORT() Returns the ratio of each value to the sum of the values.
ROW_NUMBER() Returns the current row number of the query result over the window.
Other functions
CASE WHEN ... THEN Use CASE to choose among two or more alternate expressions in your query.
CURRENT_USER() Returns the email address of the user running the query.
EVERY() Returns true if the argument is true for all of its inputs.
FROM_BASE64() Converts the base-64 encoded input string into BYTES format.
HASH() Computes and returns a 64-bit signed hash value ...
FARM_FINGERPRINT() Computes and returns a 64-bit signed fingerprint value ...
IF() If first argument is true, returns second argument; otherwise returns third argument.
POSITION() Returns the one-based, sequential position of the argument.
SHA1() Returns a SHA1 hash, in BYTES format.
SOME() Returns true if argument is true for at least one of its inputs.
TO_BASE64() Converts the BYTES argument to a base-64 encoded string.
    Attached Files
    There are no attachments for this article.
    Related Articles
    API Documentation
    Viewed 315 times since Wed, Feb 14, 2018
    Connecting to BigQuery with Jetbrains Datagrip (and most IDE)
    Viewed 162 times since Fri, Jun 22, 2018
    Google Cloud Authentication
    Viewed 245 times since Wed, Sep 12, 2018