如何合并单表的两个查询？单条SQL实现月度明细与季度聚合数据展示

核心思路 #

使用SQL窗口函数实现即可，该功能可以在保留原有明细行的前提下，按指定维度（此处为用户维度）计算聚合值，不需要折叠分组行，完全匹配你同时展示月度明细和用户季度统计数据的需求。

具体查询语句 #

假设你的业务数据表名为business_data，查询2016年1-3月数据的语句如下：

SELECT 
  users,
  date,
  pay,
  balance,
  AVG(balance) OVER (PARTITION BY users) AS `avg(balance)`,
  AVG(pay) OVER (PARTITION BY users) AS `avg(pay)`,
  SUM(pay) OVER (PARTITION BY users) AS `sum(pay)`,
  MAX(pay) OVER (PARTITION BY users) AS `max(pay)`
FROM business_data
-- 可根据实际要查询的季度调整时间过滤条件
WHERE date >= '2016-01-01' AND date <= '2016-03-31'
ORDER BY users, date;

兼容低版本SQL环境方案 #

如果你使用的数据库不支持窗口函数（例如MySQL 5.7及更早版本），可以用子查询关联的方式实现：

SELECT 
  t1.users,
  t1.date,
  t1.pay,
  t1.balance,
  t2.avg_balance AS `avg(balance)`,
  t2.avg_pay AS `avg(pay)`,
  t2.sum_pay AS `sum(pay)`,
  t2.max_pay AS `max(pay)`
FROM business_data t1
INNER JOIN (
  SELECT 
    users,
    AVG(balance) AS avg_balance,
    AVG(pay) AS avg_pay,
    SUM(pay) AS sum_pay,
    MAX(pay) AS max_pay
  FROM business_data
  WHERE date >= '2016-01-01' AND date <= '2016-03-31'
  GROUP BY users
) t2 ON t1.users = t2.users
WHERE t1.date >= '2016-01-01' AND t1.date <= '2016-03-31'
ORDER BY t1.users, t1.date;

内容的提问来源于stack exchange，提问作者Rinat Devyatyarov