Query SQL Functions and Operators

This query computes the most common first words in the ngram sample dataset that contain the letter a and occur at most 10,000 times.

SELECT
  first,
  COUNT(ngram) ngram_count
FROM
  publicdata:samples.trigrams
GROUP BY
  1
HAVING
  first contains "a"
  AND ngram_count < 10000
ORDER BY
  2 DESC
LIMIT 10;

ORDER BY Clause:

The ORDER BY clause sorts the results of a query in ascending or descending order using one or more key fields. To sort by multiple fields or aliases, enter them as a comma-separated list. The results are sorted on the fields in the order in which they are listed. Use DESC (descending) or ASC (ascending) to specify the sort direction. ASC is the default. A different sort direction can be specified for each sort key.

The ORDER BY clause is evaluated after the SELECT clause so it can reference the output of any expression computed in the SELECT. If a field is given an alias in the SELECT clause, the alias must be used in the ORDER BY clause.

LIMIT Clause:

The LIMIT clause limits the number of rows in the returned result set. Since Query queries regularly operate over very large numbers of rows, LIMIT is a good way to avoid long-running queries by processing only a subset of the rows.

NOTE: The LIMIT clause will stop processing and return results when it satisfies your requirements. This can reduce processing time for some queries, but when you specify aggregate functions such as COUNT or RDER BY clauses, the full result set must still be processed before returning results. The LIMIT clause is the last to be evaluated.

NOTE: A query with a LIMIT clause may still be non-deterministic if there is no operator in the query that guarantees the ordering of the output result set. This is because Query executes using a large number of parallel workers. The order in which parallel jobs return is not guaranteed.

NOTE: The LIMIT clause cannot contain any functions; it takes only a numeric constant.

Query Grammar

The individual clauses of Query SELECT statements are described in detail above. Here we present the full grammar of SELECT statements in a compact form with links back to the individual sections.

query:
    SELECT { * | field_path.* | expression } [ [ AS ] alias ] [ , ... ]
    [ FROM from_body
      [ WHERE bool_expression ]
      [ OMIT RECORD IF bool_expression]
      [ GROUP [ EACH ] BY [ ROLLUP ] { field_name_or_alias } [ , ... ] ]
      [ HAVING bool_expression ]
      [ ORDER BY field_name_or_alias [ { DESC | ASC } ] [, ... ] ]
      [ LIMIT n ]
    ];

from_body:
    {
      from_item [, ...] |  # Warning: Comma means UNION ALL here
      from_item [ join_type ] JOIN [ EACH ] from_item [ ON join_predicate ] |
      (FLATTEN({ table_name | (query) }, field_name_or_alias)) |
      table_wildcard_function
    }

from_item:
    { table_name | (query) } [ [ AS ] alias ]

join_type:
    { INNER | [ FULL ] [ OUTER ] | RIGHT [ OUTER ] | LEFT [ OUTER ] | CROSS }

join_predicate:
    field_from_one_side_of_the_join = field_from_the_other_side_of_the_join [ AND ...]

expression:
    {
      literal_value |
      field_name_or_alias |
      function_call
    }

bool_expression:
    {
      expression_which_results_in_a_boolean_value |
      bool_expression AND bool_expression |
      bool_expression OR bool_expression |
      NOT bool_expression
    }

A. Square brackets “[ ]” indicate optional clauses.
B. Curly braces “{ }” enclose a set of options.
C. The vertical bar “|” indicates a logical OR.
D. A comma or keyword followed by an ellipsis within square brackets “[, … ]” indicates that the preceding item can repeat in a list with the specified separator.
E. Parentheses “( )” indicate literal parentheses.

Aggregate Functions

Aggregate functions return values that represent summaries of larger sets of data, which makes these functions particularly useful for analyzing logs. An aggregate function operates against a collection of values and returns a single value per table, group, or scope:

• Table Aggregation:
  Uses an aggregate function to summarize all qualifying rows in the table. For example:

  SELECT COUNT(f1) FROM ds.Table;

• Group Aggregation:
  Uses an aggregate function and a GROUP BY clause that specifies a non-aggregated field to summarize rows by group. For example:

  SELECT COUNT(f1) FROM ds.Table GROUP BY b1; 

  The TOP function represents a specialized case of group aggregation.

• Scoped Aggregation:
  This feature applies only to tables that have nested fields.
  Uses an aggregate function and the WITHIN keyword to aggregate repeated values within a defined scope. For example:

  SELECT COUNT(m1.f2) WITHIN RECORD FROM Table;

  The scope can be RECORD, which corresponds to entire row, or a node (repeated field in a row). Aggregation functions operate over the values within the scope and return aggregated results for each record or node.

You can apply a restriction to an aggregate function using one of the following options:

• An alias in a subselect query. The restriction is specified in the outer WHERE clause.

  SELECT corpus, count_corpus_words
  FROM
    (SELECT corpus, count(word) AS count_corpus_words
    FROM publicdata:samples.shakespeare
    GROUP BY corpus) AS sub_shakespeare
  WHERE count_corpus_words > 4000

• An alias in a HAVING clause.

  SELECT corpus, count(word) AS count_corpus_words
  FROM publicdata:samples.shakespeare
  GROUP BY corpus
  HAVING count_corpus_words > 4000;

You can also refer to an alias in the GROUP BY or ORDER BY clauses.

Syntax:

TOP() functions —

TOP is a function that is an alternative to the GROUP BY clause. It is used as simplified syntax for GROUP BY … ORDER BY … LIMIT …. Generally, the TOP function performs faster than the full … GROUP BY … ORDER BY … LIMIT … query, but may only return approximate results. The following is the syntax for the TOP function:

TOP(field|alias[, max_values][,multiplier]) … COUNT(*)

When using TOP in a SELECT clause, you must include COUNT(*) as one of the fields.

A query that uses the TOP() function can return only two fields: the TOP field, and the COUNT(*) value.

TOP() examples —

• Basic example queries that use TOP():

The following queries use TOP() to return 10 rows.

SELECT
  TOP(word, 10) as word, COUNT(*) as cnt
FROM
  publicdata:samples.shakespeare
WHERE
  word CONTAINS "th";

SELECT
  word, left(word, 3)
FROM
  (SELECT TOP(word, 10) AS word, COUNT(*)
     FROM publicdata:samples.shakespeare
     WHERE word CONTAINS "th");

 

• Compare TOP() to GROUP BY…ORDER BY…LIMIT:

The query returns, in order, the top 10 most frequently used words containing “th”, and the number of documents the words was used in. The TOP query will execute much faster:

Example without TOP():

SELECT
  word, COUNT(*) AS cnt
FROM
  ds.Table
WHERE
  word CONTAINS 'th'
GROUP BY
  word
ORDER BY
  cnt DESC LIMIT 10;

Example with TOP():

SELECT
  TOP(word, 10), COUNT(*)
FROM
  ds.Table
WHERE
  word contains 'th';

 

• Using the multiplier parameter:

The following queries show how the multiplier parameter affects the query result. The first query returns the number of births per month in Wyoming. The second query uses to multiplier parameter to multiply the cnt values by 100.

Example without the multiplier parameter:

SELECT
  TOP(month,3) as month, COUNT(*) as cnt
FROM
  publicdata:samples.natality
WHERE
  state = "WY";

Returns:

+-------+-------+
| month |  cnt  |
+-------+-------+
|   7   | 19594 |
|   5   | 19038 |
|   8   | 19030 |
+-------+-------+

Example with the multiplier parameter:

SELECT
  TOP(month,3,100) as month, COUNT(*) as cnt
FROM
  publicdata:samples.natality
WHERE
  state = "WY";

Returns:

+-------+---------+
| month |   cnt   |
+-------+---------+
|   7   | 1959400 |
|   5   | 1903800 |
|   8   | 1903000 |
+-------+---------+ 

NOTE: You must include COUNT(*) in the SELECT clause to use TOP.

Advanced Examples:

• Average and standard deviation grouped by condition

The following query returns the average and standard deviation of birth weights in Ohio in 2003, grouped by mothers who do and do not smoke.

Example:

SELECT
  cigarette_use,
  /* Finds average and standard deviation */
  AVG(weight_pounds) baby_weight,
  STDDEV(weight_pounds) baby_weight_stdev,
  AVG(mother_age) mother_age
FROM
  [publicdata:samples.natality]
WHERE
  year=2003 AND state='OH'
/* Group the result values by those */
/* who smoked and those who didn't.  */
GROUP BY
  cigarette_use;

 

• Filter query results using an aggregated value

In order to filter query results using an aggregated value (for example, filtering by the value of a SUM), use the HAVING function. HAVING compares a value to a result determined by an aggregation function, as opposed to WHERE, which operates on each row prior to aggregation.

Example:

SELECT
  state,
  /* If 'is_male' is True, return 'Male', */
  /* otherwise return 'Female' */
  IF (is_male, 'Male', 'Female') AS sex,
  /* The count value is aliased as 'cnt' */
  /* and used in the HAVING clause below. */
  COUNT(*) AS cnt
FROM
  [publicdata:samples.natality]
WHERE
  state != ''
GROUP BY
  state, sex
HAVING
  cnt > 3000000
ORDER BY
  cnt DESC

Returns:

+-------+--------+---------+
| state |  sex   |   cnt   |
+-------+--------+---------+
| CA    | Male   | 7060826 |
| CA    | Female | 6733288 |
| TX    | Male   | 5107542 |
| TX    | Female | 4879247 |
| NY    | Male   | 4442246 |
| NY    | Female | 4227891 |
| IL    | Male   | 3089555 |
+-------+--------+---------+

Arithmetic Operators

Arithmetic operators take numeric arguments and return a numeric result. Each argument can be a numeric literal or a numeric value returned by a query. If the arithmetic operation evaluates to an undefined result, the operation returns NULL.

Syntax:

Bitwise Functions

Bitwise functions operate at the level of individual bits and require numerical arguments.

Three additional bitwise functions, BIT_AND, BIT_OR and BIT_XOR, are documented in aggregate functions.

Syntax:

Casting Functions

Casting functions change the data type of a numeric expression. Casting functions are particularly useful for ensuring that arguments in a comparison function have the same data type.

Syntax:

Comparison Functions

Comparison functions return true or false, based on the following types of comparisons:

• A comparison of two expressions.
• A comparison of an expression or set of expressions to a specific criteria, such as being in a specified list, being NULL, or being a non-default optional value.

Some of the functions listed below return values other than true or false, but the values they return are based on comparison operations.

You can use either numeric or string expressions as arguments for comparison functions. (String constants must be enclosed in single or double quotes.) The expressions can be literals or values fetched by a query. Comparison functions are most often used as filtering conditions in WHERE clauses, but they can be used in other clauses.

Syntax:

Date and Time Functions

Advanced Examples:

• Convert integer timestamp results into human-readable format

The following query finds the top 5 moments in time in which the most Wikipedia revisions took place. In order to display results in a human-readable format, use Kochava Query’s FORMAT_UTC_USEC() function, which takes a timestamp, in microseconds, as an input. This query multiplies the Wikipedia POSIX format timestamps (in seconds) by 1000000 to convert the value into microseconds.

Example:

SELECT
  /* Multiply timestamp by 1000000 and convert */
  /* into a more human-readable format. */
  TOP (FORMAT_UTC_USEC(timestamp * 1000000), 5)
    AS top_revision_time,
  COUNT (*) AS revision_count
FROM
  [publicdata:samples.wikipedia]; 

Returns:

+----------------------------+----------------+
|     top_revision_time      | revision_count |
+----------------------------+----------------+
| 2002-02-25 15:51:15.000000 |          20976 |
| 2002-02-25 15:43:11.000000 |          15974 |
| 2010-02-02 03:34:51.000000 |              3 |
| 2010-02-02 01:04:59.000000 |              3 |
| 2010-02-01 23:55:05.000000 |              3 |
+----------------------------+----------------+

 

• Bucketing Results by Timestamp

It’s useful to use date and time functions to group query results into buckets corresponding to particular years, months, or days. The following example uses the UTC_USEC_TO_MONTH() function to display how many characters each Wikipedia contributor uses in their revision comments per month.

Example:

SELECT
  contributor_username,
  /* Return the timestamp shifted to the
   * start of the month, formatted in
   * a human-readable format. Uses the
   * 'LEFT()' string function to return only
   * the first 7 characters of the formatted timestamp.
   */
  LEFT (FORMAT_UTC_USEC(
    UTC_USEC_TO_MONTH(timestamp * 1000000)),7)
    AS month,
  SUM(LENGTH(comment)) as total_chars_used
FROM
  [publicdata:samples.wikipedia]
WHERE
  (contributor_username != '' AND
   contributor_username IS NOT NULL)
  AND timestamp > 1133395200
  AND timestamp < 1157068800
GROUP BY
  contributor_username, month
ORDER BY
  total_chars_used DESC;

Returns (truncated):

+--------------------------------+---------+-----------------------+
|      contributor_username      |  month  | total_chars_used      |
+--------------------------------+---------+-----------------------+
| Kingbotk                       | 2006-08 |              18015066 |
| SmackBot                       | 2006-03 |               7838365 |
| SmackBot                       | 2006-05 |               5148863 |
| Tawkerbot2                     | 2006-05 |               4434348 |
| Cydebot                        | 2006-06 |               3380577 |
etc ...

IP Functions

IP functions convert IP addresses to and from human-readable form.

Syntax:

JSON Functions

Query’s JSON functions give you the ability to find values within your stored JSON data, by using JSONPath-like expressions.

Storing JSON data can be more flexible than declaring all of your individual fields in your table schema, but can lead to higher costs. When you select data from a JSON string, you are charged for scanning the entire string, which is more expensive than if each field is in a separate column. The query is also slower since the entire string needs to be parsed at query time. But for ad-hoc or rapidly-changing schemas, the flexibility of JSON can be worth the extra cost.

Use JSON functions instead of Query’s regular expression functions if working with structured data, as JSON functions are easier to use.

    SELECT
      JSON_EXTRACT('{"a": 1, "b": [4, 5]}', '$.b')
      AS str;

    SELECT
      JSON_EXTRACT_SCALAR('{"a": ["x", {"b":3}]}', '$.a[1].b')
      AS str; 

Logical Operators

Logical operators perform binary or ternary logic on expressions. Binary logic returns true or false. Ternary logic accommodates NULL values and returns true, false, or NULL.

Syntax:

Mathematical Functions

Mathematical functions take numeric arguments and return a numeric result. Each argument can be a numeric literal or a numeric value returned by a query. If the mathematical function evaluates to an undefined result, the operation returns NULL.

Syntax

Advanced Examples:

Bounding Box Query —

The following query returns a collection of points within a rectangular bounding box centered around San Francisco (37.46, -122.50).

SELECT
  year, month,
  AVG(mean_temp) avg_temp,
  MIN(min_temperature) min_temp,
  MAX(max_temperature) max_temp
FROM
  [weather_geo.table]
WHERE
  /* Return values between a pair of */
  /* latitude and longitude coordinates */
  lat / 1000 > 37.46 AND
  lat / 1000 < 37.65 AND
  long / 1000 > -122.50 AND
  long / 1000 < -122.30
GROUP BY
  year, month
ORDER BY
  year, month ASC;

Approximate Bounding Circle Query —

Return a collection of up to 100 points within an approximated circle determined by the using the Spherical Law of Cosines, centered around Denver Colorado (39.73, -104.98). This query makes use of Kochava Query’s mathematical and trigonometric functions, such as PI(), SIN(), and COS().

Because the Earth isn’t an absolute sphere, and longitude+latitude converges at the poles, this query returns an approximation that can be useful for many types of data.

SELECT
  distance, lat, long, temp
FROM
  (SELECT
    ((ACOS(SIN(39.73756700 * PI() / 180) *
           SIN((lat/1000) * PI() / 180) +
           COS(39.73756700 * PI() / 180) *
           COS((lat/1000) * PI() / 180) *
           COS((-104.98471790 -
           (long/1000)) * PI() / 180)) *
           180 / PI()) * 60 * 1.1515)
      AS distance,
     AVG(mean_temp) AS temp,
     AVG(lat/1000) lat, AVG(long/1000) long
FROM
  [weather_geo.table]
WHERE
  month=1 GROUP BY distance)
WHERE
  distance < 100
ORDER BY
  distance ASC
LIMIT 100;

Regular Expression Functions

Kochava Query provides regular expression support using the re2 library.

NOTE: The regular expressions are global matches; to start matching at the beginning of a word you must use the ^ character.

Syntax:

SELECT
   word,
   COUNT(word) AS count
FROM
   publicdata:samples.shakespeare
WHERE
   (REGEXP_MATCH(word,r'ww'ww'))
GROUP BY word
ORDER BY count DESC
LIMIT 3;

+-------+-------+
| word  | count |
+-------+-------+
| ne'er |    42 |
| we'll |    35 |
| We'll |    33 |
+-------+-------+

SELECT
   REGEXP_EXTRACT(word,r'(ww'ww)') AS fragment
FROM
   publicdata:samples.shakespeare
GROUP BY fragment
ORDER BY fragment
LIMIT 3; 

+----------+
| fragment |
+----------+
| NULL     |
| Al'ce    |
| As'es    |
+----------+

SELECT
  REGEXP_REPLACE(word, r'ne'er', 'never') AS expanded_word
FROM
  publicdata:samples.shakespeare
WHERE
  REGEXP_MATCH(word, r'ne'er')
GROUP BY expanded_word
ORDER BY expanded_word
LIMIT 5;

+---------------+
| expanded_word |
+---------------+
| Whenever      |
| never         |
| nevertheless  |
| whenever      |
+---------------+ 

Advanced Examples:

Filter Result Set by Regular Expression Match —

Kochava Query’s regular expression functions can be used to filter results in a WHERE clause, as well as to display results in the SELECT. The following example combines both of these regular expression use cases into a single query.

SELECT
  /* Replace white spaces in the title with underscores. */
  REGEXP_REPLACE(title, r's+', '_') AS regexp_title, revisions
FROM
  (SELECT title, COUNT(revision_id) as revisions
  FROM
    publicdata:samples.wikipedia
  WHERE
    wp_namespace=0
    /* Match titles that start with 'G', end with
     * 'e', and contain at least two 'o's.
     */
    AND REGEXP_MATCH(title, r'^G.*o.*o.*e$')
  GROUP BY
    title
  ORDER BY
    revisions DESC
  LIMIT 100); 

Using Regular Expression on Interger or Float Data —

While Kochava Query’s regular expression functions only work for string data, it’s possible to use the STRING() function to cast integer or float data into string format. In this example, STRING() is used to cast the integer value corpus_date to a string, which is then altered by REGEXP_REPLACE.

SELECT
  corpus_date,
  /* Cast the corpus_date to a string value  */
  REGEXP_REPLACE(STRING(corpus_date),
    '^16',
    'Written in the sixteen hundreds, in the year ''
    ) AS date_string
FROM [publicdata:samples.shakespeare]
/* Cast the corpus_date to string, */
/* match values that begin with '16' */
WHERE
  REGEXP_MATCH(STRING(corpus_date), '^16')
GROUP BY
  corpus_date, date_string
ORDER BY
  date_string DESC
LIMIT 5; 

String Functions

String functions operate on string data. String constants must be enclosed with single or double quotes. String functions are case-sensitive by default. You can append IGNORE CASE to the end of a query to enable case- insensitive matching. IGNORE CASE works only on ASCII characters and only at the top level of the query.

Wildcards are not supported in these functions; for regular expression functionality, use regular expression functions.

Syntax:

Escaping Special Characters in Strings —

To escape special characters, use one of the following methods:

• Use’xDD’ notation, where ‘x’ is followed by the two-digit hex representation of the character.
• Use an escaping slash in front of slashes, single quotes, and double quotes.
• Use C-style sequences (‘a’, ‘b’, ‘f’, ‘n’, ‘r’, ‘t’, and ‘v’) for other characters.

Some examples of escaping:

'this is a space: x20'
'this string has 'single quote' inside it'
'first line n second line'
"double quotes are also ok"
'70' -> ERROR: octal escaping is not supported

Table Wildcard Functions

Table wildcard functions are a convenient way to query data from a specific set of tables. A table wildcard function is equivalent to a comma-separated union of all the tables matched by the wildcard function. When you use a table wildcard function, Kochava Query only accesses and charges you for tables that match the wildcard. Table wildcard functions are specified in the query’s FROM clause.

If you use table wildcard functions in a query, the functions no longer need to be contained in parentheses. For example, some of the following examples use parentheses, whereas others don’t.

Syntax:

SELECT
  name
FROM
  TABLE_DATE_RANGE(mydata.people,
                    TIMESTAMP('2014-03-25'),
                    TIMESTAMP('2014-03-27'))
WHERE
  age >= 35

SELECT
  name
FROM
  (TABLE_DATE_RANGE([myproject-1234:mydata.people],
                    DATE_ADD(CURRENT_TIMESTAMP(), -2, 'DAY'),
                    CURRENT_TIMESTAMP()))
WHERE
  age >= 35 

SELECT
  name
FROM
  (TABLE_DATE_RANGE_STRICT(people,
                    TIMESTAMP('2014-03-25'),
                    TIMESTAMP('2014-03-27')))
WHERE age >= 35

SELECT
  speed
FROM (TABLE_QUERY(mydata,
                  'table_id CONTAINS "oo" AND length(table_id) >= 4'))

SELECT
  speed
FROM
  TABLE_QUERY([myproject-1234:mydata],
               'REGEXP_MATCH(table_id, r"^boo[d]{3,5}")') 

URL Functions

Syntax:

NOTE: These functions don’t perform reverse DNS lookup, so if you call these functions using an IP address the functions will return segments of the IP address rather than segments of the host name.

NOTE: All of the URL parsing functions expect lower-case characters. Upper-case characters in the URL will result in a NULL or otherwise incorrect result. Consider passing input to this function through LOWER() if your data has mixed casing.

Advanced Example:

Parse domain names from URL data

This query uses the DOMAIN() function to return the most popular domains listed as repository homepages on GitHub. Note the use of HAVING to filter records using the result of the DOMAIN() function. This is a useful function to determine referrer information from URL data.

Examples —

SELECT
  DOMAIN(repository_homepage) AS user_domain,
  COUNT(*) AS activity_count
FROM
  [publicdata:samples.github_timeline]
GROUP BY
  user_domain
HAVING
  user_domain IS NOT NULL AND user_domain != ''
ORDER BY
  activity_count DESC
LIMIT 5;

Returns —

+-----------------+----------------+
|   user_domain   | activity_count |
+-----------------+----------------+
| github.com      |         281879 |
| google.com      |          34769 |
| khanacademy.org |          17316 |
| sourceforge.net |          15103 |
| mozilla.org     |          14091 |
+-----------------+----------------+

To look specifically at TLD information, use the TLD() function. This example displays the top TLDs that are not in a list of common examples.

SELECT
  TLD(repository_homepage) AS user_tld,
  COUNT(*) AS activity_count
FROM
  [publicdata:samples.github_timeline]
GROUP BY
  user_tld
HAVING
  /* Only consider TLDs that are NOT NULL */
  /* or in our list of common TLDs */
  user_tld IS NOT NULL AND NOT user_tld
  IN ('','.com','.net','.org','.info','.edu')
ORDER BY
  activity_count DESC
LIMIT 5;

Returns —

+----------+----------------+
| user_tld | activity_count |
+----------+----------------+
| .de      |          22934 |
| .io      |          17528 |
| .me      |          13652 |
| .fr      |          12895 |
| .co.uk   |           9135 |
+----------+----------------+ 

Window Functions

Window functions, also known as analytic functions, enable calculations on a specific subset, or “window”, of a result set. Window functions make it easier to create reports that include complex analytics such as trailing averages and running totals.

Each window function requires an OVER clause that specifies the window top and bottom. The three components of the OVER clause (partitioning, ordering, and framing) provide additional control over the window. Partitioning enables you to divide the input data into logical groups that have a common characteristic. Ordering enables you to order the results within a partition. Framing enables you to create a sliding window frame within a partition that moves relative to the current row. You can configure the size of the moving window frame based on a number of rows or a range of values, such as a time interval.

SELECT <window_function>
  OVER (
      [PARTITION BY <expr>]
      [ORDER BY <expr> [ASC | DESC]]
      [<window-frame-clause>]
     ) 

PARTITION BY:

Defines the base partition over which this function operates. Specify one or more comma-separated column names; one partition will be created for each distinct set of values for these columns, similar to a GROUP BY clause. If PARTITION BY is omitted, the base partition is all rows in the input to the window function.

The PARTITION BY clause also allows window functions to partition data and parallelize execution. If you wish to use a window function with allowLargeResults, or if you intend to apply further joins or aggregations to the output of your window function, use PARTITION BY to parallelize execution.

JOIN EACH and GROUP EACH BY clauses can’t be used on the output of window functions. To generate large query results when using window functions, you must use PARTITION BY.

ORDER BY:

Sorts the partition. If ORDER BY is absent, there is no guarantee of any default sorting order. Sorting occurs at the partition level, before any window frame clause is applied. If you specify a RANGE window, you should add an ORDER BY clause. Default order is ASC.

ORDER BY is optional in some cases, but certain window functions, such as rank() or dense_rank(), require the clause.

If you use ORDER BY without specifying ROWS or RANGE, ORDER BY implies that the window extends from the beginning of the partition to the current row. In the absence of an ORDER BY clause, the window is the entire partition.

<window-frame-clause>:

{ROWS | RANGE} {BETWEEN <start> AND <end> | <start> | <end>}

A subset of the partition over which to operate. This can be the same size as the partition or smaller. If you use ORDER BY without a window-frame-clause, the default window frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. If you omit both ORDER BY and the window-frame-clause, the default window frame is the entire partition.

• ROWS – Defines a window in terms of row position, relative to the current row. For example, to add a column showing the sum of the preceding 5 rows of salary values, you would query SUM(salary) OVER (ROWS BETWEEN 5 PRECEDING AND CURRENT ROW). The set of rows typically includes the current row, but that is not required.
• RANGE – Defines a window in terms of a range of values in a given column, relative to that column’s value in the current row. Only operates on numbers and dates, where date values are simple integers (microseconds since the epoch). Neighboring rows with the same value are called peer rows. Peer rows of the CURRENT ROW are included in a window frame that specifies CURRENT ROW. For example, if you specify the window end to be CURRENT ROW and the following row in the window has the same value, it will be included in the function calculation.
• BETWEEN <start> AND <end> – A range, inclusive of the start and end rows. The range need not include the current row, but <start> must precede or equal <end>.
• <start> – Specifies the start offset for this window, relative to the current row. The following options are supported:

  {UNBOUNDED PRECEDING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}

  where <expr> is a positive integer, PRECEDING indicates a preceding row number or range value, and FOLLOWING indicates a following row number or range value. UNBOUNDED PRECEDING means the first row of the partition. If the start precedes the window, it will be set to the first row of the partition.

• <end> – Specifies the end offset for this window, relative to the current row. The following options are supported:

  {UNBOUNDED FOLLOWING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}

  where <expr> is a positive integer, PRECEDING indicates a preceding row number or range value, and FOLLOWING indicates a following row number or range value. UNBOUNDED FOLLOWING means the last row of the partition. If end is beyond the end of the window, it will be set to the last row of the partition.

Unlike aggregation functions, which collapse many input rows into one output row, window functions return one row of output for each row of input. This feature makes it easier to create queries that calculate running totals and moving averages. For example, the following query returns a running total for a small data set of five rows defined by SELECT statements:

SELECT name, value, SUM(value) OVER (ORDER BY value) AS RunningTotal
FROM
  (SELECT "a" AS name, 0 AS value),
  (SELECT "b" AS name, 1 AS value),
  (SELECT "c" AS name, 2 AS value),
  (SELECT "d" AS name, 3 AS value),
  (SELECT "e" AS name, 4 AS value); 

Returns Value:

+------+-------+--------------+
| name | value | RunningTotal |
+------+-------+--------------+
| a    |     0 |            0 |
| b    |     1 |            1 |
| c    |     2 |            3 |
| d    |     3 |            6 |
| e    |     4 |           10 |
+------+-------+--------------+ 

The following example calculates a moving average of the values in the current row and the row preceding it. The window frame comprises two rows that move with the current row.

SELECT
  name,
  value,
  AVG(value)
    OVER (ORDER BY value
          ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
    AS MovingAverage
FROM
  (SELECT "a" AS name, 0 AS value),
  (SELECT "b" AS name, 1 AS value),
  (SELECT "c" AS name, 2 AS value),
  (SELECT "d" AS name, 3 AS value),
  (SELECT "e" AS name, 4 AS value); 

Return Value:
 
+------+-------+---------------+
| name | value | MovingAverage |
+------+-------+---------------+
| a    |     0 |           0.0 |
| b    |     1 |           0.5 |
| c    |     2 |           1.5 |
| d    |     3 |           2.5 |
| e    |     4 |           3.5 |
+------+-------+---------------+

Syntax:

SELECT
   corpus_date,
   corpus,
   word_count,
   SUM(word_count) OVER (
     PARTITION BY corpus_date
     ORDER BY word_count) annual_total
FROM
   [publicdata:samples.shakespeare]
WHERE
   word='love'
ORDER BY
   corpus_date, word_count

SELECT
   word,
   word_count,
   CUME_DIST() OVER (PARTITION BY corpus ORDER BY word_count DESC) cume_dist,
FROM
   [publicdata:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

SELECT
   word,
   word_count,
   DENSE_RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) dense_rank,
FROM
   [publicdata:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

SELECT
   word,
   word_count,
   FIRST_VALUE(word) OVER (PARTITION BY corpus ORDER BY word_count DESC) fv,
FROM
   [publicdata:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 1

SELECT
   word,
   word_count,
   LAG(word, 1) OVER (PARTITION BY corpus ORDER BY word_count DESC) lag,
FROM
   [publicdata:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

SELECT
   word,
   word_count,
   LAST_VALUE(word) OVER (PARTITION BY corpus ORDER BY word_count DESC) lv,
FROM
   [publicdata:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 1

SELECT
   word,
   word_count,
   LEAD(word, 1) OVER (PARTITION BY corpus ORDER BY word_count DESC) lead,
FROM
   [publicdata:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

SELECT
   word,
   word_count,
   NTILE(2) OVER (PARTITION BY corpus ORDER BY word_count DESC) ntile,
FROM
   [publicdata:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

SELECT
   word,
   word_count,
   PERCENT_RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) p_rank,
FROM
   [publicdata:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5 

SELECT
   word,
   word_count,
   PERCENTILE_CONT(0.5) OVER (PARTITION BY corpus ORDER BY word_count DESC) p_cont,
FROM
   [publicdata:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

SELECT
   word,
   word_count,
   PERCENTILE_DISC(0.5) OVER (PARTITION BY corpus ORDER BY word_count DESC) p_disc,
FROM
   [publicdata:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

SELECT
   word,
   word_count,
   RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) rank,
FROM
   [publicdata:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5 

SELECT
   word,
   word_count,
   RATIO_TO_REPORT(word_count) OVER (PARTITION BY corpus ORDER BY word_count DESC) r_to_r,
FROM
   [publicdata:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5 

SELECT
   word,
   word_count,
   ROW_NUMBER() OVER (PARTITION BY corpus ORDER BY word_count DESC) row_num,
FROM
   [publicdata:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

Other Functions

Syntax:

SELECT
  TO_BASE64(SHA1(corpus))
FROM
  [kochavaquery-public-data:samples.shakespeare]
LIMIT
  100;

SELECT
  TO_BASE64(SHA1(title))
FROM
  [kochavaquery-public-data:samples.wikipedia]
LIMIT
  100;

Advanced Examples:

• Bucketing results into categories using conditionals
  The following query uses a CASE/WHEN block to bucket results into “region” categories based on a list of states. If the state does not appear as an option in one of the WHEN statements, the state value will default to “None.”

  Example:

  SELECT
    CASE
      WHEN state IN ('WA', 'OR', 'CA', 'AK', 'HI', 'ID',
                     'MT', 'WY', 'NV', 'UT', 'CO', 'AZ', 'NM')
        THEN 'West'
      WHEN state IN ('OK', 'TX', 'AR', 'LA', 'TN', 'MS', 'AL',
                     'KY', 'GA', 'FL', 'SC', 'NC', 'VA', 'WV',
                     'MD', 'DC', 'DE')
        THEN 'South'
      WHEN state IN ('ND', 'SD', 'NE', 'KS', 'MN', 'IA',
                     'MO', 'WI', 'IL', 'IN', 'MI', 'OH')
        THEN 'Midwest'
      WHEN state IN ('NY', 'PA', 'NJ', 'CT',
                     'RI', 'MA', 'VT', 'NH', 'ME')
        THEN 'Northeast'
      ELSE 'None'
    END as region,
    average_mother_age,
    average_father_age,
    state, year
  FROM
    (SELECT
       year, state,
       SUM(mother_age)/COUNT(mother_age) as average_mother_age,
       SUM(father_age)/COUNT(father_age) as average_father_age
     FROM
       publicdata:samples.natality
     WHERE
       father_age < 99
     GROUP BY
       year, state)
  ORDER BY
    year
  LIMIT 5;

  Returns:

  +--------+--------------------+--------------------+-------+------+
  | region | average_mother_age | average_father_age | state | year |
  +--------+--------------------+--------------------+-------+------+
  | South  | 24.342600163532296 | 27.683769419460344 | AR    | 1969 |
  | West   | 25.185041908446163 | 28.268214055448098 | AK    | 1969 |
  | West   | 24.780776677578217 | 27.831181063905248 | CA    | 1969 |
  | West   | 25.005834769924412 | 27.942978384829598 | AZ    | 1969 |
  | South  | 24.541730952905738 | 27.686430093306885 | AL    | 1969 |
  +--------+--------------------+--------------------+-------+------+

• Simulating a Pivot Table
  Use conditional statements to organize the results of a subselect query into rows and columns. In the example below, results from a search for most revised Wikipedia articles that start with the value ‘Google’ are organized into columns where the revision counts are displayed if they meet various criterea.

  Example:

  SELECT
    page_title,
    /* Populate these columns as True or False, */
    /*  depending on the condition */
    IF (page_title CONTAINS 'search',
        INTEGER(total), 0) AS search,
    IF (page_title CONTAINS 'Earth' OR
        page_title CONTAINS 'Maps', INTEGER(total), 0) AS geo,
  FROM
    /* Subselect to return top revised Wikipedia articles */
    /* containing 'Google', followed by additional text. */
    (SELECT
      TOP (title, 5) as page_title,
      COUNT (*) as total
     FROM
       [publicdata:samples.wikipedia]
     WHERE
       REGEXP_MATCH (title, r'^Google.+') AND wp_namespace = 0
    )

  ;

  Returns:

  +---------------+--------+------+
  |  page_title   | search | geo  |
  +---------------+--------+------+
  | Google search |   4261 |    0 |
  | Google Earth  |      0 | 3874 |
  | Google Chrome |      0 |    0 |
  | Google Maps   |      0 | 2617 |
  | Google bomb   |      0 |    0 |
  +---------------+--------+------+

• Using HASH to select a random sample of your data
  Some queries can provide a useful result using random subsampling of the result set. To retrieve a random sampling of values, use the HASH function to return results in which the modulo “n” of the hash equals zero.

  For example, the following query will find the HASH() of the “title” value, and then checks if that value modulo “2” is zero. This should result in about 50% of the values being labeled as “sampled.” To sample fewer values, increase the value of the modulo operation from “2” to something larger. The query uses the ABS function in combination with HASH, because HASH can return negative values, and the modulo operator on a negative value yields a negative value.

  Example:

  SELECT
    title,
    HASH(title) AS hash_value,
    IF(ABS(HASH(title)) % 2 == 1, 'True', 'False')
      AS included_in_sample
  FROM
    [publicdata:samples.wikipedia]
  WHERE
    wp_namespace = 0
  LIMIT 5;