select
substr(trx_time,1,7) as trx_mon,
count(1) as trx_cnt,
sum(trx_amt) as trx_amt
from
cmb_usr_trx_rcd
where
usr_id = 5201314520 and
date(trx_time) > '2022-11-01' and
((truncate(trx_amt,0) rlike "88$|98$" and trx_amt>200) and
hour(trx_time) in (23,1,2)
or
upper(mch_nm) rlike "足疗|保健|按摩|养生|SPA")
group by trx_mon
order by trx_mon
这个代码为什么会显示回答错误呢。。。求解
select
substr(trx_time,1,7) as trx_mon,
last_day(max(trx_time)) as last_day,
day(last_day(max(trx_time))) as days_of_mon,
sum(trx_amt) as trx_amt,
count(trx_amt) as trx_cnt,
sum(trx_amt)/day(last_day(max(trx_time))) as avg_day_amt,
count(trx_amt)/day(last_day(max(trx_time))) as avg_day_cnt
from
cmb_usr_trx_rcd a
left join
cmb_mch_typ b
on
a.mch_nm = b.mch_nm
where
usr_id = '5201314520'
and
year(trx_time) in (2023,2024)
and
mch_typ = "休闲娱乐"
group by substr(trx_time,1,7)
order by 1
这个为什么会显示回答错误呢。。。
看正确答案,day(last_day(max(trx_time))) as day_of_mon,没有days,只有day
select usr_id,
mch_nm,
trx_time,
trx_amt
from
cmb_usr_trx_rcd a
inner join
(select
min(trx_time) as first_time
from cmb_usr_trx_rcd
where
usr_id = '5201314520'
and
mch_nm like '%红玫瑰%'
) b
on
a.trx_time between b.first_time and date_add(b.first_time, interval 2 hour)
where
usr_id = '5201314520'
order by trx_time
select * from
cmb_usr_trx_rcd a
inner join
(select
min(trx_time) as first_time
from cmb_usr_trx_rcd
where
usr_id = '5201314520'
and
mch_nm like '%红玫瑰%'
) b
on
a.trx_time between b.first_time and date_add(b.first_time, interval 2 hour)
where
usr_id = '5201314520'
order by trx_time
with first_time as (
select min(trx_time) as fst
from
cmb_usr_trx_rcd
where
usr_id = '5201314520'
and
mch_nm like '%红玫瑰%'
)
select *
from cmb_usr_trx_rcd
where
usr_id = '5201314520'
and
trx_time between (select fst from first_time) and date_add((select fst from first_time), interval 2 hour)
order by trx_time
select
distinct a.trx_mon trx_mon,
coalesce(b.last_day,'1900-01-01') last_day,
coalesce(b.day_of_mon,0) day_of_mon,
round(coalesce(b.trx_amt,0),2) trx_amt,
coalesce(b.trx_cnt,0) trx_cnt,
round(coalesce(b.avg_day_amt,0),2) avg_day_amt,
round(coalesce(b.avg_day_cnt,0),2) avg_day_cnt
from
(select
substr(date_value,1,7) as trx_mon
from
date_table
where
date_value between '2023-01-01' and '2024-06-30'
) a
left join
(select
substr(aa.trx_time,1,7) trx_mon,
last_day(max(aa.trx_time)) last_day,
day(last_day(max(aa.trx_time))) day_of_mon,
sum(aa.trx_amt) trx_amt,
count(1) trx_cnt,
sum(aa.trx_amt)/day(last_day(max(aa.trx_time))) avg_day_amt,
count(1)/day(last_day(max(aa.trx_time))) avg_day_cnt
from cmb_usr_trx_rcd aa
left join
cmb_mch_typ ab
on
aa.mch_nm=ab.mch_nm
where
aa.usr_id = '5201314520'
and
date(aa.trx_time) between '2023-01-01' and '2024-06-30'
and
(ab.mch_typ = '休闲娱乐' or ab.mch_typ is NULL
)
and
aa.trx_amt >288
and
hour(aa.trx_time) in (23,0,1,2)
group by trx_mon
) b
on
a.trx_mon = b.trx_mon
order by 1
select
distinct a.trx_mon,
coalesce(b.last_day,'1900-01-01') as last_day,
coalesce(b.day_of_mon,0) as day_of_mon,
coalesce(b.trx_amt,0) as trx_amt,
coalesce(b.trx_cnt,0) as trx_cnt,
coalesce(round(b.avg_day_amt,2),0) as avg_day_amt,
coalesce(round(b.avg_day_cnt,2),0) as avg_day_cnt
from
(select substr(date_value,1,7) as trx_mon
from date_table
where substr(date_value,1,7) between '2023-01' and '2024-06'
) a
left join
(select
substr(a.trx_time,1,7) as trx_mon,
last_day(max(a.trx_time)) as last_day,
day(last_day(max(a.trx_time))) as day_of_mon,
sum(a.trx_amt) as trx_amt,
count(1) as trx_cnt,
sum(a.trx_amt)/day(last_day(max(a.trx_time))) as avg_day_amt,
count(1)/day(last_day(max(a.trx_time))) as avg_day_cnt
from
cmb_usr_trx_rcd a
left join
cmb_mch_typ b
on a.mch_nm=b.mch_nm
where
a.usr_id = '5201314520'
and
substr(a.trx_time,1,7) between '2023-01' and '2024-06'
and (b.mch_typ = '休闲娱乐' or b.mch_typ is NULL)
and hour(a.trx_time) in (23,0,1,2)
and a.trx_amt >=288
group by substr(a.trx_time,1,7)
order by 1
) b
on a.trx_mon = b.trx_mon
order by a.trx_mon
select
distinct a.trx_mon,
coalesce(b.last_day,'1900-01-01') as last_day,
coalesce(b.day_of_mon,0) as day_of_mon,
coalesce(b.trx_amt,0) as trx_amt,
coalesce(b.trx_cnt,0) as trx_cnt,
coalesce(round(b.avg_day_amt,2),0) as avg_day_amt,
coalesce(round(b.avg_day_cnt,2),0) as avg_day_cnt
from
(select substr(date_value,1,7) as trx_mon
from date_table
where substr(date_value,1,7) between '2023-01' and '2024-06'
) a
left join
(select
substr(n.trx_time,1,7) as trx_mon,
last_day(max(n.trx_time)) as last_day,
day(last_day(max(n.trx_time))) as day_of_mon,
sum(n.trx_amt) as trx_amt,
count(1) as trx_cnt,
sum(n.trx_amt)/day(last_day(max(n.trx_time))) as avg_day_amt,
count(1)/day(last_day(max(n.trx_time))) as avg_day_cnt
from
cmb_usr_trx_rcd n
left join
cmb_mch_typ m
on n.mch_nm=m.mch_nm
where
n.usr_id = '5201314520'
and
substr(n.trx_time,1,7) between '2023-01' and '2024-06'
and (m.mch_typ = '休闲娱乐' or m.mch_typ is NULL)
and hour(n.trx_time) in (23,0,1,2)
and n.trx_amt >=288
group by substr(n.trx_time,1,7)
order by 1
) b
on a.trx_mon = b.trx_mon
order by a.trx_mon
select
a.trx_mon,
coalesce(b.last_day,'1900-01-01') as last_day,
coalesce(b.day_of_mon,0) as day_of_mon,
coalesce(b.trx_amt,0) as trx_amt,
coalesce(b.trx_cnt,0) as trx_cnt,
coalesce(round(b.avg_day_amt,2),0) as avg_day_amt,
coalesce(round(b.avg_day_cnt,2),0) as avg_day_cnt
from
(select distinct substr(date_value,1,7) as trx_mon
from date_table
where substr(date_value,1,7) between '2023-01' and '2024-06'
) a
left join
(select
substr(n.trx_time,1,7) as trx_mon,
last_day(max(n.trx_time)) as last_day,
day(last_day(max(n.trx_time))) as day_of_mon,
sum(n.trx_amt) as trx_amt,
count(1) as trx_cnt,
sum(n.trx_amt)/day(last_day(max(n.trx_time))) as avg_day_amt,
count(1)/day(last_day(max(n.trx_time))) as avg_day_cnt
from
cmb_usr_trx_rcd n
left join
cmb_mch_typ m
on n.mch_nm=m.mch_nm
where
n.usr_id = '5201314520'
and
substr(n.trx_time,1,7) between '2023-01' and '2024-06'
and (m.mch_typ = '休闲娱乐' or m.mch_typ is NULL)
and hour(n.trx_time) in (23,0,1,2)
and n.trx_amt >=288
group by substr(n.trx_time,1,7)
order by 1
) b
on a.trx_mon = b.trx_mon
order by a.trx_mon
select
a.trx_mon,
coalesce(b.last_day,'1900-01-01') as last_day,
coalesce(b.day_of_mon,0) as day_of_mon,
coalesce(b.trx_amt,0) as trx_amt,
coalesce(b.trx_cnt,0) as trx_cnt,
coalesce(round(b.avg_day_amt,2),0) as avg_day_amt,
coalesce(round(b.avg_day_cnt,2),0) as avg_day_cnt
from
(select substr(date_value,1,7) as trx_mon
from date_table
where substr(date_value,1,7) between '2023-01' and '2024-06'
) a
left join
(select
substr(n.trx_time,1,7) as trx_mon,
last_day(max(n.trx_time)) as last_day,
day(last_day(max(n.trx_time))) as day_of_mon,
sum(n.trx_amt) as trx_amt,
count(1) as trx_cnt,
sum(n.trx_amt)/day(last_day(max(n.trx_time))) as avg_day_amt,
count(1)/day(last_day(max(n.trx_time))) as avg_day_cnt
from
cmb_usr_trx_rcd n
left join
cmb_mch_typ m
on n.mch_nm=m.mch_nm
where
n.usr_id = '5201314520'
and
substr(n.trx_time,1,7) between '2023-01' and '2024-06'
and (m.mch_typ = '休闲娱乐' or m.mch_typ is NULL)
and hour(n.trx_time) in (23,0,1,2)
and n.trx_amt >=288
group by substr(n.trx_time,1,7)
order by 1
) b
on a.trx_mon = b.trx_mon
order by a.trx_mon
select
substr(trx_time,1,7) as trx_mon,
count(1) as trx_cnt,
sum(trx_amt) as trx_amt
from
cmb_usr_trx_rcd
where
usr_id = 5201314520 and
date(trx_time) > '2022-11-01' and
((truncate(trx_amt,0) rlike "88$|98$" and trx_amt>200) and
hour(trx_time) in (23,1,2)
or
upper(mch_nm) rlike "足疗|保健|按摩|养生|SPA")
group by trx_mon
order by trx_mon
select
substr(trx_time,1,7) as trx_mon
,count(1) as trx_cnt
,sum(trx_amt) as trx_amt
from cmb_usr_trx_rcd
where usr_id='5201314520'
and
(
(truncate(trx_amt,0) like '%88' or truncate(trx_amt,0) like '%98')
and
(hour(trx_time) between 0 and 2 or hour(trx_time)=23)
or mch_nm rlike '.*(足疗|按摩|养生|保健|SPA).*'
)
and
substr(trx_time,1,7) between '2022-11' and '2024-12'
group by 1
order by 1
select case
when
truncate(trx_amt,0) rlike "88$|98$" and trx_amt>=200 and hour(trx_time) in (23,0,1,2,3) then 'illegal'
else
'other'
end as trx_typ,
count(1) as trx_cnt,
sum(trx_amt) as trx_amt,
count(distinct mch_nm) as mch_cnt
from cmb_usr_trx_rcd
where usr_id = '5201314520'
group by trx_typ
order by mch_cnt desc
select case
when
trx_amt rlike "88$|98$" and trx_amt>=200 and hour(trx_time) in (23,0,1,2) then 'illegal'
else
'other'
end as trx_typ,
count(1) as trx_cnt,
sum(trx_amt) as trx_amt,
count(distinct mch_nm) as mch_cnt
from cmb_usr_trx_rcd
where usr_id = '5201314520'
group by trx_typ
order by mch_cnt desc
select
case
when
mch_nm like "%按摩保健休闲%" then '按摩保健休闲'
when
upper(mch_nm) rlike "按摩|保健|休闲|养生|SPA|会所" then '按摩、保健、休闲、养生、SPA、会所'
end as reg_rules,
count(distinct mch_nm) as mch_cnt
from
cmb_usr_trx_rcd
where mch_nm like '%按摩保健休闲%'
or
upper(mch_nm) rlike"按摩|保健|休闲|养生|SPA|会所"
group by reg_rules
order by mch_cnt desc
WITH start_hotel AS (
SELECT start_loc
FROM didi_sht_rcd
WHERE start_loc LIKE '%酒店%'
),
ranked_routes AS (
SELECT
a.start_loc,
a.end_loc,
b.loc_ctg,
COUNT(*) AS trip_count,
ROW_NUMBER() OVER (PARTITION BY b.loc_ctg ORDER BY COUNT(*) DESC) AS rnk
FROM didi_sht_rcd a
LEFT JOIN loc_nm_ctg b
ON a.end_loc = b.loc_nm
WHERE a.start_loc IN (SELECT start_loc FROM start_hotel)
GROUP BY a.start_loc, a.end_loc, b.loc_ctg
)
SELECT
start_loc,
end_loc,
loc_ctg,
trip_count
FROM ranked_routes
WHERE rnk = 1
order by trip_count desc
WITH start_hotel AS (
SELECT start_loc
FROM didi_sht_rcd
WHERE start_loc LIKE '%酒店%'
)
SELECT
a.start_loc,
a.end_loc,
b.loc_ctg,
COUNT(b.loc_ctg) AS trip_count
FROM didi_sht_rcd a
LEFT JOIN loc_nm_ctg b
ON a.end_loc = b.loc_nm
WHERE a.start_loc IN (SELECT start_loc FROM start_hotel)
GROUP BY a.start_loc, a.end_loc, b.loc_ctg
ORDER BY trip_count DESC;
select
goods_id,
sum(order_gmv) as total_gmv,
rank() over (order by sum(order_gmv) desc) as ranking
from
order_info
where
date(order_time) = '2024-9-10'
group by goods_id
order by total_gmv desc
limit 10
select
goods_id,
sum(order_gmv) as total_gmv,
rank() over (order by sum(order_gmv) desc) as ranking
from
order_info
where
date(order_time) = '2024-10-01'
group by goods_id
order by total_gmv desc
limit 10
with first_trx as(
select min(trx_time) as first_trx_time
from
cmb_usr_trx_rcd
where
usr_id = '5201314520'
and
mch_nm like '%红玫瑰%'
)
select *
from
cmb_usr_trx_rcd
where
usr_id = '5201314520'
and
trx_time between (select first_trx_time from first_trx)
and
(select date_add(first_trx_time ,interval 2 hour) from first_trx)
order by trx_time
with first_trx as(
select min(trx_time) as first_trx_time
from
cmb_usr_trx_rcd
where
usr_id = '5201314520'
and
mch_nm like '%红玫瑰%'
)
select *
from
cmb_usr_trx_rcd
where
usr_id = '5201314520'
and
mch_nm like '%红玫瑰%'
and
trx_time between (select first_trx_time from first_trx)
and
(select date_add(first_trx_time ,interval 2 hour) from first_trx)
order by trx_time