Produits Entr'ouvert » w.c.s.

La requête SQL "brute" correspondant à ce besoin:

with
        end_statuses as (select (values('wf-finished')) status),
        start_status as (select (values('wf-new')) status),
        foo as (
SELECT
        f.id,
        f.status,
        f.receipt_time,
        array_agg(start_evo.time) as starts,
        (array_agg(end_evo.time))[1] as end_time
FROM formdata_101_allo_toulouse_signalements f
JOIN formdata_101_allo_toulouse_signalements_evolutions start_evo ON start_evo.formdata_id = f.id AND start_evo.status in (select * from start_status) 
JOIN formdata_101_allo_toulouse_signalements_evolutions end_evo on end_evo.formdata_id = f.id AND end_evo.status in (select * from end_statuses) 
GROUP BY 1  ),
        bar as (
                SELECT id, COALESCE((select * from unnest(starts) x where x <= foo.end_time order by x desc limit 1), receipt_time) as start_time, end_time FROM foo
),
zoo as (SELECT end_time - start_time as d FROM bar WHERE end_time IS NOT NULL AND start_time IS NOT NULL)
select min(d), max(d), avg(d), percentile_cont(0.5) WITHIN GROUP (ORDER BY d) from zoo;

Je vois pour intégrer ça directement dans wcs.

Note : la version optimisée ainsi s'exécute en moins de 3 secondes, contre un temps supérieur à 60s (non déterminé) pour la version python.