前言:
当前大家对“sql计算差额”大约比较着重,咱们都想要了解一些“sql计算差额”的相关知识。那么小编在网络上收集了一些有关“sql计算差额””的相关资讯,希望你们能喜欢,同学们快快来了解一下吧!本文会从一个商务分析案例入手,说明SQL窗口函数的使用方式。通过本文的5个需求分析,可以看出SQL窗口函数的功能十分强大,不仅能够使我们编写的SQL逻辑更加清晰,而且在某种程度上可以简化需求开发。
数据准备
本文主要分析只涉及一张订单表orders,操作过程在Hive中完成,具体数据如下:
-- 建表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);需求1:收入增长
在业务方面,第m1个月的收入增长计算如下:100 *(m1-m0)/ m0
其中,m1是给定月份的收入,m0是上个月的收入。因此,从技术上讲,我们需要找到每个月的收入,然后以某种方式将每个月的收入与上一个收入相关联,以便进行上述计算。计算当时如下:
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
结果输出
monthrevenueprev_month_revenuerevenue_growth2020-01-01650NULLNULL2020-02-01125065092.32020-03-011500125020
我们还可以按照按城市分组进行统计,查看某个城市某个月份的收入增长情况
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
结果输出
monthcityrevenuerevenue_growth2020-01-01上海450NULL2020-02-01上海950111.12020-03-01上海10005.32020-01-01北京200NULL2020-02-01北京300502020-03-01北京50066.7
需求2:累计求和
累计汇总,即当前元素和所有先前元素的总和,如下面的SQL:
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
结果输出
monthrevenuerunning_total2020-01-016506502020-02-01125019002020-03-0115003400
我们还可以使用下面的组合方式进行分析,SQL如下:
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
结果输出:
order_idcustomer_idcityadd_timeamountamount_totalrunning_sumrunning_sum_by_customertrailing_avg1A上海2020-01-01 00:00:00.00000020034002002002002B上海2020-01-05 00:00:00.00000025034004502502253C北京2020-01-12 00:00:00.0000002003400650200216.6666674A上海2020-02-04 00:00:00.00000040034001050600262.55D上海2020-02-05 00:00:00.000000250340013002502605D上海2020-02-05 12:00:00.00000030034001600550266.6666676C北京2020-02-19 00:00:00.00000030034001900500283.3333337A上海2020-03-01 00:00:00.00000015034002050750266.6666678E北京2020-03-05 00:00:00.00000050034002550500316.6666679F上海2020-03-09 00:00:00.00000025034002800250291.66666710B上海2020-03-21 00:00:00.00000060034003400850
需求3:处理重复数据
从上面的数据可以看出,存在两条重复的数据**(5,"D","上海","2020-02-05 00:00:00.000000",250), (5,"D","上海","2020-02-05 12:00:00.000000",300),**显然需要对其进行清洗去重,保留最新的一条数据,SQL如下:
我们先进行分组排名,然后保留最新的那条数据即可:
SELECT *FROM ( SELECT *, row_number() over (partition by order_id order by add_time desc) as rank FROM orders) tWHERE rank=1
结果输出:
t.order_idt.customer_idt.cityt.add_timet.amountt.rank1A上海2020-01-01 00:00:00.00000020012B上海2020-01-05 00:00:00.00000025013C北京2020-01-12 00:00:00.00000020014A上海2020-02-04 00:00:00.00000040015D上海2020-02-05 12:00:00.00000030016C北京2020-02-19 00:00:00.00000030017A上海2020-03-01 00:00:00.00000015018E北京2020-03-05 00:00:00.00000050019F上海2020-03-09 00:00:00.000000250110B上海2020-03-21 00:00:00.0000006001
经过上面的清洗过程,对数据进行了去重。重新计算上面的需求1,正确SQL脚本为:
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
结果输出:
monthrevenuerevenue_growth2020-01-01650NULL2020-02-01100053.82020-03-01150050
将清洗后的数据创建成视图,方便以后使用
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需求4:分组取TopN
分组取topN是最长见的SQL窗口函数使用场景,下面的SQL是计算每个月份的top2订单金额,如下:
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需求5:重复购买行为
下面的SQL计算重复购买率:重复购买的人数/总人数*100%以及第一笔订单金额与第二笔订单金额之间的典型差额:avg(第二笔订单金额/第一笔订单金额)
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
结果输出:
WITH结果输出:
orders_cleaned.order_idorders_cleaned.customer_idorders_cleaned.cityorders_cleaned.add_timeorders_cleaned.amountcustomer_order_nprev_order_amount1A上海2020-01-01 00:00:00.0000002001NULL4A上海2020-02-04 00:00:00.00000040022007A上海2020-03-01 00:00:00.00000015034002B上海2020-01-05 00:00:00.0000002501NULL10B上海2020-03-21 00:00:00.00000060022503C北京2020-01-12 00:00:00.0000002001NULL6C北京2020-02-19 00:00:00.00000030022005D上海2020-02-05 12:00:00.0000003001NULL8E北京2020-03-05 00:00:00.0000005001NULL9F上海2020-03-09 00:00:00.000000250
最终结果输出:
repeat_purchasesrevenue_expansion501.9666666666666668
总结
本文主要分享了SQL窗口函数的基本使用方式以及使用场景,并结合了具体的分析案例。通过本文的分析案例,可以加深对SQL窗口函数的理解。
gz号『大数据技术与数仓』,回复『资料』领取大数据资料包
标签: #sql计算差额