SQL查询不产生结果

Question

我有一个表，记录了各种集群的大小以及集群被扫描的日期。我需要根据最近的扫描日期获得每个集群在每个月的大小。我尝试在Impala SQL中执行以下查询，但没有产生结果。

Scandata    cluster  Size
11/4/2017   ABC 200
11/18/2017  ABC 700
11/25/2017  ABC 1009
12/4/2017   ABC 200
12/18/2017  ABC 700
12/20/2017  ABC 1100
1/4/2018    ABC 200
1/18/2018   ABC 700
1/20/2018   ABC 1009
11/4/2017   CAD 200
11/18/2017  CAD 700
11/25/2017  CAD 1009
12/4/2017   CAD 200
12/18/2017  CAD 700
12/20/2017  CAD 1100

预期结果

Data    cluster  Size
11/25/2017  ABC 1009
12/20/2017  ABC 1100
1/20/2018   ABC 1009
11/25/2017  CAD 1009
12/20/2017  CAD 1100


SELECT t.*
FROM arxview.test_summary t
INNER JOIN
(SELECT MONTH(scandate) AS month, MAX(DAY(scandate)) AS day, cluster
 FROM arxview.test_summary t
 GROUP BY MONTH(scandate), cluster) sub
ON (MONTH(t.scandate) = sub.month AND DAY(t.scandate) = sub.day AND t.cluster = sub.cluster)

Answer 1

另一种方法是使用窗口函数：

select ts.*
from (select ts.*,
             max(scandate) over (partition by year(scandate), month(scandate) as max_scandate_monthly
      from arxview.test_summary t
     ) ts
where scandate = max_scandate_monthly;