如何共享一些 WHERE 子句，但仍为多个总和添加唯一子句?

Question

我有一个表格，其中包含一些简单的数字，我想在不同的日期范围内求和。例：

date     | cost | storeId | stateId |
-------------------------------------
1/1/2021 | 100  | 1       |  1      |
1/1/2020 | 30   | 2       |  2      |

我想对不同时间跨度的成本求和，因此现在 1 个月内、现在 2 个月内的记录成本总和，等等。我还有几个需要 JOIN 的条件来计算。这导致我创建了一个大量查询，该查询重用相同的连接和 where 子句(日期范围除外)和 UNION：

SELECT SUM(cost)
FROM table1 t1
LEFT JOIN table2 t2 ON t2.id = t1.storeId
LEFT JOIN table3 t3 ON t3.id = t1.stateId
WHERE t1.date > '1monthAgo' t2.otherId = 1 AND t2.anotherId = 2 AND t3.someId = 1
UNION
SELECT SUM(cost)
FROM table1 t1
LEFT JOIN table2 t2 ON t2.id = t1.storeId
LEFT JOIN table3 t3 ON t3.id = t1.stateId
WHERE t1.date > '2monthsAgo' t2.otherId = 1 AND t2.anotherId = 2 AND t3.someId = 1
UNION
SELECT SUM(cost)
FROM table1 t1
LEFT JOIN table2 t2 ON t2.id = t1.storeId
LEFT JOIN table3 t3 ON t3.id = t1.stateId
WHERE t1.date > '3monthsAgo' t2.otherId = 1 AND t2.anotherId = 2 AND t3.someId = 1

在我的实际查询中，这些连接和 where 子句要复杂得多，使这个查询不仅一团糟，而且非常慢(~4 秒)。我尝试用 CASE 语句重写它，现在有：

SELECT 
SUM(CASE t1.date > '1monthAgo' THEN t1.cost ELSE 0 END),
SUM(CASE t1.date > '2monthsAgo' THEN t1.cost ELSE 0 END),
SUM(CASE t1.date > '3monthsAgo' THEN t1.cost ELSE 0 END),
FROM table1 t1
LEFT JOIN table2 t2 ON t2.id = t1.storeId
LEFT JOIN table3 t3 ON t3.id = t1.stateId
WHERE t2.otherId = 1 AND t2.anotherId = 2 AND t3.someId = 1

这显示数据的方式略有不同(全部在一行中)，但它运行得更快(1 秒)，并且不会重复 JOIN 和 WHERE 子句。但是，我的真实查询在 4 个不同的日期范围内有 6 个不同的总和，因此有 24 个 CASE 语句，我认为这不是正确的方法。

是否可以有一个查询来共享 JOIN 和 WHERE 子句，但允许我跨不同的日期范围进行查询以收集总和？

Answer 1

一种选择是按EOMONTH分组，这为您提供每月一个日期，然后每个月都在单独的行中：

SELECT 
MonthEnding = EOMONTH(t1.date),
TotalCost   = SUM(t1.cost)
FROM table1 t1
LEFT JOIN table2 t2 ON t2.id = t1.storeId
LEFT JOIN table3 t3 ON t3.id = t1.stateId
WHERE t2.otherId = 1 AND t2.anotherId = 2 AND t3.someId = 1
GROUP BY EOMONTH(t1.date);

如果这些"月"实际上与日历月不一致，那么您可以加入带有月份编号的VALUES表

SELECT 
v.MonthsAgo,
TotalCost   = SUM(t1.cost)
FROM table1 t1
LEFT JOIN table2 t2 ON t2.id = t1.storeId
LEFT JOIN table3 t3 ON t3.id = t1.stateId
JOIN (VALUES
(CAST('20210521' AS date), CAST('20210621' AS date), 1),
(CAST('20210421' AS date), CAST('20210521' AS date), 2),
(CAST('20210321' AS date), CAST('20210421' AS date), 3)
) v(StartDate, EndDate, MonthsAgo)
ON t1.date >= v.StartDate AND t1.date < v.EndDate
WHERE t2.otherId = 1 AND t2.anotherId = 2 AND t3.someId = 1
GROUP BY v.MonthsAgo;