Netezza LAST_VALUE 过滤器

发布于 2024-12-11 06:09:47 字数 622 浏览 2 评论 0原文

我正在尝试创建一个 NETEZZA 表，该表仅包含特定键的最新记录 - 例如，想象一个表（MYTABLE）如下：

Col1 Col2 TIMESTAMP
xxxx xxxx 13:45
xxxx xxxx 13:46
xxxx yyyy 10:00

我想返回一个表，如下所示：

Col1 Col2 TIMESTAMP
xxxx xxxx 13:46
xxxx yyyy 10:00

我猜我需要一些代码of：

  Create table MYNEWTABLE as
    select *
    from MYTABLE
    WHERE rowid in
    (
    SELECT LAST_VALUE(rowid)
    OVER (PARTITION BY COL1, COL2
          ORDER BY TIMESTAMP)
          FROM MYTABLE
    )
    ORDER BY COL1,COL2
    distribute on (COL1)

但这实际上不起作用，有人可以建议吗？（具体如何通过 col1 / col2 分区内时间戳的最后一个值过滤表）

原文

Am trying to create a NETEZZA table which has only the most recent records for a particular key - eg imagine a table (MYTABLE) as follows:

Col1 Col2 TIMESTAMP
xxxx xxxx 13:45
xxxx xxxx 13:46
xxxx yyyy 10:00

I would like to return a table as follows:

Col1 Col2 TIMESTAMP
xxxx xxxx 13:46
xxxx yyyy 10:00

I'm guessing I need some code along the lines of:

  Create table MYNEWTABLE as
    select *
    from MYTABLE
    WHERE rowid in
    (
    SELECT LAST_VALUE(rowid)
    OVER (PARTITION BY COL1, COL2
          ORDER BY TIMESTAMP)
          FROM MYTABLE
    )
    ORDER BY COL1,COL2
    distribute on (COL1)

However this isn't really working, can anyone please advise? (specifically how to filter the table by the last value of timestamp within the col1 / col2 partition)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

雨巷深深 2024-12-18 06:09:48

如果您尝试实现 SCD2 ..我的意思是仅将新记录从源表插入到目标表，那么您可以执行左外连接并将数据插入到目标。
如果您的要求不同，请纠正我

回复收藏 0 原文

慢慢从新开始 2024-12-18 06:09:47

明白了——终于！ rowid 是一个用词不当。感谢来自 Netezza 社区的 Shawn Fox 的灵感。

  Create table MYNEWTABLE as select * from
    (select *
            ,row_number() over (
                 partition by COL1, COL2 order by TIMESTAMP desc
                               ) row
       from MYTABLE 
    ) x
    WHERE x.row=1
    distribute on (COL1)

Got it - finally! rowid was a misnomer. Credit to Shawn Fox of Netezza Community for inspiration.

  Create table MYNEWTABLE as select * from
    (select *
            ,row_number() over (
                 partition by COL1, COL2 order by TIMESTAMP desc
                               ) row
       from MYTABLE 
    ) x
    WHERE x.row=1
    distribute on (COL1)

回复收藏 0 原文