情景:有交易的日期有记录,无交易的日期无记录 思路:先借助窗口函数生成一列连续递增的数字;将日期与数字列相减,如果日期是连续的,那差值就是一样的;统计差值列相同取值的最大个数就是最长连续交易天数
SELECT partner_id
,MAX(count_val) AS max_count
FROM (
SELECT partner_id
,symbol_date
,COUNT(*) AS count_val
FROM (
SELECT partner_id
,dt
,date_sub(dt, CAST(rn AS INT)) AS symbol_date
FROM (
SELECT partner_id
,to_date(dt,'yyyymmdd') AS dt
,ROW_NUMBER() OVER(PARTITION BY partner_id ORDER BY to_date(dt,'yyyymmdd')) AS rn
FROM t_base1
) t1
) t2
GROUP BY partner_id
,symbol_date
) t3
GROUP BY partner_id
;
参考链接:hive实现用户连续登陆的最大天数
碎碎念:今天建军节,之前这个节日对我来说是热血沸腾的红,这两年过来,这个庄重的节日有了更多样的色彩,有了更亲切的温度。人生旅程,一定要珍惜有缘相遇的人呐!
|