Changes

Jump to navigation Jump to search
+ CASE example
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 ====

Navigation menu