select mch_nm as merchant_name,
		case 
when mch_nm regexp '拼多多|上海寻梦信息技术' then '拼多多'
 		when mch_nm regexp '京东|北京京东世纪贸易' then '京东'
 		when mch_nm regexp '淘宝|天猫|浙江淘宝网络|浙江天猫网络' then '淘系'
 		when mch_nm regexp '抖音|北京抖音信息服务' then '抖音'
 		when mch_nm regexp '小红书|行吟信息科技' then '小红书'
else '其他'
end as platform
from 
(select mch_nm,count(*)
from ccb_trx_rcd
group by mch_nm
order by 2 desc) a 
order by 2 desc

select cust_uid,
		cust_uid_1
from
(
		select a.cust_uid,
 			b.cust_uid as cust_uid_1,
 			a.mch_nm
			from 
					(select distinct cust_uid,mch_nm from mt_trx_rcd1 where cust_uid='MT10000') a 
					inner join 
						(select distinct cust_uid,mch_nm from mt_trx_rcd1 where cust_uid<>'MT10000') b 
				on a.mch_nm=b.mch_nm
				group by 1,2,3) c 
group by 1,2
having count(mch_nm)>=14

select a.cust_uid,b.cust_uid as cust_uid_1
from 
(select distinct cust_uid,mch_nm from mt_trx_rcd1 where cust_uid='MT10000') a 
left join 
(select cust_uid from mt_trx_rcd1 
 group by cust_uid 
 having count(distinct case when mch_nm in ('庄家界(千灯店)','黄记烘培宫廷桃酥王','品众素心素食餐厅') then mch_nm else null end)=3
) b
on a.cust_uid<>b.cust_uid
group by 1,2
order by 2

select a.cust_uid,b.cust_uid as cust_uid_1
from 
(select distinct cust_uid,mch_nm from mt_trx_rcd1 where cust_uid='MT10000') a 
left join 
(select cust_uid from mt_trx_rcd1 
 group by cust_uid 
 having count(distinct case when mch_nm in ('庄家界(千灯店)','黄记烘培宫廷桃酥王','品众素心素食餐厅') then mch_nm else null end)=3
) b
on a.cust_uid<>b.cust_uid
order by 2

select a.cust_uid,
		c.cust_uid as cust_uid_1 
from 
(select distinct cust_uid,mch_nm from mt_trx_rcd1 where cust_uid='MT10000') a
left join 
(select b1.cust_uid
  from 
  (select distinct cust_uid, mch_nm from mt_trx_rcd1 where mch_nm='庄家界(千灯店)' ) b1
inner join 
  (select distinct cust_uid, mch_nm from mt_trx_rcd1 where mch_nm='黄记烘培宫廷桃酥王' ) b2
on b1.cust_uid = b2.cust_uid) c 
on a.cust_uid<>c.cust_uid 
group by 1,2
order by 2

select t1.cust_uid,
			t2.cust_uid_1,
t1.mch_nm
from 
		(select distinct cust_uid, 
 				mch_nm 
			from mt_trx_rcd1
			where cust_uid='MT10000'
			order by mch_nm) t1 
	inner join 
		(select distinct cust_uid as cust_uid_1,
		 			mch_nm
			from mt_trx_rcd1
			where mch_nm='兰州李晓明拉面馆'
			order by 1) t2
on t1.mch_nm=t2.mch_nm and t1.cust_uid<>t2.cust_uid_1
order by 2

select t1.cust_uid,
			t2.cust_uid_1,
t1.mch_nm
from 
		(select distinct cust_uid, 
 				mch_nm 
			from mt_trx_rcd1
			where cust_uid='MT10000'
			order by mch_nm) t1 
	inner join 
		(select distinct cust_uid as cust_uid_1,
		 			mch_nm
			from mt_trx_rcd1
			where mch_nm='兰州李晓明拉面馆'
			order by 1) t2
on t1.mch_nm=t2.mch_nm
order by 2

select t1.cust_uid,
			t2.cust_uid_1,
t1.mch_nm
from 
		(select distinct cust_uid, 
 				mch_nm 
			from mt_trx_rcd1
			where cust_uid='MT10000'
			order by mch_nm) t1 
	right join 
		(select distinct cust_uid as cust_uid_1,
		 			mch_nm
			from mt_trx_rcd1
			where mch_nm='兰州李晓明拉面馆'
			order by 1) t2
on t1.mch_nm=t2.mch_nm
order by 2

select distinct cust_uid,
		mch_nm
from mt_trx_rcd1
where mch_nm='兰州李晓明拉面馆'
order by 1

select distinct cust_uid,
			mch_nm
from mt_trx_rcd1
where cust_uid='MT10000'
order by 2

select dt,
			tmp_h,
tmp_l,
con 
from weather_rcd_china
where city='beijing'
and con like '%雪%'

select dt,
			tmp_h,
tmp_l,
wnd 
from weather_rcd_china
where city='beijing'
and con like '%雪%'

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 year(dt) as Y,
 			round(sum(case when city='beijing' then cast(tmp_h as decimal(10,5)) else 0 end)/
 count(case when city='beijing' then cast(tmp_h as decimal(10,5)) else null end),2)as '北京',
 round(sum(case when city='shanghai' then cast(tmp_h as decimal(10,5)) else 0 end)/
 count(case when city='shanghai' then cast(tmp_h as decimal(10,5)) else null end),2)as '上海',
 round(sum(case when city='shenzhen' then cast(tmp_h as decimal(10,5)) else 0 end)/
 count(case when city='shenzhen' then cast(tmp_h as decimal(10,5)) else null end),2)as '深圳',
 round(sum(case when city='guangzhou' then cast(tmp_h as decimal(10,5)) else 0 end)/
 count(case when city='guangzhou' then cast(tmp_h as decimal(10,5)) else null end),2)as '广州'
 from weather_rcd_china
 where (year(dt) between 2011 and 2022)
 and city in ('beijing','shanghai','shenzhen','guangzhou')
 group by 1
 order by 1

select city,
			sum(case when month(dt) in (12,1,2) and con like '%雪%' then 1 else 0 end) as snowy_days
from weather_rcd_china
group by city 
order by 2 desc

select t1.city,
 		count(t1.dt) as cloudy_days,
concat(round((count(t1.dt)/t2.ttl)*100,2),'%') as p 
 from weather_rcd_china t1 
 		inner join 
		 	(select city,
 		count(dt) as ttl
 			from weather_rcd_china
 			where year(dt)=2021
 			group by city) t2 
 		on t1.city=t2.city
 where year(dt)=2021
 and con like '%多云%'
 group by city 
 order by 2 desc

select city,
			cast(avg(tmp_h) as decimal(10,2)) as avg_tmp_h
 from weather_rcd_china
 where year(dt) = 2021
 group by city 
 order by 2 desc

select dt as year,
		 cast(avg_tmp_h as decimal(10,2)) as avg_tmp_h,
 significant_change
from 
(select dt,
		 avg_tmp_h,
 lag(avg_tmp_h,1) over(),
 avg_tmp_h-lag(avg_tmp_h,1) over(),
 case when abs(avg_tmp_h-lag(avg_tmp_h,1) over(order by dt))>=1 then 'Yes' else 'No' end as significant_change
from 
	(select year(dt) as dt,
 		 		avg(replace(tmp_h,'℃','')+0) as avg_tmp_h
	from weather_rcd_china
 where city = 'shenzhen'
	group by 1
	order by 1) t1) t2

select dt as year,
		 round(avg_tmp_h,2) as avg_tmp_h,
 significant_change
from 
(select dt,
		 avg_tmp_h,
 lag(avg_tmp_h,1) over(),
 avg_tmp_h-lag(avg_tmp_h,1) over(),
 case when abs(avg_tmp_h-lag(avg_tmp_h,1) over(order by dt))>=1 then 'Yes' else 'No' end as significant_change
from 
	(select year(dt) as dt,
 		 		avg(replace(tmp_h,'℃','')+0) as avg_tmp_h
	from weather_rcd_china
 where city = 'shenzhen'
	group by 1
	order by 1) t1) t2

select dt as year,
		 round(avg_tmp_h,2) as avg_tmp_h,
 significant_change
from 
(select dt,
		 avg_tmp_h,
 lag(avg_tmp_h,1) over(),
 avg_tmp_h-lag(avg_tmp_h,1) over(),
 case when avg_tmp_h-lag(avg_tmp_h,1) over()>=1 then 'Yes' else 'No' end as significant_change
from 
	(select year(dt) as dt,
 		 		avg(tmp_h) as avg_tmp_h
	from weather_rcd_china
 where city = 'shenzhen'
	group by 1
	order by 1) t1) t2