sql统计最近8个季度 无数据自动填充
时间: 2023-12-27 13:02:35 浏览: 57
mysql 统计一天24小时数据默认补0SQL
假设你有一个表 `sales` 存储了每个季度的销售数据,可以使用以下 SQL 语句来统计最近 8 个季度的销售总额,并且能够自动填充缺失的季度:
```sql
SELECT
CONCAT(YEAR(q.date_quarter_start), 'Q', QUARTER(q.date_quarter_start)) as quarter,
COALESCE(SUM(s.amount), 0) as sales_amount
FROM
(
SELECT
MAKEDATE(YEAR(CURRENT_DATE), 1) + INTERVAL (QUARTER(CURRENT_DATE)-1)*3 MONTH as date_quarter_start
UNION
SELECT
MAKEDATE(YEAR(CURRENT_DATE)-1, 1) + INTERVAL (QUARTER(CURRENT_DATE)-1)*3 MONTH as date_quarter_start
UNION
SELECT
MAKEDATE(YEAR(CURRENT_DATE)-2, 1) + INTERVAL (QUARTER(CURRENT_DATE)-1)*3 MONTH as date_quarter_start
UNION
SELECT
MAKEDATE(YEAR(CURRENT_DATE)-3, 1) + INTERVAL (QUARTER(CURRENT_DATE)-1)*3 MONTH as date_quarter_start
UNION
SELECT
MAKEDATE(YEAR(CURRENT_DATE)-4, 1) + INTERVAL (QUARTER(CURRENT_DATE)-1)*3 MONTH as date_quarter_start
UNION
SELECT
MAKEDATE(YEAR(CURRENT_DATE)-5, 1) + INTERVAL (QUARTER(CURRENT_DATE)-1)*3 MONTH as date_quarter_start
UNION
SELECT
MAKEDATE(YEAR(CURRENT_DATE)-6, 1) + INTERVAL (QUARTER(CURRENT_DATE)-1)*3 MONTH as date_quarter_start
UNION
SELECT
MAKEDATE(YEAR(CURRENT_DATE)-7, 1) + INTERVAL (QUARTER(CURRENT_DATE)-1)*3 MONTH as date_quarter_start
) AS q
LEFT JOIN sales s ON
s.date BETWEEN q.date_quarter_start AND DATE_ADD(q.date_quarter_start, INTERVAL 2 MONTH)
GROUP BY quarter
ORDER BY quarter DESC;
```
这个 SQL 查询首先使用 `UNION` 操作将最近 8 个季度的开始日期合并成一个临时表 `q`。然后使用 `LEFT JOIN` 连接 `sales` 表,使用 `COALESCE` 函数填充缺失的销售额数据,并使用 `GROUP BY` 按季度进行分组汇总,最后使用 `ORDER BY` 按季度倒序排序。
阅读全文