with data1 as (
select distinct usr_id,
date(login_time) as login_date
from user_login_log
),
data2 as (
select usr_id,
login_date,
first_value(login_date) over (partition by usr_id order by login_date) as `start_date`,
lag(login_date) over (partition by usr_id order by login_date) as `pre_date`
from data1
),
data3 as (
select distinct data1.usr_id,
data1.login_date,
case
when pre_date is null and datediff(data1.login_date,start_date) = 0 then 'flag-1'
when pre_date is not null and datediff(data1.login_date,pre_date) <= 3 then 'flag-2'
else 'flag-3'
end as flag
from data1
left join data2
on (data1.usr_id,data1.login_date) = (data2.usr_id,data2.login_date)
),
data4 as (
select login_date,
round((sum(if(flag = 'flag-1', 1, 0)) / count(1)) * 100, 2) as rate_flag_1,
round((sum(if(flag = 'flag-2', 1, 0)) / count(1)) * 100, 2) as rate_flag_2,
round((sum(if(flag = 'flag-3', 1, 0)) / count(1)) * 100, 2) as rate_flag_3
from data3
group by login_date
)
select login_date,
concat_ws(',', rate_flag_1, rate_flag_2, rate_flag_3) as pct
from data4
where year(login_date) = '2024'
group by login_date
order by login_date;
求指点这个思路的错误呀，一直是0过不去
with tmp1 as (
select
distinct
usr_id,
date(login_time) as login_date
from user_login_log
where login_time >= date_sub(curdate(), interval 30 day)
)
select
usr_id,
start_date,
date_add(start_date, interval cnt-1 day) as end_date,
cnt as consecutive_days
from(
select
usr_id,
start_date,
count(*) as cnt
from(
select
usr_id,
date_sub(login_date, interval rn day) as start_date
from(
select
usr_id,
login_date,
row_number() over(partition by usr_id order by login_date) as rn
from tmp1
)t1
)t2
group by usr_id,start_date
)t3
where cnt >= 3;
with tmp1 as (
select
distinct
usr_id,
date(login_time) as login_date
from user_login_log
where login_time >= date_sub(curdate(), interval 30 day)
)
select
usr_id,
start_date,
date_add(start_date, interval cnt day) as end_date,
cnt as consecutive_days
from(
select
usr_id,
start_date,
count(*) as cnt
from(
select
usr_id,
date_sub(login_date, interval rn day) as start_date
from(
select
usr_id,
login_date,
row_number() over(partition by usr_id order by login_date) as rn
from tmp1
)t1
)t2
group by usr_id,start_date
)t3
where cnt >= 3;
with tmp1 as (
select
distinct
usr_id,
date(login_time) as login_date
from user_login_log
where login_time >= date_sub(current_date(), interval 30 day)
)
select
usr_id,
start_date,
date_add(start_date, interval cnt day) as end_date,
cnt as consecutive_days
from(
select
usr_id,
start_date,
count(*) as cnt
from(
select
usr_id,
date_sub(login_date, interval rn day) as start_date
from(
select
usr_id,
login_date,
row_number() over(partition by usr_id order by login_date) as rn
from tmp1
)t1
)t2
group by usr_id,start_date
)t3
where cnt >= 3;
select
t3.live_id,
ks_live_t2.live_nm,
max_cnt as max_online_users,
min(log_time) as first_peak_time,
max(log_time) as last_peak_time
from(
select
t2.live_id,
log_time,
cur_cnt,
max(cur_cnt) over(partition by live_id) as max_cnt
from(
select
live_id,
log_time,
sum(flag) over(partition by live_id order by log_time) as cur_cnt
from(
select
live_id,
enter_time as log_time,
1 as flag
from ks_live_t1
union all
select
live_id,
leave_time as log_time,
-1 as flag
from ks_live_t1
)t1
)t2
)t3
left join ks_live_t2 on t3.live_id = ks_live_t2.live_id
where cur_cnt = max_cnt
group by live_id,max_cnt,live_nm;
select
tt1.live_id,
ks_live_t2.live_nm,
max(cur_cnt) as max_online_users
from(
select
live_id,
sum(flag) over(partition by live_id order by log_time) as cur_cnt
from(
select
live_id,
enter_time as log_time,
1 as flag
from ks_live_t1
union all
select
live_id,
leave_time as log_time,
-1 as flag
from ks_live_t1
)t1
)tt1
left join ks_live_t2 on tt1.live_id = ks_live_t2.live_id
group by live_id,live_nm
WITH OnlineUsers AS (
SELECT
t1.live_id,
t1.usr_id
FROM
ks_live_t1 t1
WHERE
'2021-09-12 23:48:38' BETWEEN t1.enter_time AND t1.leave_time
)
SELECT
ou.live_id,
t2.live_nm,
count(distinct ou.usr_id) as online_users
FROM
OnlineUsers ou
JOIN
ks_live_t2 t2
ON
ou.live_id = t2.live_id
GROUP BY
ou.live_id, t2.live_nm
ORDER BY
online_users DESC,live_id
;
WITH OnlineUsers AS (
SELECT
t1.live_id,
t1.usr_id
FROM
ks_live_t1 t1
WHERE
'2021-09-12 23:48:38' BETWEEN t1.enter_time AND t1.leave_time
)
SELECT
ou.live_id,
t2.live_nm,
count(distinct ou.live_id) as online_users
FROM
OnlineUsers ou
JOIN
ks_live_t2 t2
ON
ou.live_id = t2.live_id
GROUP BY
ou.live_id, t2.live_nm
ORDER BY
online_users DESC,live_id
;
with tmp as (
select
live_id,
enter_time,
leave_time
from ks_live_t1
where
date_format(enter_time, '%Y-%m-%d %H:%i:%s') <= '2021-09-12 23:48:38'
and date_format(leave_time, '%Y-%m-%d %H:%i:%s') >= '2021-09-12 23:48:38'
)
select
tt1.live_id,
ks_live_t2.live_nm,
tt1.online_users
from(
select
live_id,
sum(flag) as online_users
from(
select
live_id,
enter_time as log_time,
1 as flag
from tmp
union all
select
live_id,
leave_time as log_time,
-1 as flag
from tmp
)t1
group by live_id
)tt1
left join ks_live_t2 on tt1.live_id = ks_live_t2.live_id
;
select
distinct
usr_id
from(
select
usr_id,
login_date,
lag(login_date,3)over(partition by usr_id order by login_date) as pre_date
from(
select
usr_id,
date_format(trx_time,'%Y-%m-%d') as login_date
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲'
)a
)b
where datediff(login_date,pre_date) <= 3
order by usr_id asc;
select
usr_id
from(
select
usr_id,
login_date,
lag(login_date,3)over(partition by usr_id order by login_date) as pre_date
from(
select
usr_id,
date_format(trx_time,'%Y-%m-%d') as login_date
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲'
)a
)b
where datediff(login_date,pre_date) <= 2
order by usr_id asc;
select
usr_id
from(
select
usr_id,
login_date,
lag(login_date,3)over(partition by usr_id order by login_date) as pre_date
from(
select
usr_id,
date_format(trx_time,'%Y-%m-%d') as login_date
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲'
)a
)b
where datediff(login_date,pre_date) <= 3
order by usr_id asc;
select
c.live_id,
live_nm,
max_online_users
from(
select
live_id,
max(cur_cnt) as max_online_users
from(
select
live_id,
user_time,
sum(flag) over(partition by live_id order by user_time) as cur_cnt
from (
select
live_id,
enter_time as user_time,
1 as flag
from ks_live_t1
union all
select
live_id,
leave_time as user_time,
-1 as flag
from ks_live_t1
)a
)b
group by live_id
)c
left join ks_live_t2 t2
on c.live_id = t2.live_id
order by max_online_users desc;
select
t1.live_id,
live_nm,
count(1) as online_users
from ks_live_t1 t1
join ks_live_t2 t2
on t1.live_id = t2.live_id
where enter_time <= '2021-09-12 23:48:38' and leave_time >= '2021-09-12 23:48:38'
group by live_id,live_nm
order by online_users desc;
select
t1.live_id,
live_nm,
count(1) as online_users
from ks_live_t1 t1
join ks_live_t2 t2
on t1.live_id = t2.live_id
where enter_time <= '2021-09-12 11:48:38' and leave_time >= '2021-09-12 11:48:38'
group by live_id,live_nm
order by online_users desc;
select
distinct
usr_id
from(
select
usr_id,
trx_time,
lag(trx_time,3)over(partition by usr_id order by trx_time) as pre_time
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲'
)a
where datediff(trx_time,pre_time) <= 3
order by usr_id;
select
distinct
usr_id
from(
select
usr_id,
trx_time,
lag(trx_time,3)over(partition by usr_id order by trx_time) as pre_time
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲'
)a
where datediff(trx_time,pre_time) <= 4
order by usr_id;
select
usr_id
from(
select
usr_id,
trx_time,
lag(trx_time,3)over(partition by usr_id order by trx_time) as pre_time
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲'
)a
where datediff(trx_time,pre_time) <= 4
order by usr_id;
select
live_id,
live_nm,
max(cur_cnt) as max_online_users
from(
select
a.live_id,
t2.live_nm,
flag_time,
flag,
sum(flag) over(partition by live_id order by flag_time) as cur_cnt
from(
select
live_id,
enter_time as flag_time,
1 as flag
from ks_live_t1
union all
select
live_id,
leave_time as flag_time,
-1 as flag
from ks_live_t1
)a
left join ks_live_t2 t2 on a.live_id = t2.live_id
)b
group by live_id, live_nm
order by max_online_users desc;
select
t1.live_id,
live_nm,
count(*) as online_users
from ks_live_t1 t1
left join ks_live_t2 t2
on t1.live_id = t2.live_id
where date_format(t1.enter_time,'%Y-%m-%d %H:%i:%s') <= '2021-09-12 23:48:38' and date_format(t1.leave_time,'%Y-%m-%d %H:%i:%s') >= '2021-09-12 23:48:38'
group by live_id,live_nm
order by online_users desc;
select
live_id,
live_nm,
live_type,
total_duration,
total_users,
avg_duration
from(
select
live_id,
live_nm,
live_type,
total_duration,
total_users,
avg_duration,
row_number()over(partition by live_type order by avg_duration desc) as rn
from(
select
t1.live_id,
live_nm,
live_type,
sum(timestampdiff(SECOND,t1.enter_time,t1.leave_time)) as total_duration,
count(*) as total_users,
sum(timestampdiff(SECOND,t1.enter_time,t1.leave_time))/count(*) as avg_duration
from ks_live_t1 t1
left join ks_live_t2 t2
on t1.live_id = t2.live_id
where date_format(t1.enter_time, '%Y-%m-%d %H') >= '2021-09-12 23' and date_format(t1.leave_time, '%Y-%m-%d %H') <= '2021-09-13 04'
group by live_id,live_nm,live_type
)a
)b
where rn = 1
order by live_id asc;
with data1 as ( select distinct usr_id, date(login_time) as login_date from user_login_log ), data2 as ( select usr_id, login_date, first_value(login_date) over (partition by usr_id order by login_date) as `start_date`, lag(login_date) over (partition by usr_id order by login_date) as `pre_date` from data1 ), data3 as ( select distinct data1.usr_id, data1.login_date, case when pre_date is null and datediff(data1.login_date,start_date) = 0 then 'flag-1' when pre_date is not null and datediff(data1.login_date,pre_date) <= 3 then 'flag-2' else 'flag-3' end as flag from data1 left join data2 on (data1.usr_id,data1.login_date) = (data2.usr_id,data2.login_date) ), data4 as ( select login_date, round((sum(if(flag = 'flag-1', 1, 0)) / count(1)) * 100, 2) as rate_flag_1, round((sum(if(flag = 'flag-2', 1, 0)) / count(1)) * 100, 2) as rate_flag_2, round((sum(if(flag = 'flag-3', 1, 0)) / count(1)) * 100, 2) as rate_flag_3 from data3 group by login_date ) select login_date, concat_ws(',', rate_flag_1, rate_flag_2, rate_flag_3) as pct from data4 where year(login_date) = '2024' group by login_date order by login_date; 求指点这个思路的错误呀，一直是0过不去