时间戳

首页 > 解决方案 > 使用 MAX 的子句不能像我在 HIVE 查询中所期望的那样工作

sql - 使用 MAX 的子句不能像我在 HIVE 查询中所期望的那样工作

问题描述

我正在尝试选择仅与表中的最大 DATE1 列和上个月匹配的记录。

我已经尝试使用标准的有子句语法来编写它，但这不起作用，所以我能够使用 CTE 获得我期望的结果。这个解决方案应该适用于我正在尝试做的事情，但我更想了解为什么 HAVING 子句不起作用。在这些示例中，MAX(DATE1)= 2018-02-28

我期望工作的查询

select
    ID,
    sum(money) as money,
    date1
from
    table1
group by
    ID,
    date1
having
    date1 between add_months(max(date1),-1) and max(date1)

这将返回与此类似的结果集

| ID | Money | date1      |
|----|-------|------------|
| 1  | 50    | 2017-12-31 |
| 2  | 600   | 2018-01-31 |
| 3  | 200   | 2018-02-28 |

这个使用 CTE 的查询返回预期的结果集

with period as (
    select
        max(date1) as maxdate1,
        add_months(max(date1),-1) as priordate1
    from
        table1 

select
    id,
    sum(money),
    date1
from
    table1
join period on
    1 = 1
where
    date1 between priordate1 and maxdate1
group by
    id,
    date1

预期结果集

| ID | Money | date1      |
|----|-------|------------|
| 1  | 50    | 2018-02-28 |
| 2  | 600   | 2018-01-31 |
| 3  | 200   | 2018-02-28 |

标签： sqlhadoophivehiveql

解决方案

您的代码不起作用，因为date1在group by. 您可以使用窗口函数来避免join：

select id, sum(money), maxdate1
from (select t1.*, max(date1) over () as maxdate1
      from table1 t1
     ) t1
where date1 between add_months(maxdate1, -1) and maxdate1
group by id, maxdate1

推荐阅读