[大数据]hive sql 统计每个pid上一次有交易的日期

情景：有交易的日期有记录，无交易的日期无记录
思路：数据表先自连接，再按日期和交易金额筛选数据；新数据表借助窗口函数，对每个pid按照每天之前有交易的日期排序，取最近的一天

set odps.sql.type.system.odps2=true;
DROP TABLE IF EXISTS table1 ;

CREATE TABLE table1 AS
SELECT  t1.partner_id
        ,t1.dt
        ,t1.trd_cnt
        ,t1.trd_amt
        ,t2.dt AS last_trd_date
FROM    what_f t1 INNER
JOIN    what_f t2
ON      t1.partner_id = t2.partner_id
WHERE   
t1.dt>t2.dt
AND    
 t2.trd_cnt > 0
;


CREATE TABLE table2 AS
SELECT  partner_id
        ,dt
        ,trd_amt
        ,trd_cnt
        ,last_trd_date
        ,COALESCE(
            DATEDIFF(TO_DATE(dt,'yyyy-mm-dd'),TO_DATE(last_trd_date,'yyyy-mm-dd'),'dd')
            ,0
        ) AS INTERVAL
        ,(
            28-datediff(to_date('20210515','yyyymmdd'), to_date(dt,'yyyy-mm-dd') , 'dd')
        ) AS dt_index,
        rn
FROM    (
            SELECT  partner_id
                    ,dt
                    ,trd_cnt
                    ,trd_amt
                    ,last_trd_date
                    ,ROW_NUMBER() OVER (PARTITION BY partner_id,dt ORDER BY last_trd_date DESC) AS rn
            FROM    table1
        ) 
WHERE   to_date(dt,'yyyy-mm-dd') BETWEEN to_date('2021-04-18','yyyy-mm-dd')
AND     to_date('2021-05-15','yyyy-mm-dd')
AND     rn = 1
;