Postei a mesma pergunta no SO, mas pensei em tentar aqui também, já que qualquer outro tipo de otimização e conselho são muito bem-vindos :) De qualquer forma, aqui está minha postagem, palavra por palavra:
Preciso de ajuda para escrever/otimizar uma consulta para recuperar a versão mais recente de cada linha por tipo e realizar alguns cálculos dependendo do tipo. Acho que seria melhor se eu ilustrasse com um exemplo.
Dado o seguinte conjunto de dados:
+-------+-------------------+---------------------+-------------+---------------------+--------+----------+
| id | event_type | event_timestamp | message_id | sent_at | status | rate |
+-------+-------------------+---------------------+-------------+---------------------+--------+----------+
| 1 | create | 2016-11-25 09:17:48 | 1 | 2016-11-25 09:17:48 | 0 | 0.500000 |
| 2 | status_update | 2016-11-25 09:24:38 | 1 | 2016-11-25 09:28:49 | 1 | 0.500000 |
| 3 | create | 2016-11-25 09:47:48 | 2 | 2016-11-25 09:47:48 | 0 | 0.500000 |
| 4 | status_update | 2016-11-25 09:54:38 | 2 | 2016-11-25 09:48:49 | 1 | 0.500000 |
| 5 | rate_update | 2016-11-25 09:55:07 | 2 | 2016-11-25 09:50:07 | 0 | 1.000000 |
| 6 | create | 2016-11-26 09:17:48 | 3 | 2016-11-26 09:17:48 | 0 | 0.500000 |
| 7 | create | 2016-11-27 09:17:48 | 4 | 2016-11-27 09:17:48 | 0 | 0.500000 |
| 8 | rate_update | 2016-11-27 09:55:07 | 4 | 2016-11-27 09:50:07 | 0 | 2.000000 |
| 9 | rate_update | 2016-11-27 09:55:07 | 2 | 2016-11-25 09:55:07 | 0 | 2.000000 |
+-------+-------------------+---------------------+-------------+---------------------+--------+----------+
O resultado esperado deve ser:
+------------+--------------------+--------------------+-----------------------+
| sent_at | sum(submitted_msg) | sum(delivered_msg) | sum(rate_total) |
+------------+--------------------+--------------------+-----------------------+
| 2016-11-25 | 2 | 2 | 2.500000 |
| 2016-11-26 | 1 | 0 | 0.500000 |
| 2016-11-27 | 1 | 0 | 2.000000 |
+------------+--------------------+--------------------+-----------------------+
No final do post está a consulta que é utilizada para obter este resultado. Estou disposto a apostar que deve haver uma maneira de otimizá-lo, já que está usando subconsultas com junções e, pelo que li sobre o BigQuery, é melhor evitar junções. Mas primeiro alguns antecedentes:
Em essência, o conjunto de dados representa uma tabela anexada, na qual vários eventos são gravados. O tamanho dos dados está na casa das centenas de milhões e crescerá para mais de bilhões. Como as atualizações no BigQuery não são práticas e os dados estão sendo transmitidos para o BQ, preciso de uma maneira de recuperar o mais recente de cada evento, realizar alguns cálculos com base em determinadas condições e retornar um resultado preciso. A consulta é gerada dinamicamente, com base na entrada do usuário, portanto, mais campos/cálculos podem ser incluídos, mas foram omitidos para simplificar.
- Há apenas um
createevento, masnde qualquer outro tipo - Para cada grupo de eventos, apenas o último deve ser levado em consideração ao fazer os cálculos.
- status_update - atualiza o status
- rate_update - atualiza a taxa
- criar - auto explicativo
- Todo evento que não é
createpode não carregar o restante das informações do original/pode não ser preciso (exceto para message_id e o campo em que o evento está operando) (o conjunto de dados é simplificado, mas imagine que há muito mais colunas e mais eventos serão adicionados mais tarde)- Por exemplo, a
rate_updatepode ou não ter o campo de status definido, ou ser um valor que não é o final, então nenhum cálculo pode ser feito no campo de status de umrate_updateevento e o mesmo vale parastatus_update
- Por exemplo, a
- Pode-se supor que a tabela seja particionada por data e cada consulta fará uso das partições. Essas condições foram omitidas em favor da simplicidade por enquanto.
Então acho que tenho algumas perguntas:
- Como essa consulta pode ser otimizada?
- Será uma ideia melhor colocar os eventos, além de
createsuas próprias tabelas, onde os únicos campos disponíveis serão os relevantes para os eventos e necessários para as junções (message_id, event_timestamp)? Isso reduzirá a quantidade de dados processados? - Qual seria a maneira ideal de adicionar mais eventos no futuro, que terão suas próprias condições e cálculos?
Na verdade, qualquer conselho sobre como consultar esse conjunto de dados de maneira eficiente e amigável é mais do que bem-vindo! Obrigada! :)
A monstruosidade que eu inventei é a seguinte. São INNER JOINSusados para recuperar a versão mais recente de cada linha, conforme este recurso
select
sent_at as sent_at,
sum(submitted_msg) as submitted,
sum(delivered_msg) as delivered,
sum(sales_rate_total) as sales_rate_total
FROM (
#DELIVERED
SELECT
d.message_id,
FORMAT_TIMESTAMP('%Y-%m-%d 00:00:00', sent_at) AS sent_at,
0 as submitted_msg,
sum(if(status=1,1,0)) as delivered_msg,
0 as sales_rate_total
FROM `events` d
INNER JOIN
(
select message_id, max(event_timestamp) as ts
from `events`
where event_type = "status_update"
group by 1
) g on d.message_id = g.message_id and d.event_timestamp = g.ts
GROUP BY 1,2
UNION ALL
#SALES RATE
SELECT
s.message_id,
FORMAT_TIMESTAMP('%Y-%m-%d 00:00:00', sent_at) AS sent_at,
0 as submitted_msg,
0 as delivered_msg,
sum(sales_rate) as sales_rate_total
FROM `events` s
INNER JOIN
(
select message_id, max(event_timestamp) as ts
from `events`
where event_type in ("rate_update", "create")
group by 1
) f on s.message_id = f.message_id and s.event_timestamp = f.ts
GROUP BY 1,2
UNION ALL
#SUBMITTED & REST
SELECT
r.message_id,
FORMAT_TIMESTAMP('%Y-%m-%d 00:00:00', sent_at) AS sent_at,
sum(if(status=0,1,0)) as submitted_msg,
0 as delivered_msg,
0 as sales_rate_total
FROM `events` r
INNER JOIN
(
select message_id, max(event_timestamp) as ts
from `events`
where event_type = "create"
group by 1
) e on r.message_id = e.message_id and r.event_timestamp = e.ts
GROUP BY 1, 2
) k
group by 1