avoiding creating 2 views

[Fawze Abujaber <fa...@gmail.com>]

Hello community,

I have a table called *table1 *and i'm using the below query, I'm wondering
if i can do the below in one view or temp table instead of using 2.

The difference is that i'm using at one last_value(max(a)) and the other
last_value(max(c)).

After these query o'm joining the 2 with a 3rd table.

with ci AS(
SELECT
b as b
,max(event_time) as event_time
,last_value(max(a)) over(partition by b order by max(event_time) ROWS
BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as a
FROM table1
WHERE a IS NOT NULL
AND  concat(year,month,day) between '20180429' and '20180506'

group by
b
),
token AS(
SELECT
b as b
,max(event_time) as event_time
,last_value(max(c)) over(partition by b order by max(event_time) ROWS
BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as c
FROM table1
WHERE c IS NOT NULL
AND  concat(year,month,day) between '20180429' and '20180506'
group by
b
)



Thanks in advance


-- 
Take Care
Fawze Abujaber

Re: avoiding creating 2 views

[Greg Rahn <gr...@gmail.com>]

Maybe... the two queries have different IS NOT NULL predicates which can
impact max(event_time) differently for each query block.   However, you
could calculate each independently with a case when like such:

max(case when a is not null then event_time end) as max_a_not_null,
max(case when b is not null then event_time end) as max_b_not_null,

The window function should not be impacted by the IS NOT NULL predicates
since aggregates already ignore nulls in the calculation (predicate column
is used in wf aggregate)

Try something like:

select
  max(case when a is not null then event_time end) as
max_event_time_a_not_null,
  max(case when b is not null then event_time end)
as max_event_time_b_not_null,
  last_value(max(a))
  over (
    partition by b
    order by max(event_time)
    rows between unbounded preceding and unbounded following ) as a,
  last_value(max(c))
  over (
    partition by b
    order by max(event_time)
    rows between unbounded preceding and unbounded following ) as c
from
  table1
where concat(year, month, day) between '20180429' and '20180506'
group by b


On Sun, May 6, 2018 at 9:39 AM, Fawze Abujaber <fa...@gmail.com> wrote:

> Hello community,
>
> I have a table called *table1 *and i'm using the below query, I'm
> wondering if i can do the below in one view or temp table instead of using
> 2.
>
> The difference is that i'm using at one last_value(max(a)) and the other
> last_value(max(c)).
>
> After these query o'm joining the 2 with a 3rd table.
>
> with ci AS(
> SELECT
> b as b
> ,max(event_time) as event_time
> ,last_value(max(a)) over(partition by b order by max(event_time) ROWS
> BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as a
> FROM table1
> WHERE a IS NOT NULL
> AND  concat(year,month,day) between '20180429' and '20180506'
>
> group by
> b
> ),
> token AS(
> SELECT
> b as b
> ,max(event_time) as event_time
> ,last_value(max(c)) over(partition by b order by max(event_time) ROWS
> BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as c
> FROM table1
> WHERE c IS NOT NULL
> AND  concat(year,month,day) between '20180429' and '20180506'
> group by
> b
> )
>
>
>
> Thanks in advance
>
>
> --
> Take Care
> Fawze Abujaber
>