如何连接同一张表来获取两天的差价？

Question

我有一个表"Product“，其中我有一天的产品价格，现在我想知道哪些是今天缺失的产品，哪些是昨天存在的，如果这两天它们都存在，那么价格变化的差异是什么。

例如表"Products"：

locale     productID        price         date
US         pro1             100           2015-04-02
US         pro2             200           2015-04-02
CA         pro1             100           2015-04-02

US         pro1             110           2015-04-01
US         pro3             500           2015-04-01
CA         pro4             100           2015-04-01

因此，期望的输出应该是：-

locale_t|productID_t|price_t|date_t|locale_y|productID_y|price_y|date_y|change
US       pro1        100   2015-04-02  US    pro1        110   2015-04-01   10
US       pro2        200   2015-04-02        REMOVED                       200
         REMOVED                       US    pro3        500   2015-04-01  500
CA       pro1        100   2015-04-02        REMOVED                       100
         REMOVED                       CA    pro4        100               100

其中locale_t表示locale_today，locale_y表示locale_yesterday。所以我可以清楚地分辨出昨天有多少产品被删除了，而今天又增加了多少。

Answer 1

你想要一个自交叉连接，但是由于你实际上连接了两个不同的子集，这就变得复杂了。

要实现这一点，一种方法是连接相同数据上的两个子查询，例如http://sqlfiddle.com/#!15/2d6ae/15：

SELECT 
  coalesce(yesterday.locale, today.locale) AS locale,
  coalesce(yesterday."productID", today."productID") AS "productID",
  today.date AS date_t,
  today.price AS price_t,
  yesterday.date AS date_y,
  yesterday.price AS price_y,
  today.price - yesterday.price AS "change"
FROM (
    SELECT * FROM Table1 WHERE "date" = '2015-04-01'
  ) AS yesterday
  FULL OUTER JOIN (
    SELECT * FROM Table1 WHERE "date" = '2015-04-02'
  ) AS today
  ON (today."locale" = yesterday."locale"
      AND today."productID" = yesterday."productID")
 ORDER BY 1, 2;

 locale | productID |   date_t   | price_t |   date_y   | price_y | change 
--------+-----------+------------+---------+------------+---------+--------
 CA     | pro1      | 2015-04-02 |     100 |            |         |       
 CA     | pro4      |            |         | 2015-04-01 |     100 |       
 US     | pro1      | 2015-04-02 |     100 | 2015-04-01 |     110 |    -10
 US     | pro2      | 2015-04-02 |     200 |            |         |       
 US     | pro3      |            |         | 2015-04-01 |     500 |       
(5 rows)

预期的结果并不完全相同，但您并没有真正尝试用语言来指定您想要的内容，我也不想猜测。

我怀疑您可能希望在change列中使用coalesce来生成null结果，如下所示：

coalesce(today.price - yesterday.price, today.price, -yesterday.price) AS "change"