Eu tenho uma tabela como abaixo onde as ações do usuário são armazenadas com um carimbo de data/hora. Meu objetivo é identificar a ação que aconteceu antes de uma ação específica (chamada reference_action) e contar o número dessas ações para ver quais ações acontecem antes das ações específicas e como elas são distribuídas.
Estou ciente de funções de janela como LAG() onde posso obter a linha antes de uma determinada linha, mas não consigo descobrir como incluir uma restrição como WHERE action_name = "reference_action".
O mecanismo de consulta é Presto e as tabelas são tabelas Hive, mas estou mais interessado na abordagem SQL geral, portanto, isso não deve importar muito.
| sessão | action_name | carimbo de data/hora |
|---|---|---|
| 1 | "alguma_ação" | 1970-01-01 00:01:00 |
| 1 | "alguma_ação" | 1970-01-01 00:02:00 |
| 1 | "alguma_ação" | 1970-01-01 00:03:00 |
| 1 | "desired_action1" | 1970-01-01 00:04:00 |
| 1 | "reference_action" | 1970-01-01 00:05:00 |
| 1 | "alguma_ação" | 1970-01-01 00:06:00 |
| 1 | "alguma_ação" | 1970-01-01 00:07:00 |
| 2 | "alguma_ação" | 1970-01-01 01:23:00 |
| 2 | "alguma_ação" | 01-01-1970 02:34:00 |
| 2 | "desired_action1" | 1970-01-01 03:45:00 |
| 2 | "reference_action" | 01-01-1970 04:56:00 |
| 2 | "alguma_ação" | 1970-01-01 05:58:00 |
| 3 | "alguma_ação" | 1970-01-01 01:23:00 |
| 3 | "alguma_ação" | 01-01-1970 02:34:00 |
| 3 | "desired_action2" | 1970-01-01 03:45:00 |
| 3 | "reference_action" | 01-01-1970 04:56:00 |
| 3 | "alguma_ação" | 1970-01-01 05:58:00 |
O resultado deve se parecer com:
| ação | contar |
|---|---|
| "desired_action1" | 2 |
| "desired_action2" | 1 |
Existem duas linhas em que "desired_action1" é seguido diretamente por uma linha com "reference_action", quando ordenado por timestamp, portanto, a contagem é 2. A mesma lógica se aplica ao motivo pelo qual a contagem é 1 para "desired_action2".
O objetivo é saber o que um usuário fez antes de fazer uma compra (compra = reference_action). Para entender o que ele fez antes, quero pesquisar a ação que aconteceu antes de uma compra. Portanto, preciso saber o action_name na linha antes de um reference_action. as ações_desejadas precisam ser contadas, as ações_referência são apenas as linhas após as ações que eu quero contar e usadas para determinar quais valores devem ser contados.
As etapas para fazer isso são realmente muito simples em qualquer variante SQL que suporte
LAG:action_nameusandoLAG, em uma tabela derivada ou CTE.'reference_action', essas linhas também terão a ação anterior da primeira etapa. Você quer fazer isso do lado de fora, caso contrário você não obterá osLAGvalores corretos.