SQL是数据世界中的通用语言,是数据从业人员最重要的技能之一。许多数据探索、数据操作、管道开发和仪表板创建都可以通过 SQL 完成的。
伟大的数据科学家与优秀的数据科学家的区别在于,伟大的数据科学家可以尽可能多地利用 SQL 的功能来处理数据。要充分利用 SQL 提供的所有功能,很大一部分是要知道如何使用窗口函数。
LEAD()和 LAG()主要用于将某个时间段与给定指标的前一个时间段进行比较,比如:
获得每年销售额与上一年销售额之间的差值
获得每月注册/转换/网站访问次数的增量
按月比较用户流失率
以如何查询成本的每月百分比变化为例
with monthly_costs as ( SELECT date , monthlycosts , LEAD(monthlycosts) OVER (ORDER BY date) as previousCosts FROM costs )SELECT date , (monthlycosts - previousCosts) / previousCosts * 100 AS costPercentChange FROM monthly_costs
以 SUM() 或 COUNT() 开头的窗口函数简单地计算运行总计。当你想要显示特定指标随时间的增长时,这是一个必备的工具,它在以下情况下很有用:
获得一段时间内的总收入和成本
获取每个用户在应用程序上花费的总时间
获取一段时间内的总转化次数
以如何包含每月费用的累积总和列:
SELECT date , monthlycosts , SUM(monthlycosts) OVER (ORDER BY date) as cumCosts FROM cost_table
AVG() 在窗口函数中非常强大,因为它可以计算一段时间内的移动平均值。移动平均线是一种简单但有效的短期预测值的方法。比如
获取每周销售额的总体趋势
获取每周转换或网站访问的总体趋势。例子:
以查询是获取转化次数的 10 天移动平均值的示例
SELECT Date , dailyConversions , AVG(dailyConversions) OVER (ORDER BY Date ROWS 10 PRECEDING) AS 10_dayMovingAverage FROM conversions
当想要获取第一条或最后一条记录时,ROW_NUMBER() 特别有用。
如何使用 ROW_NUMBER() 获取每个用户访问的第一个日期。
with numbered_visits as ( SELECT memberId , visitDate , ROW_NUMBER() OVER (PARTITION BY customerId ORDER BY purchaseDate) as rowNumber FROM gym_visits )SELECT * FROM numbered_visits WHERE rowNumber = 1
DENSE_RANK() 与 ROW_NUMBER() 类似,只是它为相等的值返回相同的排名。例如:
想拉出本周观看次数最多的 10 部 Netflix 节目
想根据花费的金额获得前 100 名用户
想查看 1000 个最不活跃用户的行为 例子:
如果你想按总销售额对顶级客户进行排名,则 DENSE_RANK() 将是一个合适的函数
SELECT customerId , totalSales , DENSE_RANK() OVER (ORDER BY totalSales DESC) as rank FROM customers
如果你对这5个概念了如指掌,那么当涉及到大多数SQL窗口函数问题时,你会做得很好。有所收获,点赞支持。