Как выполнить оконную функцию с предложениемwhere?
Из столбцов 1–4 в таблице ниже я хотел бы создать запрос, который может возвращать столбцы 5 и 6:
Логика запроса:
По сути, это поворот данных, содержащихся в столбцах 1–4.
Столбец 5:
- Если
cat_2_id= 1, отобразитеmy_valueдля этой строки. - Если
cat_2_id!= 1, то отобразитеmy_valueдля последней строки, гдеcat_id_1соответствует текущей строке, аcat_2_id= 1.
Столбец 6:
Это тот же принцип, что и в столбце 5, только для cat_2_id = 2. Итак:
- Если
cat_2_id= 2, отобразитеmy_valueдля этой строки. - Если
cat_2_id!= 2, то отобразитеmy_valueдля последней строки, гдеcat_id_1соответствует текущей строке, аcat_2_id= 2.
Текущий прогресс:
У меня есть решение, которое включает в себя CASE:
- получить
my_value, еслиcat_2_idусловие выполнено, или; - получите
my_valueдля последней строки, используя оконную функциюLAST_VALUEвместе с комбинациейPARTITION BY(cat_1_id) иORDER BY(my_date) иRANGE BETWEEN.
Однако я не могу понять, как встроить дополнительный фильтр в оконную функцию, чтобы обеспечить выполнение условия cat_2_id для последней строки.
Я нашел этот пост, в котором FILTER используется для postgresql, но я не смог найти его в документации MySQL (к тому же там сказано, что он не реализован для неагрегированных оконных функций).
Если нет способа сделать это с помощью оконной функции, то я был бы рад всему, что делает эту работу!
Вот данные для воссоздания:
CREATE TABLE test.pivot_cols (
pivot_by_cols_id INT AUTO_INCREMENT PRIMARY KEY,
my_date DATE NOT NULL,
cat_1_id INT NOT NULL,
cat_2_id INT NOT NULL,
my_value INT NOT NULL
);
INSERT INTO `test`.`pivot_cols` (`my_date`, `cat_1_id`, `cat_2_id`, `my_value`) VALUES ('2000-01-01', '1', '1', '1');
INSERT INTO `test`.`pivot_cols` (`my_date`, `cat_1_id`, `cat_2_id`, `my_value`) VALUES ('2000-01-01', '2', '1', '2');
INSERT INTO `test`.`pivot_cols` (`my_date`, `cat_1_id`, `cat_2_id`, `my_value`) VALUES ('2000-01-02', '1', '2', '3');
INSERT INTO `test`.`pivot_cols` (`my_date`, `cat_1_id`, `cat_2_id`, `my_value`) VALUES ('2000-01-02', '2', '2', '4');
INSERT INTO `test`.`pivot_cols` (`my_date`, `cat_1_id`, `cat_2_id`, `my_value`) VALUES ('2000-01-02', '1', '1', '5');
INSERT INTO `test`.`pivot_cols` (`my_date`, `cat_1_id`, `cat_2_id`, `my_value`) VALUES ('2000-01-02', '2', '1', '6');
Это довольно ужасно, но дает желаемый результат — db<>fiddle
@ user1191247 - спасибо :). Поскольку таблица состоит из нескольких миллионов строк, вероятно, это решение будет довольно медленным? Я вижу, что Акина предложил использовать CTE, что, как я полагаю, будет быстрее, но я не могу точно следовать тому, что он предлагает...
Только коррелирующие запросы. Невозможно сделать с помощью оконных функций.
Если подумать, гарантированно ли cat_2_id будет равен 1 или 2?
Может ли быть несколько строк для одной и той же пары (cat_1_id, cat_2_id) на одном и том же my_date? Существует ли возможность увеличения pivot_by_cols_id при уменьшении my_date между любыми двумя строками?
@user1191247 user1191247 - нет, не может. Извините, пропустил это в операторе создания таблицы.
Ответы 1
Вы можете начать с коррелированного подзапроса, который легче реализовать и понять:
select *, case when cat_2_id = 1 then my_value else (
select my_value
from pivot_cols as x
where cat_1_id = pivot_cols.cat_1_id
and cat_2_id = 1
and my_date < pivot_cols.my_date
order by my_date desc
limit 1
) end as id_1_value, case when cat_2_id = 2 then my_value else (
select my_value
from pivot_cols as x
where cat_1_id = pivot_cols.cat_1_id
and cat_2_id = 2
and my_date < pivot_cols.my_date
order by my_date desc
limit 1
) end as id_2_value
from pivot_cols
order by my_date, cat_1_id
Если вам необходимо использовать оконные функции, это могло бы быть проще, если бы MySQL поддерживал ignore nulls в оконных функциях. К сожалению, это не так, поэтому вам нужно использовать какой-то трюк. В следующем запросе я использую max() over (), чтобы условно найти предыдущую дату. Дополнительные соединения используются для получения значений:
with cte as (
select
*,
max(case when cat_2_id = 1 then my_date end) over prev_rows as date_1,
max(case when cat_2_id = 2 then my_date end) over prev_rows as date_2
from pivot_cols
window prev_rows as (
partition by cat_1_id
order by my_date rows between unbounded preceding and 1 preceding
)
)
select
cte.*,
case when cte.cat_2_id = 1 then cte.my_value else j1.my_value end as id_1_value,
case when cte.cat_2_id = 2 then cte.my_value else j2.my_value end as id_2_value
from cte
left join pivot_cols as j1 on cte.cat_1_id = j1.cat_1_id and cte.date_1 = j1.my_date
left join pivot_cols as j2 on cte.cat_1_id = j2.cat_1_id and cte.date_2 = j2.my_date
order by cte.my_date, cte.cat_1_id
Как выполнить оконную функцию с предложениемwhere? Это абсолютно невозможно. Примените окно fn в CTE, затем проверьте соответствующий столбец во внешнем WHERE.