PostgreSQL -显示前一年+当年周的数据

Question

我需要做一个专栏，显示前一年的利润，直到一年的周。因此，它将在几周内分割当年，并将显示给定一周的利润。更清楚的是，比如说前一年的利润是1000英镑。今年的第一周利润是100，第二周是200，三分之二，周-100 (亏损)等等。所以应该是这样的：

week1|week2|week3|
1100 |1300 |1200 |

我试过的是：

SELECT
CASE when f1.year = DATE_PART('year', now()) THEN f1.week END as week,
profit as profit
FROM (
SELECT 
DATE_PART('week', so.date_order) as week,
DATE_PART('year', so.date_order) as year,
so.profit as profit
FROM
sale_order as so
GROUP BY
week, year, profit
WHERE
so.date_order >= date_trunc('year', now() - '1 year'::interval)::timestamp::date  and so.date_order <= date_trunc('year', now()+ '1 year'::interval)::timestamp::date-1 
)as f1
GROUP BY
week, profit
ORDER BY
week

但这并不像我所需要的那样有效，因为它每一周都会分红利润。我的意思是，它只显示了周利润，但我需要‘那周利润’+‘前几年利润’。

我的查询尝试窗口函数：

(
SELECT
x.id as id,week as week, x.last_year_profit + y.running_profit as week_profit
FROM
(
SELECT
min(sol.id) as id,
 --DATE_PART('year',  so.date_order) AS calcyear, DATE_PART('week',  so.date_order) AS calcweek,
sum(sol.price_subtotal - (CASE WHEN sol.account_cost_amount != 0 THEN sol.account_cost_amount ELSE sol.purchase_price END )) as last_year_profit
-- sum(sol.price_subtotal) as price_unit, sum(sol.purchase_price) as purchase_price, sum(sol.account_cost_amount) as account_cost_amount
FROM
sale_order as so
INNER JOIN sale_order_line as sol ON (sol.order_id = so.id)
INNER JOIN res_partner as rp ON (so.partner_id = rp.id)
WHERE EXISTS (
SELECT * FROM  res_partner_category_rel rpcl
WHERE 
rpcl.partner_id=rp.id and rpcl.category_id=37
and (so.date_order >= date_trunc('year', now() - '1 year'::interval)::timestamp::date  and so.date_order <= date_trunc('year', now())::timestamp::date-1 )
and so.state != 'cancel'
)
) as x
CROSS JOIN (
SELECT
date_trunc('week', so.date_order) as week,
sum(sum(sol.price_subtotal - (CASE WHEN sol.account_cost_amount != 0 THEN sol.account_cost_amount ELSE sol.purchase_price END ))) OVER  ( ORDER BY date_trunc('week', so.date_order)) as running_profit

FROM
sale_order as so
INNER JOIN sale_order_line as sol ON (sol.order_id = so.id)
INNER JOIN res_partner as rp ON (so.partner_id = rp.id)
WHERE EXISTS (
SELECT * FROM  res_partner_category_rel rpcl
WHERE 
rpcl.partner_id=rp.id and rpcl.category_id=37
AND so.date_order >= date_trunc('year', now())::timestamp::date
AND    so.date_order <  date_trunc('year', now() + '1 year'::interval)::timestamp::date 
and so.state != 'cancel'
)
GROUP BY
week
) as y
GROUP BY
id, week,week_profit
) as f1

由于某些原因，它不会在几周内分割利润，但只显示如下一行总计：

week    |week_profit|
20130114| 1500       |

Answer 1

运行和的基本查询

使用众所周知的聚合函数sum()作为window function。

SELECT week, x.last_year_profit + y.running_profit AS week_profit
FROM (         -- total last year
   SELECT sum(profit) AS last_year_profit
   FROM   sale_order
   WHERE  date_order >= date_trunc('year', now() - interval '1 year')
   AND    date_order <  date_trunc('year', now()) 
   ) x
CROSS JOIN (   -- running sum current year
   SELECT date_trunc('week', date_order) AS week
         ,sum(sum(profit)) OVER (ORDER BY date_trunc('week', date_order))
                                                        AS running_profit
   FROM   sale_order
   WHERE  date_order >= date_trunc('year', now() - interval '1 year')
   AND    date_order <  date_trunc('year', now() + interval '1 year')
   GROUP  BY 1
   ) y;

结果：

week       | week_profit
-----------+------------
2012-01-02 | 1100
2012-01-09 | 1300
2012-01-16 | 1200
...

这里的高级特性是，我将window和聚合函数组合在一个查询级别上--即使是在一个表达式(！)中，产生这个SELECT项，这对无辜的人来说可能是令人惊讶的：

sum(sum(profit)) OVER (ORDER BY date_trunc('week', date_order))

在这个紧密相关的答案中，可以找到关于这个问题如何工作的详细解释：

Postgres window function and group by exception

还请注意我在查询中改进的多个其他细节。

->SQLfiddle

数组/交叉表()

将所有星期集中在一行中的一种基本方法是将结果聚合到一个数组中：

SELECT ARRAY( 
    SELECT x.last_year_profit + y.running_profit  -- only one column
    FROM (
    -- rest like query above
   ) a

结果：

{1100,1300,1200, ...}

或者，更高级的是，您使用了类似于这个相关答案中概述的crosstab()查询：

PostgreSQL Crosstab Query

许多相关的横切回答之一，特别是处理时态数据：

Querying row counts segregated by date ranges