PostgreSQL-显示前一年+本年周的数据 [英] PostgreSQL - show data of previous year + current year's week

问题描述

我需要创建一个列，以显示到给定年份的一周为止的上一年的利润。
因此它将把本年度分成几周，并显示给定周的利润是多少。
更清楚地说，假设上一年的利润为1000。今年的第一周利润为100，第二周为200，三分之二，周-100（亏损），依此类推。
因此，它应该看起来像这样：

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 |

我尝试过的是：

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       |

推荐答案

求和的基本查询

使用众所周知的聚合函数 sum（）作为 窗口功能 。

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
...

此处的高级功能是我结合了窗口和汇总函数，即使在单个查询级别（！）中也是如此，从而导致此 SELECT 项看起来像无辜的眼睛感到惊讶：

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

在此密切相关的答案中找到有关其工作原理的详细说明：

Postgres窗口函数和按例外分组

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

一种原始方法，可以在单个ro中累积所有星期w将是将结果汇总到一个数组中：

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

结果：

{1100,1300,1200, ...}

或更高级的方法是，使用 crosstab（）查询，如相关答案：

PostgreSQL交叉表查询

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

crossbab的许多相关答案之一，尤其是处理时间数据：

查询按日期范围划分的行计数

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

这篇关于PostgreSQL-显示前一年+本年周的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！