开窗函数

1.排名：

ROW_NUMBER()，RANK()，DENSE_RANK()

SELECT employee_region region, employee_key, annual_salary, 
ROW_NUMBER() OVER (PARTITION BY employee_region ORDER BY annual_salary) 'Row Number'
RANK() OVER (PARTITION BY employee_region ORDER BY annual_salary) Rank, 
DENSE_RANK() OVER (PARTITION BY employee_region ORDER BY annual_salary) 'Dense Rank' 
FROM employee_dimension;

         region        | employee_key | annual_salary | Row Number | Rank | Dense Rank
-----------------------+--------------+---------------+------------+------+------------
 West                  |         5248 |          1200 |          1 |    1 |          1
 West                  |         6880 |          1204 |          2 |    2 |          2
 West                  |         5700 |          1214 |          3 |    3 |          3
 West                  |         9857 |          1218 |          4 |    4 |          4
 West                  |         6014 |          1218 |          5 |    4 |          4
 West                  |         9221 |          1220 |          6 |    6 |          5
 West                  |         7646 |          1222 |          7 |    7 |          6
 West                  |         6621 |          1222 |          8 |    7 |          6
 West                  |         6488 |          1224 |          9 |    9 |          7
 East                  |         8353 |          1200 |          1 |    1 |          1
 East                  |         9743 |          1202 |          2 |    2 |          2
 East                  |         9975 |          1202 |          3 |    2 |          2
 East                  |         9205 |          1204 |          4 |    4 |          3
 East                  |         8894 |          1206 |          5 |    5 |          4
 East                  |         7740 |          1206 |          6 |    5 |          4
 East                  |         7324 |          1208 |          7 |    7 |          5
 East                  |         6505 |          1208 |          8 |    7 |          5
 East                  |         5404 |          1208 |          9 |    7 |          5
 East                  |         9114 |          1212 |         10 |   10 |          6
 

应用

• 单日安装最大游戏

with t_install as (
select  t.game_name
        ,t.date
        ,sum(t.installs) as installs
        ,row_number() over(partition by t.date order by sum(t.installs) desc) as rn
from test.game t
where t.date >= '2020-01-01'
and t.date <= '2020-01-07'
group by t.game_name
         ,t.date
)
select game_name
       ,date
       ,installs
from t_install t
where rn = 1
order by t.date
;

  game_name  |     date    |  install  
-------------+-------------+-----------
 xx手游      |  2020-01-01 |   17517
 xx手游      |  2020-01-02 |   16524  
 xx装扮      |  2020-01-03 |   15436
 xx手游      |  2020-01-04 |   17865
 xx重击      |  2020-01-05 |   23043 
 xx装扮      |  2020-01-06 |   18432
 xx重击      |  2020-01-07 |   17864  
 

• 找出每个顾客第一次下单的时间及金额

SELECT t.CustomerID,t.TotalMoney,t.CreateTime 
FROM (SELECT CustomerID,TotalMoney,CreateTime,
             ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY createTime) AS num 
             FROM OrderInfo) t
WHERE t.num=1

• 找出每个顾客金额最大的订单金额 

SELECT t.CustomerID,t.TotalMoney 
FROM (SELECT CustomerID,TotalMoney,
             ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY TotalMoney desc) AS num
             FROM OrderInfo) t
WHERE t.num=1

• 统计每一个客户最近下的订单是第几次下的订单 

SELECT t.CustomerID,MAX(t.num) 
FROM (SELECT CustomerID,CreateTime,
             ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY CreateTime) AS num
             FROM OrderInfo) t
GROUP BY t.CustomerID

•  统计所有客户第3次下单订单信息

WITH cet AS(
SELECT *,ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY CreateTime) AS num
FROM OrderInfo)
SELECT * FROM cet WHERE num=3

 参考文章https://www.lanhusoft.com/Article/170.html

2.聚合函数

1）  sum() over (partition by ... order by ...) 

SELECT month, transaction_type, sales_quantity,
    SUM(sales_quantity)  OVER (PARTITION BY calendar_month_name ORDER BY date_dimension.date_key) AS SUM 
FROM store.store_sales_fact 
JOIN date_dimension USING(date_key)
WHERE calendar_month_name IN ('January') 
AND transaction_type= 'return';

  month  | transaction_type | sales_quantity | SUM
---------+------------------+----------------+------
 January | return           |              7 |  24
 January | return           |              3 |  24
 January | return           |              7 |  24
 January | return           |              7 |  24
 Febrary | return           |              6 |  22
 Febrary | return           |              3 |  22
 Febrary | return           |              7 |  22
 Febrary | return           |              5 |  22
 Febrary | return           |              1 |  22

 应用

• sum() over()：部分占整体百分比

with t_os_type as (
select t.os_type
        ,sum(t.impressions) as impressions 
        ,sum(t.clicks) as clicks    
        ,sum(t.spend) as spend 
        ,sum(sum(t.impressions)) over() as total_impressions
        ,sum(sum(t.clicks)) over() as total_clicks
        ,sum(sum(t.spend)) over() as total_spend
from datamart.dm_fact_multi_channel_country  t
where t.calendar_date >= date_trunc('year',current_date-1)
and t.os_type<>''
group by t.os_type
)
select t.os_type,'Impressions' type,round((t.impressions/t.total_impressions)*100,2)::numeric(18,2)||'%' rate
from t_os_type t 
union all 
select t.os_type,'Clicks' type_,round((t.clicks/t.total_clicks)*100,2)::numeric(18,2)||'%' rate
from t_os_type t 
union all 
select t.os_type,'Spend' type_,round((t.spend/t.total_spend)*100,2)::numeric(18,2)||'%' rate
from t_os_type t 
;

结果：                                                     对应效果图：

 

 

2） avg() over (partition by ... order by ...)