Open
Description
Voici un exemple de requête pour venir typer les segments en 'FISHING' d'un bateau entre deux timestamp
Je crée d'abord une function avec paramètres:
- _mmsi (int)
- _from/_to (carchar): le type source de segment à transformer vers le type cible ('AT_SEA', 'DEFAULT_AIS', 'FISHING')
- _start/_end (timestamptz): la plage horaires pour laquelle il faut modifier le type de segment _from vers _to
Je conseille de faire un script complet en y ajoutant à la fin tous les appels à function avec les bateau/transtypage/plage horaires et le stocker dans un fichier dans le datasets et/ou dans le dépôt de code pour pouvoir le réappliquer au besoin sur des backups et/ou si on relance les process ETL complet en attendant que le ETL le fasse +/- directement
Voici la base du script à compléter en fin de fichier
DROP function if EXISTS public.bloom_set_segment_activity;
CREATE OR REPLACE FUNCTION public.bloom_set_segment_activity(_mmsi int8, _from varchar, _to varchar, _start timestamptz, _end timestamptz)
RETURNS int8
LANGUAGE plpgsql
AS $function$
DECLARE lines int8;
BEGIN
select count(*) from fct_segment seg
into lines
join fct_excursion fe on seg.excursion_id = fe.id
join dim_vessel dv on dv.id = fe.vessel_id
where dv.mmsi = _mmsi
and (
seg.timestamp_start between _start and _end
or
seg.timestamp_end between _start and _end
)
and seg."type" = _from;
update fct_segment fs2
set "type" = _to
where fs2.id in (
select seg.id from fct_segment seg
join fct_excursion fe on seg.excursion_id = fe.id
join dim_vessel dv on dv.id = fe.vessel_id
where dv.mmsi = _mmsi
and (
seg.timestamp_start between _start and _end
or
seg.timestamp_end between _start and _end
)
and seg."type" = _from
);
return lines;
END;
$function$
;
-- Ajouter ici la liste des appels à fonction pour chaque bateau, type from/to, start/end
-- Exemple pour le mmsi: 205077000, typage de AT_SEA à FISHING entre 2024-12-24 04:56:00.0000+00:00 et 2024-12-24 08:57:00.0000+00:00
select bloom_set_segment_activity(205077000,'AT_SEA','FISHING','2024-12-24 04:56:00.0000+00:00','2024-12-24 08:57:00.0000+00:00');