WITH temp AS
(SELECT
didi.*
FROM
didi_sht_rcd didi
INNER JOIN
loc_nm_ctg loc1
ON
didi.start_loc=loc1.loc_nm
INNER JOIN
loc_nm_ctg loc2
ON
didi.end_loc=loc2.loc_nm
WHERE
loc1.loc_ctg='酒店'
AND
loc2.loc_ctg='餐饮'
ORDER BY
start_tm ASC)
SELECT
start_loc,
end_loc,
COUNT(*) AS trip_count
FROM
temp
GROUP BY
start_loc,end_loc
ORDER BY
trip_count DESC
LIMIT 1
SELECT
didi.*
FROM
didi_sht_rcd didi
INNER JOIN
loc_nm_ctg loc1
ON
didi.start_loc=loc1.loc_nm
INNER JOIN
loc_nm_ctg loc2
ON
didi.end_loc=loc2.loc_nm
WHERE
loc1.loc_ctg='住宅'
AND
loc2.loc_ctg='写字楼'
ORDER BY
start_tm ASC
with temp as(select
di.*
from
didi_sht_rcd di
inner join
loc_nm_ctg lo
on
di.start_loc =lo.loc_nm
where
loc_ctg='餐饮'
order by
start_tm asc)
select
t1.*
from
temp t1
inner join
loc_nm_ctg lo
on
t1.end_loc =lo.loc_nm
where
loc_ctg='餐饮'
order by
start_tm asc
SELECT
gi.gd_id,
gi.gd_nm,
gi.gd_typ
FROM
gd_inf gi
INNER JOIN
xhs_fav_rcd xfr
ON
gi.gd_id=xfr.mch_id
LEFT JOIN
xhs_pchs_rcd xpr
on
xfr.mch_id =xpr.mch_id
WHERE
xpr.cust_uid IS NULL
GROUP BY
gi.gd_id,
gi.gd_nm,
gi.gd_typ
SELECT
gi.gd_id,
gi.gd_nm,
gi.gd_typ,
xfr.cust_uid
FROM
gd_inf gi
INNER JOIN
xhs_fav_rcd xfr
ON
gi.gd_id=xfr.mch_id
LEFT JOIN
xhs_pchs_rcd xpr
on xfr.mch_id =xpr.mch_id
SELECT
gi.gd_typ,
COUNT(DISTINCT(xpr.cust_uid))as buyer_count
FROM
gd_inf gi
INNER JOIN
xhs_pchs_rcd xpr
ON
gi.gd_id=xpr.mch_id
GROUP BY
gi.gd_typ
ORDER BY buyer_count DESC
limit 1
SELECT
gi.gd_typ,
COUNT(xpr.mch_id) as buyer_count
FROM
gd_inf gi
INNER JOIN
xhs_pchs_rcd xpr
ON
gi.gd_id=xpr.mch_id
GROUP BY
gi.gd_typ
ORDER BY buyer_count DESC
limit 1
SELECT
gi.gd_id,
gi.gd_nm,
COUNT(xfr.fav_trq) as fav_count
FROM
gd_inf gi
INNER JOIN
xhs_fav_rcd xfr
ON
gi.gd_id=xfr.mch_id
GROUP BY
gi.gd_id,gi.gd_nm
limit 1
with temp as(select
date_format(t1.trx_time,'%Y-%m') as trx_mon,
t2.mch_typ,
sum(t1.trx_amt) as trx_amt1
from
cmb_usr_trx_rcd t1
inner join cmb_mch_typ t2
on t1.mch_nm =t2.mch_nm
where
usr_id=5201314520
and date_format(t1.trx_time,'%Y-%m') between '2023-01' and '2023-12'
and t2.mch_typ ='休闲娱乐'
group by date_format(t1.trx_time,'%Y-%m'),t2.mch_typ
order by date_format(t1.trx_time,'%Y-%m')),
date_temp as(
select
distinct(substr(dt.date_value,1,7) )as trx_mon,
te1.trx_amt1
from date_table dt
left join temp te1
on substr(dt.date_value,1,7)=te1.trx_mon
where date_value between '2023-01-01'and '2023-12-31')
select
trx_mon,
sum(trx_amt1)over(
order by trx_mon
rows between unbounded preceding and current row) as trx_amt
from date_temp
with temp as (
select
date_format(t1.trx_time,'%Y-%m') as trx_mon,
t2.mch_typ,
sum(t1.trx_amt) as trx_amt1
from
cmb_usr_trx_rcd t1
inner join cmb_mch_typ t2
on t1.mch_nm =t2.mch_nm
where
usr_id=5201314520
and date_format(t1.trx_time,'%Y-%m') between '2023-01' and '2024-12'
and t2.mch_typ ='休闲娱乐'
group by date_format(t1.trx_time,'%Y-%m'),t2.mch_typ
order by date_format(t1.trx_time,'%Y-%m'))
select
trx_mon,
sum(trx_amt1)over(
partition by trx_mon
order by trx_mon
rows between unbounded preceding and current row) as trx_amt
from temp
select
distinct(date_format(t1.trx_time,'%Y-%m') )as trx_mon,
sum(t1.trx_amt) over (
order by date_format(t1.trx_time,'%Y-%m'))
as trx_amt2
from cmb_usr_trx_rcd t1
inner join cmb_mch_typ t2
on t1.mch_nm =t2.mch_nm
where usr_id=5201314520
and date_format(t1.trx_time,'%Y-%m') between '2023-01' and '2024-12'
and t2.mch_typ ='休闲娱乐'
with temp as (
select
date_format(t1.trx_time,'%Y-%m') as trx_mon,
t2.mch_typ,
sum(t1.trx_amt) as trx_amt1
from
cmb_usr_trx_rcd t1
inner join cmb_mch_typ t2
on t1.mch_nm =t2.mch_nm
where
usr_id=5201314520
and date_format(t1.trx_time,'%Y-%m') between '2023-01' and '2024-12'
and t2.mch_typ ='休闲娱乐'
group by date_format(t1.trx_time,'%Y-%m'),t2.mch_typ
order by date_format(t1.trx_time,'%Y-%m'))
select
trx_mon,
sum(trx_amt1)over(
order by trx_mon
rows between unbounded preceding and current row) as trx_amt
from temp
select
date_format(t1.trx_time,'%Y-%m') as trx_mon,
sum(t1.trx_amt) over (
partition by date_format(t1.trx_time,'%Y-%m')
order by date_format(t1.trx_time,'%Y-%m')) as trx_amt
from
cmb_usr_trx_rcd t1
inner join
cmb_mch_typ t2
on t1.mch_nm =t2.mch_nm
where usr_id=5201314520
and t2.mch_typ ='休闲娱乐'
and date_format(t1.trx_time,'%Y-%m') between '2023-01' and '2024-12'
select
date_format(t1.trx_time,'%Y-%m') as trx_mon,
last_day(max(t1.trx_time)) as last_day,
day(last_day(max(t1.trx_time))) as days_of_mon,
sum(t1.trx_amt) as trx_amt,
count(*) as trx_cnt,
sum(t1.trx_amt)/day(last_day(max(t1.trx_time))) as avg_day_amt,
count(*)/ day(last_day(max(t1.trx_time))) as avg_day_cnt
from cmb_usr_trx_rcd t1
inner join cmb_mch_typ t2
on t1.mch_nm =t2.mch_nm
and t2.mch_typ="休闲娱乐"
where t1.usr_id='5201314520'
and date_format(t1.trx_time,'%Y-%m') between 2023 and 2024
group by date_format(t1.trx_time,'%Y-%m')
order by trx_mon asc
select
date_format(t1.trx_time,'%Y-%m') as trx_mon,
last_day(max(t1.trx_time)) as last_day,
day(last_day(max(t1.trx_time))) as days_of_mon
from cmb_usr_trx_rcd t1
inner join cmb_mch_typ t2
on t1.mch_nm =t2.mch_nm
and t2.mch_typ="休闲娱乐"
where t1.usr_id='5201314520'
and date_format(t1.trx_time,'%Y-%m') between 2023 and 2024
group by date_format(t1.trx_time,'%Y-%m')
order by trx_mon asc
select
mch_nm,
sum(trx_amt) as sum_trx_amt
from
cmb_usr_trx_rcd
where
year(trx_time) =2024
and usr_id='5201314520'
group by
mch_nm
order by
sum_trx_amt desc
select
m.mch_typ,
c.mch_nm,
count(c.trx_amt) as trx_cnt,
sum(c.trx_amt) as trx_amt
from cmb_usr_trx_rcd c
left join cmb_mch_typ m
on c.mch_nm =m.mch_nm
where c.usr_id = 5201314520
and date_format(c.trx_time,'%Y')='2024'
and m.mch_typ is null
group by m.mch_typ,c.mch_nm
order by trx_cnt desc
select
m.mch_typ,
m.mch_nm,
count(c.trx_amt) as trx_cnt,
sum(c.trx_amt) as trx_amt
from cmb_usr_trx_rcd c
left join cmb_mch_typ m
on c.mch_nm =m.mch_nm
where c.usr_id = 5201314520
and date_format(trx_time,'%Y')='2024'
and m.mch_typ is null
group by m.mch_typ,m.mch_nm
order by trx_cnt desc
select
m.mch_typ,
count(c.trx_amt) as trx_cnt,
sum(c.trx_amt) as trx_amt
from cmb_usr_trx_rcd c
left join cmb_mch_typ m
on c.mch_nm =m.mch_nm
where c.usr_id = 5201314520
and date_format(trx_time,'%Y')='2024'
and m.mch_typ is null
group by m.mch_typ
order by trx_cnt desc