Comment calculer la proportion de valeurs vides/égales à zéro dans chaque colonne d'une table

Comment calculer la proportion de valeurs vides/égales à zéro dans chaque colonne d’une table

Si une colonne est clairsemée (vide ou contient principalement des zéros), ClickHouse peut l’encoder dans un format clairsemé et optimiser automatiquement les calculs : les données n’ont pas besoin d’être entièrement décompressées pendant les requêtes. En effet, si vous savez à quel point une colonne est clairsemée, vous pouvez définir sa proportion à l’aide du paramètre ratio_of_defaults_for_sparse_serialization afin d’optimiser la sérialisation.

Cette requête pratique peut prendre un certain temps, mais elle analyse chaque ligne de votre table et détermine la proportion de valeurs égales à zéro (ou à la valeur par défaut) dans chaque colonne de la table spécifiée :

SELECT *
    APPLY x -> (x = defaultValueOfArgumentType(x)) APPLY avg APPLY x -> round(x, 3)
FROM table_name
FORMAT Vertical

Par exemple, nous avons exécuté la requête ci-dessus sur la table environmental sensors dataset, nommée sensors, qui contient plus de 20 milliards de lignes et 19 colonnes :

SELECT *
    APPLY x -> (x = defaultValueOfArgumentType(x)) APPLY avg APPLY x -> round(x, 3)
FROM sensors
FORMAT Vertical

Voici la réponse :


Row 1:
──────
round(avg(equals(sensor_id, defaultValueOfArgumentType(sensor_id))), 3):                 0
round(avg(equals(sensor_type, defaultValueOfArgumentType(sensor_type))), 3):             0.159
round(avg(equals(location, defaultValueOfArgumentType(location))), 3):                   0
round(avg(equals(lat, defaultValueOfArgumentType(lat))), 3):                             0.001
round(avg(equals(lon, defaultValueOfArgumentType(lon))), 3):                             0.001
round(avg(equals(timestamp, defaultValueOfArgumentType(timestamp))), 3):                 0
round(avg(equals(P1, defaultValueOfArgumentType(P1))), 3):                               0.474
round(avg(equals(P2, defaultValueOfArgumentType(P2))), 3):                               0.475
round(avg(equals(P0, defaultValueOfArgumentType(P0))), 3):                               0.995
round(avg(equals(durP1, defaultValueOfArgumentType(durP1))), 3):                         0.999
round(avg(equals(ratioP1, defaultValueOfArgumentType(ratioP1))), 3):                     0.999
round(avg(equals(durP2, defaultValueOfArgumentType(durP2))), 3):                         1
round(avg(equals(ratioP2, defaultValueOfArgumentType(ratioP2))), 3):                     1
round(avg(equals(pressure, defaultValueOfArgumentType(pressure))), 3):                   0.83
round(avg(equals(altitude, defaultValueOfArgumentType(altitude))), 3):                   1
round(avg(equals(pressure_sealevel, defaultValueOfArgumentType(pressure_sealevel))), 3): 1
round(avg(equals(temperature, defaultValueOfArgumentType(temperature))), 3):             0.532
round(avg(equals(humidity, defaultValueOfArgumentType(humidity))), 3):                   0.544

1 row in set. Elapsed: 992.041 sec. Processed 20.69 billion rows, 1.39 TB (20.86 million rows/s., 1.40 GB/s.)

D'après les résultats ci-dessus :

la colonne sensor_id n'est pas du tout clairsemée. En fait, chaque ligne a une valeur différente de zéro
la colonne sensor_type n'est clairsemée qu'à environ 15,9 %
la colonne P0 est très clairsemée : 99,9 % des valeurs sont égales à zéro
la colonne pressure est assez clairsemée, à 83 %
et la colonne temperature a 53,2 % de ses valeurs manquantes ou égales à zéro

Comme nous l'avons dit, c'est une requête pratique pour calculer le degré de parcimonie de vos colonnes dans une table ClickHouse !