pt.daily.no.003

博主分享了自己在Java领域的学习经历,目前正专注于模式和数据结构的学习,并计划补充数据库方面的知识。
我无视论坛那些天书一样都技术讨论,我坚信一步一个脚印都走下去会成功。J2SE学的差不多了,重点放在模式和数据结构上了,有空补补数据库,这东西没碰过
WITH daily_calls AS ( -- 先按手机号和日期聚合,得到每天的拨打次数 SELECT phone_no, TO_CHAR(send_time, 'yyyymmdd') AS call_date, COUNT(*) AS daily_count FROM ( -- 去除重复记录 SELECT DISTINCT phone_no, send_time, id, pt, mkt_type FROM za_ab.cdm_ab_mkt_policy_dd_all WHERE pt = max_pt('za_ab.cdm_ab_mkt_policy_dd_all') AND mkt_type = '智能外呼' AND send_time >= TO_CHAR('202401', 'yyyymm') AND send_time < TO_CHAR('202507', 'yyyymm') -- 用开区间更高效 ) t GROUP BY phone_no, TO_CHAR(send_time, 'yyyymmdd') ), rolling_7days AS ( -- 计算每个手机号在连续7天窗口内的总拨打次数 SELECT phone_no, call_date, SUM(daily_count) OVER ( PARTITION BY phone_no ORDER BY call_date RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW ) AS total_7day_calls FROM daily_calls ) -- 筛选出7天内拨打超过2次的手机号及其相关日期 SELECT DISTINCT phone_no, MIN(call_date) OVER (PARTITION BY phone_no) AS first_date_in_window, MAX(call_date) OVER (PARTITION BY phone_no) AS last_date_in_window, total_7day_calls FROM rolling_7days WHERE total_7day_calls > 2 ORDER BY phone_no, first_date_in_window; 报错了:ODPS-0130071:[32,27] Semantic analysis exception - INTERVAL_DAY_TIME type is not enabled in current mode. Please set odps.sql.type.system.odps2=true to use it. ODPS-0130071:[31,13] Semantic analysis exception - offset type STRING is invalid, only numeric or temporal type is allowed in RANGE windowing clause 目标是查询连续 7 天内同一个手机号拨打超过 2 次
09-16
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值