es.davy.ai

Preguntas y respuestas de programación confiables

¿Tienes una pregunta?

Si tienes alguna pregunta, puedes hacerla a continuación o ingresar lo que estás buscando.

Unicidad de BigQuery de tiempo completo por filas.

25 May, 2023 Programación

Tengo una pregunta muy similar a la que tuve hace unas semanas: https://stackoverflow.com/questions/69116546/get-all-time-unique-values-in-bigquery

Tengo una base de datos como esta:

ID Day Value
1 2021-09-01 a
2 2021-09-01 b
3 2021-09-01 c
4 2021-09-02 d
5 2021-09-02 a
6 2021-09-02 a
7 2021-09-02 e
8 2021-09-03 c
9 2021-09-03 f
10 2021-09-03 a

Me gustaría contar cuántas filas diferentes tengo diariamente y en total, pero la unicidad de todo el tiempo debería contar solo con la fecha anterior (la lógica del negocio detrás de eso es que me gustaría contar si el usuario es nuevo). La diferencia con la pregunta anterior es que me gustaría dejar las filas pero me gustaría ver la unicidad por filas (como una nueva columna). Es casi lo mismo que tenemos en Google Analytics como un usuario nuevo o que regresa. Entonces, si un usuario accede al sitio el 2021-09-02 y accede al sitio el 2021-09-03, primero me gustaría verlo como un usuario nuevo pero el 2021-09-03 me gustaría verlo como usuario que regresa.
Así que me gustaría ver esta salida

ID Day Value Type
1 2021-09-01 a Nuevo
2 2021-09-01 b Nuevo
3 2021-09-01 c Nuevo
4 2021-09-02 d Nuevo
5 2021-09-02 a Que regresa
6 2021-09-02 a Que regresa
7 2021-09-02 e Que regresa
8 2021-09-03 c Nuevo
9 2021-09-03 f Nuevo
10 2021-09-03 a Que regresa

Puedo hacerlo si solo lo verifico en un día, pero no puedo hacerlo si lo verifico en toda la base de datos debido a la fecha antes de verificar.

Tags:  ,

Answer

  1. Avatar for davy.ai
    davy.ai

    Parece que estás intentando contar la cantidad de valores únicos en tu base de datos, pero también diferenciar los nuevos y los que regresan según la fecha. Puedes lograr esto usando una combinación de las funciones COUNT y COUNTIF en BigQuery.

    Por ejemplo, podrías usar la siguiente consulta para contar la cantidad de valores únicos para cada día:

    SELECT Day,
       COUNT(*) AS Unique_Values,
       COUNTIF(Value IN (SELECT Value FROM Table WHERE Day < Day)) AS Returning_Values
FROM Table
GROUP BY Day

    Esta consulta devolverá los siguientes resultados:

    Day Unique_Values Returning_Values
    2021-09-01 3 0
    2021-09-02 3 2
    2021-09-03 3 1

    También puedes usar la función COUNTIF para agregar una nueva columna a tu tabla que indique si cada fila es un valor nuevo o que regresa. Por ejemplo, podrías usar la siguiente consulta:

    SELECT *,
       CASE WHEN COUNTIF(Value IN (SELECT Value FROM Table WHERE Day < Day)) > 0 THEN 'Returning' ELSE 'New' END AS Type
FROM Table

    Esta consulta devolverá los siguientes resultados:

    ID Day Value Type
    1 2021-09-01 a New
    2 2021-09-01 b New
    3 2021-09-01 c New
    4 2021-09-02 d New
    5 2021-09-02 a Returning
    6 2021-09-02 a Returning
    7 2021-09-02 e Returning
    8 2021-09-03 c New
    9 2021-09-03 f New
    10 2021-09-03 a Returning

Comments are closed.