针对一个表的SQL多个WHERE子句，带有CASE语句

Question

我编写了这个查询，它似乎正在收集正确的结果，然而，它也需要很长时间。我只是想知道是否有办法让它更有效率？(我知道这是低效的，因为它正在创建数据表并将它们重新连接在一起——我只是不知道如何绕过它，特别是在包括CASE问题的情况下(。

我在Excel中使用ODBC连接到AS400。问号允许用户在Excel单元格中输入参数。

with W as
(
select yr as YEAR, pd as PERIOD, sum(amt1 + amt2 + amt3 + amt4) as SALES
from TABLE
group by yr, pd
order by yr desc, pd desc
), X as
(
select yr as YEAR, pd as PERIOD, sum(amt1 + amt2 + amt3 + amt4) as BSALES
from TABLE
where type = 'B'
group by yr, pd
order by yr desc, pd desc
), Y as
(
select
CASE WHEN pd = 1 THEN yr - 1 ELSE yr END as YEAR, 
CASE WHEN pd = 1 THEN 12 ELSE pd - 1 END as PERIOD, 
SUM(OM) as MODOM
from TABLE
group by yr, pd
order by yr desc, pd desc
), Z as
(
select
CASE WHEN pd = 1 THEN yr - 1 ELSE yr END as YEAR, 
CASE WHEN pd = 1 THEN 12 ELSE pd - 1 END as PERIOD, 
SUM(BOM) as BMODOM
from TABLE
where type = 'B'
group by yr, pd
order by yr desc, pd desc
)
select w.YEAR, w.PERIOD, w.SALES, x.BSales, y.MODOM, z.BMODOM
from w inner join x
on w.YEAR = x.YEAR and w.PERIOD = x.PERIOD
inner join y
on w.YEAR = y.YEAR and w.PERIOD = y.PERIOD
inner join z
on w.YEAR = z.YEAR and w.PERIOD = z.PERIOD
where w.YEAR between ? and ? and w.PERIOD between ? and ?
order by YEAR desc, PERIOD desc

出于隐私考虑，我不得不稍微修改一下代码，但我相信这一切都是正确的。

示例数据：

Yr  Pd  Type    Amt OM
18  2   A       45  181
18  2   B       33  163
18  2   A       40  153
18  1   B       39  136
18  1   B       24  142
18  1   B       53  143
18  1   A       41  186
18  1   A       78  197
17  12  A       98  139
17  12  A       54  159
17  12  B       78  181
17  12  B       45  101
17  11  A       28  134
17  11  A       77  192
17  11  A       75  110
17  11  B       60  135
17  11  B       83  170
17  10  B       72  114
17  10  A       26  118
17  10  A       95  111
17  9   A       12  112
17  9   B       14  171

示例结果

Yr  Pd  Sales   Bsales  MODOM   BMODOM
18  2   118     33      804     421
18  1   235     116     580     282
17  12  275     123     741     305
17  11  323     143     343     114
17  10  193     72      283     171

请注意，我需要总销售额，然后是B类销售额。我还需要一个TOTAL级别的OM，然后还有一个B类级别——无论如何——我需要抵消一个周期。因此，17-10的MODOM反映了表中17-9期间的OM总和。(我希望这是有道理的(。

编辑我把这个倒过来了。17-10的MODOM实际上会反映17-11的OM值，而不是相反。更正预期结果。

Yr  Pd  Sales   Bsales  MODOM   BMODOM
18  2   118     33      0       0
18  1   235     116     497     163
17  12  275     123     804     421
17  11  323     143     580     282
17  10  193     72      741     305

Answer 1

因此，使用条件聚合，您至少可以去掉2个常用表表达式，这将有所帮助。在最终的演示查询之前不排序，而不是在每个通用表表达式中排序也会有所帮助。使用一个实际日期，例如一个时期的第一个月的第一天，可以帮助消除在定义前一个时期时可能代价高昂的案例表达式。

写这篇文章有几种方法，但这将给您一个条件聚合的例子。

注意LEFT JOIN而不是INNER，因为如果你使用INNER ，你的第一个句点总是会从你的查询中掉下来

WITH PeriodSales AS(
SELECT
yr as YEAR
,pd AS PERIOD
,SUM(amt) as SALES
,SUM(CASE WHEN type = 'B' THEN amt END) as BSALES
FROM
Table
GROUP BY
yr
,pd
)
, PreviousPeriod AS 
(
SELECT
CASE WHEN pd = 12 THEN yr + 1 ELSE yr END as YEAR
,CASE WHEN pd = 12 THEN 1 ELSE pd + 1 END as PERIOD
,SUM(OM) as MODOM
,SUM(CASE WHEN type = 'B' THEN OM END) as BMODOM
FROM
Table
GROUP BY
CASE WHEN pd = 12 THEN yr + 1 ELSE yr END
,CASE WHEN pd = 12 THEN 1 ELSE pd + 1 END
)
SELECT
ps.YEAR
,ps.PERIOD
,ps.SALES
,ps.BSALES
,pp.MODOM
,pp.BMODOM
FROM
PeriodSales ps
LEFT JOIN PreviousPeriod pp
ON ps.YEAR = pp.YEAR
AND ps.PERIOD = pp.PERIOD
ORDER BY
ps.YEAR DESC
,ps.PERIOD DESC

根据您的编辑，要与"上一个周期"对齐以获得您想要的OM金额，您实际上需要添加一个周期，而不是在我使用的示例中减去一个周期。我已经测试过了，它确实有效。如果不了解更多关于表和执行计划等的信息，我们可能无法发现许多其他性能因素。