Line 149:
Line 149:
│ 716207 │
│ 716207 │
└─────────────┘
└─────────────┘
+
+
==== Count things ====
+
+
CASE allows to filter things when counting, eg. a given value in a list. Example:
+
+
-- count the number of products (nb),
+
-- the number of products with en:ingredients-photo-selected (nb_ing_photo_selected),
+
-- the number of products with en:ingredients-photo-to-be-selected (nb_ing_photo_to_be_selected),
+
-- the number of products with ingredients-photo-selected or ingredients-photo-to-be-selected (nb_ing_photo_tags),
+
-- and the number of products lacking either en:ingredients-photo-selected
+
-- or en:ingredients-photo-to-be-selected (ing_photo_tags_lacking)
+
SELECT
+
count(*) as nb,
+
count(CASE WHEN 'en:ingredients-photo-selected' IN (SELECT unnest(states_tags)) THEN 1 END) AS nb_ing_photo_selected,
+
count(CASE WHEN 'en:ingredients-photo-to-be-selected' IN (SELECT unnest(states_tags)) THEN 1 END) AS nb_ing_photo_to_be_selected,
+
nb_ing_photo_selected + nb_ing_photo_to_be_selected as nb_ing_photo_tags,
+
nb - nb_ing_photo_tags as ing_photo_tags_lacking
+
FROM read_parquet('food.parquet');
+
┌─────────┬───────────────────────┬─────────────────────────────┬───────────────────┬────────────────────────┐
+
│ nb │ nb_ing_photo_selected │ nb_ing_photo_to_be_selected │ nb_ing_photo_tags │ ing_photo_tags_lacking │
+
├─────────┼───────────────────────┼─────────────────────────────┼───────────────────┼────────────────────────┤
+
│ 3621956 │ 951676 │ 1949239 │ 2900915 │ 721041 │
+
└─────────┴───────────────────────┴─────────────────────────────┴───────────────────┴────────────────────────┘
+
==== Annex: parquet schema ====
==== Annex: parquet schema ====