# Validité 2021 : Diane public
Passation
[ToC]
:::info
A faire (pas commencé) : :soon:
Commencé / en cours : : :wrench:
En pause / attente de ... : :construction:
Terminé : :heavy_check_mark:
Problème ::no_entry:
:::
## Création d'un échantillon pour recette du script
:::warning
:memo: Créer un petit échantillon pour recetter le script
- [x] En QF :heavy_check_mark: (scripts ancienne et nouvelle ventilation lancés jusqu'au bout sur la campagne c41 Elodie)
- [ ] :question: En Prod ? :question: :construction:
:::
:clipboard: `Créer échantillon FP Diane QF pour recette du script.sql`
:::spoiler
```sql
/*********************************************************************/
/* Création d'un échantillon Fonction publique 2021 pour tester le script */
/********************************************************************/
-- Cf. https://gitlab.insee.fr/siera/pirenees-communs/forum/-/issues/239
/*************************************************************************/
--Etape 1 : préparation des données dans ARTEMIS-CLONE
/*************************************************************************/
/* ==> Editeur sql : Clone Artemis */
-- Nombre de sirets déclarants en septembre 2021 (FP)
select count(distinct employeur_sortie) as nbsiret_sept
from dsn_prod.clore_c74_f0n4_data
where validite='2021-09-01'
;
select count(*) as nbobs_sept from dsn_prod.clore_c74_f0n4_data
where validite='2021-09-01';
/*
Création d'un échantillon
- sur un mois (septembre 2021)
- une seule "ligne" Artemis par siret * pcs_complement * pcs_complement_ese_fp * nat_contrat * statempl * statconv * unitmesureref, pour avoir une certaine diversité de contrats
*/
drop table if exists public.eyiq15_c74_f0n4_echant;
create table public.eyiq15_c74_f0n4_echant as
select *
from (SELECT DISTINCT ON (employeur_sortie,pcs_complement,pcs_complement_ese_fp,nat_contrat,statempl,statconv,unitmesureref)
*
FROM dsn_prod.clore_c74_f0n4_data where validite='2021-09-01') as toto
/*order by random() limit 10000*/;
select count(*) from public.eyiq15_c74_f0n4_echant;
;/*282.046 observations, pour 24.767 sirets */
SELECT pg_size_pretty( pg_total_relation_size('public.eyiq15_c74_f0n4_echant'));
/*************************************************************************/
--Etape 2 : transfert dans ARTEMIS-QF via PasPlat
/*************************************************************************/
-- Mettez votre table dans le schéma public pour être sûr de ne rien écraser.
-- Pensez à supprimer la table du clone une fois le transfert effectué.
-- Compter 1 heure de temps de transfert pour 12 Go de données (c'est déjà un bel échantillon).
/* Le Pasplat se trouve ici : X:\HAB-Siera2025\Utilitaires\passe_plat\passe_plat.bat
* Pour pouvoir le lancer, il faut avoir au préalable :
* - créé un dossier "propertiesUtilitaires" dans votre disque Z: personnel
* - créé un fichier properties.config avec le contenu suivant :
username = xxx
password = xxx
(idep en minuscule)
Le transfert prend quelques minutes (une fenêtre de confirmation apparaît quand c'est terminé).
*/
/*************************************************************************/
-- Etape 3 : Placer les données correctement dans ARTEMIS-QF
/*************************************************************************/
/*
Il faut choisir un bac à sable (exemple dsn_bas8).
--> Cf l'occupation des BaS ici : https://gitlab.insee.fr/siera/pirenees-communs/forum/-/issues/257
Les données importées doivent être placées dans
dsn_bas5.clore_data
Il faut supprimer les autres données du bac à sable contenues dans les tables filles
dsn_bas5.clore_cXX_data
Rappel : la phase d'import de l'application DIANE va lire les données contenues dans la table maitre clore_data ou ses filles clore_cXX_data.
L'ouverture de DIANE a été développée pour prendre en compte ce cas (si il n'y a pas de table fille, ce sont les données de la table mère qui sont prises). Par contraposé, quand on veut que des données de la table mère soient prises, il ne faut pas qu'il y a ait des tables filles.
*/
/* ==> Artemis QF */
/* Je me place dans le bac à sable n°5 */
drop table if exists dsn_bas5.clore_data;
create table dsn_bas5.clore_data as
select * from public.eyiq15_c74_f0n4_echant;
select count(*) from dsn_bas5.clore_data;
/*************************************************************************/
-- Etape 4 : Etape 4 : paramétrer votre campagne DIANE et la lancer
/*************************************************************************/
--Administration -> Gerer les campagnes
Paramètres à renseigner :
/*
Validité inférieure --> 2021-01-01
Validité supérieure --> 2021-12-01
décalage --> 0
Population à garder --> (typrem::text is null or typrem::text like '%REM%' or typrem::text like '%PRI%')
Environnement Artemis --> Bas5
année --> 2021
Periodicité --> M (peu importe)
n° --> 9 (peu importe)
Commentaire --> ce qu'on veut
partition --> abs(hashtext(coalesce(employeur_sortie,'')))
*/
--Puis créer un échantillon si on veut réduire la taille (ici je sélectionne 8000 nir au hasard)
--"Administration --> Tirage échantillon"
échantillon n°8
/*
Nombre d'individus : 8000
Tirer sur le Nir : oui
Tirer sur le Siret : non
Expression filtre : [vide]
*/
-- Affecter cet échantillon à notre campagne (cocher la case dans gestion des campagnes pour voir le tableau d'échantillonnage s'afficher).
-- Ouvrir la campagne.
Résutat (à lancer dans un Editeur branché sur Diane QF) :
/* ==> Editeur sql :Diane QF*/
select count(*)
from diane_eyiq15.c41_e8_ouvrir_campagne_data;
;
select count(distinct employeur_sortie)
from diane_eyiq15.c41_e8_ouvrir_campagne_data;
;
select count(distinct personne_sortie)
from diane_eyiq15.c41_e8_ouvrir_campagne_data;
;
*/
```
:::
\
Dans le script Diane, modifier par "Remplacer tout" :
* l'identifiant
* le numéro de la campagne
* associé à toutes les règles (exemple : `41::big`)
* associé aux paramètres : `id_campagne='41'::text;`
<br/>
>Temps de traitement de l'échantillon (8000 Nir) cf. table `pilotage_campagne` dans le schéma associé à l'idep :
| Phase | Durée |
| -------- | -------- |
| Ouvrir | 1 min |
| Pré-calculs | 2 min |
| Conversion | 4 min |
| Cohérence | 1 min |
| Capitalisation | 0 |
| Métier | 2 min |
>Tables en sortie :
:::spoiler
```Sql
select '01-ouvrir' as table, count(*) from diane_eyiq15.c41_e8_ouvrir_campagne_data
union
select '02-pré-calculs' as table, count(*) from diane_eyiq15.c41_e8_pre_calcul_data
union
select '03-pour gestion grade' as table, count(*) from diane_eyiq15.c41_e8_pre_calcul_produit_gestion_grade
union
select '04-doublons' as table, count(*) from diane_eyiq15.c41_e8_pre_calcul_produit_rebut_doublons
union
select '05-conversion' as table, count(*) from diane_eyiq15.c41_e8_convertir_donnees_data
union
select '06-siret sans salaries' as table, count(*) from diane_eyiq15.c41_e8_convertir_donnees_produit_siret_sans_salarie
union
select '07-coherence' as table, count(*) from diane_eyiq15.c41_e8_calculer_salaire_prd_grappe_periode
union
select '08-pr_tout' as table, count(*) from diane_eyiq15.c41_e8_calculer_metier_pr_tout
union
select '09-pr_CJ7' as table, count(*) from diane_eyiq15.c41_e8_calculer_metier_pr_cj7
union
select '10-pr_prive' as table, count(*) from diane_eyiq15.c41_e8_calculer_metier_pr_prive
union
select '11-pr_inx' as table, count(*) from diane_eyiq15.c41_e8_calculer_metier_pr_inx
union
select '12-pr_hv' as table, count(*) from diane_eyiq15.c41_e8_calculer_metier_pr_hv
;
```
---
## Nomenclatures grade
Les tables de nomenclatures utilisées :
| Spécifique FP | Commun privé |
| -------- | -------- |
| nmcl_siasp_NEH_val2020 | nmcl_age_intervalle
| nmcl_siasp_NET_val2020 | nmcl_ape_2008 |
| nmcl_siasp_NNE_val2020 | nmcl_cog_2020 |
| nmcl_art_p19v01 | nmcl_cs |
| nmcl_pcs_complement_ese | nmcl_mesureactivite |
| karnaugh_public_2020 | nmcl_niv2 |
| | nmcl_tranche_effectif |
|{param:table_reprise_grade} | reco_type_emploi |
| nmcl_pcs_2017 | reco_typecontrat_2020 |
:bulb: *Code pour obtenir les tables externes utilisées dans un script :*
```sql
SELECT DISTINCT regexp_replace(expression,'[\r\n\t]',' ','g')::jsonb->> 'table' AS nom_table_externe
FROM diane_[idep].ihm_outil
WHERE id_campagne = '[num_campagne]'
```
:::warning
:memo:
Les 5 premières tables doivent être mises à jour
:warning: **+ créer une table pour les militaires ?**
*(Rque : la table pcs_complement_ese a été créée en dur en sql, il s'agit de quelques codes listés directement dans la norme)*
- [x] En QF
- [x] En Prod
:::
#### Mise à jour des tables externes en QF
***Outil d'import des nomenclatures***
:question:
***Outil d'import de gros fichiers***
* Renommer tous les « code0 », « modalite », .. en « code »
* au format
* txt --> pas d'en-tête avec le nom des variables
* csv --> **avec ou sans** en-tête avec le nom des variables
* convertir en UTF-8
* séparateur = ;
* A déposer sous Applishare : `\\horsprod.aus.applishare.insee.fr\ep_data_hp\diane_qf\fichiers-batch\DEPOT`
\+ fichier descripteur
* Paramétrage dans Diane :
* nom du fichier : diane_nmcl.nmcl_xxxx
* clé primaire : variable de code
* :warning: Ensuite en sql :
* enlever les doublons (NNE) : `distinct code, libelle `--> ca ne passera pas si on utilise la clé primaire !!
* ajouter une colonne d'ordre *(en theorie inutile vu qu'on utilise une jointure exacte (15), mais c'est pour être cohérents avec les autres table nmcl ?)*
* Ajouter une ligne balai (ordre : 999999, code : '.*', libelle = 'balai') *en theorie inutile vu qu'on utilise une jointure exacte (15), mais nécessaire dans les règles Diane*
> Numéro d'ordre :
```Sql
CREATE TABLE diane_nmcl.nmcl_pcs_complement_ese2 as select rank() OVER ( ORDER BY code) as ordre, * from diane_nmcl.nmcl_pcs_complement_ese
CREATE TABLE diane_nmcl.nmcl_art_p19v012 as select rank() OVER ( ORDER BY code) as ordre, * from diane_nmcl.nmcl_art_p19v01
CREATE TABLE diane_nmcl.nmcl_neh_p19v012 as select rank() OVER ( ORDER BY code) as ordre, * from diane_nmcl.nmcl_neh_p19v01
CREATE TABLE nmcl_siasp_NET_val2020_2 as select rank() OVER ( ORDER BY code) as ordre, * from nmcl_siasp_NET_val2020
CREATE TABLE diane_nmcl.nmcl_nne_p20v012 as select rank() OVER ( ORDER BY code) as ordre, * from diane_nmcl.nmcl_nne_p20v01
```
#### Mise à jour des tables externes en Prod
:heavy_check_mark: Exporter les tables à partir de l'IHM de QF et les importer en Prod via l'IHM:
- Dans l’IHM de QF, aller dans administration/gérer les nomenclatures, choisir le type de table , la table et cliquer sur Télécharger la table.
- Dézipper l’archive (au chargement, seul le format csv sera accepté …)
- Dans l’IHM de PROD (https://diane.insee.fr/), environnement PROD, aller dans administration/gérer les nomenclatures, choisir le type de table , cliquer sur parcourir pour choisir l’emplacement du fichier et saisir le nom de la table (ou le nom qu’on veut donner) dans « Nom de la nomenclature ». Cliquer sur Transférer.
Les tables à récupérer :
- nomenclatures (NET, NNE, NEH, PCS, ART, complement_ESE)
- karnaugh
- gestion grade à blanc (puis par la suite gestion grade effective)
---
## Tables de reprise grade
### Table par défaut (à blanc)
:::warning
:memo:
A initialiser une fois ; pas forcément amenée à changer d'une année sur l'autre.
- [x] En QF :heavy_check_mark:
- [x] En Prod :heavy_check_mark:
:::
#### En QF
```
/***************************************************************************/
/* Diane
Création d'une table de reprise grade "à blanc" pour ne pas mettre le script Diane en erreur */
/***************************************************************************/
DROP TABLE IF EXISTS diane_nmcl.reco_grade_a_reintegrer_defaut_nps;
CREATE TABLE diane_nmcl.reco_grade_a_reintegrer_defaut_nps
(versant_fp text, siret text, libemploi text, statut_contrat_gest text,apet_ref text, apet_affectation text, nat_contrat text, statconv text, pcs text, pcs_sortie text, grade_init text, grade_gestion text, nomenc_grade_gestion text, i_grade_gestion text,pcs_grade text, origine_codage_pcsgrade text, ordre bigint)
;
INSERT INTO diane_nmcl.reco_grade_a_reintegrer_defaut_nps (versant_fp, siret, libemploi, statut_contrat_gest, apet_ref, apet_affectation,nat_contrat,
statconv, pcs, pcs_sortie, grade_init, grade_gestion, nomenc_grade_gestion, i_grade_gestion, pcs_grade, origine_codage_pcsgrade,ordre)
VALUES ('.*', '.*', '.*', '.*','.*','.*','.*', '.*', '.*', '.*','.*', NULL, NULL, NULL,NULL,NULL, 0);
```
#### En Prod
Passer par l'**import de nomenclature** classique. (pas besoin de passer par gros fichier)
Exporter la nomenclature reco_xxx en QF.
L'importer en Prod (pour nommer la nomenclature, ne pas oublier "reco_").
### Table issue de la gestion
:::warning
:memo:
- [x] En QF --> Créée directement dans la BDD Diane par le programme R d'export de la gestion grade
- [x] En Prod --> Le programme R exporte également un fichier csv ; Passer par l'import de fichiers Diane
:::
Pour 2021, je n'ai pas accès en écriture au dépot Applishare ...?
Je passe donc **par l'import de nomenclature classique** (ne fait pas le contrôle de l'unicité de la clé d'appariement par contre) de type reco_ (ca passe malgré la taille importante). Il faut importer un fichier csv, comportant en 2ème ligne le type des variables (automatiquement généré par le programme R).
---
## Paramètres
:::warning
:memo:
A mettre à jour dans le script :heavy_check_mark:
:::
Cf. `paramètres 2020.odt` pour les valeurs.
| Spécifique FP | Commun privé |
| -------- | -------- |
| fp_point_indice | tx_crds |
| tx_cotisations_sociales_fonc | tx_csg_non_deductible |
| tx_cotisations_sociales_contpub | tx_csg |
| | tx_cotisations_sociales|
| part_prime_feu | pass |
| part_prime_as | smic |
| liste_feu | (smic_stag) |
| table_reprise_grade | (dernier_jour_annee) |
| (seuil_trmt) | (dernier_vendredi_annee) |
| | table_motifin |
:::warning
:memo:
A mettre à jour également dans le script **+ :warning: dans les programmes gestion grade** :
- [ ] Codage du versant
- [ ] Codage de Statut_contrat
- [ ] :question:
:wrench: Fait dans le script, à faire dans les programmes grade
:::
---
## Prendre en compte la nouvelle norme dans le script
:::warning
:memo:
Cf. note récapitulant les changements de la norme par rapport à N-1 (ajout de variables, ajout de modalités, modification karnaugh, ...).
:heavy_check_mark:
:::
---
## Compléter le filtre privé / public si besoin
:::warning
:memo:
- [ ] Filtre dans l'IHM DIane pour séparer les campagnes public / privé
- [ ] variable champ_public dans le script
- [ ] dans les programmes gestion grade ??
:soon:
:::
---
## Prendre en compte les changements côté privé
:::warning
:memo:
Au fil de l'eau (voir avec Joan). Comparaison script privé avant/après (:bulb: Notepad ++, module `Compare`)
:::
---
## Faire un programme de contrôle des fichiers "période"
:::warning
:memo:
A mettre en place en repartant des programmes de Joan :
* `Micro-contrôles en direct_DIANE.sql`
* `Micro-contrôles en direct_DIANE_distrib.sql`
* Spécificités FP :
* indicatrice estimation des heures
* indicatrice nb DSN sans heures renseignées
* indicatrices net / bruts / bascsg non renseigné
* redressement heures supp ?
* indicatrice grade
* indicatrice pcsgrade
* :question:
* :question: Ajouts à partir de la visionneuse FP ? (des choses en périodes ?)
:::
:::warning
:memo:
A mettre en place : contrôle ventilation
A minima :
* vérifier égalité des masses entre précalculs et conversion
* regarder la répartition des masses entre les années pour les principales variables
* :question: répartition des périodes en fonction du remplissage net / bruts / bascsg
:::
Table pré-calculs
:::spoiler
```sql
/*************************************************************/
/* Masses de rémunérations */
/*************************************************************/
-- Référence = Pré-calculs
select sum(coalesce(montant_rem001,0)) as somme_avant_rem001
, sum(coalesce(montant_rem002,0)) as somme_avant_rem002
, sum(coalesce(montant_rem003,0)) as somme_avant_rem003
, sum(coalesce(montant_rem010,0)) as somme_avant_rem010
, sum(coalesce(montant_rem012,0)) as somme_avant_rem012
, sum(coalesce(montant_rem014,0)) as somme_avant_rem014
, sum(coalesce(montant_rem017,0)) as somme_avant_rem017
, sum(coalesce(montant_rem018,0)) as somme_avant_rem018
, sum(coalesce(montant_rem019,0)) as somme_avant_rem019
, sum(coalesce(montant_vfv,0)) as somme_avant_vfv
, sum(coalesce(montant_vpe,0)) as somme_avant_vpe
, sum(coalesce(montant_bas02,0)) as somme_avant_bas02
, sum(coalesce(montant_bas03,0)) as somme_avant_bas03
, sum(coalesce(montant_bas04,0)) as somme_avant_bas04
, sum(coalesce(montant_bas10,0)) as somme_avant_bas10
, sum(coalesce(montant_bas28,0)) as somme_avant_bas28
, sum(coalesce(montant_bas46,0)) as somme_avant_bas46
, sum(coalesce(montant_bas48,0)) as somme_avant_bas48
, sum(coalesce(montant_bas49,0)) as somme_avant_bas49
, sum(coalesce(montant_pri011,0)) as somme_avant_pri011
, sum(coalesce(montant_pri017,0)) as somme_avant_pri017
, sum(coalesce(montant_pri034,0)) as somme_avant_pri034
, sum(coalesce(montant_pri040,0)) as somme_avant_pri040
, sum(coalesce(montant_pri041,0)) as somme_avant_pri041
, sum(coalesce(montant_pri043,0)) as somme_avant_pri043
, sum(coalesce(montant_pri020,0)) as somme_avant_pri020
, sum(coalesce(montant_pri026,0)) as somme_avant_pri026
, sum(coalesce(montant_pri027,0)) as somme_avant_pri027
, sum(coalesce(montant_pri903,0)) as somme_avant_pri903
, sum(coalesce(montant_arv03,0)) as somme_avant_arv03
, sum(coalesce(montant_arv17,0)) as somme_avant_arv17
, sum(coalesce(montant_arv18,0)) as somme_avant_arv18
, sum(coalesce(montant_arv31,0)) as somme_avant_arv31
, sum(coalesce(montant_arv11,0)) as somme_avant_arv11
, sum(coalesce(montant_arv92,0)) as somme_avant_arv92
, sum(coalesce(montant_arv93,0)) as somme_avant_arv93
from (
select employeur_sortie, personne_sortie
, montant_u * (typrem_u = 'REM001')::int as montant_rem001
, montant_u * (typrem_u = 'REM002')::int as montant_rem002
, montant_u * (typrem_u = 'REM003')::int as montant_rem003
, montant_u * (typrem_u = 'REM010')::int as montant_rem010
, montant_u * (typrem_u = 'REM012')::int as montant_rem012
, montant_u * (typrem_u = 'REM014')::int as montant_rem014
, montant_u * (typrem_u = 'REM017')::int as montant_rem017
, montant_u * (typrem_u = 'REM018')::int as montant_rem018
, montant_u * (typrem_u = 'REM019')::int as montant_rem019
, montant_u * (typrem_u = 'BAS04')::int * (ind = 1)::int as montant_bas04
, montant_u * (typrem_u = 'BAS10')::int * (ind = 1)::int as montant_bas10
, montant_u * (typrem_u = 'BAS03')::int * (ind = 1)::int as montant_bas03
, montant_u * (typrem_u = 'BAS02')::int * (ind = 1)::int as montant_bas02
, montant_u * (typrem_u = 'BAS28')::int * (ind = 1)::int as montant_bas28
, montant_u * (typrem_u = 'BAS46')::int * (ind = 1)::int as montant_bas46
, montant_u * (typrem_u = 'BAS48')::int * (ind = 1)::int as montant_bas48
, montant_u * (typrem_u = 'BAS49')::int * (ind = 1)::int as montant_bas49
, montant_u * (typrem_u = 'VFV')::int * (ind = 1)::int as montant_vfv
, montant_u * (typrem_u = 'VPE')::int * (ind = 1)::int as montant_vpe
, montant_u * (typrem_u = 'PRI011')::int as montant_pri011
, montant_u * (typrem_u = 'PRI017')::int as montant_pri017
, montant_u * (typrem_u = 'PRI034')::int as montant_pri034
, montant_u * (typrem_u = 'PRI040')::int as montant_pri040
, montant_u * (typrem_u = 'PRI041')::int as montant_pri041
, montant_u * (typrem_u = 'PRI043')::int as montant_pri043
, montant_u * (typrem_u = 'PRI020')::int as montant_pri020
, montant_u * (typrem_u = 'PRI026')::int as montant_pri026
, montant_u * (typrem_u = 'PRI027')::int as montant_pri027
, montant_u * (typrem_u = 'PRI903')::int as montant_pri903
, montant_u * (typrem_u = 'ARV03')::int * (ind = 1)::int as montant_arv03
, montant_u * (typrem_u = 'ARV17')::int * (ind = 1)::int as montant_arv17
, montant_u * (typrem_u = 'ARV18')::int * (ind = 1)::int as montant_arv18
, montant_u * (typrem_u = 'ARV31')::int * (ind = 1)::int as montant_arv31
, montant_u * (typrem_u = 'ARV11')::int * (ind = 1)::int as montant_arv11
, montant_u * (typrem_u = 'ARV92')::int * (ind = 1)::int as montant_arv92
, montant_u * (typrem_u = 'ARV93')::int * (ind = 1)::int as montant_arv93
from (
select employeur_sortie, personne_sortie, unnest(typrem) as typrem_u, unnest(montant)as montant_u, ind
from (
select *, row_number() over(partition by employeur_sortie, substring(grprem::TEXT from 'VPE[0-9]*'), srcrem, personne_sortie, fichier_source) as ind
from diane_eyiq15.c_e0_pre_calcul_data
) a0
)a
) b
;
```
:::
\
Table conversion
:::spoiler
```sql
--ventilation APRES_VFV (conversion) : c13
select substr(date_debut_periode_ref::text,1,4) as annee_rem,
sum(coalesce(montant_nbi_point_periode,0)) as fp_montant_nbi,
sum(coalesce(fp_nbheur_redr,0)) as fp_nbheur_redr,
-- sum(coalesce(fp_alexis_nbheur_redr,0)) as fp_alexis_nbheur_redr,
sum(coalesce(fp_heures_estimees_periode,0)) as fp_heures_estim,
sum(coalesce(fp_alexis_heures_estimees_periode,0)) as fp_alexis_heures_estim,
sum(coalesce(mesureactv_present_rem_periode,0)) as h_mesureactv,
sum(coalesce(mesureactv_present_heure_periode,0)) as h_present_heure,
sum(coalesce(mesureactv_present_jour_periode,0)) as h_present_jour,
sum(coalesce(mesureactv_arret_periode,0)) as h_arret,
sum(coalesce(nbhrsup_rem011,0)) as nbhrsup_rem011,
sum(coalesce(nbhrsup_rem014,0)) as nbhrsup_rem014,
sum(coalesce(nbhrsup_rem017,0)) as nbhrsup_rem017,
sum(coalesce(nbhrsup_rem018,0)) as nbhrsup_rem018,
sum(coalesce(nbh_rem019,0)) as nbhrsup_rem019,
sum(montant_rem001) as rem001,
sum(montant_rem002) as rem002 ,
sum(montant_rem003) as rem003,
sum(montant_rem010) as rem010,
sum(montant_rem012) as rem012,
sum(montant_rem014) as rem014,
sum(montant_rem017) as rem017,
sum(montant_rem018) as rem018,
sum(montant_rem019) as rem019,
sum(montant_vfv) as vfv,
sum(montant_vpe) as vpe,
sum(montant_bas02) as bas02,
sum(montant_bas03) as bas03,
sum(montant_bas04) as bas04,
sum(montant_bas10) as bas10,
sum(montant_bas28) as bas28,
sum(montant_bas46) as bas46,
sum(montant_bas48) as bas48,
sum(montant_bas49) as bas49,
sum(montant_pri011) as pri011 ,
sum(montant_pri017) as pri017 ,
sum(montant_pri034) as pri034 ,
sum(montant_pri040) as pri040 ,
sum(montant_pri041) as pri041 ,
sum(montant_pri043) as pri043 ,
sum(montant_pri020) as pri020 ,
sum(montant_pri026) as pri026,
sum(montant_pri027) as pri027 ,
sum(montant_pri903) as pri903 ,
sum(montant_arv03) as arv03 ,
sum(montant_arv17) as arv17 ,
sum(montant_arv18) as arv18 ,
sum(montant_arv31) as arv31 ,
sum(montant_arv11) as arv11 /*,
sum(montant_arv92) as arv92 ,
sum(montant_arv93) as arv93 */
from diane_eyiq15.c14_e0_convertir_donnees_data
group by substr(date_debut_periode_ref::text,1,4)
;
```
:::
\
Comparaison pré-calculs vs Conversion (si masses totales différentes)
:::spoiler
```sql
/*************************************************************/
/* Comparaison poste par poste (Précalculs vs Conversion) */
/*************************************************************/
drop table if exists compar_ouvr ;
create temporary table compar_ouvr as
select *
from (
select employeur_sortie_periode, personne_sortie_periode
,sum(coalesce(montant_rem001,0)) as somme_apres_rem001
, sum(coalesce(montant_rem002,0)) as somme_apres_rem002
, sum(coalesce(montant_rem003,0)) as somme_apres_rem003
, sum(coalesce(montant_rem010,0)) as somme_apres_rem010
, sum(coalesce(montant_rem019,0)) as somme_apres_rem019
, sum(coalesce(montant_vfv,0)) as somme_apres_vfv
, sum(coalesce(montant_vpe,0)) as somme_apres_vpe
, sum(coalesce(montant_bas02,0)) as somme_apres_bas02
, sum(coalesce(montant_bas03,0)) as somme_apres_bas03
, sum(coalesce(montant_bas04,0)) as somme_apres_bas04
, sum(coalesce(montant_bas10,0)) as somme_apres_bas10
, sum(coalesce(montant_bas28,0)) as somme_apres_bas28
, sum(coalesce(montant_pri011,0)) as somme_apres_pri011
, sum(coalesce(montant_pri017,0)) as somme_apres_pri017
, sum(coalesce(montant_pri034,0)) as somme_apres_pri034
, sum(coalesce(montant_pri043,0)) as somme_apres_pri043
, sum(coalesce(montant_pri020,0)) as somme_apres_pri020
, sum(coalesce(montant_pri026,0)) as somme_apres_pri026
, sum(coalesce(montant_pri027,0)) as somme_apres_pri027
, sum(coalesce(montant_pri903,0)) as somme_apres_pri903
, sum(coalesce(montant_arv03,0)) as somme_apres_arv03
, sum(coalesce(montant_arv17,0)) as somme_apres_arv17
, sum(coalesce(montant_arv18,0)) as somme_apres_arv18
, sum(coalesce(montant_arv31,0)) as somme_apres_arv31
from diane_eyiq15.c33_e0_convertir_donnees_data
group by employeur_sortie_periode, personne_sortie_periode
) taba
left join (
select employeur_sortie, personne_sortie
, sum(coalesce(montant_rem001,0)) as somme_avant_rem001
, sum(coalesce(montant_rem002,0)) as somme_avant_rem002
, sum(coalesce(montant_rem003,0)) as somme_avant_rem003
, sum(coalesce(montant_rem010,0)) as somme_avant_rem010
, sum(coalesce(montant_rem019,0)) as somme_avant_rem019
, sum(coalesce(montant_vfv,0)) as somme_avant_vfv
, sum(coalesce(montant_vpe,0)) as somme_avant_vpe
, sum(coalesce(montant_bas02,0)) as somme_avant_bas02
, sum(coalesce(montant_bas03,0)) as somme_avant_bas03
, sum(coalesce(montant_bas04,0)) as somme_avant_bas04
, sum(coalesce(montant_bas10,0)) as somme_avant_bas10
, sum(coalesce(montant_bas28,0)) as somme_avant_bas28
, sum(coalesce(montant_pri011,0)) as somme_avant_pri011
, sum(coalesce(montant_pri017,0)) as somme_avant_pri017
, sum(coalesce(montant_pri034,0)) as somme_avant_pri034
, sum(coalesce(montant_pri043,0)) as somme_avant_pri043
, sum(coalesce(montant_pri020,0)) as somme_avant_pri020
, sum(coalesce(montant_pri026,0)) as somme_avant_pri026
, sum(coalesce(montant_pri027,0)) as somme_avant_pri027
, sum(coalesce(montant_pri903,0)) as somme_avant_pri903
, sum(coalesce(montant_arv03,0)) as somme_avant_arv03
, sum(coalesce(montant_arv17,0)) as somme_avant_arv17
, sum(coalesce(montant_arv18,0)) as somme_avant_arv18
, sum(coalesce(montant_arv31,0)) as somme_avant_arv31
from (
select employeur_sortie, personne_sortie
, montant_u * (typrem_u = 'REM001')::int as montant_rem001
, montant_u * (typrem_u = 'REM002')::int as montant_rem002
, montant_u * (typrem_u = 'REM003')::int as montant_rem003
, montant_u * (typrem_u = 'REM010')::int as montant_rem010
, montant_u * (typrem_u = 'REM019')::int as montant_rem019
, montant_u * (typrem_u = 'BAS04')::int * (ind = 1)::int as montant_bas04
, montant_u * (typrem_u = 'BAS10')::int * (ind = 1)::int as montant_bas10
, montant_u * (typrem_u = 'BAS03')::int * (ind = 1)::int as montant_bas03
, montant_u * (typrem_u = 'BAS02')::int * (ind = 1)::int as montant_bas02
, montant_u * (typrem_u = 'BAS28')::int * (ind = 1)::int as montant_bas28
, montant_u * (typrem_u = 'VFV')::int * (ind = 1)::int as montant_vfv
, montant_u * (typrem_u = 'VPE')::int * (ind = 1)::int as montant_vpe
, montant_u * (typrem_u = 'PRI011')::int as montant_pri011
, montant_u * (typrem_u = 'PRI017')::int as montant_pri017
, montant_u * (typrem_u = 'PRI034')::int as montant_pri034
, montant_u * (typrem_u = 'PRI043')::int as montant_pri043
, montant_u * (typrem_u = 'PRI020')::int as montant_pri020
, montant_u * (typrem_u = 'PRI026')::int as montant_pri026
, montant_u * (typrem_u = 'PRI027')::int as montant_pri027
, montant_u * (typrem_u = 'PRI903')::int as montant_pri903
, montant_u * (typrem_u = 'ARV03')::int * (ind = 1)::int as montant_arv03
, montant_u * (typrem_u = 'ARV17')::int * (ind = 1)::int as montant_arv17
, montant_u * (typrem_u = 'ARV18')::int * (ind = 1)::int as montant_arv18
, montant_u * (typrem_u = 'ARV31')::int * (ind = 1)::int as montant_arv31
from (
select employeur_sortie, personne_sortie, unnest(typrem) as typrem_u, unnest(montant)as montant_u, ind
from (
select *, row_number() over(partition by employeur_sortie, substring(grprem::TEXT from 'VPE[0-9]*'), srcrem, personne_sortie, fichier_source) as ind
from diane_eyiq15.c33_e0_pre_calcul_data
) a0
)a
) b
group by employeur_sortie, personne_sortie
) tabb
on taba.employeur_sortie_periode = tabb.employeur_sortie
and taba.personne_sortie_periode = tabb.personne_sortie
;
select * from compar_ouvr where abs(somme_apres_rem001 - somme_avant_rem001)>1
```
:::
\
Comparaison poste par poste (entre 2 campagnes) en tenant compte de l'ANNEE
:::spoiler
```sql=
/* Comparaison poste par poste (entre 2 campagnes) en tenant compte de l'ANNEE */
drop table if exists compar_camp13_an ;
create temporary table compar_camp13_an as
select *
from (
select substr(date_debut_periode_ref::text,1,4) as apres_annee_rem,
employeur_sortie_periode, personne_sortie_periode,
sum(coalesce(fp_nbheur_redr,0)) as fp_apres_nbheur_redr,
/* sum(coalesce(fp_alexis_nbheur_redr,0)) as fp_alexis_apres_nbheur_redr,*/
sum(coalesce(fp_heures_estimees_periode,0)) as fp_apres_heures_estim
/* sum(coalesce(fp_alexis_heures_estimees_periode,0)) as fp_alexis_apres_heures_estim */
, sum(coalesce(mesureactv_present_rem_periode,0)) as somme_apres_mesureactv
, sum(coalesce(montant_rem001,0)) as somme_apres_rem001
, sum(coalesce(montant_rem002,0)) as somme_apres_rem002
, sum(coalesce(montant_rem003,0)) as somme_apres_rem003
, sum(coalesce(montant_rem019,0)) as somme_apres_rem019
, sum(coalesce(montant_rem018,0)) as somme_apres_rem018
, sum(coalesce(montant_rem017,0)) as somme_apres_rem017
, sum(coalesce(nbhrsup_rem017,0)) as apres_nbhrsup_rem017
, sum(coalesce(montant_vfv,0)) as somme_apres_vfv
, sum(coalesce(montant_vpe,0)) as somme_apres_vpe
, sum(coalesce(montant_bas02,0)) as somme_apres_bas02
, sum(coalesce(montant_bas03,0)) as somme_apres_bas03
, sum(coalesce(montant_bas04,0)) as somme_apres_bas04
from diane_eyiq15.c13_e0_convertir_donnees_data
group by employeur_sortie_periode, personne_sortie_periode, substr(date_debut_periode_ref::text,1,4)
) taba
left join
(
select substr(date_debut_periode_ref::text,1,4) as avant_annee_rem,
employeur_sortie_periode as employeur_sortie , personne_sortie_periode as personne_sortie,
sum(coalesce(fp_nbheur_redr,0)) as fp_avant_nbheur_redr,
/* sum(coalesce(fp_alexis_nbheur_redr,0)) as fp_alexis_avant_nbheur_redr,*/
sum(coalesce(fp_heures_estimees_periode,0)) as fp_avant_heures_estim,
/* sum(coalesce(fp_alexis_heures_estimees_periode,0)) as fp_alexis_avant_heures_estim, */
sum(coalesce(mesureactv_present_rem_periode,0)) as somme_avant_mesureactv
, sum(coalesce(montant_rem001,0)) as somme_avant_rem001
, sum(coalesce(montant_rem002,0)) as somme_avant_rem002
, sum(coalesce(montant_rem003,0)) as somme_avant_rem003
, sum(coalesce(montant_rem019,0)) as somme_avant_rem019
, sum(coalesce(montant_rem018,0)) as somme_avant_rem018
, sum(coalesce(montant_rem017,0)) as somme_avant_rem017
, sum(coalesce(nbhrsup_rem017,0)) as avant_nbhrsup_rem017
, sum(coalesce(montant_vfv,0)) as somme_avant_vfv
, sum(coalesce(montant_vpe,0)) as somme_avant_vpe
, sum(coalesce(montant_bas02,0)) as somme_avant_bas02
, sum(coalesce(montant_bas03,0)) as somme_avant_bas03
, sum(coalesce(montant_bas04,0)) as somme_avant_bas04
from diane_eyiq15.c18_e0_convertir_donnees_data a
/* where exists (select 1 from diane_eyiq15.c33_e0_convertir_donnees_data b where a.personne_sortie_periode=b.personne_sortie_periode and a.employeur_sortie_periode=b.employeur_sortie_periode)*/
group by employeur_sortie_periode, personne_sortie_periode, substr(date_debut_periode_ref::text,1,4)
) tabb
on taba.employeur_sortie_periode = tabb.employeur_sortie
and taba.personne_sortie_periode = tabb.personne_sortie
and taba.apres_annee_rem = tabb.avant_annee_rem
;
select * from compar_camp13_an where apres_annee_rem='2020' and abs(somme_apres_bas04 - somme_avant_bas04) > 1 ;
select * from compar_camp33_an where apres_annee_rem='2020' and abs(fp_apres_nbheur_redr - fp_avant_nbheur_redr) > 1 and fp_apres_heures_estim =fp_avant_heures_estim
```
:::
\
Exemples pour comprendre la déclaration, la ventilation, l'agrégation
```sql
/*************************************************************/
/* Exemples pour comprendre */
/*************************************************************/
-- Déclaration : contrat :
select personne_sortie,employeur_sortie, fichier_source, id_row, srcrem, unnest(typrem) as typrem_u,
unnest(montant)as montant_u, unnest(date_debut_rem) as debut_rem, unnest(date_fin_rem) as fin_rem, unnest(mesureactv) as mesureactv_uf, unnest(nbhrsup) as nbhrsup , txtempspartiel_fp, quotite, quotite_ref, nat_poste_fp, nbi_fp
from diane_eyiq15.c13_e0_pre_calcul_data
where personne_sortie='1720864102025'
order by srcrem, id_row,debut_rem ,typrem_u
;
-- Conversion :
select personne_sortie_periode as personne_sortie,
employeur_sortie_periode as employeur_sortie,
date_debut_periode_ref::text,
date_fin_periode_ref::text,
mesureactv_present_rem_periode,
montant_rem002 , montant_rem001,montant_rem003,montant_rem010,montant_rem012,montant_rem017,montant_rem018,montant_vfv,montant_vpe,montant_bas02,montant_bas03,montant_bas04,
montant_bas10,montant_bas28,montant_bas29,
montant_pri011, montant_pri027, montant_pri034, montant_pri028,
montant_arv03 , montant_arv07, montant_arv09,montant_arv17 ,montant_arv18,montant_arv11, montant_arv31,montant_pri027,
mesureactv_present_heure_periode, mesureactv_present_jour_periode,mesureactv_arret_periode, nbhrsup_rem017, nbhrsup_rem017_r, fp_quo_siasp_periode, fp_heures_estimees_periode, fp_nbheur_redr, quotite_periode, quotite_ref_periode, ind_heures_non_decl_periode/*, unit_non_conv_periode*/
from diane_eyiq15.c9_e0_convertir_donnees_data
where personne_sortie_periode ='1720864102025'
order by date_debut_periode_ref;
```
---
---
---
# :information_source: Quelques codes SQL pour manipulation dans la BDD
:::info
**A lancer avant de travailler sur un clone**
:::
```sql
set enable_mergejoin=off; set enable_material=off; set enable_seqscan=off; set enable_nestloop=off; set enable_bitmapscan=on;
set work_mem="16MB";
set maintenance_work_mem="16MB";
SELECT timeofday() into debut;
```
:::info
**Savoir quelle requête est en train de tourner en temps réel**
:::
```sql
SELECT usename, query, query_start, state_change, wait_event_type, wait_event
FROM get_sa()
-- WHERE usename = 'user_artmida' */
;
```
:::info
**Longueur d'un vecteur**
:::
```sql
SELECT typrem, tx_remun_fp
, array_length(typrem,1) AS lg1
, array_length(tx_remun_fp,1) AS lg2
FROM diane_eyiq15.c124_e22_pre_calcul_data
WHERE array_length(typrem,1)= array_length(tx_remun_fp,1)
;
```
:::info
**Modifier des valeurs dans une table**
:::
```sql
-- On ne met à jour que les éléments dont la CJ est NULL. Attention clé de jointure (siret X validite)
UPDATE diane_eyiq15.c132_e0_ouvrir_campagne_data foo
SET cj = bar.categoriejuridiqueunitelegale
FROM public.apisirene_c1_data bar
WHERE foo.cj IS NULL AND foo.employeur_sortie = bar.siret AND foo.validite::text = bar.validite::text
;
```
:::info
**Diane : taille des schémas et des tables**
:::
:::spoiler
```sql
SELECT pg_size_pretty(pg_database_size('ri_pg_diane_qf03'));
-- Par schéma :
SELECT schemaname
, sum(size_brute) as size_brute
, pg_size_pretty(sum(size_brute)) as size
FROM
(
SELECT schemaname, relname
, pg_total_relation_size(schemaname||'.'||relname) as size_brute
FROM pg_stat_user_tables
where (schemaname ~ '^diane' or schemaname in ('heritage', 'public'))
and not relname ~ '^sauv'
) foo
group by schemaname
order by size_brute desc
;
-- Analyse du schéma heritage par table
SELECT table_maitre
, pg_size_pretty(sum(size_brute)) AS size
, sum(size_brute) as size_brute
FROM
(
SELECT relname
, n_live_tup
, pg_total_relation_size(schemaname||'.'||relname) AS size_brute
, CASE WHEN schemaname = 'heritage' THEN regexp_replace(relname, '\$[0-9]{4}$', '') ELSE relname END AS table_maitre
FROM pg_stat_user_tables
where schemaname = 'heritage'
) foo
GROUP BY table_maitre
ORDER BY size_brute DESC
;
-- analyse par campagne du schéma heritage
SELECT campagne
, pg_size_pretty(sum(size_brute)) AS size
, sum(size_brute) as size_brute
FROM
(
SELECT relname
, n_live_tup
, pg_total_relation_size(schemaname||'.'||relname) AS size_brute
, regexp_replace(relname, '_.*', '') AS campagne
FROM pg_stat_user_tables
where schemaname = 'heritage'
) foo
GROUP BY campagne
ORDER BY size_brute DESC
;
-- Nombre de ligne de la campagne c13
SELECT table_maitre
, pg_size_pretty(sum(size_brute)) AS size
, sum(size_brute) as size_brute
, sum(n_live_tup) as n_live_tup
FROM
(
SELECT relname
, n_live_tup
, pg_total_relation_size(schemaname||'.'||relname) AS size_brute
, CASE WHEN schemaname = 'heritage' THEN regexp_replace(relname, '\$[0-9]{4}$', '') ELSE relname END AS table_maitre
FROM pg_stat_user_tables
where schemaname = 'heritage' and relname ~'c13'
) foo
GROUP BY table_maitre
ORDER BY size_brute DESC
;
```
:::
\
:::info
**Créer un échantillon dans Diane "bouchonné"**
:::
Il faut que la campagne ait déjà été ouverte, pour que les tables de gestion de la campagne soient à peu près à jour.
Faire un retour arrière sur pré-calculs.
Ecraser la table d'ouverture de campagne :
```sql
DROP TABLE IF EXISTS diane_eyiq15.c13_e0_ouvrir_campagne_data CASCADE;
CREATE TABLE diane_eyiq15.c13_e0_ouvrir_campagne_data AS
SELECT * FROM diane_eyiq15.c9_e0_ouvrir_campagne_data;
--where personne_sortie in ('1530575017083') ;
```
:::info
**Vérifier / corriger le "id_hash"**
:::
En cas de "bouchonnage", le nombre d'id_hash (nombre de partitions) est souvent trop élevé par rapport au nombre de lignes de la table ; cela peut créer des lenteurs importantes du moteur générique. Il faut donc reclculer l'id_hash adapté à la taille de léchantillon (1 pour un petit nombre de lignes, sinon à calculer avec la procédure ci-dessous).
**Quel est le id_hash actuel ?**
:::spoiler
```sql
SELECT
count(1) AS nb_row,
max(id_hash)
FROM diane_eyiq15.c13_e0_pre_calcul_data ;
```
:::
\
**Quel id_hash souhaitable ?**
:::spoiler
```sql
WITH tmp_count AS (
SELECT (count(*)*10000) + 1 AS n
FROM diane_eyiq15.c9_e0_ouvrir_campagne_data tablesample system(0.01)
)
SELECT CASE WHEN log(n) <= 5 THEN 2
WHEN log(n) <= 6 THEN 8
WHEN log(n) <= 7 THEN 64
ELSE 1024
END AS n
FROM tmp_count
;
```
:::
\
**Forcer le id_hash à 1**
:::spoiler
```sql
DROP TABLE IF EXISTS public.eyiq15;
CREATE TABLE public.eyiq15
AS
SELECT * FROM diane_eyiq15.c38_e0_ouvrir_campagne_data
;
-- DROP id_hash
ALTER TABLE public.eyiq15 DROP COLUMN id_hash;
DROP TABLE IF EXISTS public.eyiq15_propre;
CREATE TABLE public.eyiq15_propre
AS
SELECT *
, 1 AS id_hash
FROM public.eyiq15
;
DROP TABLE IF EXISTS diane_eyiq15.c38_e0_ouvrir_campagne_data CASCADE;
CREATE TABLE diane_eyiq15.c38_e0_ouvrir_campagne_data
AS
SELECT * FROM public.eyiq15_propre
;
```
:::
\
**Forcer le id_hash à ? (ici : 8)** (:bulb:si id_hash théorique >64, forcer à 64, sinon c'est trop long)
:::spoiler
```sql
DROP TABLE IF EXISTS public.eyiq15;
CREATE TABLE public.eyiq15
AS
SELECT * FROM diane_eyiq15.c38_e0_ouvrir_campagne_data
;
-- DROP id_hash
ALTER TABLE public.eyiq15 DROP COLUMN id_hash;
-- Recalcul de id_hash
DROP TABLE IF EXISTS public.eyiq15_propre;
CREATE TABLE public.eyiq15_propre
AS
SELECT *
, abs(hashtext(coalesce(employeur_sortie,'')))% 8 + 1 AS id_hash
FROM public.eyiq15
;
-- Remplacement de l'ancienne table avec les bonnes données
DROP TABLE IF EXISTS diane_eyiq15.c32_e0_ouvrir_campagne_data CASCADE;
CREATE TABLE diane_eyiq15.c32_e0_ouvrir_campagne_data
AS
SELECT * FROM public.eyiq15_propre
;
```
:::
\
:::info
**Recherche d'un libellé**
:::
```sql
select * from diane_eyiq15.c3_e1_calculer_salaire_produit_grappe_periode
where libelle_emploi_periode like '%MOULEUR UP1%';
```
:question::question: :question:
:x:
/*************************************************************/
/* Nb lignes */
/*************************************************************/
select count (*),validite from diane_eyiq15.c8_e0_ouvrir_campagne_data group by validite ;
select count (*),validite from diane_eyiq15.c18_e0_ouvrir_campagne_data group by validite ;
select count (*),validite from diane_eyiq15.c13_e0_ouvrir_campagne_data group by validite ;
select count (*),validite from diane_eyiq15.c9_e0_pre_calcul_data group by validite ;/* */
select count (*),validite from diane_eyiq15.c18_e0_pre_calcul_data group by validite ;/* */
select count (*),validite from diane_eyiq15.c13_e0_pre_calcul_data group by validite ;/* */
select count (*) from diane_eyiq15.c9_e0_convertir_donnees_data ; /* */
select count (*) from diane_eyiq15.c18_e0_convertir_donnees_data ; /* */
select count (*) from diane_eyiq15.c33_e0_convertir_donnees_data ;
select count (*) from diane_eyiq15.c9_e0_calculer_metier_pr_cj7 ; /* */
select count (*) from diane_eyiq15.c18_e0_calculer_metier_pr_cj7 ; /* */
select count (*) from diane_eyiq15.c13_e0_calculer_metier_pr_cj7 ;
select count (*) from diane_eyiq15.c9_e0_calculer_metier_pr_inx ; /* */
select count (*) from diane_eyiq15.c18_e0_calculer_metier_pr_hv ; /* */
select count (*) from diane_eyiq15.c13_e0_calculer_metier_pr_hv ;
select * from diane_eyiq15.c13_e0_calculer_metier_pr_hv limit 10 ;
select count (*) from diane_eyiq15.c26_e0_convertir_donnees_data ; /* */
select count (*) from diane_eyiq15.c32_e0_convertir_donnees_data ; /* */
select count (*) from diane_eyiq15.c33_e0_convertir_donnees_data ;
/*************************************************************/
/* Nb periodes par années */
/*************************************************************/
-- périodes agrégées
select substr(date_fin_periode_ref::text,1,4), count(*)
from diane_eyiq15.c9_e0_convertir_donnees_data
group by substr(date_fin_periode_ref::text,1,4)
;
select substr(date_fin_periode_ref::text,1,4), count(*)
from diane_eyiq15.c18_e0_convertir_donnees_data
group by substr(date_fin_periode_ref::text,1,4)
;
select substr(date_fin_periode_ref::text,1,4), count(*)
from diane_eyiq15.c13_e0_convertir_donnees_data
group by substr(date_fin_periode_ref::text,1,4)
;
/* Nb périodes*/
select count(*) as nb_periodes
from diane_eyiq15.c14_e0_calculer_metier_pr_cj7
;
/* Nb Nir*/
select count(distinct nir) as nb_nir from diane_eyiq15.c9_e0_calculer_metier_pr_cj7;
select count(distinct nir) as nb_nir from diane_eyiq15.c18_e0_calculer_metier_pr_cj7;
select count(distinct nir) as nb_nir from diane_eyiq15.c13_e0_calculer_metier_pr_cj7;
/* Nb postes*/
select count(distinct siret_affectation||nir) as nb_postes from diane_eyiq15.c8_e0_calculer_metier_pr_cj7
;
/* Nb postes par versant*/
select count(distinct siret_affectation||nir) as nb_postes ,versant_fp
from diane_eyiq15.c13_e0_calculer_metier_pr_cj7
GROUP BY versant_fp ;
/* Nb postes par versant et statut_contrat*/
select count(distinct siret_affectation||nir) as nb_postes,versant_fp,statut_contrat_fp
from diane_eyiq15.c13_e0_calculer_metier_pr_cj7
GROUP BY versant_fp,statut_contrat_fp ;
/* Equations utilisées dans le Karnaugh */
select /*versant_fp,*/ statut_contrat_fp, pop_part_pub, ind_fonc, num_k_periode, eqsalbrut, eqsalnet,count(*), 'c9' as campagne
FROM diane_eyiq15.c9_e0_calculer_metier_pr_cj7
group by /* versant_fp,*/ind_fonc, statut_contrat_fp, pop_part_pub, num_k_periode, eqsalbrut, eqsalnet
union
select /* versant_fp, */statut_contrat_fp, pop_part_pub, ind_fonc, num_k_periode, eqsalbrut, eqsalnet,count(*), 'c18' as campagne
FROM diane_eyiq15.c18_e0_calculer_metier_pr_cj7
group by /*versant_fp,*/ind_fonc, statut_contrat_fp, pop_part_pub, num_k_periode, eqsalbrut, eqsalnet
union
select /*versant_fp, */statut_contrat_fp, pop_part_pub, ind_fonc, num_k_periode, eqsalbrut, eqsalnet,count(*),'c13' as campagne
FROM diane_eyiq15.c13_e0_calculer_metier_pr_cj7
group by /* versant_fp,*/ind_fonc, statut_contrat_fp, pop_part_pub, num_k_periode, eqsalbrut, eqsalnet
;
select /*versant_fp, */ num_k_periode, eqsalbrut, eqsalnet,count(*)
FROM diane_eyiq15.c8_e0_calculer_metier_pr_cj7
group by versant_fp, num_k_periode, eqsalbrut, eqsalnet
order by versant_fp, num_k_periode, eqsalbrut, eqsalnet
;
/* Salaires */
select versant_fp,
sum (s_brut) as sum_sbrut,
sum (s_net) as sum_snet,
sum (bascsg) as sum_csg,
sum (brut_s) as sum_deplaf,
sum (net_d) as sum_netd
from diane_eyiq15.c13_e0_calculer_metier_pr_cj7
group by versant_fp
/* Nb postes par versant et statut_contrat*/
drop table if exists postes_c13 ;
create table postes_c13 as
select siret_affectation||nir as poste,
nir, motif_fin_per_fp,
max(versant_fp) as versant,
sum (sal_chom) as sum_rem002,
sum (montant_rem010) as sum_rem010,
sum (base_ircantec) as sum_ircantec,
sum (s_brut) as sum_sbrut,
sum (s_net) as sum_snet,
sum (bascsg) as sum_csg,
sum (brut_s) as sum_deplaf,
sum (net_d) as sum_netd,
sum (net_verse) as sum_netverse,
max(statut_contrat_fp) as statut_contrat,
max(pop_part_pub) as pop_part_pub
from diane_eyiq15.c13_e0_calculer_metier_pr_cj7
GROUP BY siret_affectation,nir, motif_fin_per_fp ;
select * from postes limit 1
select
avg(sum_csg) as avg_csg,
PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY sum_csg ASC) as pct05_csg,
PERCENTILE_CONT(0.10) WITHIN GROUP (ORDER BY sum_csg ASC) as pct10_csg,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY sum_csg ASC) as pct25_csg,
PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY sum_csg ASC) as pct50_csg,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY sum_csg ASC) as pct75_csg,
PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY sum_csg ASC) as pct90_csg,
PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY sum_csg ASC) as pct95_csg
from postes_c13 ;
drop table if exists fusion;
create table fusion as
select *,
case when poste is null then 'c9'
when poste_c9 is null then 'c13'
else 'les2' end as presence
from postes_c13 as c13
full join
(select nir as nir_c9, motif_fin_per_fp as motiffin_c9, poste as poste_c9,
versant as versant_c9,
sum_sbrut as sum_sbrut_c9,
sum_snet as sum_snet_c9,
sum_csg as sum_csg_c9,
sum_deplaf as sum_deplaf_c9,
sum_netd as sum_netd_c9,
statut_contrat as statut_contrat_c9,
pop_part_pub as pop_part_pub_c9
from postes_c9) as c9
on c9.poste_c9=c13.poste;
select count(*),presence
from fusion
group by presence;
/* Salaires */
select versant,
sum (sum_sbrut) as sum_sbrut,
sum (sum_snet) as sum_snet,
sum (sum_csg) as sum_csg,
sum (sum_deplaf) as sum_deplaf,
sum (sum_netd) as sum_netd
from fusion
where presence='c13'
group by versant;
select
avg(sum_csg_c8) as avg_csg,
PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY sum_csg_c8 ASC) as pct05_csg,
PERCENTILE_CONT(0.10) WITHIN GROUP (ORDER BY sum_csg_c8 ASC) as pct10_csg,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY sum_csg_c8 ASC) as pct25_csg,
PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY sum_csg_c8 ASC) as pct50_csg,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY sum_csg_c8 ASC) as pct75_csg,
PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY sum_csg_c8 ASC) as pct90_csg,
PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY sum_csg_c8 ASC) as pct95_csg
from fusion
where presence='les2';
select * from fusion
where presence='les2' and sum_csg_c9=0 and sum_netd_c9>0 and sum_deplaf_c9=0 and versant='FPT';
Sign in with Wallet
Connect another wallet