123
edits
Changes
Jump to navigation
Jump to search
Line 1:
Line 1: − + − But no worries, '''DuckDB''' handles any type of data! Learn how to use the database by solving the most common use-cases asked by the community.+ − + + Line 34:
Line 35: − '''Who are the biggest contributors?'''+ − Line 61:
Line 61: − '''Number of added products per year'''+ − Line 91:
Line 90: − '''How many images where uploaded in 2024?'''+ −
no edit summary
The OFF database contains a variety of data in different format, such as '''TEXTS, LISTS, STRUCT, DATES''', and even more...
The Parquet file dump of the Open Food Facts database contains a variety of data in different format, such as string, list, struct, timestamp,...
In this cheatsheet, we will learn how to use the Parquet dump using DuckDB to perform data analysis, by solving some of the most common use-cases asked by the community.
'''Display product name (for main language) of all products of with category en:butters'''
We assume that you've downloaded the <code>food.parquet</code> file locally, and that you've launched Duckdb CLI.
==== Display product name (for main language) of all products of with category <code>en:butters</code> ====
SELECT
SELECT
code,
code,
└─────────────────────────────────────────────────────────────────────────────────────┘
└─────────────────────────────────────────────────────────────────────────────────────┘
==== Who are the biggest contributors? ====
''Ex: Top 10 best contributors in OFF''
''Ex: Top 10 best contributors in OFF''
└──────────────────────────────────────┘
└──────────────────────────────────────┘
==== Number of added products per year ====
--entry_dates_tags is a list of texts. We take the value at position 3: the year
--entry_dates_tags is a list of texts. We take the value at position 3: the year
SELECT entry_dates_tags[3] AS year, count(*) AS count
SELECT entry_dates_tags[3] AS year, count(*) AS count
└──────────────────┘
└──────────────────┘
==== How many images where uploaded in 2024? ====
SELECT
SELECT
SUM(images_filter_count) AS image_count
SUM(images_filter_count) AS image_count