六条鲜为人知的SQL技巧，帮你每月省下100小时！

在我过去八年的数据职业生涯中，我使用过一些 SQL 技巧，节省了很多分析和构建 ETL 管道的时间，这些方法虽然简单，但是很少有人知道。

在本文中，我将分享六个我经常使用的技巧，如果你想了解更多数据分析相关内容，可以阅读以下这些文章：
数据分析新工具MindsDB–用SQL预测用户流失
DS数据科学家和DA数据分析师：要学习什么不同内容？
Pandas和SQL，数据科学家应该用哪个？
这六个SQL小技巧，让你的分析效率突飞猛进！

• 从表格中查找并删除重复项

with x as (select *, row_number() over(partition by [key],[key],[key] order by [key]) as rowRank from {schema}.{table})
select * from x where rowRank > 1;

重复是数据分析里很糟糕的事。重复项让数据生命周期变得非常复杂。重复项会弄乱分析或图表——尤其是那些不能用 DISTINCT 的分析或图表。识别重复项的方法很多——但我发现上面这个操作是最简单的。

只需将主查询包在 CTE 中，然后，在你想检查的变量后面，在所有table keys上添加用来分区的row_number。分区必须包含所有table keys能正常工作，否则可能会分类到非重复项。这里，row_number的目的是对你的keys进行排序。CTE后，运行一个简单的select，过滤 WHERE，条件是新的 row_number 函数字段大于 1。输出将返回所有重复记录 – 因为所有 rowRank > 1 的记录在表中都有一个重复的key。此外，你可以通过运行以下命令查看有多少重复记录：

with x as (select *, row_number() over(partition by [key],[key],[key] order by [key]) as rowRank from {schema}.{table})
select [keys], max(rowRank) - 1 num_duplicates from x group by [keys];

最后，如果你想删除所有重复项，实际上，你可以使用带有 CTE 的delete语句！

with x as (select *, row_number() over(partition by [key],[key],[key] order by [key]) as rowRank from {schema}.{table})
delete * from x where rowRank > 1;

注意：delete语句会永久删除表中的记录——所以操作的时候要非常小心。如果想要测试此方法，你可以建一个临时的副本，先在副本上运行delete操作。然后，在对主表执行delete操作之前，要保证函数的质量。

• 从表中查询最近的一组记录

select a.*, a.[date] from {schema}.{table} a 
join (select max(date) maxDate from schema.table) b 
on a.date = b.maxDate

大多数数据专业人员都要处理大量时间序列数据。然而，时间序列不仅仅是带日期戳的值——也可以是带有日期标记的数据集版本。例如，在我目前的工作中，我们经常“快照”数据集当天版本的副本，以便跟踪数据随时间的变化。因此，从表中获取最新的记录集（即最新的“版本”）变得很重要。上述查询通过将有问题的表join到MAX日期字段上的表，实现这一点。Inner join可以过滤掉非最大日期的所有记录。或者，你可以使用left join，然后使用 where进行过滤：

select a.*, a.[date], b.maxDate from {schema}.{table} a 
left join (select max(date) maxDate from schema.table) b 
on a.date = b.maxDate
where date = maxDate

• 在每月或每周开始/结束时汇总每日数据

每月

select [key], sum([field]),  DATEADD(month, DATEDIFF(month, 0, [date field]), 0) as month from {schema}.{table} group by [key]

每周开始

select [key], sum([field]),  DATEADD(wk, DATEDIFF(wk, 6, [date]), 6) as weekBeginning from {schema}.{table} group by [key]

上述两个技巧可以帮助你快速、轻松地在每月或每周开始的时候，对每天的时间序列数据进行分组。SQL 中还有其他日期函数也有类似功能，可以实现相同效果，但我发现上面这个方法是最简单的。这种技巧还可以帮助你在仪表板工具或 Excel 中轻松地显示时间序列。例如，我经常喜欢用以“YYYY-MM”来表示月份的视觉效果。以这种方式设置查询，可以让执行此操作更加容易。

• 聚合自定义（CASE WHEN）类别的数据

select [key], sum([field]), 
CASE WHEN date between '2022-09-01' and '2022-12-31' then 'Fall'
WHEN date between '2022-01-01' and '2022-03-31' then 'Winter'
WHEN date between '2022-04-01' and '2022-06-30' then 'Spring'
WHEN date between '2022-07-01' and '2022-08-31' then 'Summer' end as Seasons from {schema}.{table} group by 
CASE WHEN date between '2022-09-01' and '2022-12-31' then 'Fall'
WHEN date between '2022-01-01' and '2022-03-31' then 'Winter'
WHEN date between '2022-04-01' and '2022-06-30' then 'Spring'
WHEN date between '2022-07-01' and '2022-08-31' then 'Summer' end

通过这个技巧，你可以使用 CASE STATEMENT 和 GROUP BY 聚合自定义类别的数据。你可以像上面一样，在一个语句中完成，但如果你想避免使用长的组句，你可以用 CTE。（注意：在 GROUP BY 中，case 语句以“end”结尾，而不是像 SELECT 语句中的“end as”结尾）。

WITH X as (select [key], [field]), 
CASE WHEN date between '2022-09-01' and '2022-12-31' then 'Fall'
WHEN date between '2022-01-01' and '2022-03-31' then 'Winter'
WHEN date between '2022-04-01' and '2022-06-30' then 'Spring'
WHEN date between '2022-07-01' and '2022-08-31' then 'Summer' end as Seasons from {schema}.{table})
select [key], sum([field]), Seasons from X group by Seasons 

在这个示例中，我使用日期参数创建了一个“Seasons”字段，你也可以自己创建其他任何字段。

• 在同一张表中找出今天和昨天（或任何两个日期）之间的差异

-- MS SQL SERVER 2016 or later
with x as (
select *, row_number() over(partition by [keys] order by [date_field] desc) as dateOrder
from {schema}.{table}
where [date_field] >= dateadd(day,-2,getdate()))
,
x1 as (
select * from x where dateOrder = 1),
x2 as (select * from x where dateOrder = 2)
select [fields] from x1 
left join x2 on x1.key = x2.key (and x1.key = x2.key and x1.key = x2.key)
where x2.[key] is null 
-- POSTGRES SQL 
with x as (
select *, row_number() over(partition by [keys] order by [date_field] desc) as dateOrder
from {schema}.{table}
where [date_field] >= CURRENT_TIMESTAMP - interval '2 day'
,
x1 as (
select * from x where dateOrder = 1),
x2 as (select * from x where dateOrder = 2)
select [fields] from x1 
left join x2 on x1.key = x2.key (and x1.key = x2.key and x1.key = x2.key)
where x2.[key] is null

这个技巧非常小众，但技术人员经常使用。以下为实际用途：

• 检测每天有多少条新记录添加到表中。
• 识别在“快照”表中的两个日期之间添加的新记录（即如上所述，这些表具有相同数据集/数据源的时间戳副本）。
• 将一个表中的数据合并到另一个表中（简单的方法）

delete from {schema}.{target_table} where exists (select 1 from {schema}.{source_table} where {schema}.{source_table}.[key] = {schema}.{target_table}.[key])

把数据从一个表合并到另一个表的方法很多。实际上，MS SQL 有一个 MERGE 语句可以实现该目标。但是，我发现，在脚本化的 ETL 管道中设置数据合并时，上述方法是的最简单的。

我写过大量代码，用来自动从 API 获取数据，然后定期将新数据转储到数据库表中。通常情况下，我会这样做：利用 Python 脚本拉回新数据的指定时间（2 天 到 1 周或更长时间，具体取决于数据源），然后将所有数据推送到临时表中。新数据转到临时表之后，我会运行上面的delete语句，这个语句将扫描生产表中已经存在的记录。最后，删除目标表中的所有现有记录之后，从临时表向目标表运行一个简单的插入操作。

我希望上述的技巧中至少有一条是你没见到过的，并且可以帮助你简化查询和分析。如果你喜欢这篇文章，欢迎你关注我们！你还可以订阅我们的YouTube频道，观看大量数据科学相关公开课：https://www.youtube.com/channel/UCa8NLpvi70mHVsW4J_x9OeQ；在LinkedIn上关注我们，扩展你的人际网络！https://www.linkedin.com/company/dataapplab/

原文作者：Cameron Warren
翻译作者：Lia
美工编辑：过儿
校对审稿：Jiawei Tong
原文链接：https://towardsdatascience.com/6-lesser-known-sql-techniques-to-save-you-100-hours-a-month-10ceed47d3fe