Reference

• Data Types

• Query Syntax

  • Subqueries

• Lexical Syntax

  • Reserved Keywords

• Functions

  • Strings

    • String Functions

      • Format Specifiers

    • RegEx

  • Datetime

    • Date

    • Time

      • Timestamp

    • Format Elements

  • Window Functions

  • JSON

• Optimise Performance

Data Types

• Main Data Types

  • STRING

  • NUMERIC — a decimal number

  • FLOAT64 — floating point

  • INT64 — integer

  • TIMESTAMP — date and time

• Other Data Types

  • BOOLEAN — TRUE/FALSE

  • DATETIME — date and time

  • etc.

Structures

`project.dataset.table`

e.g

`bi_ops.aux_business_vault.line_items_view`

Rounding

Round to Multiple

E.g.

FLOOR(18/10)*10 AS make_10

CEIL(18/10)*10 AS make_20

Row Number

ROW_NUMBER can number instances, or highlight the last or first instance.

e.g. count the instances

IF(ROW_NUMBER() OVER (PARTITION BY returned_order_line_items.article_id ORDER BY returned_orders.ordered_on ASC) AS return_counter_over_all_channels

e.g. mark the latest instance

IF(ROW_NUMBER() OVER (PARTITION BY returned_order_line_items.article_id ORDER BY returned_orders.ordered_on DESC)=1,1,0) AS is_last_return_over_all_channels

Partition

e.g.

ROW_NUMBER() OVER (PARTITION BY article_pictures.article_id ORDER BY article_pictures.position ASC) AS my_rownumber_in_partition

e.g.

SELECT

  *

FROM (

  SELECT

    *,

    ROW_NUMBER() OVER (PARTITION BY my_table.id ORDER BY my_table.created_at DESC) AS row_number_desc_in_partion_over_id

  FROM my_table) AS my_latest_records

WHERE my_latest_records.row_number_desc_in_partion_over_id = 1

e.g.

SELECT

order_line_items.order_id,

order_line_items.id AS order_line_id,

order_line_items.gross_price

/SUM(order_line_items.gross_price) OVER(PARTITION BY order_line_items.order_id)

AS breakdown_factor_by_oli_gross_price

FROM order_line_items

WHERE order_line_items.order_id IN (554310, 554311)

ORDER BY order_line_items.order_id ASC

Running Sum

e-.g-

WITH

  with_table AS (

  SELECT

    TIMESTAMP('2024-03-15 00:00:00') AS date_change,

    45 AS change_value

  UNION ALL

  SELECT

    TIMESTAMP('2024-04-17 00:00:00') AS date_change,

    -12 AS change_value

  UNION ALL

  SELECT

    TIMESTAMP('2024-05-21 00:00:00') AS date_change,

    18 AS change_value)

SELECT

  t.*,

  SUM(t.change_value) OVER () AS total_change,

  SUM(t.change_value) OVER (ORDER BY t.date_change ASC) AS total_change_asc,

  SUM(t.change_value) OVER (ORDER BY t.date_change DESC) AS total_change_desc,

FROM

  with_table AS t

ORDER BY

  date_change ASC;

Pivot

• PIVOT operator

• UNPIVOT operator

The pivot part needs an aggregation like SUM() etc. for numerical values. Furthermore, MAX(), or MIN() work with string values, as does STRING_AGG, which creates a concatenation.

The data is pivoted on the values given in the IN() statement. If the values does not exist for a particular value of the IN() statement, the output for that cells is NULL.

The IN() statement needs fix values given, and getting them in a subquery will not work.

SELECT * FROM

( SELECT

item , type , value

FROM table )

PIVOT 

( STRING_AGG( value ) FOR type

IN ( 'a-type , 'b-type' , 'c-type' ) ;

e.g.

SELECT

  *

FROM (

  SELECT

    core.* EXCEPT(instance_desc,

      lastest_audit)

  FROM (

    SELECT

      audits.article_id,

      audits.station,

      audits.to_state,

      ROW_NUMBER() OVER (PARTITION BY audits.article_id, audits.station ORDER BY audits.updated_at DESC) AS instance_desc,

    IF

      (ROW_NUMBER() OVER (PARTITION BY audits.article_id ORDER BY audits.updated_at DESC)=1,1,0) AS lastest_audit

    FROM

      `rere-bi-dev.business_vault.audit_article_states_view` AS audits

    WHERE

      audits.article_id IN (2038934,

        1600001)) AS core

  WHERE

    core.instance_desc = 1 ) AS pivots PIVOT ( STRING_AGG(to_state) FOR station IN ('description',

      'detail_photo',

      'packing',

      'photo',

      'puppet_photo',

      'review',

      'sourcing',

      'station',

      'storage',

      'tagging'));

Unpiovt

e.g.

SELECT

  *

FROM (

  SELECT

    signatures.category_id,

    signatures.categeory_structural_signature,

    signatures.main_category,

    signatures.major_category,

    signatures.minor_category

  FROM

    `rere-bi-dev.auxiliary_business_vault.category_structural_signatures_view` AS signatures

  WHERE

    signatures.category_id IN (10101,10102))

      UNPIVOT(value FOR field IN (

        categeory_structural_signature,

        main_category,

        major_category,

        minor_category

        ));

With

WITH

with_name_1 AS (SELECT ... ),

with_name_2 AS (SELECT ... )

SELECT ...

FROM table

JOIN with_name_1 ON with_name_1.id = table.id

JOIN with_name_2 ON with_name_1.id = table.id ;

SAFETY MEASURES

There are many safe functions that can replace similar functions. Safe functions result in a NULL value, not in a cancellation of a query.

e.g.

• SAFE_CAST()

• SAFE_DIVIDE()

Useful in a similar way are:

• NULLIF()

Meta

structure: `project.dataset.table`

• table_catalog is the project

• table_schema is the dataset

• table_name is the table

Tables in Dataset 

SELECT

  table_catalog,

  table_schema,

  table_name

FROM

  `table_catalog.table_schema.INFORMATION_SCHEMA.TABLES`

ORDER BY

  table_catalog,

  table_schema,

  table_name

Field Names in Table

SELECT

  table_catalog,

  table_schema,

  table_name

  column_name,

  data_type

FROM

  `table_catalog.table_schema.INFORMATION_SCHEMA.COLUMNS`

WHERE

  table_name= '';