求助：PostgreSQL/SQLite3中表自连接实现同比（YoY）对比

没问题，我来帮你搞定这个同比单行展示的需求，不管用PostgreSQL还是SQLite都能轻松实现。核心思路是通过自连接关联当前日期和去年同期的数据，再把结果和其他表的查询结果合并。下面是具体方案：

核心逻辑说明 #

我们把sales表拆成两个逻辑实例：一个代表2017年1月7日的当前数据，另一个代表去年同一天（2016年1月7日）的同期数据。通过store和category保证维度匹配，再用日期偏移一年的规则关联两张"表"，最终把去年的4个指标作为额外字段追加到当前数据的同一行。

1. PostgreSQL 实现代码 #

PostgreSQL支持直接用INTERVAL做日期运算，写法很简洁：

SELECT
  -- 当前周期（2017-01-07）的所有字段
  curr.we,
  curr.store,
  curr.category,
  curr.planu,
  curr.planrev,
  curr.merchu,
  curr.merchrev,
  -- 去年同期的指标，用ly_前缀做区分
  prev.planu AS ly_planu,
  prev.planrev AS ly_planrev,
  prev.merchu AS ly_merchu,
  prev.merchrev AS ly_merchrev
FROM sales curr
JOIN sales prev
  ON curr.store = prev.store
  AND curr.category = prev.category
  AND curr.we - INTERVAL '1 year' = prev.we
-- 筛选目标日期
WHERE curr.we = '2017-01-07'
-- 可选：按门店和品类排序，方便查看
ORDER BY curr.store, curr.category;

2. SQLite3 实现代码 #

SQLite需要用DATE()函数来计算去年同期日期，调整一下日期关联条件即可：

SELECT
  curr.we,
  curr.store,
  curr.category,
  curr.planu,
  curr.planrev,
  curr.merchu,
  curr.merchrev,
  prev.planu AS ly_planu,
  prev.planrev AS ly_planrev,
  prev.merchu AS ly_merchu,
  prev.merchrev AS ly_merchrev
FROM sales curr
JOIN sales prev
  ON curr.store = prev.store
  AND curr.category = prev.category
  AND DATE(curr.we, '-1 year') = prev.we
WHERE curr.we = '2017-01-07'
ORDER BY curr.store, curr.category;

与其他表结果的UNION整合 #

要把上述结果和其他合作伙伴的查询结果合并，只需要确保所有查询的列数、列顺序、数据类型完全一致，然后用UNION ALL（保留重复数据）或UNION（自动去重）拼接即可。示例如下：

-- 先执行sales表的同比查询
SELECT
  curr.we,
  curr.store,
  curr.category,
  curr.planu,
  curr.planrev,
  curr.merchu,
  curr.merchrev,
  prev.planu AS ly_planu,
  prev.planrev AS ly_planrev,
  prev.merchu AS ly_merchu,
  prev.merchrev AS ly_merchrev
FROM sales curr
JOIN sales prev
  ON curr.store = prev.store
  AND curr.category = prev.category
  AND curr.we - INTERVAL '1 year' = prev.we
WHERE curr.we = '2017-01-07'

-- 用UNION ALL连接其他表的同比查询（以partner_sales表为例）
UNION ALL

SELECT
  curr.we,
  curr.store,
  curr.category,
  curr.planu,
  curr.planrev,
  curr.merchu,
  curr.merchrev,
  prev.planu AS ly_planu,
  prev.planrev AS ly_planrev,
  prev.merchu AS ly_merchu,
  prev.merchrev AS ly_merchrev
FROM partner_sales curr
JOIN partner_sales prev
  ON curr.store = prev.store
  AND curr.category = prev.category
  AND curr.we - INTERVAL '1 year' = prev.we
WHERE curr.we = '2017-01-07'

ORDER BY store, category;

提示：如果是SQLite环境，把日期运算部分换成DATE(curr.we, '-1 year')就可以直接复用。

内容的提问来源于stack exchange，提问作者Jim Cole