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:
CodePudding user response：
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
CodePudding user response：
You can also pivot your data.