如何使用SQL窗口函数进行商务数据分析

-- 建表CREATE TABLE orders(    order_id int,    customer_id string,    city string,    add_time string,    amount decimal(10,2));-- 准备数据                              INSERT INTO orders VALUES(1,"A","上海","2020-01-01 00:00:00.000000",200),(2,"B","上海","2020-01-05 00:00:00.000000",250),(3,"C","北京","2020-01-12 00:00:00.000000",200),(4,"A","上海","2020-02-04 00:00:00.000000",400),(5,"D","上海","2020-02-05 00:00:00.000000",250),(5,"D","上海","2020-02-05 12:00:00.000000",300),(6,"C","北京","2020-02-19 00:00:00.000000",300),(7,"A","上海","2020-03-01 00:00:00.000000",150),(8,"E","北京","2020-03-05 00:00:00.000000",500),(9,"F","上海","2020-03-09 00:00:00.000000",250),(10,"B","上海","2020-03-21 00:00:00.000000",600);

WITHmonthly_revenue as (    SELECT    trunc(add_time,'MM') as month,    sum(amount) as revenue    FROM orders    GROUP BY 1),prev_month_revenue as (    SELECT     month,    revenue,    lag(revenue) over (order by month) as prev_month_revenue -- 上一月收入    FROM monthly_revenue)SELECT   month,  revenue,  prev_month_revenue,  round(100.0*(revenue-prev_month_revenue)/prev_month_revenue,1) as revenue_growthFROM prev_month_revenueORDER BY 1

WITHmonthly_revenue as (    SELECT     trunc(add_time,'MM') as month,    city,    sum(amount) as revenue    FROM orders    GROUP BY 1,2),prev_month_revenue as (    SELECT     month,    city,    revenue,    lag(revenue) over (partition by city order by month) as prev_month_revenue    FROM monthly_revenue)SELECT month,city,revenue,round(100.0*(revenue-prev_month_revenue)/prev_month_revenue,1) as revenue_growthFROM prev_month_revenueORDER BY 2,1

WITHmonthly_revenue as (    SELECT    trunc(add_time,'MM') as month,    sum(amount) as revenue    FROM orders    GROUP BY 1)SELECT month,revenue,sum(revenue) over (order by month rows between unbounded preceding and current row) as running_totalFROM monthly_revenueORDER BY 1

SELECT   order_id,   customer_id,   city,   add_time,   amount,   sum(amount) over () as amount_total, -- 所有数据求和   sum(amount) over (order by order_id rows between unbounded preceding and current row) as running_sum, -- 累计求和   sum(amount) over (partition by customer_id order by add_time rows between unbounded    preceding and current row) as running_sum_by_customer,    avg(amount) over (order by add_time rows between 5 preceding and current row) as  trailing_avg -- 滚动求平均FROM ordersORDER BY 1

SELECT *FROM (    SELECT *,    row_number() over (partition by order_id order by add_time desc) as rank    FROM orders) tWHERE rank=1

WITHorders_cleaned as (    SELECT *    FROM (        SELECT *,        row_number() over (partition by order_id order by add_time desc) as rank        FROM orders    )t    WHERE rank=1),monthly_revenue as (    SELECT    trunc(add_time,'MM') as month,    sum(amount) as revenue    FROM orders_cleaned    GROUP BY 1),prev_month_revenue as (    SELECT     month,    revenue,    lag(revenue) over (order by month) as prev_month_revenue    FROM monthly_revenue)SELECT month,revenue,round(100.0*(revenue-prev_month_revenue)/prev_month_revenue,1) as revenue_growthFROM prev_month_revenueORDER BY 1

CREATE VIEW orders_cleaned ASSELECT    order_id,     customer_id,     city,     add_time,     amountFROM (    SELECT *,    row_number() over (partition by order_id order by add_time desc) as rank    FROM orders)tWHERE rank=1

WITH orders_ranked as (    SELECT    trunc(add_time,'MM') as month,    *,    row_number() over (partition by trunc(add_time,'MM') order by amount desc, add_time) as rank    FROM orders_cleaned)SELECT     month,    order_id,    customer_id,    city,    add_time,    amountFROM orders_rankedWHERE rank <=2ORDER BY 1

WITH customer_orders as (    SELECT *,    row_number() over (partition by customer_id order by add_time) as customer_order_n,    lag(amount) over (partition by customer_id order by add_time) as prev_order_amount    FROM orders_cleaned)SELECTround(100.0*sum(case when customer_order_n=2 then 1 end)/count(distinct customer_id),1) as repeat_purchases,-- 重复购买率avg(case when customer_order_n=2 then 1.0*amount/prev_order_amount end) as revenue_expansion -- 重复购买较上次购买差异，第一笔订单金额与第二笔订单金额之间的典型差额FROM customer_orders