ÚNASE a dos consultas SELECT con COUNT y obtenga una columna adicional
Tengo una tabla con la siguiente estructura:
name | version | processed | processing | updated | ref_time
------+---------+-----------+------------+----------+----------
abc | 1 | t | f | 27794395 | 27794160
def | 1 | t | f | 27794395 | 27793440
ghi | 1 | t | f | 27794395 | 27793440
jkl | 1 | f | f | 27794395 | 27794160
mno | 1 | t | f | 27794395 | 27793440
pqr | 1 | f | t | 27794395 | 27794160
Puedo usar la siguiente consulta para contar el número total dentro de cada uno ref_time:
SELECT ref_time, COUNT (*) AS total
FROM (SELECT * FROM status_table) AS _
GROUP BY ref_time;
ref_time | total
----------+-------
27794160 | 2259
27793440 | 2259
Y la siguiente consulta para contar el número total dentro de cada uno ref_timedonde processed=true:
SELECT ref_time, COUNT (*) AS processed FROM (SELECT * FROM status_table WHERE processed=true) AS _ GROUP BY ref_time;
ref_time | processed
----------+-----------
27794160 | 1057
27793440 | 2259
Luego intento fusionar la información usando un INNER JOINon ref_time:
SELECT * FROM
(SELECT ref_time, COUNT (*) AS total
FROM (SELECT * FROM status_table) AS _
GROUP BY ref_time) result_total
INNER JOIN
(SELECT ref_time, COUNT (*) AS processed
FROM (SELECT * FROM status_table WHERE processed=true) AS _
GROUP BY ref_time) result_processed
ON result_total.ref_time = result_processed.ref_time;
ref_time | total | ref_time | processed
----------+-------+----------+-----------
27794160 | 2259 | 27794160 | 1057
27793440 | 2259 | 27793440 | 2259
Primera pregunta: ¿cómo evito la ref_timecolumna duplicada?
Segunda pregunta: ¿cómo agrego una percentcolumna adicional derivada como (100 * processed / total)(a un dp), es decir, para dar:
ref_time | total | processed | percent
----------+-------+-----------+---------
27794160 | 2259 | 1057 | 46.8
27793440 | 2259 | 2259 | 100.0
Tercera pregunta: ¿existe una forma más eficiente de hacer esto? ¿ Puedo evitar hacer dos SELECTconsultas separadas?
Postgres tiene funciones agregadas expresivas.
Para hacer el recuento condicional, podemos usar la filtercláusula estándar directamente contra la columna processed, que es booleana. En cuanto al porcentaje (o proporción), podemos convertir el valor booleano en un número entero (lo que produce 0o 1, como era de esperar) y tomar el promedio de eso.
Entonces:
select ref_time,
count(*) cnt_total,
count(*) filter(where processed) cnt_processed,
avg(processed::int) ratio_processed
from mytable
group by ref_time
Aquí hay una demostración con sus datos de muestra, que devuelve:
| tiempo_referencia | cnt_total | cnt_procesado | ratio_procesado |
|---|---|---|---|
| 27794160 | 3 | 1 | 0.33333333333333333333 |
| 27793440 | 3 | 3 | 1.000000000000000000000 |
SELECT ref_time,count(*)as total,
SUM
(
CASE
WHEN processed='t' then 1
else 0
END
)processed
FROM YOUR_TABLE
GROUP BY ref_time
with main as (
select
ref_time,
sum(case when processed = 'true' then 1 else 0 end ) as total_processed,
count(*) as total
from <table_name>
group by 1
)
select *, round((total_processed::numeric / nullif(total::numeric,0)) * 100),2) as percent from main
SELECT ref_time
, count(*) AS total
, count(*) FILTER (WHERE processed) AS processed
, round(count(*) FILTER (WHERE processed) * 100.0 / count(*), 2) AS percent
FROM status_table
GROUP BY 1;
violín
Devolver valores porcentuales redondeados reales.
avg(processed::int), como lo hace la respuesta actualmente aceptada, es inteligente, pero la función agregada adicional más el casting involucrado agrega costo. (La repetición count(*)solo se calcula una vez). Esto suele ser más rápido.
Sobre la FILTERcláusula agregada:
- Agregar columnas con filtros adicionales (distintos)
Aparte: "nombre" no es un buen nombre. Reconsiderar.
