如何减去一列中有一个匹配值的两行

Question

我正在比较不同年份同一周的销售额。就像 2010 年和 2011 年的第一周一样。通过我的查询，我得到了这样的结果。

week|year|sales
1   |2010|5
1   |2011|10
2   |2010|7
2   |2011|13

我的查询如下所示：

SELECT
min(x.id) AS id,
week as week,
year,
COUNT(*) AS amount,
SUM(price_unit) AS price
FROM (
   SELECT
   so.id as id,
   DATE_PART('week',  so.date_order) AS week,
   DATE_PART('year',  so.date_order) AS year,
 sol.price_unit
 FROM 
sale_order AS so
INNER JOIN sale_order_line AS sol ON sol.order_id = so.id
WHERE so.date_order BETWEEN '2010-01-01' AND '2011-12-31' 
) AS x
GROUP BY
week,
year

我想要做的是减去不同年份的相同周，以获得销售额的差异。就像 week2011-week2010 一样，结果应该看起来像这样，

week|year     |difference
 1  |2011-2010|5
 2  |2011-2010|6

我只是不知道如何这样减去:)

Answer 1

要获得两年之间的差异，请尝试以下操作：

SELECT
week as week,
'2011-2010' as year,
sum(case calcyear when 2011 then 1 else -1 end) AS amount,
sum(case calcyear when 2011 then price_unit else -1*price_unit end) AS price
FROM (
   SELECT
   so.id as id,
   DATE_PART('week',  so.date_order) AS week,
   DATE_PART('year',  so.date_order) AS calcyear,
 sol.price_unit
 FROM 
sale_order AS so
INNER JOIN sale_order_line AS sol ON sol.order_id = so.id
WHERE so.date_order BETWEEN '2010-01-01' AND '2011-12-31' 
) AS x
GROUP BY
week