SQL óptimo para realizar múltiples funciones de agregado con diferentes campos de agrupación.
Para simplificar una consulta compleja en la que estoy trabajando, siento que resolver esto es clave.
Tengo la siguiente tabla
id | city | Item |
---|---|---|
1 | chicago | 1 |
2 | chicago | 2 |
3 | chicago | 1 |
4 | cedar | 2 |
5 | cedar | 1 |
6 | cedar | 2 |
7 | detroit | 1 |
Estoy tratando de encontrar la proporción del número de filas agrupadas por ciudad e item en comparación con el número de filas agrupadas solo por los items para cada par único de ciudad e item.
Entonces, me gustaría algo como esto
Ciudad | Item | groupCityItemCount | groupItemCount | Ratio |
---|---|---|---|---|
chicago | 1 | 2 | 4 | 2/4 |
chicago | 2 | 1 | 3 | 1/3 |
cedar | 1 | 1 | 4 | 1/4 |
cedar | 2 | 2 | 3 | 2/3 |
detroit | 1 | 1 | 4 | 1/4 |
Esta es mi solución actual pero es demasiado lenta.
Select city, item, (count() / (select count() from records t2 where t1.item=t2.item)) AS pen_ratio
From records t1
Group By city, item
También he reemplazado el where con groupBy y having pero eso también es lento.
Select city, item, (count() / (select count() from records t2 group by item having t1.item=t2.item)) AS pen_ratio
From records t1
Group By city, item
(Nota: He eliminado column3 y column4 de la solución para reducir el tamaño del código)
(Edit: Error tipográfico señalado por xQbert y MatBailie)
davy.ai
Simplificar una consulta compleja utilizando SQL
Cuando trabajamos con SQL, es común encontrarse con consultas complejas que pueden tardar mucho tiempo en ejecutarse. Esto puede resultar frustrante, especialmente cuando se trabaja con conjuntos de datos grandes. Como experto en tecnología de la información especializado en SQL, UNION, GROUP BY y PostgreSQL-13, una de las habilidades clave que debes dominar es cómo simplificar estas consultas para hacerlas más eficientes.
Consideremos un ejemplo de una consulta compleja que podríamos encontrar en nuestro trabajo. Tenemos una tabla con tres columnas:
id
,city
yitem
. Queremos encontrar la proporción del número de filas agrupadas porcity
yitem
respecto al número de filas agrupadas solo poritem
para cada combinación únicacity-item
. También queremos mostrar los resultados en una tabla con cuatro columnas:Ciudad
,Item
,groupCityItemCount
,groupItemCount
yRatio
.Para resolver este problema, podemos utilizar la siguiente consulta SQL:
Esta consulta utiliza dos funciones
COUNT(*)
con cláusulasFILTER
para contar el número de filas que cumplen ciertas condiciones. La cláusulaWHERE
filtra las filas porcity
, mientras que la funciónCAST
se utiliza para convertir los recuentos en valores flotantes para poder realizar la división. Finalmente, la cláusulaGROUP BY
agrupa los resultados porcity
eitem
.Esta consulta es mucho más simple que la solución original que presentamos y debería ejecutarse mucho más rápido. Como experto en tecnología de la información especializado en SQL, siempre debes esforzarte por simplificar consultas complejas como esta para hacerlas más eficientes y más fáciles de entender.