PostgreSQL - show data of previous year + current

问题:

I need to make a column that would show previous year's profit till given year's week. So it would split current year in weeks and it would show what is the profit for given week. To make it more clear let say previous year profit was 1000. This year's first week profit is 100, second week's 200, thirds, week -100 (was loss) and so on. So it should look like this:

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

What I tried was:

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

But this is not working as I need, because it splits profit for every given week. What I mean it shows only that weeks profit, but I need 'that weeks profit' + 'previous years profit'.

My query trying window function:

(
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

For some reason it does not split profit in weeks, but show only one row total like this:

week    |week_profit|
20130114| 1500       |

回答1:

Basic query for running sum

Use the well-known aggregate function sum() as 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;

Result:

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

The advanced feature here is that I combine window and aggregate functions in a single query level - even in a single expression(!), resulting in this SELECT item, that may look surprising to the innocent eye:

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

Find a detailed explanation on how this works in this closely related answer:
Postgres window function and group by exception

Also note multiple other details I improved in your query.

->SQLfiddle

Array / crosstab()

A primitive way to accumulate all weeks in a single row would be to aggregate the outcome in a array:

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

Result:

{1100,1300,1200, ...}

Or, more advanced, you use a crosstab() query like outlined in this related answer:
PostgreSQL Crosstab Query

One of the many related crossbab answers, dealing with temporal data in particular:
Querying row counts segregated by date ranges