从单个表查询比较报表

Question

SQL 新手在这里，我在理解这个问题时遇到了一些麻烦。如何编写单个 SELECT 语句，其中我可以拥有带有自己的 WHERE 子句的列、进行计算并对结果进行分组。 我可以编写查询来汇总总数并按收入中心和财政年度进行平均检查，但我无法完全掌握如何与单个查询并排比较。

SALES DATA
| RevenueCenter | FiscalYear | TotalSales | NumChecks |
|---------------|------------|------------|-----------|
|  market       |    2019    |  2000.00   |    10     |
|  restaurant   |    2019    |  5000.00   |    25     |
|  restaurant   |    2020    |  4000.00   |    20     |
|  market       |    2020    |  3000.00   |    10     |

COMPARE REPORT
| RevenueCenter | TotalSales2020 | TotalSales2019 | %Change | AvgCheck2020 | AvgCheck2019 | %Change |
| market        |   3000.00      |   2000.00      |  +50%   |    300.00    |    200.00    |   +50%  |
| restaurant    |   4000.00      |   5000.00      |  -20%   |    200.00    |    200.00    |     0%  |

Answer 1

这会有帮助吗？没什么大不了的，只是用一些算术自我连接。

SQL> with sales (revenuecenter, fiscalyear, totalsales, numchecks) as
2  -- sample data
3    (select 'market'    , 2019, 2000, 10 from dual union all
4     select 'market'    , 2020, 3000, 10 from dual union all
5     select 'restaurant', 2019, 5000, 25 from dual union all
6     select 'restaurant', 2020, 4000, 20 from dual
7    )
8  -- query you need
9  select a.revenuecenter,
10    b.totalsales totalsales2020,
11    a.totalsales totalsales2019,
12    --
13    (b.totalsales/a.totalsales) * 100 - 100 "%change totalsal",
14    --
15    b.totalsales / b.numchecks avgcheck2020,
16    a.totalsales / a.numchecks avgcheck2019,
17    --
18    (b.totalsales / b.numchecks) /
19    (a.totalsales / a.numchecks) * 100 - 100 "%change numcheck"
20  from sales a join sales b on a.revenuecenter = b.revenuecenter
21    and a.fiscalyear < b.fiscalyear;
REVENUECEN TOTALSALES2020 TOTALSALES2019 %change totalsal AVGCHECK2020 AVGCHECK2019 %change numcheck
---------- -------------- -------------- ---------------- ------------ ------------ ----------------
market               3000           2000               50          300          200               50
restaurant           4000           5000              -20          200          200                0
SQL>