---

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

• 【读书笔记】Learning_SQL by Alan_Beaulieu第三章 SQL关键子句入门
• Learning_SQL by Alan_Beaulieu第七章日期函数 行级别函数之SQL中与日期相关的函数
• [sql]Learning_SQL by Alan_Beaulieu Chapter 8 分组聚合 深刻理解分析的本质——分组聚合
• 【读书】Learning_SQL by Alan_Beaulieu-第五/十章 连接 理解多表合并，特别是Join
• 【读书】cross join妙用——在Tableau中借SQL生成“日历表” 多表合并的特殊性：笛卡尔积，构建日历表

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

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

Chapter 7 Data Generation, Convertion, and Manipulation

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时间数据。不过，在讲解每个功能时，作者是按照输入值和返回值来划分的，也就是语法的视角，而非功能的视角。

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

类型	子类型	函数functions	comments备注
字符串sting		Char()ASCii()quote()	适用于特殊字符串的处理，特别是非罗马字符
String manipulation	return numbers	length() position()locate()strcmp()	position类似于tableau的find，返回位置
	return strings	concate()insert()replace()substring()	返回字符串
Numeric	arthmetic functions	sin() cos() ……sqrt()mod()pow()
	controlling number position	ceil()floor()round()truncate()	ceiling和floor分别roll up and down to INTEGER
	handing signed data	sign()abs()
Temporal functions	abount Time Zone	getutcdate()
	generating temporal data	date()datetime()time()cast()str_to_date()current_date()current_time()current_timestamp()
	Munipulating Temportal data	–return datedate_add()last_day()convert_tz()dayname()extract()– return numbersdatediff()
convertion functions	convert	cast()

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

CHAR函数和ASCII函数彼此相对应，几乎是程序界的标配。在tableau中也有类似的函数，如果似乎对特殊字段的处理存在问题（大概率可能是我的电脑设置等问题）。

1.2 string manipulation

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

String functions that return numbers 

典型的函数是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 , 19positing函数可与tableau中find对应。

– 另一个函数是locate，它可以设置检索位置的开始位置，比如从第五个字符开始查找。 
If you want to start your search at something other than the first character of your target string, you will need to use the locate() function, 

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

很明显，tableau中没有locate函数，不过可以通过find+parameter结合实现类似的功能。同时，tableau中还有一个查找第几个字符的函数findnth。如下所示：

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 functions that return string

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

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

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

Oracle中的concat函数只能合并两个字符串，而在SQL Server中，则使用连字符代替这个功能—— concatenation operator (||) 

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

为什么要说这个？因为tableau也没有concat函数，而是采用了+加号作为 concatenation operator。

这个逻辑更加简单。

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

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

-- id 实例 CN-2020-1973789select`订单 Id`,insert(`订单 Id`,1,0,'order id'), insert(`订单 Id`,1,2,'order id'), replace(`订单 Id`,'CN', 'China') -- replace cn to china from tableau.superstore;-- 

另一个关键函数是substring ，sub-string 和insert ,concat相反， extract a substring from a string。理解的关键是extract部分。

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

3、working with Numeric data

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

3.1 Performing arithmetic functions算术计算函数

算数计算是针对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) ;+----------+-----------+---------------------+--------------------+--------------------+| pow(3,4) | mod(10,4) | sin(10) | sqrt(10) | exp(3) |+----------+-----------+---------------------+--------------------+--------------------+| 81 | 2 | -0.5440211108893699 | 3.1622776601683795 | 20.085536923187668 |+----------+-----------+---------------------+--------------------+--------------------+1 row in set (0.00 sec)mysql>

3.2 controlling number precision

主要有ceil(),floor(),round(), truncate()函数

• ceil_ round up to the closest integer.
• floor_ round down to the cloest integer.
• round()_ round up or down from the midpoint between two integers. 
• truncate_ have an optional second argument to specify the number of digits to the right of the decimal, but truncate() simply discards the unwanted digits without rounding.

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);+-----------+------------+------------+------------+--------------------+| ceil(5.1) | floor(5.8) | round(5.1) | round(5.6) | truncate(10.364,1) |+-----------+------------+------------+------------+--------------------+| 6 | 5 | 5 | 6 | 10.3 |+-----------+------------+------------+------------+--------------------+1 row in set (0.00 sec)mysql>

tableau中有完全对应的函数，除了truncate。

3.3 handing signed data

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

mysql> select sign(10.02), sign(-10.02),sign(0);+-------------+--------------+---------+| sign(10.02) | sign(-10.02) | sign(0) |+-------------+--------------+---------+| 1 | -1 | 0 |+-------------+--------------+---------+1 row in set (0.00 sec)mysql>

4. working with Temporal data

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

• Generate：from string to date/time
• Manipulate：from date/time to date/string/number

4.1 Generating temporal Data

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

• copying data from an existing data, datetime, or time column
• executing a build-in function that returns a date, datetime, or time 
• building a string representation of the temporal data to be evaluated by the server

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

• String representations of temporal data

不同的时间，对应不同的标准字符串样式。书中有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.

• String-to-date conversions

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

mysql> Select cast('2021-01-31 13:30:29' AS DATETIME) as now,  -> cast('2021-01-31 13:40:40' AS DATE) as today, -> cast('2021-01-31 13:40:40' AS TIME) as time ;+---------------------+------------+----------+| now | today | time |+---------------------+------------+----------+| 2021-01-31 13:30:29 | 2021-01-31 | 13:40:40 |+---------------------+------------+----------+1 row in set (0.00 sec)
mysql> mysql> select cast('2022-1-31 13-40-30' as DATETIME) as now;+---------------------+| now |+---------------------+| 2022-01-31 13:40:30 |+---------------------+1 row in set (0.00 sec)-- 下面的not in the proper form to use cast()mysql> select cast('20220131 134030' as DATETIME) as now;+------+| now | +------+| NULL | -- 无法正确的识别。+------+1 row in set, 1 warning (0.00 sec)

• Functions for generating dates

时间格式不要太随意，cast还是可以的，但是如果非常不规范，就需要专门的解析函数了，比如str_to_date（）If you need to generate temporal data from a string, and the string is not in the proper form to use the cast() function, you can use a built-in function that allows you to provide a format string along with the date string. MySQL includes the str_to_date() function for this purpose. 使用cast无法指定form，使用str_to_date就需要指定了，常见的formate components如下所示。

你会发现，虽然部分与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)

简而言之，非常规范的系统中就会识别为对应格式，比较规范用cast，不规范用str_to_date函数；当前日期/时间使用Current_***。

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。分别一览：

• 4.2.1 Temporal functions that return dates

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, date_add(current_date(),interval 1 month) as today_plus1m;+---------------------+--------------+| now_plus12h | today_plus1m |+---------------------+--------------+| 2022-02-01 03:48:37 | 2022-02-28 |+---------------------+--------------+1 row in set (0.00 sec)mysql>

这里的关键是interval的使用规范，如下。

• 4.2.2 Temporal functions that return strings

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;+------+-------+-------------+---------+| year | month | month_plus1 | dayname |+------+-------+-------------+---------+| 2022 | 1 | 2 | Monday |+------+-------+-------------+---------+1 row in set (0.00 sec)mysql>

dayname() 可以视为是 extract( day from …)的简化函数，不过依然建议使用extract。特别注意，extract的结果数字，而dayname是字符串（上面的Monday）。因此，extract可以对应tableau中的datepart，而dayname则对应datename函数。

• 4.2.3 Temporal functions that return numbers

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 ;+----------+| datediff |+----------+| 2 |+----------+1 row in set (0.00 sec)

4.3 dealing with Time Zones

‼️ 关于last_date计算

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

mysql> select last_day('2021-01-20'), last_day('2022-01-25');+------------------------+------------------------+| last_day('2021-01-20') | last_day('2022-01-25') |+------------------------+------------------------+| 2021-01-31 | 2022-01-31 |+------------------------+------------------------+1 row in set (0.01 sec

在tableau中，目前只能使用 多个函数来返回last-day，如下：

5. conversion functions

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函数的简化形式。

• date = cast ( field AS DATE）；
• int = CAST （field AS Integer）；