相当于impala中的first()和last()

zynd9foi 于 2021-07-15 发布在 Hadoop

关注(0)|答案(2)|浏览(622)

我来自不同传感器单元的数据与hadoop中的unix\u time stam相混合。使用first和last语句很容易获得sql中每天的第一个和最后一个值，如下所示。在这里我得到了一天开始和结束时的温度。

SELECT unit, FIRST(Temp) as Start_Day_Value, LAST(Temp) as End_Day_Value
FROM Sensor_Data.Table
WHERE cast(ts/1000 as TIMESTAMP) BETWEEN "2021-01-05 00:00:00" AND "2021-01-05 23:59:59"
GROUP BY unit
ORDER BY unit;

在impala中是否有任何等效的命令，以便我可以像在sql中一样获得每列的第一个和最后一个值。

sql hadoop mysql impala select

来源：https://stackoverflow.com/questions/66046661/equivalent-of-first-and-last-in-impala

2条答案

按热度按时间

j2qf4p5b1#

标准sql具有 FIRST_VALUE() 窗口函数，但不是聚合的等价函数。所以，在impala中或者任何其他数据库中，有一种方法是：

SELECT DISTINCT unit,
       FIRST_VALUE(Temp) OVER (PARTITION BY unit ORDER BY ts) as Start_Day_Value, 
       FIRST_VALUE(Temp) OVER (PARTITION BY unit ORDER BY ts DESC) as End_Day_Value
FROM Sensor_Data.Table
WHERE cast(ts/1000 as TIMESTAMP) >= '2021-01-05' AND 
      cast(ts/1000 as TIMESTAMP) < '2021-01-06'
ORDER BY unit;

注意，我还简化了日期比较。
注：我不确定 cast() 工作（我希望代码使用 from_timestamp() ). 但这不是你要问的。
编辑：
在 Impala ，你可以使用 GROUP BY :

SELECT u.unit,
       MAX(CASE WHEN seqnum_asc = 1 THEN temp END) as first_temp,
       MAX(CASE WHEN seqnum_asc = 1 THEN temp END) as lasst_temp
FROM (SELECT unit,
             ROW_NUMBER() OVER (PARTITION BY unit ORDER BY ts) as seqnum_asc, 
             ROW_NUMBER() OVER (PARTITION BY unit ORDER BY ts desc) as seqnum_desc _Day_Value
      FROM Sensor_Data.Table
      WHERE cast(ts/1000 as TIMESTAMP) >= '2021-01-05' AND 
            cast(ts/1000 as TIMESTAMP) < '2021-01-06'
     ) u
GROUP BY unit
ORDER BY unit;

赞(0）回复(0）举报 2021-07-15

tzdcorbm2#

我用以下方法解决了这个问题：

SELECT DISTINCT u.unit, u.initial_temp, u.final_temp, u.initial_p, u.final_p
FROM(SELECT unit,
first_value(temperature) OVER(PARTITION BY unit ORDER BY ts DESC) as final_temp,
first_value(temperature) OVER(PARTITION BY unit ORDER BY ts ASC) as initial_temp,
first_value(pressure) OVER(PARTITION BY unit ORDER BY ts DESC) as final_p,
first_value(pressure) OVER(PARTITION BY unit ORDER BY ts ASC) as initial_p
FROM Sensor_Data.Table
WHERE cast(ts/1000 as TIMESTAMP) BETWEEN "2020-12-11 00:00:00" AND "2020-12-11 23:59:59"
) AS u
ORDER BY unit;

赞(0）回复(0）举报 2021-07-15

我来回答

相当于impala中的first()和last()

2条答案

相关问题

热门标签

最新问答