ВЫБРАТЬ КАК СТРУКТУРА/ЗНАЧЕНИЯ

В следующем примере структура используется для левого соединения таблиц tbl2 и tbl1. Если в таблице 1 несколько совпадающих записей, возьмите только одну (LIMIT 1). Таким образом, размер строки таблицы tbl1 остается постоянным. SELECT AS STRUCT объединяет все строки таблицы tbl2 в одну структуру.

WITH
  tbl1 as (SELECT * FROM UNNEST([1,2]) AS x),
  tbl2 AS (SELECT "a" AS name, 1 AS val
  UNION ALL SELECT "b", 2
  UNION ALL SELECT "bb", 2 )

SELECT *,
  (SELECT AS STRUCT *
  FROM (SELECT * FROM tbl2 WHERE x=val LIMIT 1)
  ) AS joined_table
FROM tbl1

Эти две конструкции могут быть полезны при работе с типами данных STRUCT, особенно когда необходимо вернуть скалярное значение в подзапросе. Вот два примера, по одному на каждый. Обратите внимание, что SELECT AS VALUE особенно полезен, когда у нас уже есть STRUCT, а SELECT AS STRUCT особенно полезен, когда у нас его еще нет, но нужно преобразовать в него.

1. SELECT AS STRUCT

-- get the last order information for a user.
-- we use SELECT AS STRUCT to *reduce* multiple columns into a STRUCT 
-- (or more precisely, a 'values table')
WITH 
  users  AS (SELECT 1 AS id, 'David' AS name UNION ALL SELECT 2, 'Tom'),
  orders AS (SELECT 1 AS id, 1 AS user_id, 'Shoes' AS product 
                     UNION ALL SELECT 2, 1, 'Phone')
SELECT 
  users.* ,
  (SELECT AS STRUCT * FROM orders 
   WHERE user_id=users.id ORDER BY id DESC LIMIT 1) AS last_order
FROM
   users

2. SELECT AS VALUES

-- get the last event for a user.
-- we use SELECT AS VALUE to *expand* a STRUCT into
-- a values table
WITH 
  users AS (SELECT 1 AS id, 'David' AS name UNION ALL SELECT 2, 'Tom'),
  log   AS (SELECT 1 AS user_id, STRUCT("click" AS event, 123 AS t) data
           UNION ALL SELECT 1, STRUCT("purchase" AS event, 346 AS t))
SELECT
  users.*,
  (SELECT AS VALUE data FROM log 
   WHERE user_id=users.id ORDER BY data.t DESC LIMIT 1 ) as last_event
FROM 
  users

Тип STRUCT — это контейнер упорядоченных полей, где каждое поле имеет тип и имя (необязательно). Его можно использовать для объединения нескольких полей под одним именем.

Но кроме этого я не вижу особой пользы и никогда этим не пользовался. помимо тривиальных запросов отладки.

Struct можно использовать для структурирования ваших данных. Например:

CREATE TEMPORARY TABLE rectangles (
  name string,
  coordinates STRUCT<
    topLeft STRUCT<x Int64, y int64>,
    bottomRight STRUCT<x Int64, y int64>
  >
);

INSERT INTO rectangles (name, coordinates) VALUES
('rectangle 1', ((10, 10), (90, 90)));

Вот получившаяся схема таблицы и данные.

Каковы реальные примеры использования или обнаружения этих двух модификаторов? ключевые слова используются?

SELECT AS STRUCT (и другие конструкторы структур) создают структуру из нескольких значений. Это можно использовать для обхода некоторых ограничений, например, когда данные должны состоять из 1 столбца x n строк:

SELECT ARRAY(
  SELECT AS STRUCT gender, name
  FROM bigquery-public-data.usa_names.usa_1910_2013
  WHERE year = 2001 AND STate = 'WA'
  ORDER BY number
  LIMIT 5
)

Без структуры вы получите следующую ошибку:

ARRAY subquery cannot have more than one column unless
using SELECT AS STRUCT to build STRUCT values

Другой пример, когда подзапрос предложения IN должен вернуть более одного столбца для сравнения кортежей:

SELECT *
FROM bigquery-public-data.usa_names.usa_1910_2013
WHERE (state, year, number) IN (
  SELECT AS STRUCT state, year, MAX(number)
  FROM bigquery-public-data.usa_names.usa_1910_2013
  WHERE year BETWEEN 2001 AND 2003
  GROUP BY state, year
)

Без структуры вы получите следующую ошибку:

Subquery of type IN must have only one output column

Обратите внимание, что подзапрос предложения IN МОЖЕТ возвращать более одного столбца в соответствии со стандартом SQL (подробнее об этом ниже).

SELECT AS VALUE работает с выбором одного столбца, и этот столбец должен быть структурой.

Хотя входные данные для AS STRUCT и AS VALUE различаются, результатом для обоих является таблица значений — таблица с одним столбцом, где этот столбец является структурным типом и не имеет имени. Вы можете заменить SELECT AS STRUCT в предыдущем разделе на SELECT AS VALUE и внести необходимые изменения в столбцы.

Используют ли эту конструкцию (или аналогичную) какие-либо другие диалекты SQL? конструкция), а если нет, то почему GoogleSQL должен ее поддерживать?

Ответ зависит от того, что вы пытаетесь сделать.

• Вы можете думать о struct как о определяемом пользователем типе (см. пример координат в прямоугольниках выше). Только PostgreSQL CREATE TYPE с составным типом приближается к этому.
• Вы можете использовать функции JSON для преобразования произвольных данных в объект JSON. Все основные поставщики СУБД поддерживают функции JSON.
• Вы можете использовать функции XML для преобразования произвольных данных в документ XML. SQL Server поддерживает XML.
• Вы можете использовать функции массива для объединения нескольких значений в одно. Но большинство СУБД требуют одного и того же типа данных для всех элементов массива.
• Если это просто сравнение кортежей, стандарт SQL позволяет сравнивать такие кортежи, как SELECT (1, 1, 0) < (1, 1, 1) и WHERE (t1.a, t1.b, t1.c) IN (SELECT t2.a, t2.b, t2.c FROM t2 ...). Не все RDMBS реализуют это.