转换成odps sql

以下是将该SQL语句转换为ODPS SQL语句的示例：

SELECT user_id,client_id,client_platform,to_char(cast(access_time as date),'yyyymmdd') as access_dt,mode,
       NULL as action,info_id,NULL as entrance,NULL as info_type,NULL as is_detail,0 as duration,NULL as refresh_id,
       NULL as info_height,NULL as browse_height,'page' as from_table
FROM basic.dw_bi_page_fact
WHERE dt <= to_char(dateadd('day',7,'${input_job_ymd}'),'yyyymmdd') 
  AND dt >= to_char(dateadd('day',-7,'${input_job_ymd}'),'yyyymmdd') 
  AND to_char(cast(access_time as date),'yyyymmdd') <= '${input_job_ymd}'
  AND to_char(cast(access_time as date),'yyyymmdd') >= to_char(dateadd('day',-7,'${input_job_ymd}'),'yyyymmdd')
  AND datediff('day',cast(to_char(access_time,'yyyy-MM-dd') as date),cast(dt as date)) BETWEEN 0 AND 7
  AND page_code = 3 
  AND page_name IN (SELECT code FROM basic.dim_app_page_merge WHERE dt='${yester_date}' AND unified_page='MixCommunityTab')

在ODPS SQL中，日期函数、字符串函数和日期格式化函数与Spark SQL略有不同，需要进行相应的修改。同时，使用的日期函数和字符串函数的参数顺序也与Spark SQL略有不同。另外，ODPS SQL中的日期函数和日期格式化函数都需要使用 cast 函数进行类型转换。在IN子句中，ODPS SQL使用的是子查询的方式来进行数据的筛选。