select
m.prd_id, m.prd_nm,
sum(if_snd) as exposure_count,
sum(if_vw) as view_count,
sum(if_cart) as cart_count,
sum(if_buy) as buy_count,
round(sum(if_vw)*100.0/sum(if_snd),2) as view_rate,
round(sum(if_cart)*100.0/sum(if_snd),2) as view_rate,
round(sum(if_buy)*100.0/sum(if_snd),2) as view_rate
from tb_pg_act_rcd r
join tb_prd_map m on r.prd_id = m.prd_id
group by
m.prd_id, m.prd_nm
select
m.prd_id, m.prd_nm,
count(distinct case when gdr = 'M' then r.cust_uid end) as male_count,
count(distinct case when gdr = 'F' then r.cust_uid end) as female_count,
count(distinct r.cust_uid) as total_count
from tb_pg_act_rcd r
join tb_cst_bas_inf i on r.cust_uid = i.cust_uid
join tb_prd_map m on r.prd_id = m.prd_id
where r.if_buy = 1
group by
m.prd_id, m.prd_nm
order by
1
with user_info as (
select
distinct
r1.cust_uid,
r1.mch_id,
r1.fav_tm,
r2.pchs_tm
from xhs_fav_rcd r1
left join xhs_pchs_rcd r2
on r1.cust_uid = r2.cust_uid and r1.mch_id = r2.mch_id and r1.fav_tm <= r2.pchs_tm
)
select
count(distinct cust_uid) as cnt
from
user_info
where
pchs_tm is not null
with fav_info as (
select
distinct g.gd_id,
g.gd_nm,
g.gd_typ,
case when r1.fav_trq is null then '不收藏' else '收藏' end as fav
from gd_inf g
left join xhs_fav_rcd r1 on g.gd_id = r1.mch_id
)
select
f.gd_id,
f.gd_nm,
f.gd_typ,
max(case when f.fav = '收藏' and r2.pch_trq is not null then 1 else 0 end) as both_collected_and_purchased,
max(case when f.fav = '收藏' and r2.pch_trq is null then 1 else 0 end) as only_collected_not_purchased,
max(case when f.fav = '不收藏' and r2.pch_trq is not null then 1 else 0 end) as only_purchased_not_collected,
max(case when f.fav = '不收藏' and r2.pch_trq is null then 0 else 0 end) as neither_collected_nor_purchased,
count(distinct pch_trq) as purchase_count
from
fav_info f
left join
xhs_pchs_rcd r2 on f.gd_id = r2.mch_id
group by
f.gd_id, f.gd_nm, f.gd_typ
order by
4 desc
with fav_info as (
select
distinct g.gd_id,
g.gd_nm,
g.gd_typ,
case when r1.fav_trq is null then '不收藏' else '收藏' end as fav
from gd_inf g
left join xhs_fav_rcd r1 on g.gd_id = r1.mch_id
)
select
distinct f.gd_id,
f.gd_nm,
f.gd_typ,
case when f.fav = '收藏' and r2.pch_trq is not null then '收藏且购买'
when f.fav = '收藏' and r2.pch_trq is null then '收藏不购买'
when f.fav = '不收藏' and r2.pch_trq is not null then '购买不收藏'
when f.fav = '不收藏' and r2.pch_trq is null then '不收藏不购买'
end as category
from
fav_info f
left join xhs_pchs_rcd r2 on f.gd_id = r2.mch_id
SELECT *
FROM numbers_for_fun
WHERE (ABS(a) + ABS(b) > ABS(c) AND ABS(c) + ABS(b) > ABS(a) AND ABS(a) + ABS(c) > ABS(b))
AND (ABS(a)*ABS(a) + ABS(b)*ABS(b) = ABS(c)*ABS(c) OR
ABS(a)*ABS(a) + ABS(c)*ABS(c) = ABS(b)*ABS(b) OR
ABS(b)*ABS(b) + ABS(c)*ABS(c) = ABS(a)*ABS(a))
ORDER BY id;
SELECT *
FROM numbers_for_fun
WHERE (ABS(a) + ABS(b) > ABS(c) AND ABS(c) + ABS(b) > ABS(a) AND ABS(a) + ABS(c) > ABS(b))
AND (ABS(a)*ABS(a) + ABS(b)*ABS(b) < ABS(c)*ABS(c) OR
ABS(a)*ABS(a) + ABS(c)*ABS(c) < ABS(b)*ABS(b) OR
ABS(b)*ABS(b) + ABS(c)*ABS(c) < ABS(a)*ABS(a))
ORDER BY id
SELECT *
FROM numbers_for_fun
WHERE (ABS(a)*ABS(a) + ABS(b)*ABS(b) = ABS(c)*ABS(c) OR
ABS(a)*ABS(a) + ABS(c)*ABS(c) = ABS(b)*ABS(b) OR
ABS(b)*ABS(b) + ABS(c)*ABS(c) = ABS(a)*ABS(a))
ORDER BY id;
SELECT *
FROM numbers_for_fun
WHERE ABS(a) > 0 AND ABS(b) > 0 AND ABS(c) > 0
AND (ABS(a)*ABS(a) + ABS(b)*ABS(b) = ABS(c)*ABS(c) OR
ABS(a)*ABS(a) + ABS(c)*ABS(c) = ABS(b)*ABS(b) OR
ABS(b)*ABS(b) + ABS(c)*ABS(c) = ABS(a)*ABS(a))
ORDER BY id;
select
*
from
numbers_for_fun
where
case
when abs(a) >= abs(b) and abs(a) >= abs(c) then pow(abs(a),2) = pow(abs(b),2) + pow(abs(c),2)
when abs(b) >= abs(a) and abs(b) >= abs(c) then pow(abs(b),2) = pow(abs(a),2) + pow(abs(c),2)
else pow(abs(c),2) = pow(abs(a),2) + pow(abs(b),2)
end
and a*b*c <> 0
order by id asc
select
*
from
numbers_for_fun
where
case
when abs(a) >= abs(b) and abs(a) >= abs(c) then pow(abs(a),2) = pow(abs(b),2) + pow(abs(c),2)
when abs(b) >= abs(a) and abs(b) >= abs(c) then pow(abs(b),2) = pow(abs(a),2) + pow(abs(c),2)
else pow(abs(c),2) = pow(abs(a),2) + pow(abs(b),2)
end
order by id asc
with video_info as (
select
ks1.video_id,
ks1.title,
count(uid) as view_count,
sum(if_like) as like_count,
sum(if_retweet) as retweet_count,
sum(if_fav) as fav_count
from ks_video_inf ks1
join ks_video_wat_log ks2
on ks1.video_id = ks2.video_id
where
ks2.start_time >= date_sub(curdate(), interval 1 month)
group by
ks1.video_id, ks1.title
)
select
video_id,
title,
view_count,
like_count,
retweet_count,
fav_count,
(like_count + retweet_count + fav_count) as total_interactions
from
video_info
order by
7 desc
limit
3
select
count(r1.search_tm) as total_searches,
count(r2.click_page_id) as total_clicks,
round(count(r2.click_page_id)*100.0 / count(r1.search_tm),2) as click_rate
from jx_query_rcd r1
left join jx_click_rcd r2 on r1.session_id = r2.session_id
select
count(r1.search_tm) as total_searches,
count(r2.click_page_id) as total_clicks,
round(count(r2.click_page_id)*100.0 / count(r1.usr_id),2) as click_rate
from jx_query_rcd r1
left join jx_click_rcd r2 on r1.session_id = r2.session_id
select
count(r1.usr_id) as total_searches,
count(r2.click_page_id) as total_clicks,
round(count(r2.click_page_id)*100.0 / count(r1.usr_id),2) as click_rate
from jx_query_rcd r1
left join jx_click_rcd r2 on r1.session_id = r2.session_id
select
r1.key_word,
count(distinct r1.usr_id) as total_search_users,
count(distinct r2.usr_id) as users_reached_users,
round(count(distinct r2.usr_id)*100.0 / count(distinct r1.usr_id),2) as uv_conversion_rate
from jx_query_rcd r1
left join jx_click_rcd r2 on r1.session_id = r2.session_id
group by
r1.key_word
order by
4 desc
limit
10