时间戳

首页 > 解决方案 > 在加入 SQL Netezza 时使用窗口聚合函数

sql - 在加入 SQL Netezza 时使用窗口聚合函数

问题描述

请使用下面的 SQL 代码，我需要将TTS表与CMS表在哪里CMS.NEW_COLLECTED_DIGITS = TTS.FULLNUM加入，我也需要加入，TTS.LAST_UDATE_DATE with CMS.Call_Date但挑战是我需要获取TTS.LAST_UDATE_DATE between CMS.Call_Date AND CMS.Call_Date + '01:00:00'::"INTERVAL"并且 LAST_UDATE_DATE 在 CMS.Call_Date 周期间隔中有很多记录，我只需要获取 Max一，

下面的查询给了我这个错误：expression_tree_walker: Unexpected node type 801

像下面的示例

|FullNum    |LAST_UDATE_DATE    |Call_Date        |
|123456     |6/4/2020 18:10:11  |6/4/2020 18:00:11|
|123456     |6/4/2020 18:13:11  |6/4/2020 18:00:11|
|123456     |6/4/2020 18:14:11  |6/4/2020 18:00:11|
|123456     |6/4/2020 18:20:11  |6/4/2020 18:00:11|
|123456     |6/4/2020 18:30:11  |6/4/2020 18:00:11|
|123456     |6/4/2020 18:40:11  |6/4/2020 18:00:11|
|123456     |6/4/2020 18:50:11  |6/4/2020 18:00:11|

结果将是：

|FullNum    |LAST_UDATE_DATE    |Call_Date        |
|123456     |6/4/2020 18:50:11  |6/4/2020 18:00:11|

代码是

-- Step 1: 
-- Get Call Date From IVR with CMS.CALL_HEADER
 WITH CMS AS (
SELECT CALL_DATE, New_COLLECTED_DIGITS
FROM CMS),

-- Step 2
--Gett All TTS Logs Table
 TTS AS (
SELECT ITEM_ID, FullNum,  LAST_UDATE_DATE
FROM TTS)
-- Join CMS with TTS while ADSL_Phone_Num is matched and ticket log date is between call date and call date plus one hour
 SELECT *
FROM CMS
LEFT JOIN TTS ON
    CMS.NEW_COLLECTED_DIGITS = TTS.FULLNUM
    AND (FIRST_Value (TTS.LAST_UDATE_DATE) OVER (PARTITION BY TTS.ITEM_ID ORDER BY TTS.LAST_UDATE_DATE DESC )
    BETWEEN CMS.Call_Date AND CMS.Call_Date + '01:00:00'::"INTERVAL")

标签： sqljoinwindow-functionsnetezza

解决方案

嗯。. . 从您描述问题的方式来看，您可以使用 ajoin然后row_number()选择每个 cms 通话记录的最新记录：

select ct.*
from (select . . . , -- list the columns you want here,
             row_number() over (partition by cms.new_collect_digits, cms.call_date order by tts.last_update_date desc) as seqnum
      from cms left join
           tts
           on cms.new_collect_digits = tts.fullnum and
              tts.last_update_date between cms.call_date and cms.call_date + '01:00:00'::"INTERVAL"
     ) ct
where seqnum = 1;

推荐阅读