¿Cómo utilizo una función en ventana con count(case when…)?
¡Hola mundo!
Tengo una tabla de nivel “level” que se ve así:
level | id | level_date |
---|---|---|
1 | A | 2021-12-02 |
2 | A | 2021-12-04 |
3 | A | 2021-12-08 |
1 | B | 2021-12-02 |
2 | B | 2021-12-05 |
3 | B | 2021-12-09 |
y una segunda tabla de “batallas”:
id | battle_date |
---|---|
A | 2021-12-01 |
A | 2021-12-03 |
A | 2021-12-06 |
A | 2021-12-07 |
B | 2021-12-01 |
B | 2021-12-02 |
B | 2021-12-03 |
Lo que estoy tratando de hacer es encontrar el promedio de la cantidad de batallas necesarias para llegar a cada nivel.
Cuando battledate > levelX-1date, pero battledate < levelXdate, eso significa que esta batalla es necesaria para llegar al nivel X y debe contarse para el nivel X.
Entonces, para el jugador A tenemos una batalla para llegar al nivel 1, una batalla para llegar al nivel 2 y dos batallas para llegar al nivel 3.
Y para el jugador B tenemos una batalla para llegar al nivel 1, dos batallas para llegar al nivel 2 y cero batallas para llegar al nivel 3.
La tabla resultante debería verse así:
level | avgbattlecount |
---|---|
1 | 1 |
2 | 1.5 |
3 | 1 |
Estoy bastante seguro de que esto es una especie de problema de “gaps and islands”, pero no sé exactamente cómo debería construir una consulta que considere una función de ventana para contar el promedio de (battle_count) para los niveles.
davy.ai
Para resolver este problema, podemos utilizar el siguiente enfoque:
Luego, necesitamos calcular el “conteo de batallas” para cada fila en la tabla de batallas. Podemos hacer esto utilizando una subconsulta para contar la cantidad de batallas que ocurrieron entre la fecha de nivel anterior y la fecha de nivel actual para cada fila.
A continuación, necesitamos agrupar los datos por nivel y calcular el promedio del conteo de batallas para cada grupo.
Aquí está la consulta SQL que implementa este enfoque:
En esta consulta,
level_dates_table
se refiere a la primera tabla con datos de nivel ybattles_table
se refiere a la segunda tabla con datos de batallas.La subconsulta calcula el
battle_count
para cada fila contando la cantidad de batallas que ocurrieron entre lalevel_date
anterior y lalevel_date
actual, agrupadas por cada combinación delevel
eid
.Finalmente, la consulta externa agrupa el resultado por
level
y calcula el promedio delbattle_count
para cada grupo.Esta consulta debería producir el resultado deseado como se muestra en la tabla de salida de ejemplo anterior.