编辑代码

CREATE DATABASE test;
use test;
CREATE TABLE page_log (
  uid        varchar(10),
  time       varchar(30),
  page_name  varchar(30),
  refer_page varchar(30)
);

INSERT INTO page_log(uid, time, page_name, refer_page) VALUES
('a','2024-01-01','index_home_page','login_page'),
('a','2024-01-01','app_interest_select','index_home_page'),
('b','2024-01-01','cp_page_live','index_home_page'),
('c','2024-01-01','index_home_page','login_page'),
('e','2024-01-01','app_interest_select','index_home_page'),
('d','2024-01-01','cp_page_live','index_home_page'),
('a','2024-01-02','app_interest_select','cp_page_live'),
('a','2024-01-02','index_home_page','cp_page_live'),
('b','2024-01-02','cp_page_live','index_home_page'),
('c','2024-01-02','index_home_page','login_page'),
('c','2024-01-02','app_interest_select','index_logout_page'),
('d','2024-01-02','cp_page_live','index_home_page');

/*二 app里当用户在进入页面时,会有如上上报信息
1、需要统计每天当前页是 index_home_page,或者来源页是 index_home_page 的用户*/
-- select time,uid
-- from page_log
-- where page_name='index_home_page' or refer_page='index_home_page'
-- group by time,uid;

select uid
from(
select uid,time
from page_log
where page_name='index_home_page' or refer_page='index_home_page'
group by time,uid) as t
group by uid
having count(time)=2;

/*2、需要统计每天当前页是 index_home_page 的用户*/
select uid
from(
select uid,time
from page_log
where page_name='index_home_page'
group by time,uid) as t
group by uid
having count(time)=2;

/*3、需要统计每天来源页是 index_home_page 的用户*/
select uid
from(
select uid,time
from page_log
where refer_page='index_home_page'
group by time,uid) as t
group by uid
having count(time)=2;