53 lines
2.3 KiB
SQL
53 lines
2.3 KiB
SQL
-- 1. Ajouter les extensions
|
||
|
||
|
||
-- 2. Créer la table des produits
|
||
|
||
|
||
-- 4. importer les produits
|
||
|
||
\COPY produit FROM '/tmp/produits/confiture.csv' (FORMAT CSV, header, ENCODING 'UTF8');
|
||
\COPY produit FROM '/tmp/produit.csv' (FORMAT CSV, header, ENCODING 'UTF8');
|
||
|
||
-- 5. ajouter une colonne vecteur
|
||
alter table produit
|
||
add column nutrition vector(10);
|
||
|
||
-- 6. Création de l’index ivfflat
|
||
create index produit_nutrition_hnsw
|
||
on produit
|
||
using hnsw (nutrition vector_l2_ops)
|
||
with (m = 16, ef_construction = 200);
|
||
|
||
-- 7. calculer la moyenne et l'écart-type pour chaque colonne, puis construire le vecteur :
|
||
WITH stats AS (
|
||
SELECT
|
||
AVG(energie) AS mu_energie, STDDEV_SAMP(energie) AS sigma_energie,
|
||
AVG(proteines) AS mu_proteines, STDDEV_SAMP(proteines) AS sigma_proteines,
|
||
AVG(glucides) AS mu_glucides, STDDEV_SAMP(glucides) AS sigma_glucides,
|
||
AVG(sucres) AS mu_sucres, STDDEV_SAMP(sucres) AS sigma_sucres,
|
||
AVG(graisses) AS mu_graisses, STDDEV_SAMP(graisses) AS sigma_graisses,
|
||
AVG(graisses_saturees) AS mu_graisses_saturees, STDDEV_SAMP(graisses_saturees) AS sigma_graisses_saturees,
|
||
AVG(sel) AS mu_sel, STDDEV_SAMP(sel) AS sigma_sel,
|
||
AVG(fibres) AS mu_fibres, STDDEV_SAMP(fibres) AS sigma_fibres,
|
||
AVG(nutriscore) AS mu_nutriscore, STDDEV_SAMP(nutriscore) AS sigma_nutriscore,
|
||
AVG(additifs) AS mu_additifs, STDDEV_SAMP(additifs) AS sigma_additifs
|
||
FROM produit
|
||
)
|
||
UPDATE produit
|
||
SET nutrition = (
|
||
SELECT ARRAY[
|
||
((COALESCE(energie, mu_energie) - mu_energie) / NULLIF(sigma_energie,0)),
|
||
((COALESCE(proteines, mu_proteines) - mu_proteines) / NULLIF(sigma_proteines,0)),
|
||
((COALESCE(glucides, mu_glucides) - mu_glucides) / NULLIF(sigma_glucides,0)),
|
||
((COALESCE(sucres, mu_sucres) - mu_sucres) / NULLIF(sigma_sucres,0)),
|
||
((COALESCE(graisses, mu_graisses) - mu_graisses) / NULLIF(sigma_graisses,0)),
|
||
((COALESCE(graisses_saturees, mu_graisses_saturees) - mu_graisses_saturees) / NULLIF(sigma_graisses_saturees,0)),
|
||
((COALESCE(sel, mu_sel) - mu_sel) / NULLIF(sigma_sel,0)),
|
||
((COALESCE(fibres, mu_fibres) - mu_fibres) / NULLIF(sigma_fibres,0)),
|
||
((COALESCE(nutriscore, mu_nutriscore) - mu_nutriscore) / NULLIF(sigma_nutriscore,0)),
|
||
((COALESCE(additifs, mu_additifs) - mu_additifs) / NULLIF(sigma_additifs,0))
|
||
]::vector
|
||
FROM stats
|
||
);
|