一些复杂却常用的指标取法
持续更新~
一、复购周期
SELECT memberid,createdatetime,
DATEDIFF(a.createdatetime,
(SELECT MAX(b.createdatetime)
FROM member_Repurchase_cycle_duble b
WHERE b.memberid=a.memberid
AND b.createdatetime<a.createdatetime)) AS diff
FROM member_Repurchase_cycle_duble a
ORDER BY memberid,createdatetime
二、数组展开
select
json_tuple(item, 'b')
from
(
select explode_outer(list) as item
from( select
from_json(b,'ARRAY<STRING>') as list
from a.xxx
where dt=get_dt_date(get_date(-1))
)a
)b
三、presto 与 spark
- presto 只能使用 cast as int ,不能变成 string
- spark 取昨天 sysdate(‘yyyyMMdd’,-1),presto 取昨天 current_date- interval ‘1’ day
- spark 变换日期类型 get_date() / get_dt_date()