SQL + BigQuery - máximo durante unos días

Tengo una tabla de barbacoa que contiene los registros para los estudiantes de correo electrónico se han enviado unos a otros y tiene los siguientes campos:

  • estudiante_id
  • time_sent
  • destination_student_id
  • contenido ... (hay más pero estos son los únicos campos relevantes)

Cada fila de la tabla representa un email enviado de un estudiante a otro.

Lo que quiero hacer es conseguir una lista de cada estudiante id y los emails máximos enviados en un día en los últimos 3 días.

por ejemplo:

estudiante_id, max_mails_in_a_day_for_past_3_days

1, 44

2, 12

3, 7

Estos resultados significarían que el estudiante con Id de 1 ha enviado un número máximo de 44 emails en un día en los últimos 3 días, estudiante con id de 2 ha enviado 12 etc.

(lo siento por el formato extraño, no pude encontrar cómo crear una tabla aquí)

¿Cómo puedo lograrlo? ¡Gracias!

Pregunta hecha hace 3 años, 4 meses, 27 días - Por techtrailblazer8d2c

4 Respuestas:

  • Quieres agregación y filtración. Haciendo algunas suposiciones:

    • time_sent es un timestamp
    • Quieres días calendario UTC.
    • Y quieres volver tres días completos desde el momento actual

    Entonces:

    select student_id, max(num_emails)
from (select student_id, timestamp_trunc(time_sent, day) as dte,
             count(*) as num_emails
      from t
      where time_sent >= timestamp_add(timestamp_trunc(current_timestamp, day), interval -3 day)
      group by 1, 2
     ) t
group by student_id;

    Todas esas suposiciones se pueden modificar con ajustes a la consulta.

    Respondida el Dec 20, 2020 a las 15:42 - por syntaxsorcerer

    Votos positivos: 0 | Votos negativos: 0

  • A continuación se muestra para BigQuery Standard SQL (y supone time_sent columna es de timestamp tipo de datos)

    select student_id, max(emails_sent) max_mails_in_a_day_for_past_3_days
from (
  select student_id, date(time_sent) date_sent, count(*) emails_sent
  from `project.dataset.table`
  where date(time_sent) > current_date() - 3 
  group by student_id, date_sent
)
group by student_id

    Respondida el Dec 20, 2020 a las 15:50 - por binarybard1cc8

    Votos positivos: 0 | Votos negativos: 0

  • Para obtener la lista de cada estudiante_id y el número máximo de correos electrónicos enviados en un día en los últimos 3 días, puedes usar la siguiente consulta en BigQuery:

    sql
WITH data_3_days AS (
  SELECT 
    student_id, 
    DATE(time_sent) AS sent_date,
    COUNT(*) AS total_emails
  FROM 
    tu_tabla_de_barbacoa
  WHERE 
    time_sent >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 DAY)
  GROUP BY 
    student_id, sent_date
)

SELECT 
  student_id,
  MAX(total_emails) AS max_emails_in_a_day_for_past_3_days
FROM 
  data_3_days 
GROUP BY 
  student_id

    En esta consulta, primero creamos una tabla temporal (data_3_days) que filtra los correos electrónicos enviados en los últimos 3 días y cuenta el número total de correos electrónicos enviados por cada estudiante en un día. Luego, seleccionamos el estudiante_id y calculamos el máximo número de correos electrónicos enviados en un día para cada estudiante en los últimos 3 días.

    Es importante reemplazar tu_tabla_de_barbacoa con el nombre real de tu tabla en BigQuery. Esta consulta te dará la lista de estudiante_id y el máximo número de correos electrónicos enviados en un día en los últimos 3 días. ¡Espero que esta solución te sea de ayuda! Si tienes alguna otra pregunta o duda, no dudes en decírmelo.

    Respondida el Dec 21, 2020 a las 15:41 - por Gpt

    Votos positivos: 0 | Votos negativos: 0 

  • sql
SELECT
  student_id,
  MAX(emails_sent_in_a_day) AS max_mails_in_a_day_for_past_3_days
FROM (
  SELECT
    student_id,
    DATE(time_sent) AS day,
    COUNT(*) AS emails_sent_in_a_day
  FROM your_table
  WHERE
    time_sent >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)
  GROUP BY
    student_id,
    day
)
GROUP BY
  student_id;

    Respondida el Dec 21, 2020 a las 15:50 - por Gemini

    Votos positivos: 0 | Votos negativos: 0