select
DATE_FORMAT(trx_time, '%Y-%m-%d') as trx_date,
max(trx_amt) as max_trx_amt,
min(trx_amt) as min_trx_amt,
avg(trx_amt) as avg_trx_amt,
sum(trx_amt) as total_trx_amt
from
cmb_usr_trx_rcd
where
DATE_FORMAT(trx_time, '%Y-%m-%d') between '2024-09-01' and '2024-09-30'
and
mch_nm = '红玫瑰按摩保健休闲'
group by DATE_FORMAT(trx_time, '%Y-%m-%d')
order by trx_date这个错在哪
select
DATE_FORMAT(trx_time, '%Y-%m-%d') as trx_date,
max(trx_amt) as max_trx_amt,
min(trx_amt) as min_trx_amt,
avg(trx_amt) as avg_trx_amt,
sum(trx_amt) as total_trx_amt
from
cmb_usr_trx_rcd
where
DATE_FORMAT(trx_time, '%Y-%m-%d') between '2024-09-01' and '2024-09-30'
and
mch_nm = '红玫瑰按摩保健休闲'
group by DATE_FORMAT(trx_time, '%Y-%m-%d')
order by trx_date
请问这个错在哪
select id,card1,card2
from hand_permutations
where (left(card1,1) = 'A' OR left(card1,1) = 'K') AND (left(card2,1) = 'A' OR left(card2,1) = 'K')
order by id asc
select id,card1,card2
from hand_permutations
where((right(card1,1) = '♠️' or right(card1,1) = '♥') and (right(card2,1) = '♠️' or right(card2,1) = '♥')) and (left(card1,1) = 'A' OR left(card1,1) = 'K') AND (left(card2,1) = 'A' OR left(card2,1) = 'K')
order by id asc
select id,card1,card2
from hand_permutations
where(right(card1,1) = '♠️' or right(card1,1) = '♥') and (right(card2,1) = '♠️' or right(card2,1) = '♥')
order by id asc
with data1 as (
select
distinct usr_id,
date(login_time) as login_time
from user_login_log
where datediff(current_date,date(login_time)) <= 30
),
data2 as (
select
t.usr_id,
t.login_time as t_date,
t1.login_time as t1_date
from
data1 as t
left join
data1 as t1
on
t.usr_id = t1.usr_id
and datediff(t1.login_time,t.login_time) = 1
)
select
t_date as first_login_date,
concat(round(avg(t1_date is not null)*100, 2), '%') as t1_retention_rate
from
data2
group by
t_date
order by
t_date
WITH data1 AS (
SELECT DISTINCT
usr_id,
DATE(login_time) AS login_date
FROM
user_login_log
WHERE
DATEDIFF(CURRENT_DATE, DATE(login_time)) <= 30
),
data2 AS (
SELECT
T.usr_id,
T.login_date AS T_date,
T_1.login_date AS T_1_date
FROM
data1 AS T
LEFT JOIN
data1 AS T_1
ON
T.usr_id = T_1.usr_id
AND DATEDIFF(T.login_date, T_1.login_date) = -1
)
SELECT
T_date AS first_login_date,
CONCAT(ROUND(AVG(CASE WHEN T_1_date IS NOT NULL THEN 1 ELSE 0 END) * 100, 2), '%') AS T1_retention_rate
FROM
data2
GROUP BY
T_date
ORDER BY
T_date;
with data1 as (
select usr_id,date(login_time) as login_time
from user_login_log
),
data2 as (
select t.usr_id,
t.login_time as t_date,
t1.login_time as t1_date
from data1 as t
left join
data1 as t1
on t.usr_id = t1.usr_id
where datediff(t.login_time,t1.login_time)= -1
)
select
t_date,
concat(round(avg(t1_date is not null)*100, 2),'%') as t1_retention_rate
from data2
group by t_date
order by t_date
with data1 as (
select distinct usr_id ,date(login_time) as login_time
from user_login_log
where datediff(current_date,date(login_time)) <= 30),
data2 as (
select t.usr_id,
t.login_time as t_date,
t1.login_time as t1_date
from data1 as t
left join
data1 as t1
on t.usr_id = t1.usr_id
and datediff(t.login_time,t1.login_time) = -1
)
select t_date as login_date,
concat(round(avg(t1_date is not null)*100,2),'%') as t1_retention_rate
from data2
group by t_date
order by t_date
select a.mch_nm as asshole_tried,a.trx_cnt,b.mch_nm as darling_tried
from
(selectmch_nm ,count(1) as trx_cnt
from cmb_usr_trx_rcd
where usr_id = '5201314520' and year(trx_time) in (2023,2024)
group by mch_nm
having count(1) >= 20) a
left join
(
select distinct mch_nm
from cmb_usr_trx_rcd
where usr_id = '5211314521' and year(trx_time) in (2023,2024)
) b
on a.mch_nm = b.mch_nm
order by 2 desc
select a.*
from (
select distinct mch_nm
from cmb_usr_trx_rcd
where usr_id = '5201314520'and year(trx_time) = 2024
) as a
inner join
(select distinct mch_nm
from cmb_usr_trx_rcd
where usr_id = '5211314521' and year(trx_time) = 2024) b
on a.mch_nm = b.mch_nm
order by mch_nm desc
select a.*
from
(select distinct mch_nm
from cmb_usr_trx_rcd
where usr_id= 5201314520 and year(trx_time) = 2024) as a
left join
(select distinct mch_nm
from cmb_usr_trx_rcd
where usr_id= 5201314521 and year(trx_time) = 2024) as b
on a.mch_nm = b.mch_nm
order by mch_nm desc
select *
from cmb_usr_trx_rcd
where
trx_amt = (select trx_amt
from cmb_usr_trx_rcd
where usr_id = 5201314520
and year(trx_time) = 2024
group by trx_amt
order by max(trx_amt) desc
limit 1)
select *
from cmb_usr_trx_rcd
where mch_nm = (
select mch_nm
from cmb_usr_trx_rcd
where usr_id = 5201314520
and year(trx_time) = 2024
group by mch_nm
order by max(trx_amt) desc
limit 1)
and trx_amt = (select trx_amt
from cmb_usr_trx_rcd
where usr_id = 5201314520
and year(trx_time) = 2024
group by trx_amt
order by max(trx_amt) desc
limit 1)
select
case
when mch_nm like '%按摩保健休闲%' then '按摩保健休闲'
when lower(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 lower(mch_nm) rlike '.*(按摩|保健|休闲|spa|养生|会所).*'
group by reg_rules
order by mch_cnt desc;