sql - Postgresql查询每天售出库存数量

Question

我有一个 CRM 项目，它为每个组织维护产品销售订单。

我想通过按日期循环来计算每天售出的库存，但显然这是一种荒谬的方法，需要更多的时间和内存。

请帮助我在单个查询中找出它。可能吗？

这是我的数据库结构供您引用。

产品 : id (PK), name

组织 : id (PK), name

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

所选月份的预期产出:

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

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

Answer 1

创建 tablfunc :

CREATE EXTENSION IF NOT EXISTS tablefunc;

查询:

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);

请注意，使用 PostgreSQL Crosstab询问。我用过coalesce用于处理空值(当有空数据返回时交叉表查询显示“0”)。