Re: Question about double table scans for a table

On Fri, Jul 28, 2023 at 12:01 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
> On Fri, 28 Jul 2023 at 12:12, jian he <jian(dot)universality(at)gmail(dot)com> wrote:
> > I think you query is equivalent to following:
> >
> > select
> > ps_partkey,
> > sum(ps_supplycost * ps_availqty) filter (where ps_supplycost > 0
> > and ps_availqty > 0 ) as value
>
> The FILTER clause is applied before aggregation. HAVING is applied
> after aggregation. This is not even nearly the same.
>
> (You might have forgotten that numbers can be negative and also you
> might have missed the * 0.0001000000.)
>
> The original query seems to want all parts apart from the ones that
> are below 1/10000th of the total ps_supplycost * ps_availqty for all
> parts.
>
> David

Is this equivalent to the original query?

select ps_partkey, value from
(
select
ps_partkey,
sum(ps_supplycost * ps_availqty) as value,
sum(ps_supplycost * ps_availqty) over(partition by ps_partkey)
* 0.0001000000 as temp
from
PARTSUPP,
SUPPLIER,
NATION
where
ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = 'MOZAMBIQUE'
group by
ps_partkey
) sub1
where value > temp
order by value desc;