高级SQL查询技巧——利用SQL改善和增强你的数据
高级SQL查询技巧——利用SQL改善和增强你的数据
关系数据库系统和混合/云数据管理解决方案的用户都可以使用SQL灵活地访问业务数据，并以创新的方式进行转换或显示。
对业务数据库结构的透彻了解，对上游数据进行转换和聚合的巧妙解决方案，对于高效，完善的ETL至关重要。这是我在构建复杂的管道时学到的一些技巧，这些技巧使我的工作轻松而有趣。
一、计算滚动平均
使用时间序列数据时，为观察值计算滚动平均值或附加历史值可能会有所帮助。假设我想获取一家公司每天售出的小部件数量。我可能想包括7天移动平均线，或附上上周出售的工作日小部件，以查看业务与上周相比的表现。我可以通过将数据集连接到自身上，并使用日期列上的操作来选择单个值或观察范围来做到这一点。
首先，让我们开始使用Db2库中名为WIDGET_ORDER_HISTORY的表中的以下代码，按日销售小部件：
select t1.date
, sum(t1.widgets_sold) as total_widgets_sold
from db2.widget_order_history t1
where t1.date between ‘2021–04–05’ and ‘2021–05–01’
group by t1.date如果我们想在表的另一个变量（例如特定存储）上添加任何过滤条件，则可以添加一个简单的WHERE语句：
select t1.date
, sum(t1.widgets_sold) as total_widgets_sold
from db2.widget_order_history t1
where (t1.date between ‘2021–04–05’ and ‘2021–05–01’)
and t1.store = 1234
group by t1.date位于其他表上的其他条件（即STORE_DATA）将需要附加的INNER JOIN：
select t1.date
, sum(t1.widgets_sold) as total_widgets_sold
from db2.widget_order_history t1
inner join (
select store
from db2.store_data
where state = ‘NY’
) t2
on t1.store = t2.store
where t1.date between ‘2021–04–05’ and ‘2021–05–01’
group by t1.date从提供的代码生成的示例时间序列数据:
二、自连接附加历史数据
现在，如果我想附加4/25 / 21–5 / 1/21这一周的7天滚动平均值，可以通过将表连接到自身上并利用在SUM（）函数。
当您只想满足表中的特定条件时，可以使用此技术来使用分组功能（即SUM（），COUNT（），MAX（））。它只会对满足WHEN子句中包含的规则的值求和。
在下面的示例中，如果表B的值在表A上当前观察日期的前7天之内，我们可以将这些销售量相加并除以7，以获得表A的每一行的每周滚动平均值：
select a.date
, a.total_widgets_sold
, sum(
case when (b.date between a.date-7 and a.date-1)
then b.total_widgets_sold
else 0
end)/7 as seven_day_avg
from (
select date
, sum(widgets_sold) as total_widgets_sold
from db2.widget_order_history
where date between ‘2021–04–25’ and ‘2021–05–01’
group by date
) a
left join (
select date
, sum(widgets_sold) as total_widgets_sold
from db2.widget_order_history
where date between ‘2021–04–05’ and ‘2021–05–01’
group by date
) b
on a.date = b.date
group by a.date
, a.total_widgets_sold
order by a.date2021日历年第17周的小部件销售，其7天平均值处于滚动状态：
如果要将历史值附加到每个观察值，则可以避免聚合，而只需根据指定间隔时间的日期加入表即可。
下面的示例将表B联接到表A上，以将日期回溯7天以获取前一个工作日的小部件销售：
select a.date
, a.total_widgets_sold
, b.total_widgets_sold as prev_wkday_sales
from (
select date
, sum(widgets_sold) as total_widgets_sold
from db2.widget_order_history
where date between ‘2021–04–25’ and ‘2021–05–01’
group by date
) a
left join (
select date
, sum(widgets_sold) as total_widgets_sold
from db2.widget_order_history
where date between ‘2021–04–04’ and ‘2021–05–01’
group by date
) b
on a.date -7 = b.date第20周第2021日历年的窗口小部件销售以及上周的工作日窗口小部件销售：
将表联接到自身上是一种非常灵活的方式，可以向数据集添加汇总列和计算列。
分组功能（例如SUM（）和COUNT（）与CASE（）语句）的创造性使用为功能工程，分析报告和各种其他用例带来了巨大的机会。
在实践中，如果查询通过子查询加入自身，并且查询量很大，则可以预期运行时间很长。解决此问题的一种方法是使用临时表来保存具有特定问题标准的初步结果。
例如，在SAS的WORK库中为整个时间范围创建一个小部件销售表，并多次查询该表。高效的代码结构（例如使用索引）也可以提高效率。
三、使用CASE语句处理复杂的逻辑
CASE语句的语法与整个数据科学中其他常用编程语言的逻辑不同（请参阅：Python / R）。
通过使用伪代码对逻辑规则进行周到的设计可以帮助避免由于不正确/不一致的规则而导致的错误。了解如何在SQL中编码嵌套逻辑对于释放数据中的潜力至关重要。
假设有一张购物者表，其中包含给定时间范围内的年龄，家庭状态和销售情况等大量特征。有针对性的营销活动正用于尝试提高普通购物者的销售额（Marketing已将平均购物者确定为消费在 100- 200之间的人）。
一旦被识别，Z世代/千禧一代购物者将获得数字优惠券，所有其他购物者将被邮寄一张印刷优惠券，该打印优惠券将根据他们所居住的州而有所不同。为简单起见，只有三个州的购物者居住。
这是在R和SQL中如何编码此逻辑的方法：
## Example of Nested Logic in R
if(shoppers$sales<=0){ print("Error: Negative/No Sales")}
else if(shoppers&sales<=100){ print("Shopper has below-average sales.")}
else if(shoppers&sales<=200){
if(shopper$age<41){print("Shopper has average sales and is Gen Z/Millennial.")}
else{
if(shopper$state=='NJ'){print("Shopper has average sales, is Gen X/Boomer/Greatest Gen., and lives in New Jersey.")}
else if(shopper$state=='NY'){print("Shopper has average sales, is Gen X/Boomer/Greatest Gen., and lives in New York.")
else(shopper$state=='CT'){print("Shopper has average sales, is Gen X/Boomer/Greatest Gen., and lives in Connecticut.")}
}
}
else{print("Shopper has above-average sales.")}
*Example of nested logic in SQL. No need to actually nest statements!;
, case when sales < 0
then 'Error: Negative/No Sales.'
when sales <=100
then 'Shopper has below-average sales.'
when sales <=200 and age <41
then 'Shopper has average sales and is Gen Z/Millennial.'
when sales <=200 and state = 'NJ'
then 'Shopper has average sales, is Gen X/Boomer/Greatest Gen., and lives in New Jersey.'
when sales <=200 and state = 'NY'
then 'Shopper has average sales, is Gen X/Boomer/Greatest Gen., and lives in New York.'
when sales <=200 and state = 'CT'
then 'Shopper has average sales, is Gen X/Boomer/Greatest Gen., and lives in Connecticut.'
else 'Shopper has above-average sales.'
end as shopper_classification周到地使用CASE语句将使您能够构建复杂业务逻辑的任何组合。
但是，SQL逻辑与其他编程语言所需要的思维方式略有不同。
结合分组功能，这些工具可以为数据科学家提供竞争优势，以获取和转换用于特征工程，商业智能，分析报告等的数据源！
文丨Soundhearer
图丨来源于网络
腾讯云开发者
