как мне это условие если calckind = 1: если imresultfixudacc = 100, то true, иначе false…
- User
как мне это условие если calc_kind = 1: если imresult_fix_ud_acc = 100, то true, иначе false если calc_kind = 2: то если imresult_ud_acc = 100, то true, иначе false если calc_kind = 3: то если imresult_v_ud = 100, то true, иначе false записать в select в postgresql?
- Assistant
Вы можете использовать конструкцию
CASEв SQL для реализации такой логики в запросе. Вот пример, который показывает, как это сделать:SELECT CASE WHEN calc_kind = 1 AND imresult_fix_ud_acc = 100 THEN true WHEN calc_kind = 2 AND imresult_ud_acc = 100 THEN true WHEN calc_kind = 3 AND imresult_v_ud = 100 THEN true ELSE false END AS result FROM your_table_name;Здесь
your_table_name— это имя вашей таблицы. Этот запрос возвращает столбец под названиемresult, который будет принимать значениеtrueилиfalseв зависимости от условий, которые вы описали. - User
не понимаю что за ошибка SQL Error [42703]: ERROR: column la.imresult_fix_ud_acc does not exist Подсказка: Perhaps you meant to reference the column "la.imresult_fix_ud_acc". Позиция: 11468 Я же вроде все верно указал
- Assistant
Ошибка, с которой вы столкнулись, говорит о том, что PostgreSQL не может найти указанный вами столбец
imresult_fix_ud_acc. Это может произойти по нескольким причинам:-
Опечатка в имени столбца: Проверьте, правильно ли указано имя столбца. Возможно, вы допустили опечатку.
-
Неправильное указание таблицы: Убедитесь, что вы правильно указали имя таблицы и что столбцы, к которым вы обращаетесь, действительно существуют в данной таблице.
-
Схема: Если вы используете схему, убедитесь, что вы правильно указали ее. Например,
schema_name.table_name. -
Пробел или символы: Иногда ошибки могут возникать из-за лишних пробелов или символов. Попробуйте скопировать и вставить имена столбцов и таблицы в запрос.
Для устранения ошибки, попробуйте следующий подход:
- Проверьте, как именно называются столбцы в вашей таблице с помощью команды:
SELECT * FROM your_table_name LIMIT 1;- Если необходимо, укажите явное имя таблицы в вашем запросе:
SELECT CASE WHEN la.calc_kind = 1 AND la.imresult_fix_ud_acc = 100 THEN true WHEN la.calc_kind = 2 AND la.imresult_ud_acc = 100 THEN true WHEN la.calc_kind = 3 AND la.imresult_v_ud = 100 THEN true ELSE false END AS result FROM your_table_name AS la;Если после этих шагов ошибка не исчезла, пожалуйста, предоставьте больше информации о вашем запросе и структуре таблицы, чтобы я мог помочь более точно.
-
- User
Вот код, помоги разобраться: t_param as ( select * from ( values (3,1,'Вклад') , (2,1,'Оперативный накопленный') -- , (2,2,'Оперативный ненакопленный') -- пока не считаем , (1,1,'Фиксированный накопленный') -- , (1,2,'Фиксированный ненакопленный') -- пока не считаем ) as t(calc_kind, calc_type, calc_title) where calc_kind = coalesce(p_calc_kind, calc_kind) and calc_type = coalesce(p_calc_type, calc_type) ) , t_period as materialized ( select pk_id, parent_id, level_num, elem_value, no_data_elem, date_part, date_start, date_end, period_code, period_code2 from gp_planeta.periods p where (p.level_num = any (array[1, 2])) and elem_value = coalesce(p_period_key,elem_value) -- !!!!!!!!!!!! Параметризуем --and substr(elem_value,1,4)::int4 between 2024 and extract('year' from now()) ) , t_dates as materialized ( -- список дат - границ версий соединяемых таблиц select distinct case g.v when 1 then d2.begin_ts else d2.end_ts end as dt from ( -- сегменты смешиваемых таблиц select distinct begin_ts, end_ts from cupproject_v.image_results -- (1) Образы результата union all select distinct begin_ts, end_ts from cupproject_v.priority_projects -- (2) Проекты union all select distinct begin_ts, end_ts from cupproject_v.priority_events -- (3) Мероприятия union all select distinct begin_ts, end_ts from cupproject_v.priority_events_risk -- (4) Риски мероприятий union all select distinct begin_ts, end_ts from cupproject_v.priority_problems -- (5) Проблемы union all select distinct begin_ts, end_ts from cupproject_v.priority_kt -- (6) Контрольные точки union all select distinct begin_ts, end_ts from cupproject_v.priority_kt_fact -- (7) Факт KT union all select distinct begin_ts, end_ts from cupproject_v.v_ud_calc -- (8) УД ) d2 cross join generate_series(1,2) g(v) where d2.begin_ts < d2.end_ts -- отрезок положительный and d2.end_ts > d2.begin_ts + '1 min'::interval -- и не слишком короткий (интервалы - дни) -- and d2.end_ts > '2021-12-31' -- не смешиваем слишком древние сегменты ) -- select * from t_dates; -- проверка , t_segments as materialized ( select a.begin_ts, a.end_ts from ( -- a select lag(t_dates.dt) over (order by t_dates.dt) as begin_ts, t_dates.dt - '00:00:00.000001'::interval as end_ts from t_dates ) a where a.end_ts > a.begin_ts + '1 min'::interval and end_ts >= '2999-12-31' -- !!!!!!!!!!!!! Выбираем только актуальные строки ) -- t_segments -- select * from t_segments; , imresult_ud_pivot as materialized ( select v.period_key, v.project_id, v.entity_id as imresult_id, max(v.ud) filter (where v.ud_type = 'ud'::text) as imresult_ud, max(v.ud) filter (where v.ud_type = 'fix_ud'::text) as imresult_fix_ud, max(v.ud) filter (where v.ud_type = 'v_ud'::text) as imresult_v_ud, max(v.ud) filter (where v.ud_type = 'ud_acc'::text) as imresult_ud_acc, max(v.ud) filter (where v.ud_type = 'fix_ud_acc'::text) as imresult_fix_ud_acc, max(v.ud_color) filter (where v.ud_type = 'ud'::text) as imresult_ud_color, max(v.ud_color) filter (where v.ud_type = 'fix_ud'::text) as imresult_fix_ud_color, max(v.ud_color) filter (where v.ud_type = 'v_ud'::text) as imresult_v_ud_color, max(v.ud_color) filter (where v.ud_type = 'ud_acc'::text) as imresult_ud_acc_color, max(v.ud_color) filter (where v.ud_type = 'fix_ud_acc'::text) as imresult_fix_ud_acc_color, v.begin_ts, v.end_ts from cupproject_v.v_ud_calc v where entity_type = 2 --Тип сущности: 2-Образ результата and v.project_id = coalesce(p_project_id,v.project_id) -- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!! Параметризуем and v.calc_kind = coalesce(p_calc_kind, v.calc_kind) and v.period_key = coalesce(p_period_key, v.period_key) group by v.period_key, v.project_id, v.entity_id, v.begin_ts, v.end_ts ) -- select * from imresult_ud_pivot; , event_ud_pivot as materialized ( select v.period_key, v.project_id, v.entity_id as event_id, max(v.ud) filter (where v.ud_type = 'ud'::text) as event_ud, max(v.ud) filter (where v.ud_type = 'fix_ud'::text) as event_fix_ud, max(v.ud) filter (where v.ud_type = 'v_ud'::text) as event_v_ud, max(v.ud) filter (where v.ud_type = 'ud_acc'::text) as event_ud_acc, max(v.ud) filter (where v.ud_type = 'fix_ud_acc'::text) as event_fix_ud_acc, max(v.ud_color) filter (where v.ud_type = 'ud'::text) as event_ud_color, max(v.ud_color) filter (where v.ud_type = 'fix_ud'::text) as event_fix_ud_color, max(v.ud_color) filter (where v.ud_type = 'v_ud'::text) as event_v_ud_color, max(v.ud_color) filter (where v.ud_type = 'ud_acc'::text) as event_ud_acc_color, max(v.ud_color) filter (where v.ud_type = 'fix_ud_acc'::text) as event_fix_ud_acc_color, v.begin_ts, v.end_ts from cupproject_v.v_ud_calc v where entity_type = 7 --Тип сущности: 5-мероприятие ПРМ and v.project_id = coalesce(p_project_id,v.project_id) -- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!! Параметризуем and v.calc_kind = coalesce(p_calc_kind, v.calc_kind) and v.period_key = coalesce(p_period_key, v.period_key) group by v.period_key, v.project_id, v.entity_id, v.begin_ts, v.end_ts ) --select * from event_ud_pivot; , t_has_media as materialized ( select a.events_id --, a.oks_fake_code , (count(link) filter (where a.media_type = 'video') > 0) as has_video , (count(link) filter (where a.media_type = 'photo') > 0) as has_photo from ( -- a select distinct e.events_id, e.oks_fake_code , trim(m.media_type) as media_type , m.link from cupproject_v.v_priority_events_to_oks e join cupproject_v.fv_oks_media_for_gosprog() m on m.oks_fake_code = e.oks_fake_code ) a -- group by a.events_id --, a.oks_fake_code --order by a.events_id --, a.oks_fake_code ) --select * from t_has_media; , event_period as materialized ( select --p.elem_value as period_key, ir.project_id, ir.project_version_id, ir.imresult_id, ir.imresult_version_id, ir.name as imresult_name, ir.sort_order as imresult_sort_order, ir.plan_date as imresult_plan, ir.fact_date as imresult_fact, o.name as imresult_ogv, ir.num_prmpp, --count(distinct vpp.problem_id) as problem_count, count(distinct per.event_id) as risk_count, pp.plan_date_start, pp.plan_date_end, ir.full_name, --count(ir.imresult_id) filter (where pe.event_status <> 10) = 0 as is_achieved, -- Старая логика case when sr.calc_kind = 1 and la.imresult_fix_ud_acc = 100 then true when sr.calc_kind = 2 and la.imresult_ud_acc = 100 then true when sr.calc_kind = 3 and la.imresult_v_ud = 100 then true else false end is_achieved, count(m.has_photo) filter (where m.has_photo is not false) <> 0 as has_photo, count(m.has_video) filter (where m.has_video is not false) <> 0 as has_video, s.begin_ts, s.end_ts from t_segments s join t_param sr on true join t_period p on true join cupproject_v.image_results ir -- (1) Образы результата on s.begin_ts between ir.begin_ts and ir.end_ts and ir.project_id = coalesce(p_project_id,ir.project_id) -- !!!!!!!!!!!! Параметризуем and not ir.obsolete join cupproject_v.priority_projects pp -- (2) Проекты on pp.project_id = ir.project_id and s.begin_ts between pp.begin_ts and pp.end_ts and pp.project_id = coalesce(p_project_id,pp.project_id) -- !!!!!!!!!!!! Параметризуем and not pp.obsolete left join cupproject_v.priority_events pe -- (3) Мероприятия on pe.imresult_id = ir.imresult_id and not pe.obsolete and s.begin_ts between pe.begin_ts and pe.end_ts left join cupproject_v.priority_kt kt_1 -- (6) Контрольные точки on s.begin_ts between kt_1.begin_ts and kt_1.end_ts and kt_1.events_id = pe.events_id and not kt_1.obsolete left join cupproject_v.priority_kt_fact f -- (7) Контрольные точки факт on f.kt_id = kt_1.kt_id and s.begin_ts between f.begin_ts and f.end_ts and not f.obsolete left join gp_planeta.periods pf on f.period_key = pf.elem_value left join t_has_media m on m.events_id = pe.events_id left join cupproject_v.priority_events_risk per -- (4) Риски по мероприятиям on pe.events_id = per.event_id and not per.obsolete and s.begin_ts between per.begin_ts and per.end_ts left join cupproject_v.sp_events_type et -- типы мероприятий on et.id = pe.events_type and not et.obsolete left join cupproject_v.sp_organization o -- организации on o.id = ir.organization and not o.obsolete left join imresult_ud_pivot la on la.imresult_id = ir.imresult_id --and la.period_key = ep.period_key and s.begin_ts between la.begin_ts and la.end_ts left join event_ud_pivot la_event on la_event.event_id = pe.events_id --and la.period_key = ep.period_key and s.begin_ts between la_event.begin_ts and la_event.end_ts where (case when sr.calc_kind = 1 then (ir.plan_date <= p.date_end) or (ir.plan_date > p.date_end and ((pe.events_id is not null and pe.plan_date_end <= p.date_end) or la_event.event_id is not null)) or (ir.plan_date > p.date_end and pe.events_id is not null and ((pe.plan_date_end > p.date_end and kt_1.plan_date_end <= p.date_end) or (f.kt_fact_id is not null and f.date_fact <= p.date_end /and p.date_end >= pf.date_end/)) and p.date_end >= pf.date_end) or (ir.plan_date > p.date_end and la.imresult_id is not null) when sr.calc_kind = 2 then (ir.plan_date <= now()::date) or (ir.plan_date > now()::date and ((pe.events_id is not null and pe.plan_date_end <= now()::date) or la.imresult_id is not null)) or (ir.plan_date > now()::date and pe.events_id is not null and ((pe.plan_date_end > now()::date and kt_1.plan_date_end <= now()::date) or (f.kt_fact_id is not null and f.date_fact <= now()::date /and p.date_end >= pf.date_end/)) and p.date_end >= pf.date_end) or (ir.plan_date > p.date_end and la.imresult_id is not null) else true end) group by ir.project_id, ir.project_version_id, ir.imresult_id, ir.imresult_version_id , ir.name, ir.sort_order, ir.plan_date, ir.fact_date, o.name, ir.num_prmpp, ir.full_name, pp.plan_date_start, pp.plan_date_end , s.begin_ts, s.end_ts
- Assistant
В вашем SQL-запросе из PostgreSQL произошла ошибка, связанная с обращением к несуществующему столбцу. Давайте разберемся по частям.
1. Ошибка с именами столбцов
Ваша ошибка, судя по всему, связана со строками:
case when sr.calc_kind = 1 and la.imresult_fix_ud_acc = 100 then true when sr.calc_kind = 2 and la.imresult_ud_acc = 100 then true when sr.calc_kind = 3 and la.imresult_v_ud = 100 then true else false end is_achieved,Здесь вы ссылаетесь на алиас
la, который, как я понимаю, относится к подзапросуimresult_ud_pivot. Одна из здешних проблем может заключаться в том, что на момент использованияlaвevent_periodуже нет доступного контекста этого подзапроса. Убедитесь, что бот лиlaправильный и доступен в контексте вашего запроса.2. Проверка наличия столбцов
Перед выполнением вашего запроса убедитесь, что все ссылки на столбцы действительно существуют в соответствующих подзапросах. Например, вы можете выполнить запросы для подзапросов, чтобы проверить, существуют ли все упоминаемые вами столбцы.
Для
imresult_ud_pivotнеобходимо убедиться, что у вас действительно есть поляimresult_fix_ud_acc,imresult_ud_accиimresult_v_ud, и что они правильно загружены в подзапросе.-- Пример: проверить структуру подзапроса imresult_ud_pivot SELECT * FROM imresult_ud_pivot LIMIT 10; -- или другое ограничениеЕсли вы видите ошибку о том, что некоторые поля отсутствуют, вы должны вернуться и проверить, как эти агрегаты формируются в подзапросе
imresult_ud_pivot.3. Конструкция CASE
При использовании конструкций
CASEпомните, что они обычно возвращают один результат для одной записи, и их следует использовать осторожно. Также вы должны проверить, верное ли условие, которое вы написали.4. Переменные
Убедитесь, что все переменные, используемые в
coalesce, такие какp_calc_kind,p_project_idи так далее, правильно определены и передаются.Заключение
Если приведенные выше пункты не помогли, вы можете отключить некоторые части вашего SQL-кода и поочередно их включать обратно, чтобы идентифицировать, какая конкретная часть вызывает ошибку.
Если проблема останется, пожалуйста, предоставьте больше информации о структуре таблиц, используемых в ваших подзапросах, и о точной ошибке, которая возникает. Это поможет глубже понять проблему.
- User
Как можно заменить case when sr.calc_kind = 1 and la.imresult_fix_ud_acc = 100 then true when sr.calc_kind = 2 and la.imresult_ud_acc = 100 then true when sr.calc_kind = 3 and la.imresult_v_ud = 100 then true else false end is_achieved?
- Assistant
Вы можете заменить конструкцию
CASEна одну из следующих альтернатив, которые делают то же самое, но могут выглядеть более компактно или по-разному в зависимости от вашего предпочтения.1. Использование логических выражений
Одним из вариантов является использование простых логических выражений с использованием оператора
OR. Это может помочь сделать код более понятным:is_achieved = ( (sr.calc_kind = 1 AND la.imresult_fix_ud_acc = 100) OR (sr.calc_kind = 2 AND la.imresult_ud_acc = 100) OR (sr.calc_kind = 3 AND la.imresult_v_ud = 100) );2. Применение
INиUNNESTЕсли вы хотите сделать код более унифицированным и декларативным, можно использовать оператор
INвместе сUNNESTдля проверки значений в массиве. Для этого потребуется небольшая предварительная обработка:is_achieved = ( (la.imresult_fix_ud_acc = 100 AND sr.calc_kind IN (1)) OR (la.imresult_ud_acc = 100 AND sr.calc_kind IN (2)) OR (la.imresult_v_ud = 100 AND sr.calc_kind IN (3)) );3. Использование таблицы
Если значений много и часто могут добавляться новые, вы можете создать временную таблицу (или использовать CTE) с соответствиями
calc_kindи значениями, которые следует проверять:WITH achievable_conditions AS ( SELECT 1 AS calc_kind, 100 AS target_value UNION ALL SELECT 2, 100 UNION ALL SELECT 3, 100 ) SELECT EXISTS ( SELECT 1 FROM achievable_conditions ac WHERE ac.calc_kind = sr.calc_kind AND ( (ac.calc_kind = 1 AND la.imresult_fix_ud_acc = ac.target_value) OR (ac.calc_kind = 2 AND la.imresult_ud_acc = ac.target_value) OR (ac.calc_kind = 3 AND la.imresult_v_ud = ac.target_value) ) ) AS is_achievedЗаключение
Каждый из приведенных примеров по существу выполняет ту же функцию, что и ваш исходный
CASE, но выбирает различные пути для достижения результата. Выберите тот подход, который выглядит наиболее понятным для вас и ваших коллег при чтении кода. - User
как это будет выглядеть в select? is_achieved = ( (sr.calc_kind = 1 AND la.imresult_fix_ud_acc = 100) OR (sr.calc_kind = 2 AND la.imresult_ud_acc = 100) OR (sr.calc_kind = 3 AND la.imresult_v_ud = 100) );
- Assistant
Чтобы использовать выражение, которое вы написали, в
SELECT, нужно просто добавить его как новый столбец в вашем запросе. Можно написать его как логическое выражение. Например, вашSELECTможет выглядеть так:SELECT ir.project_id, ir.project_version_id, ir.imresult_id, ir.imresult_version_id, ir.name AS imresult_name, ir.sort_order AS imresult_sort_order, ir.plan_date AS imresult_plan, ir.fact_date AS imresult_fact, o.name AS imresult_ogv, ir.num_prmpp, COUNT(DISTINCT per.event_id) AS risk_count, pp.plan_date_start, pp.plan_date_end, ir.full_name, -- Новый столбец is_achieved ( (sr.calc_kind = 1 AND la.imresult_fix_ud_acc = 100) OR (sr.calc_kind = 2 AND la.imresult_ud_acc = 100) OR (sr.calc_kind = 3 AND la.imresult_v_ud = 100) ) AS is_achieved, COUNT(m.has_photo) FILTER (WHERE m.has_photo IS NOT FALSE) <> 0 AS has_photo, COUNT(m.has_video) FILTER (WHERE m.has_video IS NOT FALSE) <> 0 AS has_video, s.begin_ts, s.end_ts FROM t_segments s JOIN t_param sr ON TRUE JOIN t_period p ON TRUE JOIN cupproject_v.image_results ir ON s.begin_ts BETWEEN ir.begin_ts AND ir.end_ts AND ir.project_id = COALESCE(p_project_id, ir.project_id) AND NOT ir.obsolete JOIN cupproject_v.priority_projects pp ON pp.project_id = ir.project_id AND s.begin_ts BETWEEN pp.begin_ts AND pp.end_ts AND pp.project_id = COALESCE(p_project_id, pp.project_id) AND NOT pp.obsolete LEFT JOIN cupproject_v.priority_events pe ON pe.imresult_id = ir.imresult_id AND NOT pe.obsolete AND s.begin_ts BETWEEN pe.begin_ts AND pe.end_ts LEFT JOIN cupproject_v.priority_kt kt_1 ON s.begin_ts BETWEEN kt_1.begin_ts AND kt_1.end_ts AND kt_1.events_id = pe.events_id AND NOT kt_1.obsolete LEFT JOIN cupproject_v.priority_kt_fact f ON f.kt_id = kt_1.kt_id AND s.begin_ts BETWEEN f.begin_ts AND f.end_ts AND NOT f.obsolete LEFT JOIN gp_planeta.periods pf ON f.period_key = pf.elem_value LEFT JOIN t_has_media m ON m.events_id = pe.events_id LEFT JOIN cupproject_v.priority_events_risk per ON pe.events_id = per.event_id AND NOT per.obsolete AND s.begin_ts BETWEEN per.begin_ts AND per.end_ts LEFT JOIN cupproject_v.sp_events_type et ON et.id = pe.events_type AND NOT et.obsolete LEFT JOIN cupproject_v.sp_organization o ON o.id = ir.organization AND NOT o.obsolete LEFT JOIN imresult_ud_pivot la ON la.imresult_id = ir.imresult_id AND s.begin_ts BETWEEN la.begin_ts AND la.end_ts LEFT JOIN event_ud_pivot la_event ON la_event.event_id = pe.events_id AND s.begin_ts BETWEEN la_event.begin_ts AND la_event.end_ts WHERE ( CASE WHEN sr.calc_kind = 1 THEN (ir.plan_date <= p.date_end) OR (ir.plan_date > p.date_end AND ((pe.events_id IS NOT NULL AND pe.plan_date_end <= p.date_end) OR la_event.event_id IS NOT NULL)) OR (ir.plan_date > p.date_end AND pe.events_id IS NOT NULL AND ((pe.plan_date_end > p.date_end AND kt_1.plan_date_end <= p.date_end) OR (f.kt_fact_id IS NOT NULL AND f.date_fact <= p.date_end)) AND p.date_end >= pf.date_end) OR (ir.plan_date > p.date_end AND la.imresult_id IS NOT NULL) WHEN sr.calc_kind = 2 THEN (ir.plan_date <= NOW()::date) OR (ir.plan_date > NOW()::date AND ((pe.events_id IS NOT NULL AND pe.plan_date_end <= NOW()::date) OR la.imresult_id IS NOT NULL)) OR (ir.plan_date > NOW()::date AND pe.events_id IS NOT NULL AND ((pe.plan_date_end > NOW()::date AND kt_1.plan_date_end <= NOW()::date) OR (f.kt_fact_id IS NOT NULL AND f.date_fact <= NOW()::date)) AND p.date_end >= pf.date_end) OR (ir.plan_date > p.date_end AND la.imresult_id IS NOT NULL) ELSE TRUE END ) GROUP BY ir.project_id, ir.project_version_id, ir.imresult_id, ir.imresult_version_id, ir.name, ir.sort_order, ir.plan_date, ir.fact_date, o.name, ir.num_prmpp, ir.full_name, pp.plan_date_start, pp.plan_date_end, s.begin_ts, s.end_ts;Пояснения:
- Я добавил выражение для
is_achieved, которое вычисляет логическое значение (trueилиfalse) на основе условий, которые вы задали. - Добавление
AS is_achievedв конце делает результат более читаемым, именуя этот новый вычисляемый столбец в итоговой выборке. Вы всегда можете использовать это имя в других частях вашего запроса, если это необходимо.
- Я добавил выражение для
- User
в таком случае ( (sr.calc_kind = 1 AND la.imresult_fix_ud_acc = 100) OR (sr.calc_kind = 2 AND la.imresult_ud_acc = 100) OR (sr.calc_kind = 3 AND la.imresult_v_ud = 100) ) AS is_achieved, calc_kind должен участвовать в group by, я бы не хотел этого
- Assistant
Если вы не хотите добавлять
calc_kindвGROUP BY, вы можете использовать агрегацию или подзапрос, чтобы вычислить значениеis_achieved, не влияя на группировку.Способ 1: Использование
MAXВы можете использовать функцию агрегации, такую как
MAX, чтобы избежать явного включенияcalc_kindвGROUP BY. В этом случае вы можете сделать так:SELECT ir.project_id, ir.project_version_id, ir.imresult_id, ir.imresult_version_id, ir.name AS imresult_name, ir.sort_order AS imresult_sort_order, ir.plan_date AS imresult_plan, ir.fact_date AS imresult_fact, o.name AS imresult_ogv, ir.num_prmpp, COUNT(DISTINCT per.event_id) AS risk_count, pp.plan_date_start, pp.plan_date_end, ir.full_name, -- Используем MAX для получения значения is_achieved MAX( (sr.calc_kind = 1 AND la.imresult_fix_ud_acc = 100) OR (sr.calc_kind = 2 AND la.imresult_ud_acc = 100) OR (sr.calc_kind = 3 AND la.imresult_v_ud = 100) ) AS is_achieved, COUNT(m.has_photo) FILTER (WHERE m.has_photo IS NOT FALSE) <> 0 AS has_photo, COUNT(m.has_video) FILTER (WHERE m.has_video IS NOT FALSE) <> 0 AS has_video, s.begin_ts, s.end_ts FROM t_segments s JOIN t_param sr ON TRUE JOIN t_period p ON TRUE JOIN cupproject_v.image_results ir ON s.begin_ts BETWEEN ir.begin_ts AND ir.end_ts AND ir.project_id = COALESCE(p_project_id, ir.project_id) AND NOT ir.obsolete JOIN cupproject_v.priority_projects pp ON pp.project_id = ir.project_id AND s.begin_ts BETWEEN pp.begin_ts AND pp.end_ts AND pp.project_id = COALESCE(p_project_id, pp.project_id) AND NOT pp.obsolete LEFT JOIN cupproject_v.priority_events pe ON pe.imresult_id = ir.imresult_id AND NOT pe.obsolete AND s.begin_ts BETWEEN pe.begin_ts AND pe.end_ts LEFT JOIN cupproject_v.priority_kt kt_1 ON s.begin_ts BETWEEN kt_1.begin_ts AND kt_1.end_ts AND kt_1.events_id = pe.events_id AND NOT kt_1.obsolete LEFT JOIN cupproject_v.priority_kt_fact f ON f.kt_id = kt_1.kt_id AND s.begin_ts BETWEEN f.begin_ts AND f.end_ts AND NOT f.obsolete LEFT JOIN gp_planeta.periods pf ON f.period_key = pf.elem_value LEFT JOIN t_has_media m ON m.events_id = pe.events_id LEFT JOIN cupproject_v.priority_events_risk per ON pe.events_id = per.event_id AND NOT per.obsolete AND s.begin_ts BETWEEN per.begin_ts AND per.end_ts LEFT JOIN cupproject_v.sp_events_type et ON et.id = pe.events_type AND NOT et.obsolete LEFT JOIN cupproject_v.sp_organization o ON o.id = ir.organization AND NOT o.obsolete LEFT JOIN imresult_ud_pivot la ON la.imresult_id = ir.imresult_id AND s.begin_ts BETWEEN la.begin_ts AND la.end_ts LEFT JOIN event_ud_pivot la_event ON la_event.event_id = pe.events_id AND s.begin_ts BETWEEN la_event.begin_ts AND la_event.end_ts WHERE ( CASE WHEN sr.calc_kind = 1 THEN (ir.plan_date <= p.date_end) OR (ir.plan_date > p.date_end AND ((pe.events_id IS NOT NULL AND pe.plan_date_end <= p.date_end) OR la_event.event_id IS NOT NULL)) OR (ir.plan_date > p.date_end AND pe.events_id IS NOT NULL AND ((pe.plan_date_end > p.date_end AND kt_1.plan_date_end <= p.date_end) OR (f.kt_fact_id IS NOT NULL AND f.date_fact <= p.date_end)) AND p.date_end >= pf.date_end) OR (ir.plan_date > p.date_end AND la.imresult_id IS NOT NULL) WHEN sr.calc_kind = 2 THEN (ir.plan_date <= NOW()::date) OR (ir.plan_date > NOW()::date AND ((pe.events_id IS NOT NULL AND pe.plan_date_end <= NOW()::date) OR la.imresult_id IS NOT NULL)) OR (ir.plan_date > NOW()::date AND pe.events_id IS NOT NULL AND ((pe.plan_date_end > NOW()::date AND kt_1.plan_date_end <= NOW()::date) OR (f.kt_fact_id IS NOT NULL AND f.date_fact <= NOW()::date)) AND p.date_end >= pf.date_end) OR (ir.plan_date > p.date_end AND la.imresult_id IS NOT NULL) ELSE TRUE END ) GROUP BY ir.project_id, ir.project_version_id, ir.imresult_id, ir.imresult_version_id, ir.name, ir.sort_order, ir.plan_date, ir.fact_date, o.name, ir.num_prmpp, ir.full_name, pp.plan_date_start, pp.plan_date_end, s.begin_ts, s.end_ts;Пояснения:
- Использование
MAX(): Мы упаковка логического выражения вMAX(), чтобы вернуть одно логическое значение для группы строк. Если хотя бы одно из условий TRUE, тоMAX()вернет TRUE для всей группы. - Отсутствие
calc_kindвGROUP BY: Нет нужды добавлятьsr.calc_kindвGROUP BY, поскольку мы использовали агрегатную функциюMAX().
Способ 2: Использование подзапроса
Кроме того, вы можете рассмотреть возможность вынесения логики определения
is_achievedв отдельный подзапрос, если вы хотите больше изолировать логику:WITH is_achieved_logic AS ( SELECT la.imresult_id, MAX( (sr.calc_kind = 1 AND la.imresult_fix_ud_acc = 100) OR (sr.calc_kind = 2 AND la.imresult_ud_acc = 100) OR (sr.calc_kind = 3 AND la.imresult_v_ud = 100) ) AS is_achieved FROM imresult_ud_pivot la JOIN t_param sr ON TRUE -- Соединяем с t_param для получения calc_kind, если это необходимо GROUP BY la.imresult_id ) SELECT ir.project_id, ir.project_version_id, ir.imresult_id, ir.imresult_version_id, ir.name AS imresult_name, ir.sort_order AS imresult_sort_order, ir.plan_date AS imresult_plan, ir.fact_date AS imresult_fact, o.name AS imresult_ogv, ir.num_prmpp, COUNT(DISTINCT per.event_id) AS risk_count, pp.plan_date_start, pp.plan_date_end, ir.full_name, ia.is_achieved, -- Добавляем столбец is_achieved из подзапроса COUNT(m.has_photo) FILTER (WHERE m.has_photo IS NOT FALSE) <> 0 AS has_photo, COUNT(m.has_video) FILTER (WHERE m.has_video IS NOT FALSE) <> 0 AS has_video, s.begin_ts, s.end_ts FROM t_segments s JOIN t_param sr ON TRUE JOIN t_period p ON TRUE JOIN cupproject_v.image_results ir ON s.begin_ts BETWEEN ir.begin_ts AND ir.end_ts AND ir.project_id = COALESCE(p_project_id, ir.project_id) AND NOT ir.obsolete JOIN cupproject_v.priority_projects pp ON pp.project_id = ir.project_id AND s.begin_ts BETWEEN pp.begin_ts AND pp.end_ts AND pp.project_id = COALESCE(p_project_id, pp.project_id) AND NOT pp.obsolete LEFT JOIN cupproject_v.priority_events pe ON pe.imresult_id = ir.imresult_id AND NOT pe.obsolete AND s.begin_ts BETWEEN pe.begin_ts AND pe.end_ts LEFT JOIN cupproject_v.priority_kt kt_1 ON s.begin_ts BETWEEN kt_1.begin_ts AND kt_1.end_ts AND kt_1.events_id = pe.events_id AND NOT kt_1.obsolete LEFT JOIN cupproject_v.priority_kt_fact f ON f.kt_id = kt_1.kt_id AND s.begin_ts BETWEEN f.begin_ts AND f.end_ts AND NOT f.obsolete LEFT JOIN gp_planeta.periods pf ON f.period_key = pf.elem_value LEFT JOIN t_has_media m ON m.events_id = pe.events_id LEFT JOIN cupproject_v.priority_events_risk per ON pe.events_id = per.event_id AND NOT per.obsolete AND s.begin_ts BETWEEN per.begin_ts AND per.end_ts LEFT JOIN cupproject_v.sp_events_type et ON et.id = pe.events_type AND NOT et.obsolete LEFT JOIN cupproject_v.sp_organization o ON o.id = ir.organization AND NOT o.obsolete LEFT JOIN is_achieved_logic ia ON ia.imresult_id = ir.imresult_id WHERE ... GROUP BY ir.project_id, ir.project_version_id, ir.imresult_id, ir.imresult_version_id, ir.name, ir.sort_order, ir.plan_date, ir.fact_date, o.name, ir.num_prmpp, ir.full_name, pp.plan_date_start, pp.plan_date_end, s.begin_ts, s.end_ts;Заключение
Вы можете выбрать любой из этих способов, чтобы достичь своего результата, не добавляя
calc_kindвGROUP BYи сохраняя логику четко структурированной. - Использование
- User
как в select в where указать несколько значений? МНе нужно чтобы значения фильтровались в зависимости от входного параметра
- Assistant
В SQL вы можете использовать оператор
IN, чтобы указать множество значений для фильтрации в секцииWHERE. Это позволит вам задать несколько значений одновременно. Вот пример использования:Пример использования
INдля фильтрацииПредположим, у вас есть столбец
status, и вы хотите фильтровать записи так, чтобы они соответствовали нескольким значениям (например, 1, 2 и 3). Ваш запрос будет выглядеть следующим образом:SELECT * FROM your_table WHERE status IN (1, 2, 3);Фильтрация с использованием входного параметра
Если вы хотите, чтобы фильтрация зависела от некоторого входного параметра, например,
p_statuses— это массив значений, вы можете сделать это следующим образом:SELECT * FROM your_table WHERE status = ANY (p_statuses);Пример с использованием
ANYи массивов в PostgreSQLЕсли
p_statusesявляется массивом, вы можете использоватьANY:SELECT * FROM your_table WHERE status = ANY (ARRAY[1, 2, 3]); -- Фильтруем по массиву значенийИспользование параметров в PL/pgSQL или функции
Если вы работаете с процедуры или функцией, где
p_statusesявляется параметром функции:CREATE OR REPLACE FUNCTION filter_by_status(p_statuses INT[]) RETURNS TABLE(column1 data_type, column2 data_type) AS $$ BEGIN RETURN QUERY SELECT * FROM your_table WHERE status = ANY (p_statuses); END; $$ LANGUAGE plpgsql;Заключение
Используйте оператор
IN, если у вас известные значения, которые вы хотите фильтровать, илиANYдля работы с массивами. Это даст вам гибкость в том, как вы фильтруете данные в соответствии с вашими потребностями. - User
мне нужно чтобы при входном параметре calc_kind = 2, 3 - берем последний имеющийся факт если calc_kind = 1, то берем запись за отчётный период или последнюю имеющуюся
- Assistant
Для реализации этой логики в запросе SQL на PostgreSQL, вам нужно будет использовать условия для определения, какие записи брать на основе значения входного параметра
calc_kind. Предположим, что у вас есть таблица (например,your_table) с полемfact_date, которое вы будете использовать для определения "последнего имеющегося факта".Вот пример SQL-запроса, который будет учитывать указанные вами условия:
WITH filtered_data AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY project_id ORDER BY CASE WHEN calc_kind = 1 THEN -- Для calc_kind = 1, отбор записи за отчётный период CASE WHEN fact_date <= report_end_date THEN 0 -- Меняем 0 на 1, чтобы это было первым ELSE 1 END ELSE -- Для calc_kind = 2 и 3, выбор последней имеющейся записи 2 -- Ближайшую, используем только 2 END ) AS rn FROM your_table WHERE (calc_kind IN (1, 2, 3)) -- Убедитесь, что фильтруете нужные calc_kind ) SELECT * FROM filtered_data WHERE rn = 1; -- Выбираем первую запись для каждого project_id в зависимости от calc_kindОбъяснение:
- CTE (Common Table Expression): Мы создаем CTE
filtered_data, чтобы легко отбирать нужные записи. - ROW_NUMBER(): Мы создаем номер строки для каждой записи, разделяя их по
project_id, а затем упорядочиваем по условиям:- Для
calc_kind = 1: Мы можем определить "отчётный период" через условие наfact_date. Записи, которые совпадают с отчетным периодом, будут иметь более высокий приоритет (первые), а остальные записи - низкий приоритет. - Для
calc_kind = 2и3: Последние имеющиеся записи будут иметь более высокий приоритет по умолчанию.
- Для
- Фильтрация: Затем мы выбираем записи, где номер строки равен 1, что гарантирует, что мы берем только записи с самым высоким приоритетом.
Примечания:
- Не забудьте заменить
your_tableна актуальное имя вашей таблицы, а также определить, что такоеreport_end_date(это дата конца отчетного периода). Вам нужно будет разместить его в соответствующем контексте вашего запроса. - Я предполагаю, что у вас есть
project_id, но вы можете заменить его на другое поле, по которому хотите группировать записи.
- CTE (Common Table Expression): Мы создаем CTE
- User
а внутри where можно использовать case?
- Assistant
Да, вы можете использовать конструкцию
CASEвнутри условияWHEREв SQL. Это может быть полезно для условного фильтрации данных в зависимости от значений в других полях.Вот пример того, как вы можете использовать
CASEвWHERE:SELECT * FROM your_table WHERE (CASE WHEN calc_kind = 1 THEN fact_date <= report_end_date -- Условие для calc_kind = 1 WHEN calc_kind IN (2, 3) THEN 1=1 -- Условие для calc_kind = 2 или 3 (в данном случае просто возвращаем TRUE) ELSE FALSE END) = TRUE;Объяснение:
- В этом примере, если
calc_kindравен 1, то проверяется условиеfact_date <= report_end_date. - Если
calc_kindравен 2 или 3, то условие всегда возвращаетTRUE, что позволяет взять все записи для этих значенийcalc_kind. - В случае, если
calc_kindравен другому значению, возвращаетсяFALSE, что отфильтрует эти записи.
Более простой подход
Однако, использование
CASEвнутриWHEREможет сделать ваш запрос менее читаемым. Часто проще использовать логические операторы для описания условий. Например:SELECT * FROM your_table WHERE (calc_kind = 1 AND fact_date <= report_end_date) OR (calc_kind IN (2, 3));Заключение
Использование
CASEвWHEREвозможно, но для улучшения читаемости и простоты условия лучше пользоваться логическими операторами (AND, OR). Выбирайте тот подход, который лучше всего соответствует вашим потребностям и стилю кодирования. - В этом примере, если
- User
Можно в фильтрации where (case when sr.calc_kind in (1,2) then
в then дописать max(date)?
- Assistant
В конструкции
CASEв SQL вы не можете напрямую использовать агрегационные функции, такие какMAX(), в частиTHEN, посколькуWHEREне принимает агрегационные функции в своем контексте. Однако вы можете осуществить фильтрацию с использованием логики, которая позволит вам получить нужные результаты.Чтобы использовать
MAX(date)для фильтрации, нужно сперва сгруппировать данные и получить максимальные даты по определенным условиям. Вы можете использовать подзапрос или Common Table Expression (CTE) для этого.Использование CTE или подзапроса
Вот пример, как это может выглядеть с использованием CTE:
WITH max_dates AS ( SELECT sr.project_id, MAX(fact_date) AS max_fact_date FROM your_table sr WHERE sr.calc_kind IN (1, 2) -- Условия для calc_kind GROUP BY sr.project_id ) SELECT * FROM your_table sr JOIN max_dates md ON sr.project_id = md.project_id WHERE (sr.calc_kind = 1 AND sr.fact_date <= md.max_fact_date) OR (sr.calc_kind IN (2, 3));Объяснение:
-
CTE
max_dates: В этом CTE мы выбираемproject_idи находим максимальную датуfact_date, сгруппированную поproject_id, когдаcalc_kindравен 1 или 2. -
Основной запрос: Затем в основном запросе вы присоединяете
your_tableкmax_dates, чтобы иметь возможность использоватьmax_fact_dateв фильтрации. -
Условия в
WHERE:- Для
calc_kind = 1, проверяетсяfact_dateна предмет того, меньше ли он или равенmax_fact_date. - Для
calc_kind = 2 или 3, вы просто выбираете все записи.
- Для
Заключение
Таким образом, чтобы использовать агрегатные функции для фильтрации данных, нужно отделить процесс агрегации от самого запроса, что позволяет сохранить структуру и логику SQL. В результате вы получите требуемый результат без нарушения стандартов SQL.
-