5,889
社区成员
发帖
与我相关
我的任务
分享
select
session_id,
substr(LOGIN_TIME,1,14)||'00' TIME_ID,
LOGIN_TIME,
(select max(LOGIN_TIME) from foc_session where USER_ID=t.USER_ID and LOGIN_TIME<t.LOGIN_TIME) LAST_LOGIN_TIME,
0,
DISTRICT_ID,
AGE_ID,
JOB_ID,
ASSETS_ID,
VISIT_FLAG,
SEX_ID,
coalesce(SCREEN_ID,'99'),
ORG_ID,
to_char(to_date(substr(login_time,1,10),'YYYY-MM-DD'),'D')-1 WEEK_ID ,
(select sum(REMAIN_TIME) from FOC_TRAN_MKT_ALL_ACTION where session_id=t.session_id) REMAIN_TIME,
coalesce(BROWSER_ID,'99'),
coalesce(OS_ID,'99'),
EDUCATE_ID,
USER_ID CUST_ID,
sum(COUNTFOCID) COUNT_PV,
sum((select sum(case SESSION_FLOG when '2' then 1 else 0 end) QUIT_FLAG from FOC_TRAN_MKT_ALL_ACTION where session_id=t.session_id)) COUNT_OUTOF
from FOC_TRAN_MKT_ALL_SESSION t
group by
session_id,
LOGIN_TIME,
DISTRICT_ID,
AGE_ID,
JOB_ID,
ASSETS_ID,
VISIT_FLAG,
SEX_ID,
SCREEN_ID,
ORG_ID,
logout_time,
BROWSER_ID,
OS_ID,
EDUCATE_ID,
USER_ID;
sum((select sum(case SESSION_FLOG when '2' then 1 else 0 end) QUIT_FLAG from FOC_TRAN_MKT_ALL_ACTION where session_id=t.session_id)) COUNT_OUTOF