Micro-Structure de Marché

with p0 as (
  select '{to_date_s}'::date as to_date

       , '{dt_s}'::date as from_date
       , {pt_id.id} as ref_pt_id
),
p as (
  select * from p0
),
-- 1. Construction des 20 strates de l'histogramme
ticker_stats as (
  select ppi.product_tmpl_id as pt_id
       , min(ppi.close) as min_close
       , max(ppi.close) as max_close
       , nullif((max(ppi.close) - min(ppi.close)) / 20.0, 0) as bucket_size 
       , (min(ppi.close) + max(ppi.close)) / 2.0 as prix_milieu
  from product_pricelist_item ppi
  cross join p
  where ppi.product_tmpl_id = p.ref_pt_id
    and ppi.date >= p.from_date
    and ppi.date <= p.to_date
  group by ppi.product_tmpl_id
),
histogramme_rose as (
  select ppi.product_tmpl_id as pt_id
       , ts.bucket_size
       , ts.prix_milieu
       , floor(ppi.close / ts.bucket_size) * ts.bucket_size as price_bucket_start
       , sum(ppi.volume) as volume_barre_rose
       , count(*) as jours_dans_strate
  from product_pricelist_item ppi
  join ticker_stats ts on ppi.product_tmpl_id = ts.pt_id
  cross join p
  where ppi.date >= p.from_date
    and ppi.date <= p.to_date
  group by ppi.product_tmpl_id, ts.bucket_size, ts.prix_milieu, floor(ppi.close / ts.bucket_size)
),
bornes_extremes as (
  select max(price_bucket_start) as max_price_bucket
       , min(price_bucket_start) as min_price_bucket
  from histogramme_rose
),
-- 2. Sélection des deux POCs sur les volumes bruts (en excluant les extrémités parasites)
premier_poc_haut as (
  select price_bucket_start as prix_poc_haut
  from histogramme_rose h
  cross join bornes_extremes b
  where h.price_bucket_start >= h.prix_milieu
    and h.price_bucket_start < b.max_price_bucket
  order by volume_barre_rose desc
  limit 1
),
second_poc_bas as (
  select price_bucket_start as prix_poc_bas
  from histogramme_rose h
  cross join bornes_extremes b
  where h.price_bucket_start < h.prix_milieu
    and h.price_bucket_start > b.min_price_bucket
  order by volume_barre_rose desc
  limit 1
),
-- 3. ÉTAPE CLÉ : Lissage de tendance pour gommer le bruit local
histogramme_lisse as (
  select price_bucket_start
       , volume_barre_rose
       , avg(volume_barre_rose) over(
           order by price_bucket_start 
           rows between 1 preceding and 1 following
         ) as volume_tendance
  from histogramme_rose
),
-- 4. Recherche du CREUX (NLV) sur la tendance lissée
creux_entre_les_deux as (
  select hl.price_bucket_start as prix_creux
  from histogramme_lisse hl
  cross join premier_poc_haut ph
  cross join second_poc_bas pb
  where hl.price_bucket_start > pb.prix_poc_bas
    and hl.price_bucket_start < ph.prix_poc_haut
  order by hl.volume_tendance asc
  limit 1
),
-- 5. Restitution de la structure propre avec les vrais volumes réels
jalons_structurels as (
  select h.*
       , concat_ws(' / '
           , case when h.price_bucket_start = (select max_price_bucket from bornes_extremes) then 'max' end
           , case when h.price_bucket_start = (select min_price_bucket from bornes_extremes) then 'min' end
           , case when h.price_bucket_start = (select prix_poc_haut from premier_poc_haut) then 'poc_ancien_regime (Haut)' end
           , case when h.price_bucket_start = (select prix_poc_bas from second_poc_bas) then 'poc_nouveau_regime (Bas)' end
           , case when h.price_bucket_start = (select prix_creux from creux_entre_les_deux) then 'creux_rupture' end
         ) as info_text
  from histogramme_rose h
), data as (
select pt_id
     , round(price_bucket_start::numeric, 2) as niveau_prix_regime
     , round((price_bucket_start + bucket_size)::numeric, 2) as fin_zone_regime
     , info_text
     , round(volume_barre_rose::numeric, 0) as volume_total_strate
     , jours_dans_strate as jours_dans_la_zone
from jalons_structurels
where info_text <> ''
order by niveau_prix_regime desc
)
select * from data;