【读书】Learning_SQL by Alan_Beaulieu第7章日期函数

SQL学习系列，最早于2023年春节期间，发布于微信公众号【格物致知】系列，一年后，整理到博客：

2024年 ㊗️新年慢乐·喜乐平安

喜乐君：近期在重新思考《数据可视化分析》的修订逻辑，特别是计算部分，14天数据分析训练营给了我一个重新思考的契机。近期阅读learning SQL，有得以补充技术的角度。今日把前几日阅读的SQL中行级别计算的函数列举如下。

This chapter deals with the generation, conversion and maipulation of strings, numeric and temporal data, and the SQL language does not include commands covering this functionality. Rather, build-in functions are used to facilitate data generation, convertion and manipulation while the SQL standard does specify some funtions, the database vendors often do not comply with the function specification.

在本章中，作者介绍了各种函数——我称之为行级别函数，聚合函数则在第八章groups and aggregates。分类清晰：string字符串、numeric数字、temporal时间数据。不过，在讲解每个功能时，作者是按照输入值和返回值来划分的，也就是语法的视角，而非功能的视角。

我先整理如下，之后针对关键内容笔记：

📌 字符串函数：生成、操作与字符位置计算详解
📌 日期函数：CAST转换、DATE_ADD偏移与提取
📌 行级函数：字符串、数字、时间三类行级别运算

1、working with string data

字符串函数string functions，完成对string字符的转化、修改、计算等场景。string由character构成，所以很多地方都涉及到character的位置、计算等。

1.1 string generation

在特殊标点和字符串处理上，经常需要专门的函数转化，或者要修改数据源。特别是单引号single quote，撇号apostrophe等。专门的符号有CHAR转化，比如小写字母a的char码转化是97，还可以使用concat函数组合，示例如下: 

CHAR(97) = a
CHAR(128,129,130,131,132,133,134,135,136,137)  
= Çüéâäàåçêë 
SELECT CONCAT('danke sch',CHAR(148), 'n');   
--  output：vdanke schön

1.2 string manipulation

字符串操作是这里的关键，电脑和数据库有其特殊的规范，让数据变得规范、易读，是双向的需求，期间的修改、处理、解析都可以称之为manipulation。作者按照返回的格式氛围 String functions that return numbers和String functions that return strings。

典型的函数是length()， 顾名思义，returns the number of characters in the string.

Sql Server / Tableau——len() 

字符串长度与数据类型的长度有何关系？比如varchar（30）字符串中的长度都是30吗？显然不是。 the Mysql server removes trailing spaces from char date when it is retrieved.

— 另一个函数是position(), find the location of a substring with a string.注意 语法中有一个 IN字符。实例如下：

-- vchar_fld = 'This string is 28 characters', SELECT POSITION('characters' IN vchar_fld) FROM string_tbl;-- output , 19

-- find 'is' in field vchar-fld from the 5th characterSELECT LOCATE('is', vchar_fld, 5) FROM string_tbl;

tableau作为后期的工具，它可以充分的借鉴之前语言的优势，后期我们会在很多地方发现类似的简化，特别是日期的datepart函数，datediff函数等。

— 另一个函数是strcmp，string compare的简称，用于比较两个函数的位置，从而返回1或者0，或者-1，1代表第一个字符串在后面字符串之前，0代表相同。Another function that takes strings as arguments and returns numbers is the string comparison function strcmp(). 

我还没有想到这个的使用场景，这里咱先忽略。

字符串函数中，返回string的最典型应用是字符串的合并、提取或者追加，它们都是string in，然后sting out。

首推concat函数，concat是concatenate的简化形式。

selectconcat( 子类别,产品名称),concat(`客户 Id`,' ', 客户名称)from tableau.superstore;

-- sql server SELECT fname || ' ' || lname || ' has been a ' || title || ' since ' || start_date emp_narrative FROM employeeWHERE title = 'Teller' OR title = 'Head Teller';

这个逻辑更加简单。

–另一个函数是insert function， used to either insert or replace charateristics in a string.

看上去很专业，insert可以视为拆分+合并，只是比多个函数实现效率更高。对于数据分析而言，这个insert的功能 ，远远不如replace应用的广泛。

-- id 实例 CN-2020-1973789

在tableau中，此类函数也应用广泛，substring函数可以用left、mid、right、split拆分或者多个的组合来实现。

3、working with Numeric data

和数字有关的函数基本两类：算数计算和精度调整。典型代表是求平方和round函数。

算数计算是针对singe number的通用计算，比如sin、cos、tan等三角函数，比如ln对数、sqrt平方根、exp指数函数等。在Tableau中，常用语弧线呈现等专业场景，我向来迎难绕道，在此不表。

这里有几个常用的函数：mod计算余数，pow幂计算。参考书中的案例，我在Macos的终端中运行如下：

mysql> select pow(3,4),mod(10,4),sin(10),sqrt(10),exp(3) ;

ceil 和floor函数分别是进位或者舍位到整数；round则是四舍五人（midpoint）的策略；truncate也是简单的删除（相当于舍位），不过可以控制精度。Both truncate() and round() also allow a negative value for the second argument, meanning that numbers to the left of the decimal place are truncated or rounded. 

mysql> select ceil(5.1),floor(5.8),round(5.1),round(5.6),truncate(10.364,1);

Unsigned means only positive. 我们可以把sign函数视为布尔函数——是否大于0.正数为1，负数为-1，0则为0。

mysql> select sign(10.02), sign(-10.02),sign(0);

4. working with Temporal data

日期函数是比较复杂，而又特别重要的。一个原因是它本身有很多的数据展现方式，即便是一个国家内也有很多种写法，更有全世界time zones的影响。

4.1 Generating temporal Data

you can generate temporal data via any of ght flowing means:

书中把转化为多个场景，String representations of temporal data，String-to-date conversions和Functions for generating dates

不同的时间，对应不同的标准字符串样式。书中有7-2和7-3的两个图示。 To build a string that the server can interpret as a date, datetime, or time, you need to put the various components together in the order shown in Table 7-3.

最重要的函数是CAST()函数，可以用于转化为三种实践类型:DATE,DATETIME, TIME ;基于相同格式的时间，我可以转化三种类型，甚至并非完全规范的时间都可以。如下：

mysql> Select cast('2021-01-31 13:30:29' AS DATETIME) as now, 

你会发现，虽然部分与tableau dateparse不同，但是解析方法基本一致。解析是否成功，取决于解析规则是否准确。The str_to_date() function returns a datetime, date, or time value depending on the contents of the format string

mysql> Select str_to_date('January,31 2022','%M,%d %Y');
+-------------------------------------------+
| str_to_date('January,31 2022','%M,%d %Y') |
+-------------------------------------------+
| 2022-01-31                                |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql>

If you are trying to generate the current date/time, then you won’t need to build a string, because the following built-in functions will access the system clock and return the current date and/or time as a string for you。

mysql> SELECT CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP();
+----------------+----------------+---------------------+
| CURRENT_DATE() | CURRENT_TIME() | CURRENT_TIMESTAMP() |
+----------------+----------------+---------------------+
| 2022-01-31     | 15:32:44       | 2022-01-31 15:32:44 |
+----------------+----------------+---------------------+
1 row in set (0.01 sec)

4.2 Manipulating temporal date

this section explors the build-in functions that take date arguments and return dates, strings,or numbers.作者分为三类，非常有意思，分别输入日期/时间，返回的是日期，或者字符串，或者数字，典型的代表是dateadd，extract和datediff。分别一览：

date_add() 函数 ——add any kind of interval (e.g., days, months, years) to a specified date to generate another date. 我查询当前的时间和日期，分别增加12小时和1个月，如下：

mysql> select date_add(current_timestamp(), interval 12 Hour) as now_plus12h,

extract() extracting information from date value

mysql> SELECT EXTRACT(YEAR FROM current_date()) as year, EXTRACT(month FROM current_date()) as month ,EXTRACT(month FROM current_date())+1 as month_plus1, dayname(current_date() ) as dayname;

take two date values and determine the number of intervals (days, weeks, years) between the two dates.

在Mysql中，datediff只能用于计算日期的间隔天数，而在sql server和tableau中，datediff可以指定datepart，返回间隔的小时、天、月份等等。

mysql> select datediff('2022-1-1', '2021-12-30') datediff ;

4.3 dealing with Time Zones

【后补】和current_*类似，还有一个函数last_date(),用于返回指定日期所属月份的最后一天，在业务中合理使用，非常重要。

mysql> select last_day('2021-01-20'), last_day('2022-01-25');

While every database server includes a number of proprietary functions used to convert data from one type to another, I recommend using the cast() function, which is included in the SQL:2003 standard and has been implemen- ted by MySQL, Oracle Database, and Microsoft SQL Server.转化使用cast函数，可以实现多个格式的相互转换，关键是被众多的工具支持。在tableau中，转换函数主要有date、datetime，str，int，float等，它们可以视为是cast函数的简化形式。

• 🌐 XILEJUN.com 全球 → https://xilejun.com
• 🌐 VIZWISE.cn 国内 → https://vizwise.cn
• 【读书笔记】Learning_SQL by Alan_Beaulieu第三章 → /posts/learning-sql-by-alan-beaulieu
• Learning_SQL by Alan_Beaulieu第七章日期函数 → /posts/learning-sql-by-alan-beaulieu
• [sql]Learning_SQL by Alan_Beaulieu Chapter 8 分组聚合 → https://xilejun.com/reading/sqllearning_sql-by-alan_beaulieu-chapter-8/
• 【读书】Learning_SQL by Alan_Beaulieu-第五/十章 连接 → /posts/learning-sql-by-alan-beaulieu
• 【读书】cross join妙用——在Tableau中借SQL生成“日历表” → /posts/cross-join-calander-table