到底哪里错了?我能看到的trx_amt分别就是288、388、588、668、888、928、1088……呀
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=668 then '4.Doi'
when trx_amt=888 then '5.DoubleFly'
else '6.other' end)as ser_typ,
count(1) as trx_cnt,
min(date(trx_time)) as first_date
from cmb_usr_trx_rcd
where usr_id=5201314520 and mch_nm='红玫瑰按摩保健休闲'
group by ser_typ
order by ser_typ;
WITH user_login_days AS (
SELECT usr_id,
COUNT(DISTINCT DATE(login_time)) AS login_days
FROM user_login_log
WHERE DATE(login_time) BETWEEN CURDATE() - INTERVAL 180 DAY AND CURDATE()
GROUP BY usr_id
)
select sum(case when login_days between 1 and 5 then 1 else 0 end) as days_1_to_5,
sum(case when login_days between 6 and 10 then 1 else 0 end) as days_6_to_10,
sum(case when login_days between 11 and 20 then 1 else 0 end) as days_11_to_20,
sum(case when login_days>20 then 1 else 0 end) as days_over_20
from user_login_days
with user_login_days as(
select usr_id,
date(login_time) as login_date
from user_login_log
where date(login_time)>=date_sub(current_date(),interval 180 day)),
distinct_login_days as(
select usr_id,
count(1) as login_days
from user_login_days
group by usr_id)
select sum(case when login_days between 1 and 5 then 1 else 0 end) as days_1_to_5,
sum(case when login_days between 6 and 10 then 1 else 0 end) as days_6_to_10,
sum(case when login_days between 11 and 20 then 1 else 0 end) as days_11_to_20,
sum(case when login_days>20 then 1 else 0 end) as days_over_20
from distinct_login_days
WITH user_login_days AS (
SELECT usr_id,
DATE(login_time) AS login_date
FROM user_login_log
WHERE login_time >= DATE_SUB(CURDATE(), INTERVAL 180 DAY)
),
distinct_login_days AS (
SELECT usr_id,
COUNT(1) AS login_days
FROM user_login_days
GROUP BY usr_id
)
SELECT
SUM(CASE WHEN login_days BETWEEN 1 AND 5 THEN 1 ELSE 0 END) AS days_1_to_5,
SUM(CASE WHEN login_days BETWEEN 6 AND 10 THEN 1 ELSE 0 END) AS days_6_to_10,
SUM(CASE WHEN login_days BETWEEN 11 AND 20 THEN 1 ELSE 0 END) AS days_11_to_20,
SUM(CASE WHEN login_days > 20 THEN 1 ELSE 0 END) AS days_over_20
FROM
distinct_login_days;
select count(distinct case when time(login_time) between '07:00:00' and '09:00:00'
or time(login_time) between '18:00:00' and '20:00:00' then usr_id end) as commute,
count(distinct case when time(login_time) between '11:00:00' and '13:00:00' then usr_id end) as lunch_break,
count(distinct case when time(login_time) between '22:00:00' and '23:59:59'
or time(login_time) between '00:00:00' and '01:00:00' then usr_id end) as bedtime
from user_login_log
where login_time>=date_sub(current_date(),interval 1 month)
and login_time<current_date()
select count(distinct case when time(login_time) between '07:00:00' and '09:00:00'
or login_time between '18:00:00' and '20:00:00' then usr_id end) as commute,
count(distinct case when time(login_time) between '11:00:00' and '13:00:00' then usr_id end) as lunch_break,
count(distinct case when time(login_time) between '22:00:00' and '23:59:59'
or login_time between '00:00:00' and '01:00:00' then usr_id end) as bedtime
from user_login_log
where login_time>=date_sub(current_date(),interval 30 day)
and login_time<current_date()
select count(distinct usr_id) as active_users
from user_login_log
where login_time >= date_sub(current_date(),interval 30 day)
and login_time < current_date()
select count(distinct usr_id) as active_users
from user_login_log
where login_time<current_date() and login_time >= date_sub(current_date(),interval 30 day)
select count(distinct usr_id) as active_users
from user_login_log
where login_time>=date_sub(current_date(),interval 30 day)
and login_time<current_date()
select count(distinct usr_id) as active_users
from user_login_log
where login_time>=date_sub(current_date(),interval 30 day)
and login_time<date_format(current_date(),'%Y-%m-30 00:00:00')
select g.gd_id,g.gd_nm,
count(1) as fav_count
from gd_inf g
left join xhs_fav_rcd f
on g.gd_id=f.mch_id
group byg.gd_id
order by fav_count desc
limit 1;
select year(list_date) as Y,
count(1) as cnt
from stock_info
where ts_code like '%sz%' and industry='银行'
group by year(list_date)
order by year(list_date);
select year(dt) as Y,
cast(avg(case when city='beijing' then tmp_h end) as decimal(4,2)) as beijing,
cast(avg(case when city='shanghai' then tmp_h end) as decimal(4,2)) as shanghai,
cast(avg(case when city='shenzhen' then tmp_h end) as decimal(4,2)) as shenzhen,
cast(avg(case when city='guangzhou' then tmp_h end) as decimal(4,2)) as guangzhou
from weather_rcd_china
where year(dt) between 2011 and 2022
group by year(dt)
order by year(dt);