Anyway to improve this SQL to avoid multiple Joins

Question

I want to get sales result for 10 days for each product which is in my orders_summary table. Currently I'm joining orders_summary table 10 times to get sales for each day. Is there any better way to get this data?

Current sql:

SELECT P.ID,
    P.SKU,
    FIRST_DAY.ITEMS AS ITEMS_1,
    FIRST_DAY.ORDERS AS ORDERS_1,
    SECOND_DAY.ITEMS AS ITEMS_2,
    SECOND_DAY.ORDERS AS ORDERS_2
FROM PRODUCTS AS P
LEFT JOIN
    (SELECT SKU,
            AMOUNT AS ITEMS,
            ARRAY_LENGTH(LIST,
                1) AS ORDERS
        FROM ORDERS_SUMMARY
        WHERE ORDER_DATE = TO_TIMESTAMP(1633158000000 / 1000.0)) AS FIRST_DAY ON P.SKU = FIRST_DAY.SKU
LEFT JOIN
    (SELECT SKU,
            AMOUNT AS ITEMS,
            ARRAY_LENGTH(LIST,
                1) AS ORDERS
        FROM ORDERS_SUMMARY
        WHERE ORDER_DATE = TO_TIMESTAMP(1633676400000 / 1000.0)) AS SECOND_DAY ON P.SKU = SECOND_DAY.SKU
...

result:

You could join once, using a date range to get your 10 days, and then use conditional aggregation for your daily calculations - max(case when order_date = x then items else null end) as items_1... — Andrew
– Andrew, Commented Nov 12, 2021 at 21:11
The query contradicts your description. You say "for each product which is in my orders_summary table", but the query does it for each product which is in the products table. The difference matters - as well as your Postgres version, your table definitions, and cardinalities. — Erwin Brandstetter
– Erwin Brandstetter, Commented Nov 13, 2021 at 0:04
@Andrew That was what I meant. Thank you. Would you like to share your answer or I should I answer it by my self? — Mj Ebrahimzadeh
– Mj Ebrahimzadeh, Commented Nov 13, 2021 at 8:37

Ramin Faracov · Accepted Answer · 2021-11-13 11:02:23Z

select main.sku, jsonb_populate_record(null::examples.table_fields, main.json_data)
from 
    (
        select t2.sku, jsonb_object_agg(t2.itemNames, t2.items) || jsonb_object_agg(t2.orderNames, t2.orders) as json_data from 
        (
            select 
                pr.sku, 
                'items' || tbl_dates.num::varchar as itemNames, 
                coalesce(sum(sOrd.amount), 0) as items,
                'orders' || tbl_dates.num::varchar as orderNames, 
                coalesce(sum(sOrd.qty), 0) as orders 
                -- tbl_dates.dates 
            from products pr
            inner join (
                select tt.num, ('2021-01-01'::date + tt.num - 1) as dates 
                from (  
                    select t.num from generate_series(1, 10, 1) AS t(num)
                ) tt 
            ) tbl_dates on true 
            left join orders_summary sOrd on sOrd.sku = pr.sku and sOrd.order_date::date = tbl_dates.dates
            group by pr.sku, tbl_dates.num, tbl_dates.dates
            order by tbl_dates.num
        ) t2 
        group by t2.sku 
    ) main;

I wrote simple select query, if you want to use a function then you can change this is '2021-01-01'::date to input variable and in this code generate_series(1, 10, 1) you can change 10 to the input variable

Collectives™ on Stack Overflow

Anyway to improve this SQL to avoid multiple Joins

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related