CODEWITHSUNDEEP
sqlpostgresqlpivot
Darshan Patel
Darshan Patel

Reputation: 2899

Postgresql query for every day sold stock count

I have project on CRM which maintains product sales order for every organization.

I want to count everyday sold stock which I have managed to do by looping over by date but obviously it is a ridiculous method and taking more time and memory.

Please help me to find out it in single query. Is it possible?

Here is my database structure for your reference.

product : id (PK), name

organization : id (PK), name

sales_order : id (PK), product_id (FK), organization_id (FK), sold_stock, sold_date(epoch time)

enter image description here

Expected Output for selected month : 

organization |  product | day1_sold_stock | day2_sold_stock | ..... | day30_sold_stock

http://sqlfiddle.com/#!15/e1dc3/3

Upvotes: 1

Views: 520

Answers (3)

Jaimin Darji
Jaimin Darji

Reputation: 374

Create tablfunc :

CREATE EXTENSION IF NOT EXISTS tablefunc;

Query :

select  "proId" as ProductId ,product_name as ProductName,organizationName as OrganizationName,
    coalesce( "1-day",0) as  "1-day" ,coalesce( "2-day",0) as  "2-day" ,coalesce( "3-day",0) as "3-day" ,
    coalesce( "4-day",0) as  "4-day" ,coalesce( "5-day",0) as  "5-day" ,coalesce( "6-day",0) as  "6-day" ,
    coalesce( "7-day",0) as  "7-day" ,coalesce( "8-day",0) as  "8-day" ,coalesce( "9-day",0) as "9-day" ,
    coalesce("10-day",0) as "10-day" ,coalesce("11-day",0) as "11-day" ,coalesce("12-day",0) as "12-day" ,
    coalesce("13-day",0) as "13-day" ,coalesce("14-day",0) as "14-day" ,coalesce("15-day",0) as"15-day" ,
    coalesce("16-day",0) as "16-day" ,coalesce("17-day",0) as "17-day" ,coalesce("18-day",0) as "18-day" ,
    coalesce("19-day",0) as "19-day" ,coalesce("20-day",0) as "20-day" ,coalesce("21-day",0) as"21-day" ,
    coalesce("22-day",0) as "22-day" ,coalesce("23-day",0) as "23-day" ,coalesce("24-day",0) as "24-day" ,
    coalesce("25-day",0) as "25-day" ,coalesce("26-day",0) as "26-day" ,coalesce("27-day",0) as"27-day" ,
    coalesce("28-day",0) as "28-day" ,coalesce("29-day",0) as "29-day" ,coalesce("30-day",0) as "30-day" ,
    coalesce("31-day",0) as"31-day" 
from crosstab(
    'select hist.product_id,pr.name,o.name,EXTRACT(day FROM TO_TIMESTAMP(hist.sold_date/1000)),sum(sold_stock)
    from sales_order hist 
    left join product pr on pr.id = hist.product_id
    left join organization o on o.id = hist.organization_id
    where EXTRACT(MONTH FROM TO_TIMESTAMP(hist.sold_date/1000)) =5
    and EXTRACT(YEAR FROM TO_TIMESTAMP(hist.sold_date/1000)) = 2017 
    group by hist.product_id,pr.name,EXTRACT(day FROM TO_TIMESTAMP(hist.sold_date/1000)),o.name
    order by o.name,pr.name',
    'select d from generate_series(1,31) d') 
as ("proId" int ,product_name text,organizationName text,
"1-day" float,"2-day" float,"3-day" float,"4-day" float,"5-day" float,"6-day" float
,"7-day" float,"8-day" float,"9-day" float,"10-day" float,"11-day" float,"12-day" float,"13-day" float,"14-day" float,"15-day" float,"16-day" float,"17-day" float
,"18-day" float,"19-day" float,"20-day" float,"21-day" float,"22-day" float,"23-day" float,"24-day" float,"25-day" float,"26-day" float,"27-day" float,"28-day" float,
"29-day" float,"30-day" float,"31-day" float);

Please note, use PostgreSQL Crosstab Query. I have used coalesce for handling null values(Crosstab Query to show "0" when there is null data to return).

Upvotes: 2

Chris Travers
Chris Travers

Reputation: 26464

You have a few options here but it is important to understand the limitations first.

The big limitation is that the planner needs to know the record size before the planning stage, so this has to be explicitly defined, not dynamically defined. There are various ways of getting around this. At the end of the day, you are probably going to have somethign like Bavesh's answer, but there are some tools that may help.

Secondly, you may want to aggregate by date in a simple query joining the three tables and then pivot.

For the second approach, you could:

  1. You could do a simple query and then pull the data into Excel or similar and create a pivot table there. This is probably the easiest solution.
  2. You could use the tablefunc extension to create the crosstab for you.

Then we get to the first problem which is that if you are always doing 30 days, then it is easy if tedious. But if you want to do every day for a month, you run into the row length problem. Here what you can do is create a dynamic query in a function (pl/pgsql) and return a refcursor. In this case the actual planning takes place in the function and the planner doesn't need to worry about it on the outer level. Then you call FETCH on the output.

Upvotes: 1

Bhavesh Ghodasara
Bhavesh Ghodasara

Reputation: 2071

Following query will help to find the same:

select o.name,
       p.name,
       sum(case when extract (day from to_timestamp(sold_date))=1 then sold_stock else 0 end)day1_sold_stock,
       sum(case when extract (day from to_timestamp(sold_date))=2 then sold_stock else 0 end)day2_sold_stock,
       sum(case when extract (day from to_timestamp(sold_date))=3 then sold_stock else 0 end)day3_sold_stock,      
from sales_order so,
    organization o,
    product p
where so.organization_id=o.id
and so.product_id=p.id
group by o.name,
       p.name;

I just provided logic to find for 3 days, you can implement the same for rest of the days.

basically first do basic joins on id, and then check if each date(after converting epoch to timestamp and then extract day).

Upvotes: 1

Related Questions