SQL intern 29题记录及心得

• 表结构
  

1、

select count(distinct usr_id) 
from trx_rcd
where 
(mch_typ='休闲娱乐' or mch_typ='餐饮')
and trx_amt>=900 and substr(trx_time,1,7)='2021-08'

ps:记住or的优先级小于and

2、

select 
        weekday(load_dt)+1 as '星期'
        , count(distinct usr_id) as 'DAU'
from 
        td_load_rcd 
group by 1 
order by 2 desc

[图片]
代表含义是没有a和b=没有a没有b（非在前，内必反）

21、豹子手机号用户（4个连续数字，如6666）和非豹子号用户的笔均消费金额分别是多少？

with b as(
select usr_id,
  case
   when phone_num REGEXP '[0-9](?=\\1{3})' THEN 'Leopard' `
   else ' no-Leopard'`
  end as is_豹子
from id_inf
)
select avg(a.trx_amt),is_豹子
from trx_rcd a
left join b
on a.usr_id=b.usr_id
group by is_豹子

关于表连接的题目
情况1：A去过，但B没有去过

SELECT DISTINCT a.mch_nm  
FROM trx_rcd a  
LEFT JOIN trx_rcd b ON a.mch_nm = b.mch_nm AND b.usr_id = '4066802156346859215'  
WHERE a.usr_id = '3581980399641129' AND b.usr_id IS NULL;

关于连接是指把a列全部跟b列匹配，不会把表格合并，只是粘在一起，由于left所以b表中匹配不上的都是用null表示，and是对表b进行调整，有没有and影响的是null的取法
情况2：A、B都去过

SELECT DISTINCT a.mch_nm  
FROM trx_rcd a  
（inner） JOIN trx_rcd b ON a.mch_nm = b.mch_nm
WHERE a.usr_id = '3581980399641129' AND b.usr_id='4066802156346859215'

ST_Distance_Sphere函数来计算地球上两点之间的大圆距离

SELECT u.usr_id,s.mch_nm
FROM log_loc u  
JOIN mch_loc s ON 1 = 1
WHERE s.mch_nm = '屈臣氏东门中路店'  
  AND ST_Distance_Sphere(  
        POINT(u.lon, u.lat),  
        POINT(s.lon, s.lat)  
      ) < 500;

27、请使用窗口函数得出如下数据。

with t as(
select distinct(mid(trx_time,1,7))as month,sum(trx_amt)over(partition by mid(trx_time,1,7)) as suming
from trx_rcd
where mch_typ ='休闲娱乐')
select sum(suming) over(order by month)-1233 as xsuming,month,suming
from t
order by month desc

28、请用rank窗口函数找出珠宝首饰和服饰美容分类下，人均消费金额最高的商家。

with t as(
SELECT  
    mch_typ,  
    mch_nm,  
    SUM(trx_amt) AS total_spent,  
    COUNT(DISTINCT usr_id) AS unique_customers,
    SUM(trx_amt)/ COUNT(DISTINCT usr_id) as aim
  FROM  
    trx_rcd 
  WHERE  
    mch_typ IN ('珠宝首饰','服饰美容')  
  GROUP BY mch_nm,mch_typ
  )
  select 
    mch_typ
    ,mch_nm
    ,rank() over(partition by mch_typ order by aim desc) as ranking
  from t

心得：group by不要和窗口函数放在一起，感觉容易出错误