hive hql：销售线索然后创建表，获得错误的销售线索数据

7nbnzgx9 于 2023-02-04 发布在 Hive

关注(0)|答案(1)|浏览(205)

不良情况：

CREATE TABLE IF NOT EXISTS tmp12 AS
   WITH tmp_table AS
(
SELECT dt, time, LEAD(time,1,'99999') OVER(PARTITION BY urs) AS last_time, urs, tag FROM
    (
        SELECT dt, urs, time, tag, count(1) FROM table0 LIMIT 10000
    ) t1
GROUP BY dt, urs, time, tag
ORDER BY urs, time
)

然后

select * from tmp12

我得到错误的数据，例如：
WRONG RESULT(dt, time, last_time, urs)
有一些last_time〈time。
当我删除CREATE TABLE时，我得到了一个很好的案例：

SELECT dt, time, LEAD(time,1,'99999') OVER(PARTITION BY  urs) AS last_time, urs, tag FROM
    (
        SELECT dt, urs, time, tag, count(1) FROM table0 LIMIT 10000
    ) t1
GROUP BY dt, urs, time, tag
ORDER BY urs, time

正确结果如下：
GOOD RESULT(dt, time, last_time, urs)
所有的最后一次。
为什么？我只是创建了一个表，然后从表中选择，但是last_time出错了？

Hive

来源：https://stackoverflow.com/questions/75319259/hql-lead-then-create-table-get-wrong-lead-data

1条答案

按热度按时间

hmae6n7t1#

你能试试这个吗：

CREATE TABLE IF NOT EXISTS tmp12 AS
   WITH tmp_table AS
(
SELECT dt, time, LEAD(time,1,'99999') OVER(PARTITION BY urs ORDER BY time) AS last_time, urs, tag FROM
    (
        SELECT dt, urs, time, tag, count(1) FROM table0 LIMIT 10000
    ) t1
GROUP BY dt, urs, time, tag
ORDER BY urs, time
)

我猜窗口函数中缺少的order by是问题所在。

赞(0）回复(0）举报 2023-02-04

我来回答

hive hql：销售线索然后创建表，获得错误的销售线索数据

1条答案

相关问题

热门标签

最新问答