时间戳

首页 > 解决方案 > SQL 在 MAX 之后执行 AVG

sql - SQL 在 MAX 之后执行 AVG

问题描述

我有两张桌子。Table1：

| ID1 | ID2 | ID3 | ID4 |
|-----+-----+-----+-----|
| 200 | 125 | 300 | 201 |
| 206 | 128 | 650 | 261 |
| 230 | 543 | 989 | 403 |

和Table2：

| ID1 | ID2 | ID3 | ID4 |  Date  | Cost |
|-----+-----+-----+-----+--------+------|
| 200 | 125 | 300 | 201 | 1/1/19 | 0.32 |
| 200 | 125 | 300 | 201 | 1/1/19 | 0.33 |
| 200 | 125 | 300 | 201 | 1/1/19 | 0.34 |
| 200 | 125 | 300 | 201 | 1/2/13 | 0.00 |
| 200 | 125 | 300 | 201 | 9/5/05 | 0.01 |

我试图Join Table1在Table2过滤输出的同时，只显示这些分类的最大值的一行，date并且还显示该最大日期的平均值cost。这是我当前的代码：

SELECT t1.ID1, t1.ID2, t1.ID3, t1.ID4, maxDate, avgCost
FROM Table1 t1
JOIN ( SELECT ID1, ID2, ID3, ID4, MAX(Date) as maxDate, AVG(Cost) as avgCost
FROM Table2 t2
GROUP BY ID1, ID2, ID3, ID4 ) t2
ON t2.ID1 = t1.ID1
AND t2.ID2 = t1.ID2
AND t2.ID3 = t1.ID3
AND t2.ID4 = t1.ID4

使用上面给出的示例数据，我的结果如下所示：

| ID1 | ID2 | ID3 | ID4 |  MaxDate  | AvgCost |
|-----+-----+-----+-----+-----------+---------|
| 200 | 125 | 300 | 201 |  1/1/19   |   0.20  |

当它应该看起来像这样

| ID1 | ID2 | ID3 | ID4 | MaxDate   | AvgCost |
|-----+-----+-----+-----+-----------+---------|
| 200 | 125 | 300 | 201 |   1/1/19  |   0.33  |

平均成本包括date未达到最大值的值。我假设这是由于在过滤AVG(cost)之前执行的。这是我尝试过的：Table2MAX(Date)

SELECT t1.ID1, t1.ID2, t1.ID3, t1.ID4, maxDate, avgCost
FROM Table1 t1
JOIN ( SELECT ID1, ID2, ID3, ID4, MAX(Date) as maxDate, AVG(Cost) as avgCost
FROM Table2 t2
GROUP BY ID1, ID2, ID3, ID4 ) t2
ON t2.ID1 = t1.ID1
AND t2.ID2 = t1.ID2
AND t2.ID3 = t1.ID3
AND t2.ID4 = t1.ID4
WHERE maxDate = (SELECT MAX(Date) from Table2);

和

SELECT t1.ID1, t1.ID2, t1.ID3, t1.ID4, maxDate, avgCost
FROM Table1 t1
JOIN ( SELECT ID1, ID2, ID3, ID4, MAX(Date) as maxDate, AVG(Cost) as avgCost
FROM Table2 t2
GROUP BY ID1, ID2, ID3, ID4 ) t2
ON t2.ID1 = t1.ID1
AND t2.ID2 = t1.ID2
AND t2.ID3 = t1.ID3
AND t2.ID4 = t1.ID4
WHERE maxDate = (SELECT MAX(Date) from Table2 GROUP BY ID1, ID2, ID3, ID4);

第一个没有结果，第二个导致错误，ORA-01427: single-row subquery returns more than one row. 我尝试的其余部分基本上是上述的变体，但我仍然没有得到预期的结果。我不确定如何使该AVG功能仅Date在其最大值处执行...

标签： sqloraclejoinmaxaverage

解决方案

您的定义t2如下所示：

( SELECT ID1, ID2, ID3, ID4, MAX(Date) as maxDate, AVG(Cost) as avgCost
FROM Table2 t2
GROUP BY ID1, ID2, ID3, ID4 ) t2

相反，要仅计算最近日期的平均值，它应该使用不同的聚合函数 -LAST函数，如下所示：

( SELECT ID1, ID2, ID3, ID4, MAX(Date) as maxDate, 
         AVG(Cost) KEEP (DENSE_RANK LAST ORDER BY Date) as avgCost
FROM Table2 t2
GROUP BY ID1, ID2, ID3, ID4 ) t2

推荐阅读