select
year(list_date) as Y
,sum(case when industry in ('全国地产', '区域地产') then 1 else 0 end) as '地产'
,sum(case when industry = '软件服务' then 1 else 0 end) as '软件服务'
from stock_info
where year(list_date) between '2000' and '2024'
group by year(list_date)
order by 1
select
year(dt) as Y
,cast(avg(case when city = 'beijing' then tmp_h else null end) as decimal(4,2)) as '北京'
,cast(avg(case when city = 'shanghai' then tmp_h else null end) as decimal(4,2)) as '上海'
,cast(avg(case when city = 'shenzhen' then tmp_h else null end) as
decimal(4,2)) as '深圳'
,cast(avg(case when city = 'guangzhou' then tmp_h else null end)as
decimal(4,2)) as '广州'
from
weather_rcd_china
where year(dt) between 2011 and 2022
group by year(dt)
select
gd.gd_id
,gd.gd_nm
,count(fav.fav_trq) cont
from xhs_fav_rcd fav
join gd_inf gd on fav.mch_id = gd.gd_id
group by gd.gd_id, gd.gd_nm order by cont desc limit 1
select a.cust_uid, c.cust_uid from
(
select cust_uid, mch_nm from mt_trx_rcd1 where cust_uid='MT10000' group by cust_uid, mch_nm) a
left join
(
select cust_uid, mch_nm from mt_trx_rcd1 group by cust_uid, mch_nm
)c
on a.cust_uid <> c.cust_uid and a.mch_nm = c.mch_nm
group by a.cust_uid, c.cust_uid
having count(distinct a.mch_nm) >= 14
WITH mt10000_mch AS (
SELECT mch_nm
FROM mt_trx_rcd1
WHERE cust_uid = 'MT10000'
GROUP BY mch_nm
)
SELECT 'MT10000' cust_uid, t.cust_uid AS cust_uid_1
FROM mt_trx_rcd1 t
INNER JOIN mt10000_mch m ON t.mch_nm = m.mch_nm
WHERE t.cust_uid <> 'MT10000'
GROUP BY t.cust_uid
HAVING COUNT(*) >= 14;
with can as(
select
mch_nm
from mt_trx_rcd1
where cust_uid = 'MT10000' group by mch_nm
),
uid as(
select
cust_uid
frommt_trx_rcd1
where mch_nm in(
select * from can
) and cust_uid != 'MT10000'
)
select
a.cust_uid
,b.cust_uid
from mt_trx_rcd1 a inner join uid b on a.cust_uid <> b.cust_uid
where a.cust_uid = 'MT10000' group by a.cust_uid, b.cust_uid
with a as(
select
cust_uid
from mt_trx_rcd1 where mch_nm = '庄家界(千灯店)' and cust_uid != 'MT10000'
),
b as(
select
cust_uid
from mt_trx_rcd1 where mch_nm = '黄记烘培宫廷桃酥王' and cust_uid != 'MT10000'
),
c as(
select
cust_uid
from mt_trx_rcd1 where mch_nm = '品众素心素食餐厅' and cust_uid != 'MT10000'
),
d as(
select
cust_uid
from mt_trx_rcd1 where mch_nm = '一枚帅哥做的菜' and cust_uid != 'MT10000'
),
e as(
select
a.cust_uid
from a inner join b on a.cust_uid = b.cust_uid
inner join c on a.cust_uid = c.cust_uid
inner join d on a.cust_uid = d.cust_uid
)
select
d1.cust_uid
,e.cust_uid
from mt_trx_rcd1 d1
inner join e ond1.cust_uid <> e.cust_uid
where d1.cust_uid = 'MT10000'
group by d1.cust_uid, e.cust_uid
with zjj as(
select
cust_uid
from mt_trx_rcd1 where mch_nm = '庄家界(千灯店)' and cust_uid != 'MT10000' group by cust_uid
),
hj as(
select
cust_uid
from mt_trx_rcd1 where mch_nm = '黄记烘培宫廷桃酥王' and cust_uid != 'MT10000' group by cust_uid
),
pz as(
select
cust_uid
from mt_trx_rcd1 where mch_nm = '品众素心素食餐厅' and cust_uid != 'MT10000' group by cust_uid
),
all_1 as(
select
zjj.cust_uid
from zjj inner join hj onzjj.cust_uid = hj.cust_uid
inner join pz onzjj.cust_uid = pz.cust_uid
)
select
d1.cust_uid
,a.cust_uid
from mt_trx_rcd1 d1
inner join all_1 a on d1.cust_uid != a.cust_uid where d1.cust_uid = 'MT10000'
group by d1.cust_uid
,a.cust_uid
with zjj as(
select
cust_uid
from mt_trx_rcd1 where mch_nm = '庄家界(千灯店)' and cust_uid != 'MT10000' group by cust_uid
),
hj as(
select
cust_uid
from mt_trx_rcd1 where mch_nm = '黄记烘培宫廷桃酥王' and cust_uid != 'MT10000' group by cust_uid
),
pz as(
select
cust_uid
from mt_trx_rcd1 where mch_nm = '品众素心素食餐厅' and cust_uid != 'MT10000' group by cust_uid
),
all_1 as(
select
zjj.cust_uid
from zjj inner join hj onzjj.cust_uid = hj.cust_uid
inner join pz onzjj.cust_uid = pz.cust_uid
)
select
d1.cust_uid
,a.cust_uid
from mt_trx_rcd1 d1
inner join all_1 a on d1.cust_uid != a.cust_uid where d1.cust_uid = 'MT10000'
select
a.cust_uid
,c.cust_uid
from(
select
cust_uid
,mch_nm
from mt_trx_rcd1
where cust_uid = 'MT10000' group by cust_uid, mch_nm) a
left join
(
select
b1.cust_uid
from
(
select
cust_uid, mch_nm
from mt_trx_rcd1 where mch_nm = '庄家界(千灯店)' group by cust_uid, mch_nm
)b1
inner join
(
select
cust_uid, mch_nm
from mt_trx_rcd1 where mch_nm = '黄记烘培宫廷桃酥王' group by cust_uid, mch_nm
)b2 on b1.cust_uid = b2.cust_uid
)c on a.cust_uid <> c.cust_uid
group by a.cust_uid, c.cust_uid
order by 2
select
d1.cust_uid, d2.cust_uid, d2.mch_nm
from mt_trx_rcd1 d1
join mt_trx_rcd1 d2 on d1.mch_nm = d2.mch_nm
where d1.cust_uid = 'MT10000'
and (d2.mch_nm = '庄家界(千灯店)' or d2.mch_nm = '黄记烘培宫廷桃酥王')
group by d1.cust_uid, d2.cust_uid, d2.mch_nm
select
d1.cust_uid, d2.cust_uid, d2.mch_nm
from mt_trx_rcd1 d1
join mt_trx_rcd1 d2 on d1.mch_nm = d2.mch_nm
where d1.cust_uid = 'MT10000'
and d2.mch_nm = '庄家界(千灯店)' or d2.mch_nm = '黄记烘培宫廷桃酥王'
group by d1.cust_uid, d2.cust_uid, d2.mch_nm
with temp_table_tx as(
select
cust_uid
,count(*) transaction_count
from
mt_trx_rcd_f
group by cust_uid
),
temp_table_act as(
select
cust_uid
,count(distinct date(trx_dt)) active_days_count
from
mt_trx_rcd_f
group by cust_uid
),
ranked_temp_tx as (
select
cust_uid
,transaction_count
,rank() over(order by transaction_count desc) rn
from
temp_table_tx
),
ranked_temp_act as(
select
cust_uid
,active_days_count
,rank() over(order by active_days_count desc) rn
from
temp_table_act
)
select
t.cust_uid
,rt.transaction_count
,rt.rn transaction_rank
,ra.active_days_count
,ra.rn active_days_rank
,abs(cast(rt.rn AS SIGNED) - cast(ra.rn AS SIGNED)) as rank_difference
from
temp_table_tx t
join ranked_temp_tx rt on t.cust_uid = rt.cust_uid
join temp_table_act a on t.cust_uid = a.cust_uid
join ranked_temp_act ra on t.cust_uid = ra.cust_uid
with temp_table_tx as(
select
cust_uid
,count(*) transaction_count
from
mt_trx_rcd_f
group by cust_uid
),
temp_table_act as(
select
cust_uid
,count(distinct date(trx_dt)) active_days_count
from
mt_trx_rcd_f
group by cust_uid
),
ranked_temp_tx as (
select
cust_uid
,row_number() over(order by transaction_count desc) rn
from
temp_table_tx
),
ranked_temp_act as(
select
cust_uid
,row_number() over(order by active_days_count desc) rn
from
temp_table_act
)
select
t.cust_uid
,t.transaction_count
,rt.rn transaction_rank
,a.active_days_count
,ra.rn active_days_rank
,abs(cast(rt.rn AS SIGNED) - cast(ra.rn AS SIGNED)) as rank_difference
from
temp_table_tx t
join ranked_temp_tx rt on t.cust_uid = rt.cust_uid
join temp_table_act a on t.cust_uid = a.cust_uid
join ranked_temp_act ra on t.cust_uid = ra.cust_uid
with temp_table_tx as(
select
cust_uid
,count(*) transaction_count
from
mt_trx_rcd_f
group by cust_uid
),
temp_table_act as(
select
cust_uid
,count(distinct date(trx_dt)) active_days_count
from
mt_trx_rcd_f
group by cust_uid
),
ranked_temp_tx as (
select
cust_uid
,row_number() over(order by transaction_count desc) rn
from
temp_table_tx
),
ranked_temp_act as(
select
cust_uid
,row_number() over(order by active_days_count desc) rn
from
temp_table_act
)
select
t.cust_uid
,t.transaction_count
,rt.rn transaction_rank
,a.active_days_count
,ra.rn active_days_rank
,cast(rt.rn AS SIGNED) - cast(ra.rn AS SIGNED) as rank_difference
from
temp_table_tx t
join ranked_temp_tx rt on t.cust_uid = rt.cust_uid
join temp_table_act a on t.cust_uid = a.cust_uid
join ranked_temp_act ra on t.cust_uid = ra.cust_uid