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
3 respuestas
Un par de cosas aqui:
- 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.
- 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}$')
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
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 operandoY
. 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
).
Preguntas relacionadas
Nuevas preguntas
google-bigquery
Google BigQuery es un producto de Google Cloud Platform que proporciona consultas sin servidor de conjuntos de datos a escala de petabytes utilizando SQL. BigQuery proporciona múltiples canales de lectura y escritura, y permite el análisis de datos que transforman la forma en que las empresas analizan los datos.