Вот исходная информация:
Внимание - на колонки
latest_vizit_date и
latest_vizit_date_2. Выбор каждой из них аналогичен, только в
latest_vizit_date_2 я пробегаюсь ещё функцией
over().
Моя задача в колонке
latest_vizit_date_2 у каждого
dispenser_id вывести максимальное значение в каждой строке. К примеру
dispenser_id = 3176 - как видно по колонке
latest_vizit_date - у него максимальное значение - 2021-05-19, но в каждой строке колонки
latest_vizit_date_2 выводится непонятная дата 2019-01-23, которая в окне выборки не участвовала никак вообще. Я ожидал увидеть, что все четыре строки заполнятся датой 2021-05-19.
Что я делаю не так?
Исходный код:
select
disp.apteka_id as apteka_id,
disp.id as dispenser_id,
date_format(
from_unixtime(disp.created),
'%Y-%m-%d'
) as dispenser_created,
date_format(
from_unixtime(wf_state.created),
'%Y-%m-%d'
) as dispenser_deleted,
d_config.field_ma_disp_configtype_value as dispenser_config,
max(date_format(
from_unixtime(v_date_all.field_vizit_date_value),
'%Y-%m-%d'
)) as latest_vizit_date,
date_format(
from_unixtime(max(v_date_all.field_vizit_date_value) over (partition by disp.id)),
'%Y-%m-%d'
) as latest_vizit_date_2,
max(month(from_unixtime(v_date_all.field_vizit_date_value))) as latest_vizit_month,
quarter(from_unixtime(v_date_all.field_vizit_date_value)) as latest_vizit_quarter,
max(year(from_unixtime(v_date_all.field_vizit_date_value))) as latest_vizit_year,
max(if(((year(from_unixtime(v_date.field_vizit_date_value)) =
year(from_unixtime(v_date_all.field_vizit_date_value))) and
(quarter(from_unixtime(v_date.field_vizit_date_value)) =
quarter(from_unixtime(v_date_all.field_vizit_date_value)))), f_photos.field_ma_disp_photos_fid,
NULL)
) as latest_photo_id,
max(date_format(from_unixtime(if(
(f_photos.field_ma_disp_photos_fid is not null) and
(year(from_unixtime(v_date.field_vizit_date_value)) = year(from_unixtime(v_date_all.field_vizit_date_value))) and
(quarter(from_unixtime(v_date.field_vizit_date_value)) = quarter(from_unixtime(v_date_all.field_vizit_date_value))),
v_date.field_vizit_date_value,
NULL
)),
'%Y-%m-%d')
) as latest_photo_date
from crm_aptekas a
join eck_ma_apteka_data disp on (
(disp.type = 'dispenser') and
(disp.apteka_id = a.apteka_id)
)
left join field_data_field_ma_disp_configtype d_config on (
(d_config.entity_type = 'ma_apteka_data') and
(d_config.entity_id = disp.id)
)
left join field_data_field_ma_disp_states disp_states on (
(disp_states.entity_type = 'ma_apteka_data') and
(disp_states.entity_id = disp.id) and
(disp_states.delta = 0)
)
left join eck_workflow_state wf_state on (
(wf_state.id = disp_states.field_ma_disp_states_target_id) and
(wf_state.state_code = 'deleted')
)
inner join eck_vizit_data v_data on (
(v_data.adata_id = disp.id) and
(v_data.created >= unix_timestamp(cast('2018-01-01 00:00:00' as date)))
)
left join field_data_field_vizit_date v_date on (
(v_date.entity_id = v_data.vizit_id)
)
left join field_data_field_ma_disp_photos f_photos on (
(f_photos.entity_id = v_data.id)
)
left join field_data_field_vizit_place v_place_all on (
(v_place_all.field_vizit_place_target_id = disp.apteka_id) and
(v_place_all.entity_id >= v_data.vizit_id)
)
left join field_data_field_vizit_date v_date_all on (
(v_date_all.entity_id = v_place_all.entity_id) and
(year(from_unixtime(v_date_all.field_vizit_date_value)) * 10) + quarter(from_unixtime(v_date_all.field_vizit_date_value))
<= coalesce((year(from_unixtime(wf_state.created)) * 10) + quarter(from_unixtime(wf_state.created)), 99999)
)
inner join eck_vizit v on (
(v.id = v_data.vizit_id) and
(v.state <> 5)
)
where disp.id in (3176, 53182)
group by disp.id, quarter(from_unixtime(v_date_all.field_vizit_date_value))
order by disp.id, quarter(from_unixtime(v_date_all.field_vizit_date_value))
Средний
