让你的 SQL 从优秀走向卓越：第 2 部分

神译局是36氪旗下编译团队，关注科技、商业、职场、生活等领域，重点介绍国外的新技术、新观点、新风向。

编者按：做开发的多少都要懂点SQL。但是只会点皮毛跟精通的差别还是很大的。有很多事情如果用SQL处理的话，会让你的后端开发工作事半功倍。那有哪些堪称神奇的SQL技巧呢？Taylor Brownlow用4篇系列文章总结了四个方面的SQL使用技巧，相信你会用得上的。此为第一部分，原文发表在Medium上，标题是：Take Your SQL from Good to Great: Part 2。

这是最有价值的SQL技巧系列文章的第 2 部分。本系列包括：

第 1 部分：通用数据表表达式

第 2 部分：跟日期有关的一切

第 3 部分：不一样的JOIN

第 4 部分：Window函数

SQL 的日期与时间

不管你写 SQL写了有多长的时间，有一点可以肯定，你不会喜欢跟日期和时间打交道。日期时间的处理棘手得令人惊讶，有时候甚至会折腾你几个小时。你怎么想也没想到要这么久吧？

所以，在这篇系列文章里面，我会介绍一些有用的日期/时间处理“技巧”，不管是菜鸟还是专家应该都可以从中受益。

行动卡

在深入研究代码之前，重要的是评估一下有哪些高级工具可以使用。这就像玩棋盘游戏 《瘟疫》，让你的角色的行动卡始终保持可见，好提醒你每一回合可以执行哪些动作。

下面就是我们的 SQL 日期/时间行动卡，其中包括了可以对日期/时间执行的动作。每一个动作都有一系列独立的函数和语法，但站在较高的层面对分析进行规划很有帮助。

比方说，假设你手头有一些字符串化的日期列销售数据，老板跟你说想看下季度汇总，那么你需要：

将字符串解析为日期（“20200110" ➡ 2020年1月10日）

把日期截断成每季度的开始日期（2020年1月10日➡ 2020 -01-01 ）

对日期进行格式化，让它们在图表或表格里面显得比较好看（2020 -01-01 ➡ “2020 Q1”）

SQL 日期/时间行动卡。

对于这些操作，不同的SQL语言会有自己的函数，不过令人遗憾的是，大家的语法也不一样。因此，我不会详细介绍每一个操作，不过你可以去看看我提供的指南：

BigQuery的日期与时间

Snowflake的日期与时间

最有价值的技巧

好了，接下来是有趣的部分——我们来点真正有用的技巧。以下是我最喜欢的日期/时间处理“技巧”清单：

1. 隐式转换

大多数数据库都有类似“隐式转换”的功能，也就是说，在部分上下文环境下，输入日期字符串（“2020-01-01”），数据库就会自动计算出你想用日期型的2020年1月1日。

这一点对于过滤日期字段特别有用。

例子：下面这个查询会提取 2017 年伦敦所有的天气数据。

好处：如果没有这个的话，我就得先把字符串转换/解析成日期，或者在过滤的时候用析取函数来获取我想要的日期部分。但是对于类似这么简单的日期范围，我肯定会一直用 'YYYY-MM-DD' 这样的字符串格式作为过滤器的。

警告：跟我下面要讲的一点会有直接冲突，大多数数据库要求这些日期是采取标准的ISO格式：“YYYY-MM-DD”，不过有的可以让你自己定制。

2. 放眼全球

作为住在欧洲的美国人，每当我看到 2020/10/06 之类的日期时，我就会感到一阵恐慌，因为我不能确定我们在谈论的是 10 月 6 日还是 6 月 10 日。

而且，当我看到 2020/10/06 10:00 AM 时，我会更加困惑。这是指当地时间上午 10 点吗？还是世界标准时间？或者夏令时？

就算你的数据跟团队都在同一个时区内，要考虑日期和时间的存放和呈现方式也至关重要。

为了解决这个问题，我一般：

始终采用UTC 时间戳，如果需要的话，给针对时区调整的日期和时间创建新的字段

在显示日期的时候，我总会在最后一步把日期重新格式化为：MONTH DAY, YEAR（月日年，Oct 6, 2020，2020 年 10 月 6 日）。（但我知道其他地方大多数人的做法肯能会不一样）

例子：在我对 Netflix 数据进行的一项分析当中，每一个节目的开始时间都用UTC 时间戳的形式给出。但是为了计算出一天当中我看电视最多的时段，我需要把时间转换为当地时间。

全球时间戳格式化的标准化。

好处：看到我的日期和时间可以用UTC和当地时间表示会让我感到安心，然后我就知道怎么去用合适的格式处理相应问题。

警告：每一种工具默认的日期显示方式会有所不同。比方说，在很多 BI 工具理念，时间戳会自动调整为本地时间，所以你的定制转换有可能是多此一举的。如有疑问的话，可更改自己的计算机时区去看看查询的输出有没有改变！

3.日期生成

如果我们要的不是找出在我们的数据集里面事情的的发生时间，而是想知道什么时候没有发生那样的事情该怎么办？比方说这名用户什么时候没有在用我们的app？

这类工作需要一组完全不是来自于我们的数据的统一日期。

在进行聚类分析的时候这个特别有用。

例子：假设我想查看自己每天在给定时间段内观看了多久的电视（（包括我没有观看任何 Netflix 内容的那些时间））。我需要生成自己想要了解的日期，然后把它加入我的 Netflix 数据里面。当我在那一天没有收看任何电视时，那个日期应该会出现一个 NULL 值：

好处：找出某件事在某些时段没有发生的频率跟找出其发生频率一样的重要。

警告：比较烦人的是，不同的SQL语言做这件事情在语法上很不一样。

译者：boxi。