Así que tengo que crear ID de usuario a partir de los correos electrónicos de los usuarios y todos los datos están presentes en BigQuery. En Python, pandas, es una frase sencilla como:
all_data['user_id'] = all_data['email'].factorize()[0]
Pero no encuentro una manera de hacer esto en BigQuery SQL. Intenté usar la función RANK() pero no está ni cerca. Actualmente estoy intentando hacer uso de las funciones de Windows con RANK() pero parece un poco exagerado tener ese enfoque para una tarea tan simple. Todos los datos ya están en BigQuery, por lo que cualquier consejo sobre hacerlo de otra manera, incluso fuera de SQL, también sería bueno.
Un poco de contexto...
La función pandas factorize() asigna una identificación única según la columna proporcionada, de modo que si los correos electrónicos son como [correo electrónico protegido], [correo electrónico protegido], [correo electrónico protegido], [correo electrónico protegido], [correo electrónico protegido], [correo electrónico protegido],devolvería: [0, 1, 0, 2, 0, 1] y así sucesivamente.
También tengo otras columnas en la base de datos, por lo que RANK() y ROW_NUMBER() parecen no ayudar por sí solas. Aunque estoy tratando de moverme.
¿Te refieres a ROW_NUMBER()?
-Mark Rotteveel
28 de marzo de 2021 a las 8:34
No. Ya que los correos electrónicos se pueden repetir. ROW_NUMBER() por sí solo no sirve. Eso es lo que estoy intentando.ng para lograr ahora mismo
- Hamza
28 de marzo de 2021 a las 8:35
¿Entonces tal vez DENSE_RANK?
-Mark Rotteveel
28 de marzo de 2021 a las 8:36
Eso todavía abarca toda la fila. Lo que necesito es tener ID basados en columnas generados a partir de correos electrónicos teniendo en cuenta solo los correos electrónicos
- Hamza
Mamá28 de enero de 2021 a las 8:38
2
Usar DENSE_RANK() OVER (ORDER BY email) debería hacer exactamente eso, AFAIK.
-Mark Rotteveel
28 de marzo de 2021 a las 8:40
------------------------------------
Puedes utilizar la función de ventana DENSE_RANK() para este propósito:
select dataset.*, DENSE_RANK() OVER (ORDER BY email)
from dataset
order by sent;
Esto produciría algo como (usando los datos de ejemplo de Mikhail Berlyant como punto de partida):
ENVIADO
CORREO ELECTRÓNICO
DENSE_RANK
2021-01-01 00:01:00
[correo electrónico protegido]
3
2021-01-01 00:02:00
[correo electrónico protegido]
1
2021-01-01 00:03:00
[correo electrónico protegido]
3
2021-01-01 00:04:00
[correo electrónico protegido]
2
2021-01-01 00:05:00
[correo electrónico protegido]
3
2021-01-01 00:06:00
[correo electrónico protegido]
1
------------------------------------
Considere a continuación dos opciones
Tenga en cuenta que estoy usando un ejemplo de datos ligeramente modificado; verá por qué (espero)
with `project.dataset.table` as (
select '2021-01-01 00:01:00' sent , '[email protected]' recipient union all
select '2021-01-01 00:02:00', '[email protected]' union all
select '2021-01-01 00:03:00', '[email protected]' union all
select '2021-01-01 00:04:00', '[email protected]' union all
select '2021-01-01 00:05:00', '[email protected]' union all
select '2021-01-01 00:06:00', '[email protected]'
)
Opción 1:
En caso de que haya un orden en el que se deben configurar esos correos electrónicos antes de asignar ID_único, por ejemplo, por columna de enviados. En este caso considere a continuación
#standardSQL
create temp function factorize(item string, list any type) as ((
select unique_id from (
select as struct recipient, row_number() over(order by min(sent)) - 1 unique_id
from unnest(list)
group by recipient
)
where recipient = item
));
select t.*,
factorize(recipient, array_agg(struct(recipient, sent)) over()) unique_id
from `project.dataset.table` t
con salida
Opción 2:
En caso de que realizar pedidos no importe mucho y pueda simplemente ordenar alfabéticamente, considere a continuación una consulta ligeramente [en mi opinión] más simple con el uso de la función range_bucket incorporada
#standardSQL
create temp function factorize(item string, list any type) as (
range_bucket(item, list) - 1
);
with all_recipients as (
select array_agg(recipient order by recipient) recipients from (
select recipient
from `project.dataset.table`
group by recipient
)
)
select t.*,
factorize(recipient, recipients) unique_id
from `project.dataset.table` t, all_recipients
con salida
Obviamente, en este caso puedes omitir el uso de udf y simplemente usar rabge_bucket dentro de la selección final (en lugar de dentro de udf)
select t.*,
range_bucket(recipient, recipients) - 1 unique_id
0
