Maximum per partition

user_16614413 0
Viewed 32

Tell me how to correctly compose a query so that the following records are displayed, as in the example. I have a partition by id_1 and id_2 and I only take entries with the maximum operdate.

select a.* 
from (select t1.*,
       rank() over (partition by id_1,id_2 order by operdate desc) rn
      from table1 t1)
where a.rn=1

And I need to display the maximum end_circulation in the partition for these records. I understand how to do it.

select a.*
from (select t1.*,
       rank() over (partition by id_1,id_2 order by operdate desc) rn,
       max(end_circulation) over(partition by id_1,id_2) max_end_circulation
      from table1 t1)
where a.rn=1

But I need that if there is at least one NULL in end_circulation, then NULL is also set in max_end_circulation. How to do it right?

id_1 id_2 end_circulation operdate rn
2123 6 2021-06-07 00:00:00 2021-03-04 1
2123 6 2021-05-05 00:00:00 2021-02-01 2
3222 3 2021-06-07 00:00:00 2021-01-01 1
3222 3 NULL 2020-12-11 2

RESULT:

id_1 id_2 end_circulation operdate rn max_end_circulation
2123 6 2021-06-07 00:00:00 2021-03-04 1 2021-06-07 00:00:00
3222 3 2021-06-07 00:00:00 2021-01-01 1 NULL
hive mysql sql
1 Answers

Calculate additional has_null flag and use in the CASE expression:

select ...
       case when has_null=1 then NULL else max_end_circulation end as max_end_circulation

from
(
select t1.*,
       rank() over (partition by id_1,id_2 order by operdate desc) rn,
       max(end_circulation) over(partition by id_1,id_2) max_end_circulation
       max(case when end_circulation is null then 1 else 0 end) over(partition by id_1,id_2) has_null
from table1 t1
)s
where rn=1

One more approach is to convert NULLs to string 'NULL' and later convert it back. 'NULL' string is greater than any date and will be picked by max().

select ...
       case when max_end_circulation = 'NULL' then NULL else max_end_circulation end as max_end_circulation

from
(
select t1.*,
       rank() over (partition by id_1,id_2 order by operdate desc) rn,
       max(coalesce(end_circulation,'NULL')) over(partition by id_1,id_2) max_end_circulation
from table1 t1
)s
where rn=1
user_27003440
Related