En BigQuery, puedo ejecutar correctamente la siguiente consulta con SQL estándar:

SELECT 
  COUNT(*) AS totalCount,
  city,
  DATE_TRUNC(timeInterval.intervalStart, YEAR) AS start
FROM 
  sandbox.CountByCity
GROUP BY 
    city, start

Pero falla cuando anido el valor start en un STRUCT, como este ...

SELECT 
  COUNT(*) AS totalCount,
  city,
  STRUCT(
    DATE_TRUNC(timeInterval.intervalStart, YEAR) AS start
  ) as timeSpan
FROM 
  sandbox.CountByCity
GROUP BY 
    city, timeSpan.start

En este caso, aparece el siguiente mensaje de error:

No se pueden agrupar por referencias de campo de SELECT lista alias timeSpan a las [10:11]

¿Cuál es la forma correcta de escribir la consulta para que el valor start esté anidado dentro de un STRUCT?

6
Greg McFall 11 dic. 2017 a las 21:29

2 respuestas

La mejor respuesta

Puede hacer esto usando ANY_VALUE. El valor de estructura que obtiene está bien definido, ya que el valor es el mismo para todo el grupo:

SELECT 
  COUNT(*) AS totalCount,
  city,
  ANY_VALUE(STRUCT(
    DATE_TRUNC(timeInterval.intervalStart, YEAR) AS start
  )) as timeSpan
FROM 
  sandbox.CountByCity
GROUP BY 
    city, DATE_TRUNC(timeInterval.intervalStart, YEAR);

Aquí hay un ejemplo con algunos datos de muestra:

WITH `sandbox.CountByCity` AS (
  SELECT 'Seattle' AS city, STRUCT(DATE '2017-12-11' AS intervalStart) AS timeInterval UNION ALL
  SELECT 'Seattle', STRUCT(DATE '2016-11-10' AS intervalStart) UNION ALL
  SELECT 'Seattle', STRUCT(DATE '2017-03-24' AS intervalStart) UNION ALL
  SELECT 'Kirkland', STRUCT(DATE '2017-02-01' AS intervalStart)
)
SELECT 
  COUNT(*) AS totalCount,
  city,
  ANY_VALUE(STRUCT(
    DATE_TRUNC(timeInterval.intervalStart, YEAR) AS start
  )) as timeSpan
FROM 
  `sandbox.CountByCity`
GROUP BY 
    city, DATE_TRUNC(timeInterval.intervalStart, YEAR);

También podría considerar enviar una solicitud de función para habilitar GROUP BY con tipos STRUCT.

8
Elliott Brossard 11 dic. 2017 a las 18:54

No estoy seguro de por qué exactamente querría esto, pero cree que es por alguna razón, así que intente a continuación (al menos formalmente hace lo que pide)

#standardSQL
SELECT
  totalCount, 
  city, 
  STRUCT(start) timeSpan
FROM (
  SELECT 
    COUNT(*) AS totalCount,
    city,
    DATE_TRUNC(timeInterval.intervalStart, YEAR) AS start
  FROM `sandbox.CountByCity`
  GROUP BY city, start
)
1
Mikhail Berlyant 11 dic. 2017 a las 18:53