SELECT p.dt,p.types,count( distinct p.vid)uv,count(distinct s.vid)cartuv from
(select p.dt,p.vid,p2.types,p.page_page_id from
(select * from
mds_log_weblog_pageview p
where p.dt>=‘2021-12-01’
and p.dt<‘2021-12-05’
and p.is_bot is null
and p.country_id<>‘CN’
and p.site in (‘www’,‘wap’,‘app’)
and p.event_type<>‘native_page_end’
and p.user_id is NOT NULL
and p.user_id<>’’ ) p
inner join(
SELECT DISTINCT p.vid,p.dt,
case when b.first_order_date is NOT NULL and to_date(b.first_order_date)<p.dt then ‘Old’ else ‘New’ end types
from (select * from
mds_log_weblog_pageview p
where p.dt>=‘2021-12-01’
and p.dt<‘2021-12-05’
and p.is_bot is null
and p.country_id<>‘CN’
and p.site in (‘www’,‘wap’,‘app’)
and p.event_type<>‘native_page_end’
and p.user_id is NOT NULL
and p.user_id<>’’ ) p
INNER join(
SELECT b.buyer_id,b.first_order_date,b.buyer_class
from mds_buyer_info b
where b.dt=‘2021-12-22’
)b on p.user_id=b.buyer_id
)p2 on p.dt=p2.dt and p.vid=p2.vid
where p.dt>=‘2021-12-01’
and p.dt<‘2021-12-05’
and p.is_bot is null
and p.country_id<>‘CN’
and p.site in (‘www’,‘wap’,‘app’)
and p.event_type<>‘native_page_end’)p
left join(
SELECT s.dt,s.vid
from mds_log_weblog_server s
where s.dt>=‘2021-12-01’
and s.dt<‘2021-12-05’
and s.event_type=‘cart’
)s on s.dt=p.dt and s.vid=p.vid ~~~~和 on s.vid=p.vid and s.dt=p.dt 是两个结果
GROUP BY 1,2
order by 2,1
dt在前vid在后.txt (5.2 MB) vid在前dt在后.txt (5.2 MB)
关闭cbo后查询结果正常