三个新的函数:
- LEFT
- RIGHT
- LENGTH
LEFT 从起点(或左侧)开始,从特定列中的每行获取一定数量的字符。正如此处看到的,你可以使用 LEFT(phone_number, 3) 获取电话号码中的前三位。
RIGHT 从末尾(或右侧)开始,从特定列中的每行获取一定数量的字符。正如此处看到的,你可以使用 RIGHT(phone_number, 8) 获取电话号码的最后 8 位。
LENGTH 提供了特定列每行的字符数。这里,我们可以使用 LENGTH(phone_number) 得出每个电话号码的长度。
-
SELECT RIGHT(website, 3) AS domain, COUNT(*) num_companies FROM accounts GROUP BY 1 ORDER BY 2 DESC;
-
SELECT LEFT(UPPER(name), 1) AS first_letter, COUNT(*) num_companies FROM accounts GROUP BY 1 ORDER BY 2 DESC;
-
有 350 个公司名称以字母开头,1 个公司以数字开头。因此有 350/351 的公司名称以字母开头,即百分比是 99.7%。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;
-
有 80 的公司名称以元音开头,271 的公司以其他音节开头。元音的比例是 80/351,或 22.8%。因此,有 77.2% 的公司名称没有以元音开头。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;
- POSITION
- STRPOS
- LOWER
- UPPER
POSITION 获取字符和列,并提供该字符在每行的索引。第一个位置的索引在 SQL 中是 1。如果你之前学习了其他编程语言,就会发现很多语言的索引是从 0 开始。这里,你发现可以使用 POSITION(',' IN city_state) 获取逗号的索引。
STRPOS 和 POSITION 提供的结果相同,但是语法不太一样,如下所示:STRPOS(city_state, ‘,’)。
注意,POSITION 和 STRPOS 都区分大小写,因此查找 A 的位置与查找 a 的结果不同。
因此,如果你想获取某个字母的索引,但是不区分大小写,则需要使用 LOWER 或 UPPER 让所有字符变成小写或大写。
POSITION、STRPOS 和 SUBSTR 解决方案
-
SELECT LEFT(primary_poc, STRPOS(primary_poc, ' ') -1 ) first_name, RIGHT(primary_poc, LENGTH(primary_poc) - STRPOS(primary_poc, ' ')) last_name FROM accounts;
-
SELECT LEFT(name, STRPOS(name, ' ') -1 ) first_name, RIGHT(name, LENGTH(name) - STRPOS(name, ' ')) last_name FROM sales_reps;
- CONCAT
- Piping
||
这两个工具都能将不同行的列组合到一起。在此视频中,你学习了如何将存储在不同列中的名字和姓氏组合到一起,形成全名:CONCAT(first_name, ' ', last_name),或者使用双竖线:first_name || ' ' || last_name。
-
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;
-
你可能注意到了,在上一个答案中,有些公司名称存在空格,肯定不适合作为邮箱地址。看看你能否通过删掉客户名称中的所有空格来创建合适的邮箱地址,否则你的答案就和问题
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, name
FROM accounts)
SELECT first_name, last_name, CONCAT(first_name, '.', last_name, '@', REPLACE(name, ' ', ''), '.com')
FROM t1;
-
我们还需要创建初始密码,在用户第一次登录时将更改。初始密码将是
primary_poc
的名字的第一个字母(小写),然后依次是名字的最后一个字母(小写)、姓氏的第一个字母(小写)、姓氏的最后一个字母(小写)、名字的字母数量、姓氏的字母数量,然后是合作的公司名称(全大写,没有空格)
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;
- TO_DATE
- CAST
- 使用
::
进行转型
DATE_PART('month', TO_DATE(month, 'month')) 将月份名称改成了与该月相关的数字。
然后,你可以使用 CAST 将字符串改为日期。CAST 实际上可以用来更改各种列类型。经常,你会像视频中一样,使用 CAST(date_column AS DATE) 将字符串
改成日期
。但是,你可能还会对列的数据类型做出其他更改。你可以在此处看到其他例子。
在此示例中,除了 CAST(date_column AS DATE) 之外,你可以使用 date_column::DATE。
大部分函数都特定于字符串,它们不适用于日期、整数或浮点数。但是,使用这些函数将自动将数据转换为相应的类型。
LEFT、RIGHT 和 TRIM 都仅用来选择特定的字符串元素,但是使用它们选择数字或日期元素,系统会将它们处理为字符串。虽然我们没有明确地在这节课介绍 TRIM,但是它可以用来删掉字符串开头和末尾的字符,这样就可以删掉一行开头或末尾的空格,从 Excel 或其他存储系统转移过来的数据经常就需要这么处理。
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;
通常,COALESCE 返回的是每行的第一个非 NULL 值。因此如果在此示例中,行中的值是 NULL,上述解决方案使用了 no_poc。
-
SELECT * FROM accounts a LEFT JOIN orders o ON a.id = o.account_id WHERE o.total IS NULL;
-
SELECT COALESCE(a.id, a.id) filled_id, a.name, a.website, a.lat, a.long, a.primary_poc, a.sales_rep_id, o.* FROM accounts a LEFT JOIN orders o ON a.id = o.account_id WHERE o.total IS NULL;
-
SELECT COALESCE(a.id, a.id) filled_id, a.name, a.website, a.lat, a.long, a.primary_poc, a.sales_rep_id, COALESCE(o.account_id, a.id) account_id, o.occurred_at, o.standard_qty, o.gloss_qty, o.poster_qty, o.total, o.standard_amt_usd, o.gloss_amt_usd, o.poster_amt_usd, o.total_amt_usd FROM accounts a LEFT JOIN orders o ON a.id = o.account_id WHERE o.total IS NULL;
-
SELECT COALESCE(a.id, a.id) filled_id, a.name, a.website, a.lat, a.long, a.primary_poc, a.sales_rep_id, COALESCE(o.account_id, a.id) account_id, o.occurred_at, COALESCE(o.standard_qty, 0) standard_qty, COALESCE(o.gloss_qty,0) gloss_qty, COALESCE(o.poster_qty,0) poster_qty, COALESCE(o.total,0) total, COALESCE(o.standard_amt_usd,0) standard_amt_usd, COALESCE(o.gloss_amt_usd,0) gloss_amt_usd, COALESCE(o.poster_amt_usd,0) poster_amt_usd, COALESCE(o.total_amt_usd,0) total_amt_usd FROM accounts a LEFT JOIN orders o ON a.id = o.account_id WHERE o.total IS NULL;