SQL（五）

目录

字符串函数

LEFT 和 RIGHT

LEFT & RIGHT 练习

POSITION、STRPOS 和 SUBSTR

POSITION 和 STRPOS 练习

​​​​​​CONCAT 

CONCAT & || 练习

CAST

---

字符串函数

你将学习多个技能，从而能够

1. 清理和重新整理混乱的数据。
2. 将列转换为不同的数据类型。
3. 处理 NULL 的技巧。

这样，你将能够对原始数据进行清理，并获得适合分析的整洁数据。

LEFT 和 RIGHT

我们查看了三个新的函数：

1. LEFT
2. RIGHT
3. LENGTH

LEFT 从起点（或左侧）开始，从特定列中的每行获取一定数量的字符。你可以使用 LEFT(phone_number, 3) 获取电话号码中的前三位。

RIGHT 从末尾（或右侧）开始，从特定列中的每行获取一定数量的字符。你可以使用 RIGHT(phone_number, 8) 获取电话号码的最后 8 位。

LENGTH 提供了特定列每行的字符数。我们可以使用 LENGTH(phone_number) 得出每个电话号码的长度。

LEFT & RIGHT 练习

1、在 accounts 表格中，有一个列存储的是每个公司的网站。最后三个数字表示他们使用的是什么类型的网址。请获取这些扩展并得出 accounts 表格中每个网址类型的存在数量。

select RIGHT(website,3) web,
             count(*) web_counts
from accounts
group by web;

2、请从 accounts 表格中获取每个公司名称的第一个字母，看看以每个字母（数字）开头的公司名称分布情况。

select left(name,1),count(*)
from accounts
group by 1;

3、使用 accounts 表格和 CASE 语句创建两个群组：一个是以数字开头的公司名称群组，另一个是以字母开头的公司名称群组。以字母开头的公司名称所占的比例是多少？

SELECT SUM(num) nums, SUM(letter) letters
FROM (SELECT name, CASE WHEN LEFT(UPPER(name), 1) IN ('0','1','2','3','4','5','6','7','8','9') 
                       THEN 1 ELSE 0 END AS num, 
         CASE WHEN LEFT(UPPER(name), 1) IN ('0','1','2','3','4','5','6','7','8','9') 
                       THEN 0 ELSE 1 END AS letter
      FROM accounts) t1;

4、元音是指 a、e、i、o 和 u。有多少比例的公司名称以元音开头，以其他音节开头的公司名称百分比是多少？

SELECT SUM(vowels) vowels, SUM(other) other
FROM (SELECT name, CASE WHEN LEFT(UPPER(name), 1) IN ('A','E','I','O','U') 
                        THEN 1 ELSE 0 END AS vowels, 
          CASE WHEN LEFT(UPPER(name), 1) IN ('A','E','I','O','U') 
                       THEN 0 ELSE 1 END AS other
         FROM accounts) t1;

有 80 的公司名称以元音开头，271 的公司以其他音节开头。元音的比例是 80/351，或 22.8%。因此，有 77.2% 的公司名称没有以元音开头。

POSITION、STRPOS 和 SUBSTR

我们查看了三个新的函数：

1. POSITION
2. STRPOS
3. LOWER
4. UPPER

POSITION 获取字符和列，并提供该字符在每行的索引。第一个位置的索引在 SQL 中是 1。如果你之前学习了其他编程语言，就会发现很多语言的索引是从 0 开始。这里，你发现可以使用 POSITION(',' IN city_state) 获取逗号的索引。

select *,position(' ' in name) as mm
from sales_reps
limit 1;

STRPOS 和 POSITION 提供的结果相同，但是语法不太一样，如下所示：STRPOS(city_state, ‘,’)。
注意，POSITION 和 STRPOS 都区分大小写，因此查找 A 的位置与查找 a 的结果不同。

因此，如果你想获取某个字母的索引，但是不区分大小写，则需要使用 LOWER 或 UPPER 让所有字符变成小写或大写。

POSITION 和 STRPOS 练习

1、使用 accounts 表格创建一个名字和姓氏列，用于存储 primary_poc 的名字和姓氏。

select left(primary_poc,position(' ' in primary_poc) - 1) as first_name, 
right(primary_poc,length(primary_poc)-position(' ' in primary_poc)) as last_name
from accounts;

2、现在创建一个包含 sales_rep 表格中每个销售代表姓名的列，同样，需要提供名字和姓氏列。

select left(name,position(' ' in name)) as first_name, 
right(name,length(name)-position(' ' in name)) as last_name
from sales_reps;

select left(name,strpos(name,' ')) as first_name,
right(name,length(name)-strpos(name,' ')) as last_name
from sales_reps

​​​​​​CONCAT 

1. CONCAT
2. Piping ||

这两个工具都能将不同行的列组合到一起。如何将存储在不同列中的名字和姓氏组合到一起，形成全名：CONCAT(first_name, ' ', last_name)，或者使用双竖线：first_name || ' ' || last_name

CONCAT & || 练习

1、accounts 表格中的每个客户都想为每个 primary_poc 创建一个电子邮箱。邮箱应该是 primary_poc的名字.primary_poc的姓氏@公司名称.com。

WITH t1 AS (
 SELECT LEFT(primary_poc,STRPOS(primary_poc, ' ') -1 ) first_name,
RIGHT(primary_poc,LENGTH(primary_poc) -STRPOS(primary_poc, ' ')) last_name, name
 FROM accounts)
SELECT first_name, last_name, CONCAT(first_name, '.', last_name, '@', name, '.com')
FROM t1;

2、你可能注意到了，在上一个答案中，有些公司名称存在空格，肯定不适合作为邮箱地址。看看你能否通过删掉客户名称中的所有空格来创建合适的邮箱地址，否则你的答案就和问题 1. 的一样。

WITH t1 AS (
 SELECT LEFT(primary_poc,STRPOS(primary_poc, ' ') -1 ) first_name,
RIGHT(primary_poc,LENGTH(primary_poc) -STRPOS(primary_poc, ' ')) last_name, 
replace(name,' ','') c_name
 FROM accounts)
SELECT first_name, last_name, CONCAT(first_name, '.', last_name, '@', c_name, '.com')
FROM t1;

3、我们还需要创建初始密码，在用户第一次登录时将更改。初始密码将是 primary_poc 的名字的第一个字母（小写），然后依次是名字的最后一个字母（小写）、姓氏的第一个字母（小写）、姓氏的最后一个字母（小写）、名字的字母数量、姓氏的字母数量，然后是合作的公司名称（全大写，没有空格）

with t1 as(
  select lower(primary_poc) as primary_poc,
  upper(name) as name 
  from accounts),
t2 as (
select left(primary_poc,strpos(primary_poc,' ')-1) first_name,
right(primary_poc,length(primary_poc)-strpos(primary_poc,' ')) last_name,
replace(name,' ','') c_name
from t1),
t3 as(
select left(first_name,1) ff1,right(first_name,1) fr1,left(last_name,1) lf1,right(last_name,1) lr1,
length(first_name) l_first_name,
length(last_name) l_last_name,c_name
from t2)
select ff1,fr1,lf1,lr1,l_first_name,l_last_name,
ff1 || fr1 || lf1 || lr1 || l_first_name || l_last_name || c_name mm
from t3;

||可以连接整数，concat不可。

WITH t1 AS (
 SELECT LEFT(primary_poc,STRPOS(primary_poc, ' ') -1 ) first_name,  
 RIGHT(primary_poc, LENGTH(primary_poc) - STRPOS(primary_poc, ' ')) last_name, 
 name
 FROM accounts)
SELECT first_name, last_name, 
CONCAT(first_name, '.', last_name, '@', name, '.com'), 
LEFT(LOWER(first_name), 1) || RIGHT(LOWER(first_name), 1) || 
LEFT(LOWER(last_name), 1) || RIGHT(LOWER(last_name), 1) || LENGTH(first_name) || LENGTH(last_name) || 
REPLACE(UPPER(name), ' ', '')
FROM t1;

CAST

你学习了其他数据处理功能，包括

1. TO_DATE
2. CAST
3. 使用 :: 进行转型

DATE_PART('month', TO_DATE(month, 'month')) 将月份名称改成了与该月相关的数字。

然后，你可以使用 CAST 将字符串改为日期。CAST 实际上可以用来更改各种列类型。使用 CAST(date_column AS DATE) 将字符串改成日期。但是，你可能还会对列的数据类型做出其他更改。你可以在此处看到其他例子。

除了 CAST(date_column AS DATE) 之外，你可以使用 date_column::DATE。

SELECT date orig_date, (SUBSTR(date, 7, 4) || '-' || LEFT(date, 2) || '-' || SUBSTR(date, 4, 2)) new_date
FROM sf_crime_data;


SELECT date orig_date, (SUBSTR(date, 7, 4) || '-' || LEFT(date, 2) || '-' || SUBSTR(date, 4, 2))::DATE new_date
FROM sf_crime_data;

大部分函数都特定于字符串，它们不适用于日期、整数或浮点数。但是，使用这些函数将自动将数据转换为相应的类型。

LEFT、RIGHT 和 TRIM 都仅用来选择特定的字符串元素，但是使用它们选择数字或日期元素，系统会将它们处理为字符串。虽然我们没有明确地在这节课介绍 TRIM，但是它可以用来删掉字符串开头和末尾的字符，这样就可以删掉一行开头或末尾的空格，从 Excel 或其他存储系统转移过来的数据经常就需要这么处理。

这些函数有很多变体，还有没介绍的其他几个字符串函数。 不同的数据库使用的这些函数有所不同，如果你连接的是私有数据库，一定要查看相应的数据库语法。Postgres literature 中包含了大量相关的函数。