select
start_loc,
end_loc,
start_ctg,
end_ctg,
cnt
from
(select
start_loc,
end_loc,
count(*) cnt,
start_ctg,
end_ctg,
row_number()over(partition by case when end_ctg='酒店' then start_ctg else end_ctg end order by count(*) desc) as rnk
from
(select
a.start_loc,
a.end_loc,
b.loc_ctg as start_ctg,
c.loc_ctg as end_ctg
from didi_sht_rcd a
left join loc_nm_ctg b on a.start_loc=b.loc_nm
left join loc_nm_ctg c on a.end_loc=c.loc_nm
where (b.loc_ctg='酒店' or c.loc_ctg='酒店')and c.loc_ctg is not null and b.loc_ctg is not null)t1
group by start_loc,end_loc,start_ctg,end_ctg
order by cnt desc
)t2
where rnk=1
order by cnt desc,start_loc asc 请问我的代码在哪里的逻辑上有问题啊
select
count(case when cnt between 1 and 5 then usr_id end),
count(case when cnt between 6 and 10 then usr_id end),
count(case when cnt between 11 and 20 then usr_id end),
count(case when cnt >20 then usr_id end)
from
(select
usr_id,
count(distinct login_date) as cnt
from
(select
usr_id,
date(login_time) as login_date
from user_login_log
where login_time>=date_sub(curdate(),interval 180 day))t1
group by usr_id)t2
WITH TimeExtracted AS (
SELECT
cust_uid,
start_loc,
end_loc,
CAST(SUBSTR(start_tm, 4, 2) AS SIGNED) AS hour,
car_cls
FROM didi_sht_rcd
),
FilteredData AS (
SELECT *
FROM TimeExtracted
WHERE hour BETWEEN 18 AND 23
),
HourlyCounts AS (
SELECT
hour,
COUNT(*) AS total_orders,
SUM(CASE WHEN car_cls = 'A' THEN 1 ELSE 0 END) AS A_orders
FROM FilteredData
GROUP BY hour
),
PercentageAOrders AS (
SELECT
hour,
(A_orders * 100.0 / total_orders) AS percentage_A_orders
FROM HourlyCounts
),
AggregatedData AS (
SELECT
COUNT(*) AS n,
SUM(hour) AS sum_x,
SUM(percentage_A_orders) AS sum_y,
SUM(hour * percentage_A_orders) AS sum_xy,
SUM(hour * hour) AS sum_x2,
SUM(percentage_A_orders * percentage_A_orders) AS sum_y2
FROM PercentageAOrders
)
SELECT
cast((n * sum_xy - sum_x * sum_y) /
(SQRT(n * sum_x2 - sum_x * sum_x) * SQRT(n * sum_y2 - sum_y * sum_y)) as decimal(10,2)) AS R
FROM AggregatedData;
select
t.mch_typ,
u.mch_nm,
count(u.mch_nm),
sum(trx_amt)
from cmb_usr_trx_rcd u
left join cmb_mch_typ t on u.mch_nm=t.mch_nm
where u.usr_id='5201314520' and year(trx_time)=2024
group by t.mch_typ,
u.mch_nm
having t.mch_typ is null
select
m.mch_typ,
count(u.mch_nm),
sum(u.trx_amt)
from cmb_usr_trx_rcd u
left join cmb_mch_typ m on u.mch_nm=m.mch_nm
where u.usr_id='5201314520' and year(u.trx_time)=2024
group by m.mch_typ
select
date_format(trx_time,'%Y-%m') as trx_mon,
count(1) as trx_cnt,
sum(trx_amt) as trx_amt1
from cmb_usr_trx_rcd
where (date(trx_time)>'2022-11-01' and date(trx_time)<='2024-12-31') and usr_id='5201314520'and ((trx_amt>200 and (substr(trx_amt,-5) like '88%' or substr(trx_amt,-5)like '98%') and hour(trx_time) in (0,23,1,2)) or upper(mch_nm) rlike ('足疗|保健|按摩|养生|SPA'))
group by trx_mon
order by trx_mon
select
date_format(trx_time,'%Y-%m') as trx_mon,
count(1) as trx_cnt,
sum(trx_amt) as trx_amt1
from cmb_usr_trx_rcd
where (date(trx_time)>'2022-11-01' and date(trx_time)<='2024-12-31') and usr_id='5201314520'and ((trx_amt>200 and (substr(trx_amt,-5)='88.00' or substr(trx_amt,-5)='98.00') and hour(trx_time) in (0,23,1,2)) or upper(mch_nm) rlike ('足疗|保健|按摩|养生|SPA'))
group by trx_mon
order by trx_mon
select
date_format(trx_time,'%Y-%m') as trx_mon,
count(1) as trx_cnt,
sum(trx_amt) as trx_amt1
from cmb_usr_trx_rcd
where date(trx_time) between'2022-11-01' and '2024-12-31' and usr_id='5201314520'and ((trx_amt>200 and (substr(trx_amt,-5)='88.00' or substr(trx_amt,-5)='98.00') and hour(trx_time) in (0,23,1,2)) or upper(mch_nm) rlike ('足疗|保健|按摩|养生|SPA'))
group by trx_mon
order by trx_mon
select
date_format(trx_time,'%Y-%m') as trx_mon,
count(1) as trx_cnt,
sum(trx_amt) as trx_amt1
from cmb_usr_trx_rcd
where trx_time between'2022-11-01' and '2024-12-31' and usr_id='5201314520'and ((trx_amt>200 and (substr(trx_amt,-5)='88.00' or substr(trx_amt,-5)='98.00') and hour(trx_time) in (0,23,1,2)) or upper(mch_nm) rlike ('足疗|保健|按摩|养生|SPA'))
group by trx_mon
order by trx_mon
select
date_format(trx_time,'%Y-%m') as trx_mon,
count(1) as trx_cnt,
sum(trx_amt) as trx_amt1
from cmb_usr_trx_rcd
where trx_time between'2022-11-01' and '2024-12-31' and (substr(trx_amt,-5)='88.00' or substr(trx_amt,-5)='98.00') and usr_id='5201314520' and upper(mch_nm) rlike ('足疗|保健|按摩|养生|SPA')
and (hour(trx_time)=23 or (hour(trx_time) between 0 and 2))
group by trx_mon
order by trx_mon
select
date_format(trx_time,'%Y-%m') as trx_mon,
count(1) as trx_cnt,
sum(trx_amt) as trx_amt1
from cmb_usr_trx_rcd
where trx_time between'2022-11-01' and '2024-12-31' and (substr(trx_amt,-5)='88.00' or substr(trx_amt,-5)='98.00') and usr_id='5201314520' and mch_nm rlike ('足疗|保健|按摩|养生|SPA')
group by trx_mon
order by trx_mon
select
date_format(trx_time,'%Y-%m') as trx_mon,
count(1) as trx_cnt,
sum(trx_amt) as trx_amt1
from cmb_usr_trx_rcd
where trx_time between'2022-11-01' and '2024-12-31' and (substr(trx_amt,-5)='88.00' or substr(trx_amt,-5)='98.00') and usr_id='5201314520' and mch_nm rlike ('足疗|保健|按摩|养生|SPA')
group by trx_mon
select
case when
trx_amt>=200 and (truncate(trx_amt, 0) like '%88' or truncate(trx_amt, 0) like '%98') and (hour(trx_time)=23 or hour(trx_time) between 0 and 3)
then 'illegal'
else'other' end as trx_typ,
count(1),
sum(trx_amt),
count(distinct mch_nm)
from cmb_usr_trx_rcd
where usr_id='5201314520'
group by trx_typ
order by trx_typ desc
with a as(
select
mch_nm
from cmb_usr_trx_rcd
where usr_id='5211314521' and year(trx_time) between 2023 and 2024
),
b as(
select
mch_nm,
count(mch_nm) as trx_cnt
from cmb_usr_trx_rcd
where usr_id='5201314520' and year(trx_time) between 2023 and 2024
group by mch_nm
having count(mch_nm)>=20)
select
b.mch_nm,
b.trx_cnt,
a.mch_nm
from b
left join a on a.mch_nm=b.mch_nm
group by b.mch_nm
select
distinct mch_nm
from cmb_usr_trx_rcd
where usr_id='5211314521' and mch_nm in (
select mch_nm
from cmb_usr_trx_rcd
where usr_id='5201314520' and year(trx_time)=2024
) and year(trx_time)=2024
select
case when mch_nm rlike '按摩|保健|休闲|养生|SPA|会所' and mch_nm not like '%按摩保健休闲%' then'按摩、保健、休闲、养生、SPA、会所'
when mch_nm like '%按摩保健休闲%' then '按摩保健休闲'
end as reg_rules,
count(distinct mch_nm)
from cmb_usr_trx_rcd
wheremch_nm rlike '按摩|保健|休闲|养生|SPA|会所' or mch_nm like '%按摩保健休闲%'
group by reg_rules
select
case
when trx_amt=288 then '1.WithHand'
when trx_amt=388 then '2.WithMimi'
when trx_amt=588 then '3.BlowJobbie'
when trx_amt=888 then '4.Doi'
when trx_amt=1288 then '5.DoubleFly'
else '6.other'
end as ser_typ,
count(1) as trx_cnt,
date(min(trx_time)) as first_date
from cmb_usr_trx_rcd
where usr_id='5201314520' and mch_nm='红玫瑰按摩保健休闲'
group by ser_typ
order by 1
select
usr_id,
mch_nm,
sum(trx_amt),
count(*) as trx_cnt,
min(trx_time) as first_time
from cmb_usr_trx_rcd
where usr_id='5201314520' and trx_amt>=288
group by mch_nm
order by trx_cnt desc