123
edits
Changes
Jump to navigation
Jump to search
Line 3:
Line 3: − + + Line 9:
Line 10: − + − + − + − ┌───────────────┬──────────────────────────────────────────────────────────────┐+ − │ code │ product_name │+ − │ varchar │ varchar │+ − ├───────────────┼──────────────────────────────────────────────────────────────┤+ − │ 0000234022960 │ Croissants pur beurre │+ − │ 0002000013363 │ Grandes galettes au beurre │+ − │ 0008421369416 │ Brioche Pur Beurre │+ − │ 00089739 │ Beurre doux │+ − │ 0016073123478 │ Beurre d'arachide en poudre │+ − │ · │ · │+ − │ · │ · │+ − │ · │ · │+ − │ 3560071532741 │ Beurre demi-sel À teneur réduite en matière grasse 60% Mat… │+ − │ 77646187 │ Beurre sans sel │+ − │ 3596710524006 │ Cosmia crème corps nourrissante - beurre de macadamia + ni… │+ − │ 3596710523986 │ Cosmia crème mains nourrissante - au beurre de karité et à… │+ − │ 4820154481786 │ Petit Beurre │+ − ├───────────────┴──────────────────────────────────────────────────────────────┤ − │ 13791 rows (10 shown) 2 columns │ − └──────────────────────────────────────────────────────────────────────────────┘ − − * '''Search by category''' − − ''Ex:'' ''search for all product belonging to "plant-based food" and "cereals" categories'' − SELECT code, product_name − FROM read_parquet('food.parquet') − WHERE categories ILIKE '%plant-based foods%' AND categories ILIKE '%cereal%'; − ┌───────────────┬───────────────────────────────────────────┐ − │ code │ product_name │ − │ varchar │ varchar │ − ├───────────────┼───────────────────────────────────────────┤ − │ 0000127534587 │ Today's temptations, lithuanian rye bread │ − │ 0000236555909 │ Bakers Best, White Bread │ − │ 0000236598784 │ Bakers Best, Rye Bread │ − │ 0000946909078 │ Augason Farms, Vital Wheat Gluten │ − │ 0003026400168 │ Grainaissance, mochi, cashew-date │ − │ · │ · │ − │ · │ · │ − │ · │ · │ − │ 0003003400513 │ Bread, hearty rye │ − │ 0003003400510 │ Stone Ground Wheat Bread │ − │ 0003003400514 │ Homestyle potato bread │ − │ 20043131 │ Sliced Plain Bagel │ − │ 7020656900144 │ Havregranola Jordbær og bringebær │ − ├───────────────┴───────────────────────────────────────────┤ − │ 51377 rows (10 shown) 2 columns │ − └───────────────────────────────────────────────────────────┘ Line 121:
Line 92: − ===== DICTIONNARY fields =====+ − * '''Extract macro-nutriments'''+ + + + + + + + + + + + + + + − ''Nutriments are stored as a dictionnary {"proteins": "4.2", "proteins_unit": "g", ...}''+ − --TRY_CAST attributes the FLOAT type to the extracted value, while preventing errors+ − SET threads to 4; --avoid out of memory issue by limiting the number of threads+ − SELECT+ − code,+ − product_name,+ − TRY_CAST(nutriments -> 'proteins' AS FLOAT) as proteins, − TRY_CAST(nutriments -> 'fat' AS FLOAT) as fat, − TRY_CAST(nutriments -> 'carbohydrates' AS FLOAT) as carbohydrates − FROM read_parquet('food.parquet') − WHERE − (nutriments ->> 'proteins_unit') = 'g' AND − (nutriments ->> 'fat_unit') = 'g' AND − (nutriments ->> 'carbohydrates_unit') = 'g'; − ┌───────────────┬──────────────────────────────────────────────┬───────────┬───────┬───────────────┐ − │ code │ product_name │ proteins │ fat │ carbohydrates │ − │ varchar │ varchar │ float │ float │ float │ − ├───────────────┼──────────────────────────────────────────────┼───────────┼───────┼───────────────┤ − │ 0000101209159 │ Véritable pâte à tartiner noisettes chocol… │ 8.0 │ 48.0 │ 36.0 │ − │ 0000105000011 │ Lagg's, chamomile herbal tea │ 0.0 │ 0.0 │ 70.0 │ − │ 0000105000042 │ Lagg's, herbal tea, peppermint │ 0.0 │ 0.0 │ 1.47 │ − │ 0000105000059 │ Linden Flowers Tea │ 0.0 │ 0.0 │ 53.33 │ − │ 0000105000073 │ Herbal Tea, Hibiscus │ 66.67 │ 0.0 │ 60.0 │ − │ · │ · │ · │ · │ · │ − │ · │ · │ · │ · │ · │ − │ · │ · │ · │ · │ · │ − │ 4711252014016 │ Peanup choco │ 15.8 │ 32.0 │ 34.6 │ − │ 18785784 │ Not required │ 1.4 │ 16.0 │ 17.0 │ − │ 4994860304117 │ 塩羊羮 │ 3.9 │ 0.5 │ 60.6 │ − │ 5601312079989 │ Mini delícias (cobertas com chocolate de l… │ 6.3333335 │ 24.0 │ 64.0 │ − │ 8017104001033 │ Peperoni grigliati │ 1.8 │ 0.1 │ 7.6 │ − ├───────────────┴──────────────────────────────────────────────┴───────────┴───────┴───────────────┤ − │ 2396027 rows (10 shown) 5 columns │ − └──────────────────────────────────────────────────────────────────────────────────────────────────┘ − ''Enjoy playing with the database!''
no edit summary
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:'''
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:'''
''Ex: display product name (for main language) of all products of with category '''en:butters'''.
* '''Display product name (for main language) of all products of with category en:butters'''.
SELECT
SELECT
code,
code,
list_filter (product_name, x -> x.lang = = 'main')
list_filter (product_name, x -> x.lang = = 'main')
) ['text']
) ['text']
FROM
FROM
'food.parquet'
'food.parquet'
WHERE
WHERE
list_contains (categories_tags, 'en:milks')
list_contains (categories_tags, 'en:milks')
LIMIT
LIMIT
10;';
10;';
┌───────────────┬─────────────────────────────────────────────────────────────────────┐
│ code │ unnest(list_filter(product_name, (x -> (x.lang = 'main'))))['text'] │
│ varchar │ varchar │
├───────────────┼─────────────────────────────────────────────────────────────────────┤
│ 0011110807625 │ Quality food centers, salted butter │
│ 0011110808998 │ Unsalted Butter │
│ 0011110842640 │ Ralphs, Salted Sticks, Butter │
│ 0011110852878 │ Salted butter │
│ 0011110854384 │ Qfc, unsalted butter │
│ 0011110862600 │ King Soopers City Market, Salted Butter │
│ 0011110863256 │ Salted Butter │
│ 0011110863270 │ Unsalted Butter │
│ 0011110893017 │ Salted Butter │
│ 0011110893055 │ Unsalted butter sticks │
├───────────────┴─────────────────────────────────────────────────────────────────────┤
│ 10 rows 2 columns │
└─────────────────────────────────────────────────────────────────────────────────────┘
* '''Who are the biggest contributors?'''
* '''Who are the biggest contributors?'''
└──────────────────┘
└──────────────────┘
* '''How many images where uploaded in 2024?'''
SELECT
SUM(images_filter_count) AS image_count
FROM
(
SELECT
len (
list_filter (
images,
x -> struct_extract (x, 'uploaded_t') > epoch (TIMESTAMP '2024-01-01 00:00:00')
AND struct_extract (x, 'uploaded_t') < epoch (TIMESTAMP '2025-01-01 00:00:00')
)
) as images_filter_count
FROM
'food.parquet'
);
┌─────────────┐
│ image_count │
│ int128 │
├─────────────┤
│ 716207 │
└─────────────┘