Cómo seleccionar datos con porcentajes en ClickHouse?

17 Jul, 2023 Programación 0

Dada la siguiente tabla:

CREATE TABLE main
(
    `job_id` UUID,
    `request_time` DateTime,
    `host_id` UInt8,
    `status_code` LowCardinality(String),
)
ENGINE = MergeTree
ORDER BY request_time
SETTINGS index_granularity = 8192

Estoy tratando de obtener todos los estados por host, con su porcentaje relativo. Para hacer eso, necesito contar los resultados agrupados por host y estado, y dividir cada campo contado por el total de campos de host.

Por ejemplo, esta consulta habría funcionado en MySQL:

SELECT
    main.host_id,
    main.status_code,
    COUNT(*) AS status_count,
    COUNT(*) / sub.host_total * 100 AS percent
FROM
    main
INNER JOIN (
    SELECT host_id, COUNT(*) AS host_total
    FROM main
    GROUP BY host_id
) AS sub ON (sub.host_id = main.host_id)
GROUP BY
    main.host_id,
    main.status_code

Pero ClickHouse arroja el siguiente error:

DB::Exception: Identificador desconocido: host_total; hay columnas: host_id, status_code, count(): al procesar host_id, status_code, count() AS status_count, (count() / host_total) * 100 AS percent. (UNKNOWN_IDENTIFIER)

Probablemente porque las subconsultas correlacionadas (dependientes) no son compatibles con ClickHouse.

Me sugirieron usar CTE (expresiones de tabla comunes), así que intenté esto:

WITH sub AS (
    SELECT host_id, COUNT(*) AS host_total
    FROM main
    GROUP BY host_id
)
SELECT
    main.host_id,
    main.status_code,
    COUNT(*) AS status_count,
    COUNT(*) / (SELECT host_total FROM sub WHERE sub.host_id = main.host_id) * 100 AS percent
FROM
    main
GROUP BY
    main.host_id,
    main.status_code

Pero aún no tuve suerte:

DB::Exception: Columnas faltantes: 'main.host_id' al procesar la consulta: 'SELECT host_total FROM sub WHERE host_id = main.host_id', columnas requeridas: 'host_total' 'host_id' 'main.host_id' 'host_total' 'host_id' 'main.host_id': al procesar (SELECT host_total FROM sub WHERE sub.host_id = main.host_id) AS _subquery20: al procesar count() / ((SELECT host_total FROM sub WHERE sub.host_id = main.host_id) AS subquery20): al procesar (count() / ((SELECT host_total FROM sub WHERE sub.host_id = main.host_id) AS subquery20)) * 100 AS percent. (UNKNOWN_IDENTIFIER)

davy.ai

July 17, 2023 at 8:20 pm

Solución para obtener todos los estados por host con el porcentaje relativo.

Para obtener todos los estados por host con el porcentaje relativo utilizando la base de datos ClickHouse, necesitamos contar los resultados agrupados por host y estado, y dividir cada campo contado por el total de campos de host. Aquí hay una consulta que podría funcionar:

CON sub AS (
    SELECT host_id, 
           COUNT() AS host_total 
    FROM main 
    GROUP BY host_id
)
SELECT main.host_id, 
       main.status_code, 
       COUNT() AS status_count,
       COUNT() * 100.0 / sub.host_total AS percent
FROM main
INNER JOIN sub ON (sub.host_id = main.host_id)
GROUP BY main.host_id, 
         main.status_code, 
         sub.host_total

La CTE (expresión de tabla común) “sub” se utiliza para calcular el número total de registros para cada host. Esta subconsulta calcula el número total de registros para cada host agrupando la tabla “main” por “host_id”. Luego, en la consulta principal, utilizamos el valor calculado de “host_total” para encontrar el porcentaje de registros para cada código de estado.

Es importante tener en cuenta que al utilizar la función “COUNT()” sin especificar ningún nombre de columna, se obtiene el recuento de todos los registros en el grupo, y multiplicamos por 100.0 para forzar el cálculo como decimal. Esta consulta evita el problema de la subconsulta correlacionada utilizando una cláusula JOIN para combinar la tabla “sub” con la tabla “main”.

Esta consulta debería proporcionar la salida deseada de todos los estados por host, con su porcentaje relativo.

Answer

davy.ai

July 17, 2023 at 8:20 pm
Solución para obtener todos los estados por host con el porcentaje relativo.

Para obtener todos los estados por host con el porcentaje relativo utilizando la base de datos ClickHouse, necesitamos contar los resultados agrupados por host y estado, y dividir cada campo contado por el total de campos de host. Aquí hay una consulta que podría funcionar:
```
CON sub AS (
    SELECT host_id, 
           COUNT() AS host_total 
    FROM main 
    GROUP BY host_id
)
SELECT main.host_id, 
       main.status_code, 
       COUNT() AS status_count,
       COUNT() * 100.0 / sub.host_total AS percent
FROM main
INNER JOIN sub ON (sub.host_id = main.host_id)
GROUP BY main.host_id, 
         main.status_code, 
         sub.host_total
```
La CTE (expresión de tabla común) “sub” se utiliza para calcular el número total de registros para cada host. Esta subconsulta calcula el número total de registros para cada host agrupando la tabla “main” por “host_id”. Luego, en la consulta principal, utilizamos el valor calculado de “host_total” para encontrar el porcentaje de registros para cada código de estado.

Es importante tener en cuenta que al utilizar la función “COUNT()” sin especificar ningún nombre de columna, se obtiene el recuento de todos los registros en el grupo, y multiplicamos por 100.0 para forzar el cálculo como decimal. Esta consulta evita el problema de la subconsulta correlacionada utilizando una cláusula JOIN para combinar la tabla “sub” con la tabla “main”.

Esta consulta debería proporcionar la salida deseada de todos los estados por host, con su porcentaje relativo.

Comments are closed.

es.davy.ai

¿Tienes una pregunta?

Answer

davy.ai