data_collection
Y
Normal
N
1000
100
-
2025/02/27 12:45:14.184
-
2025/02/27 12:45:14.184
Table input
Merge join
Y
Table input 2
Merge join
Y
Merge join
Table output
Y
Merge join
MergeJoin
Y
1
none
INNER
recruitment_id
idm_id
Table input
Table input 2
768
368
Table input
TableInput
Y
1
none
postgres.person_registry
N
0
WITH year_series AS (
SELECT generate_series(
date_trunc('year', CURRENT_DATE) - INTERVAL '54 years',
date_trunc('year', CURRENT_DATE) - INTERVAL '1 year',
INTERVAL '1 year'
)::date AS year_date
)
SELECT
COALESCE(r.target_recruitment_id, r.current_recruitment_id) as recruitment_id,
ys.year_date AS year,
COALESCE(COUNT(r.id) FILTER (
WHERE r.military_registration_date >= ys.year_date
AND r.military_registration_date < ys.year_date + INTERVAL '1 year'
), 0) AS count_all,
COALESCE(COUNT(r.id) FILTER (
WHERE r.military_registration_date >= ys.year_date
AND r.military_registration_date < ys.year_date + INTERVAL '1 year'
AND DATE_PART('year', AGE(r.military_registration_date, r.birth_date)) < 18
), 0) AS reaching_17_age,
COALESCE(COUNT(r.id) FILTER (
WHERE r.military_registration_date >= ys.year_date
AND r.military_registration_date < ys.year_date + INTERVAL '1 year'
AND r.gender = 'FEMALE'
), 0) AS women_military_specialty,
'Department' as schema
FROM year_series ys
LEFT JOIN recruits r ON 1=1
GROUP BY ys.year_date, COALESCE(r.target_recruitment_id, r.current_recruitment_id)
ORDER BY recruitment_id, year DESC;
N
544
304
Table input 2
TableInput
Y
1
none
ervu-dashboard
N
0
-- Берем только записи с максимальной версией для каждого idm_id
SELECT DISTINCT ON (idm_id) idm_id
FROM ervu_dashboard.recruitment
WHERE schema = 'Department'
ORDER BY idm_id, updated_at DESC
N
544
496
Table output
TableOutput
Y
1
none
1000
ervu-dashboard
recruitment_id
recruitment_id
recording_date
year
count_all
count_all
reaching_17_age
reaching_17_age
women_military_specialty
women_military_specialty
schema
schema
N
N
N
N
Y
N
forecast
Y
N
Y
Y
Y
960
368