select
trx_mon,
round(sum(sum_trx_amt) over(order by trx_mon asc),2) as trx_amt
from
(select
date_format(trx_time,'%Y-%m') as trx_mon,
sum(trx_amt) as sum_trx_amt
from cmb_usr_trx_rcd r
join cmb_mch_typ t on r.mch_nm=t.mch_nm
where usr_id=5201314520
and mch_typ='休闲娱乐'
group by date_format(trx_time,'%Y-%m'))a
where trx_mon between '2023-01' and '2024-12'
select
trx_mon,
round(sum(sum_trx_amt) over(order by trx_mon asc),2) as trx_amt
from
(select
date_format(trx_time,'%Y-%m') as trx_mon,
sum(trx_amt) as sum_trx_amt
from cmb_usr_trx_rcd r
where usr_id=5201314520
group by date_format(trx_time,'%Y-%m'))a
where trx_mon between '2023-01' and '2024-12'
select
*
from
(select
*,
dense_rank()over(order by trx_cnt desc) rnk
from
(select
'all' as mch_typ,
r.mch_nm,
count(r.mch_nm) as trx_cnt
from cmb_usr_trx_rcd r
join cmb_mch_typ t on r.mch_nm=t.mch_nm
where usr_id=5201314520
group by r.mch_nm)c)d
where rnk=1
union all
select
*
from
(select
*,
dense_rank()over(partition by mch_typ order by trx_cnt desc) as rnk
from
(select
t.mch_typ,
r.mch_nm,
count(r.mch_nm) as trx_cnt
from cmb_usr_trx_rcd r
join cmb_mch_typ t on r.mch_nm=t.mch_nm
where usr_id=5201314520
and t.mch_typ in ('交通出行','休闲娱乐','咖啡奶茶')
group by t.mch_typ, r.mch_nm) a) b
where rnk=1
select
*
from
(select
*,
dense_rank()over(order by trx_cnt desc) rk
from
(select
'all' as mch_typ,
r.mch_nm,
count(r.mch_nm) as trx_cnt
from cmb_usr_trx_rcd r
join cmb_mch_typ t on r.mch_nm=t.mch_nm
group by r.mch_nm)c)d
where rk=1
union all
select
*
from
(select
*,
dense_rank()over(partition by mch_typ order by trx_cnt desc) as drk
from
(select
t.mch_typ,
r.mch_nm,
count(r.mch_nm) as trx_cnt
from cmb_usr_trx_rcd r
join cmb_mch_typ t on r.mch_nm=t.mch_nm
where t.mch_typ in ('交通出行','休闲娱乐','咖啡奶茶')
group by t.mch_typ, r.mch_nm) a) b
where drk=1
select *
from (
select
agg.mch_typ,
agg.mch_nm,
agg.trx_cnt,
dense_rank() over(order by agg.trx_cnt desc) as rk
from (
select
'all' as mch_typ,
r.mch_nm,
count(*) as trx_cnt
from cmb_usr_trx_rcd r
join cmb_mch_typ t on r.mch_nm = t.mch_nm
group by r.mch_nm
) agg
) ranked_all
where ranked_all.rk = 1
union all
select *
from (
select
agg2.mch_typ,
agg2.mch_nm,
agg2.trx_cnt,
dense_rank() over(partition by agg2.mch_typ order by agg2.trx_cnt desc) as drk
from (
select
t.mch_typ,
r.mch_nm,
count(*) as trx_cnt
from cmb_usr_trx_rcd r
join cmb_mch_typ t on r.mch_nm = t.mch_nm
where t.mch_typ in ('交通出行','休闲娱乐','咖啡奶茶')
group by t.mch_typ, r.mch_nm
) agg2
) ranked_cat
where ranked_cat.drk = 1;
select
*
from
(select
year(trx_time) as trx_mon,
mch_nm,
sum(trx_amt) as sum_trx_amt
from cmb_usr_trx_rcd r
where usr_id='5201314520'
and year(trx_time)=2024
group by year(trx_time),r.mch_nm
order by sum(trx_amt) desc
limit 3) b
union all
select
a.trx_mon,
a.mch_nm,
a.sum_trx_amt
from
(select
date_format(trx_time,'%Y-%m') as trx_mon,
r.mch_nm,
sum(trx_amt) as sum_trx_amt,
row_number() over(partition by date_format(trx_time,'%Y-%m') order by sum(trx_amt) desc) as rn
from cmb_usr_trx_rcd r
where usr_id='5201314520'
and year(trx_time)=2024
group by date_format(trx_time,'%Y-%m'),r.mch_nm) a
where rn<=3
order by trx_mon asc, sum_trx_amt desc
select
start_loc,
end_loc,
loc_ctg,
lane_count as trip_count
from
(select
*,
row_number() over(partition by a.loc_ctg order by lane_count desc) as rnk
from
(select
r.start_loc,
l_end.loc_ctg,
r.end_loc,
count(*) as lane_count
from didi_sht_rcd r
join loc_nm_ctg l_start on r.start_loc=l_start.loc_nm
join loc_nm_ctg l_end on r.end_loc=l_end.loc_nm
where l_start.loc_ctg='酒店'
group by r.start_loc, l_end.loc_ctg, r.end_loc)a
) b
where rnk=1
select
start_loc,
end_loc,
loc_ctg,
lane_count as trip_count
from
(select
*,
row_number() over(partition by a.start_loc,a.loc_ctg order by lane_count desc) as rnk
from
(select
r.start_loc,
l_end.loc_ctg,
r.end_loc,
count(*) as lane_count
from didi_sht_rcd r
join loc_nm_ctg l_start on r.start_loc=l_start.loc_nm
join loc_nm_ctg l_end on r.end_loc=l_end.loc_nm
where l_start.loc_ctg='酒店'
group by r.start_loc, l_end.loc_ctg, r.end_loc)a
) b
where rnk=1
select
r.start_loc,
r.end_loc,
count(*) as trip_count
from didi_sht_rcd r
join loc_nm_ctg c on r.start_loc=c.loc_nm
join loc_nm_ctg c1 on r.end_loc=c1.loc_nm
where c.loc_ctg='酒店'
and c1.loc_ctg='餐饮'
group by r.start_loc, r.end_loc
order by trip_count desc
limit 1
select
r.*
from didi_sht_rcd r
join loc_nm_ctg l_start on r.start_loc=l_start.loc_nm
join loc_nm_ctg l_end on r.end_loc=l_end.loc_nm
where l_start.loc_ctg='住宅'
and l_end.loc_ctg='写字楼'
order by r.start_tm asc;
SELECT r.*
FROM didi_sht_rcd r
JOIN loc_nm_ctg l_start ON r.start_loc = l_start.loc_nm
JOIN loc_nm_ctg l_end ON r.end_loc = l_end.loc_nm
WHERE l_start.loc_ctg = '餐饮'
AND l_end.loc_ctg = '餐饮'
ORDER BY r.start_tm ASC;
select
r.*
from didi_sht_rcd r
join loc_nm_ctg c on r.start_loc=c.loc_nm
join loc_nm_ctg c1 on r.end_loc=c.loc_nm
where c.loc_ctg='餐饮'
and c1.loc_ctg='餐饮'
SELECT
gd.gd_id,
gd.gd_nm,
gd.gd_typ
FROM
xhs_fav_rcd fav
JOIN
gd_inf gd ON fav.mch_id = gd.gd_id
LEFT JOIN
xhs_pchs_rcd pchs ON fav.mch_id = pchs.mch_id
where
pchs.mch_id IS NULL
group by
gd.gd_id,
gd.gd_nm,
gd.gd_typ
select distinct
g.gd_id,
g.gd_nm,
g.gd_typ
from
(select
fav.mch_id
from xhs_fav_rcd fav
where not exists(
select 1
from xhs_pchs_rcd pchs
where pchs.mch_id=fav.mch_id
)) a
join gd_inf g
on a.mch_id=g.gd_id
select
gd.gd_typ,
count(distinct pchs.cust_uid) as buyer_count
from gd_inf gd
join xhs_pchs_rcd pchs
on gd.gd_id=pchs.mch_id
group by gd.gd_typ
order by buyer_count desc
limit 1;
select
gd.gd_typ,
count(*) as buyer_count
from gd_inf gd
join xhs_pchs_rcd pchs
on gd.gd_id=pchs.mch_id
group by gd.gd_typ
order by buyer_count desc
limit 1
select
x.mch_id,
g.gd_nm,
count(*) as fav_count
from gd_inf g
join xhs_fav_rcd x
on g.gd_id=x.mch_id
group by x.mch_id,g.gd_nm
order by fav_count desc
limit 1
SELECT
t2.live_id,
t2.live_nm,
COUNT(*) AS enter_cnt
FROM
ks_live_t1 t1
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'
GROUP BY
t1.live_id, t2.live_nm
ORDER BY
enter_cnt DESC
LIMIT 5;