大数据开发-SQL

一。with ... as (...)

之前临时表都是一层一层嵌套的，最后嵌套下次很难阅读，使用这种方式可以大大提高sql的可阅读性

with temp1 as (
select * from ... where ..
),
temp2 as (
select * from ... where...
),
...
tempn as (
select * from ... inner join ... where
)
select a.*,b.*,c.* from temp1 a inner join temp2 b on a.id = b.id left join ..tempn c on a.iid = c.iid where ...

二.窗口函数

row_number() over(partition by ... order by ...)：排序1-2-3-4-5

rank() over(partition by ... order by ...)：排序 1-1-3

dense_rank() over(partition by ... order by ...)：排序 1-1-2-3

count() over(partition by ... order by ...)：求分组后的总数。

max() over(partition by ... order by ...)：求分组后的最大值。

min() over(partition by ... order by ...)：求分组后的最小值。

avg() over(partition by ... order by ...)：求分组后的平均值。

percent_rank() over(partition by ... order by ...) 求百分比，在当前的数据中处于前多少。比如你已经超过全国多少用户

 举一个场景，查询会员消费最多积分订单或者是消费金额最多的订单

--原理就是按userId分组按totalAmount排序赋值，然后我们取排第一的就是我们要的每个人的top
--类似的还可以取每个部门工资最高的
select *from 
(  select  
    row_number() over(partition by userId order by totalAmount desc) as rn,
    userId,orderId,totalAmount
    from  tb_order 
)a 
where rn=1

 再举一个场景，要求会员消费品类总额占总数的百分比，比如张三年度账单，衣服消费多少，通信费消费多少，餐饮消费多少，这些消费占比

select
  userId
  c.num/SUM(c.num) OVER(PARTITION by c.userId) AS percent
from 
(
  select 
  userId,b.category,count(category) as num
  from order
  where 
  group by a.userId,category
) c

三。case ....when...

比如要查看数据分布情况

select 

CASE

    WHEN a.orderNum>=1  AND a.orderNum<4 THEN "[1,4)"
    WHEN a.orderNum>=4  AND a.orderNum<7 THEN "[4,7)"
    WHEN a.orderNum>=7  AND a.orderNum<10 THEN "[7,10)"
    WHEN a.orderNum>=10  AND a.orderNum<13 THEN "[10,13)"
    WHEN a.orderNum>=13  AND a.orderNum<16 THEN "[13,16)"
    WHEN a.orderNum>=16  AND a.orderNum<20 THEN "[16,20)"
    WHEN a.orderNum>=20  AND a.orderNum<30 THEN "[20,30)"
    WHEN a.orderNum>=30  AND a.orderNum<45 THEN "[30,45)"
     WHEN a.orderNum>=45  THEN "[45,+∞)"
    END AS 单量,
    COUNT(*) AS 客户量

from 
a
GROUP BY
    CASE
    
  
    WHEN a.orderNum>=1  AND a.orderNum<4 THEN "[1,4)"
    WHEN a.orderNum>=4  AND a.orderNum<7 THEN "[4,7)"
    WHEN a.orderNum>=7  AND a.orderNum<10 THEN "[7,10)"
    WHEN a.orderNum>=10  AND a.orderNum<13 THEN "[10,13)"
    WHEN a.orderNum>=13  AND a.orderNum<16 THEN "[13,16)"
    WHEN a.orderNum>=16  AND a.orderNum<20 THEN "[16,20)"
    WHEN a.orderNum>=20  AND a.orderNum<30 THEN "[20,30)"
    WHEN a.orderNum>=30  AND a.orderNum<45 THEN "[30,45)"
     WHEN a.orderNum>=45  THEN "[45,+∞)"
END;