Re: Question about double table scans for a table

On Fri, Jul 28, 2023 at 12:01 PM David Rowley <dgrowleyml@gmail.com> wrote:
>
> On Fri, 28 Jul 2023 at 12:12, jian he <jian.universality@gmail.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;