Con Google BIGQUERY, necesito verificar que los valores en una columna llamada birth_day_col tengan el formato de fecha correcto y deseado: AAAA-MM-DD. Los valores en esta columna se definen como STRING. Además, los valores de esta columna tienen actualmente el siguiente formato: AAAA-MM-DD.

Investigué mucho en Internet y encontré una solución interesante. La siguiente consulta:

SELECT
    DISTINCT birth_day_col
    FROM `project.dataset.datatable`
    WHERE birth_day_col LIKE '[1-2][0-9][0-9][0-9]/[0-1][0-9]/[0-3][0-9]'
    AND country_code = 'country1'

Pero el resultado es: "Esta consulta no devolvió resultados".

Luego verifiqué con NOT, usando el siguiente código:

SELECT
    DISTINCT birth_day_col
    FROM `project.dataset.datatable`
    WHERE NOT(birth_day_col LIKE '[1-2][0-9][0-9][0-9]/[0-1][0-9]/[0-3][0-9]')
    AND country_code = 'country1'

Sorprendentemente, dio todos los valores en birth_dat_col, que he verificado y tienen el formato de fecha correcto, pero este resultado podría ser una coincidencia.

Y es muy extraño (incorrecto) que haya utilizado una consulta que solo debe dar como resultado las fechas de formato incorrectas, pero en realidad me da las correctas. Todo sobre estas dos consultas parece una inversión del papel de cada uno.

El resultado esperado de cualquier consulta para este caso de negocios es hacer un recuento de todas las fechas con formato incorrecto (incluso si actualmente es 0).

Gracias por su ayuda!

Robert

1
Robert Mocanu 10 may. 2019 a las 16:35

3 respuestas

La mejor respuesta

Un par de cosas aqui:

  1. Lea la documentación para el operador LIKE si lo desea para entender cómo usarlo. Parece que está tratando de usar la sintaxis de expresión regular, pero el operador LIKE no toma una expresión regular como entrada.
  2. El formato estándar para las fechas de BigQuery es AAAA-MM-DD, por lo que puede intentar emitir y ver si el resultado es una fecha válida, por ejemplo:
SELECT SAFE_CAST(birth_day_col AS DATE) AS birth_day_col
FROM `project`.dataset.table

Esto devolverá nulo para cualquier valor que no tenga el formato correcto. Si desea encontrar todos los que no tienen el formato correcto, puede usar SAFE_CAST dentro de un filtro:

SELECT DISTINCT birth_day_col AS invalid_date
FROM `project`.dataset.table
WHERE SAFE_CAST(birth_day_col AS DATE) IS NULL

El resultado de esta consulta serán todas las cadenas de fecha que no utilizan el formato AAAA-MM-DD. Si desea buscar barras, puede usar REGEXP_CONTAINS, p. prueba esto:

SELECT
  date,
  REGEXP_CONTAINS(date, r'^[0-9]{4}/[0-9]{2}/[0-9]{2}$')
FROM (
  SELECT '2019/05/10' AS date UNION ALL
  SELECT '2019-05-10' UNION ALL
  SELECT '05/10/2019'
)

Si desea buscar todas las fechas con el formato ya sea AAAA-MM-DD o AAAA / MM / DD, puede usar una consulta como esta:

SELECT
  DISTINCT date
FROM `project`.dataset.table
WHERE REGEXP_CONTAINS(date, r'^[0-9]{4}[/\-][0-9]{2}[/\-][0-9]{2}$')

Por ejemplo:

SELECT
  DISTINCT date
FROM (
  SELECT '2019/05/10' AS date UNION ALL
  SELECT '2019-05-10' UNION ALL
  SELECT '05/10/2019'
)
WHERE REGEXP_CONTAINS(date, r'^[0-9]{4}[/\-][0-9]{2}[/\-][0-9]{2}$')
2
Elliott Brossard 10 may. 2019 a las 14:52

Otro ejemplo más de BigQuery Standrad SQL, con el uso de SAFE.PARSE_DATE

#standardSQL
WITH `project.dataset.table` AS (
  SELECT '1980/08/10' AS birth_day_col UNION ALL
  SELECT '1980-08-10' UNION ALL
  SELECT '08/10/1980'
)
SELECT birth_day_col
FROM `project.dataset.table`
WHERE SAFE.PARSE_DATE('%Y-%m-%d', birth_day_col) IS NULL   

Con el resultado de la lista de todas las fechas que no están formateadas como aaaa-mm-dd

Row birth_day_col    
1   1980/08/10   
2   08/10/1980   
2
Mikhail Berlyant 10 may. 2019 a las 19:07

El operador LIKE de Google BigQuery no admite la coincidencia dígitos ni utiliza el carácter [ en su sintaxis (tampoco creo que SQL estándar ISO lo haga - LIKE no es tan poderoso como Regex).

X [NOT] LIKE Y

Comprueba si STRING en el primer operando X coincide con un patrón especificado por el segundo operando Y. Las expresiones pueden contener estos caracteres:

  • Un signo de porcentaje "%" coincide con cualquier número de caracteres o bytes
  • Un guión bajo "_" coincide con un solo carácter o byte
  • Puede escapar de "\", "_" o "%" con dos barras invertidas. Por ejemplo, "\%". Si está utilizando cadenas sin formato, solo se requiere una barra invertida. Por ejemplo, r "\%".

Deberías usar REGEX_CONTAINS en su lugar.

Sin embargo, noto que las pruebas de formato de cadena no le dirán si una fecha es válida o no. Tenga en cuenta que 2019-02-31 tiene un formato de fecha válido, pero un valor de fecha no válido. Sugiero usar una función de conversión de tipo de datos (para convertir el valor STRING en un valor DATE).

1
Dai 10 may. 2019 a las 13:45