Stéphane's Database course : Lecture 05

Lecture 04 部分内容

自定义字符串排序规则 ：

示例代码 ：

order by

    case credited_as

        when 'D' then 1

        when 'P' then 2

        when 'A' then 3

    end

获取前10行 ：

示例代码 ：

-- select top 10 (SQLServer)

select title, country, year_released

from movies

order by title

limit 10 -- PSQL, MySQL, SQLite

-- fetch first 10 rows (DB2, ORACLE, PSQL)

获取20到30行 ： 

limit 10 offset 20 (PSQL, MySQL, SQLite)

排序应用 ： 物化路径(Materialized path)

select message, . . . .

from forum_posts . . .

connect by answered_postid = prior postid

start with answered_postid is null and topicid = . . .

order siblings by postid

正文

WITH Q (postid, message) AS

              ( SELECT postid, message FROM forum_posts 

                WHERE answered_postid is null

                      AND topicid =  . . . 

                UNION ALL

                SELECT f.postid, f.message

                FROM forum_posts f 

                      INNER JOIN Q

                               ON f.answered_postid = q.postid )

SELECT * FROM Q

另外一种非常重要的排序函数是窗口函数(Window Functions)。

窗口函数有两种 ： 非分级(Non-ranking)和分级(Ranking)

 

Non-ranking window function : 

找出每个国家发行日期最老的电影：

​

SELECT country, min(year_released) earliest_year FROM movies 

GROUP BY country

(如果试图添加一个列，如title，查询会返回随机的title[SQLite]，或直接报错[pSQL])​​​

​

燃鹅​(swan in fire)，聚合函数(aggregate function)的缺点就是细节的忽略，得到的结果只有一个国家和电影，其他细节都消失了。

进阶，找出相应的电影名 ：

​

SELECT a.country, a.title, a.year_released FROM movies a 

INNER JOIN (SELECT country, min(year_released) minyear FROM movies

                     GROUP BY country) b

ON b.country= a.country AND b.minyear = a.year_released

​

为了得到细节，这种操作会检索两次相同的表，会消耗更多时间。进一步简化：使用窗口函数

​

SELECT country, title, year_released, min(year_released) over (partition by country) as earliest_year FROM movies​

返回两个year, 一个是当前的电影的实际year_released，另一个是这个国家最早发行的电影的时间。​

ar | Un cuento chino | 2011 | 1945​

​要只找到最早发行的电影，只需要在外嵌套一层，只取出最早的年份。

​

SELECT m.country, m.title, m.year_released

FROM(SELECT country, title, year_released, min(year_released) over (partition by country) as earliest_year FROM movies)​ m

WHERE m.year_released = m.earliest_year​

func (parameters) over (magic clause)

窗口函数可以返回单行结果，而这个结果可以是多行计算的结果​

DBMS产品支持窗口函数，这样每一个聚合函数(aggregate function)都可以作为窗口函数。

MIN(year_released) OVER (PARTITION BY country)

-- min函数应用在相同的country的行，找出最小的year_released，只返回最小的            -- year_released的行

窗口函数作用在属于结果集的行。一个与此相关的显著特征是，它们只可以出现在SELECT关键字后面，而不能再WHERE条件，且与带有聚合函数的Having关键字没有相似之处。提交函数就是选择了行。

min(year_released) over()

当你只想从整个表格计算出一个结果的时候，你可以使用一个不带GROUP BY的聚合函数。可以使用一个空的OVER，指出你要的是计算所有行的结果。如果删去这个OVER，那么这个函数就不再是窗口函数，而如果有这个OVER，这个函数还是窗口函数。

普通的聚合函数只返回一行，窗口函数返回多行。​​

用CROSS JOIN可以在行末端添加列。T1与T2之间的CROSS JOIN，返回T1的所有行与T2所有行结合。你一般不会这样做，但如果有一个表只有一行的话，CROSS JOIN也可以用于结合行。

如果所有的聚合函数都被用作窗口函数，那么有些窗口函数可以提供等级划分(ranking)功能。这些函数都是原始函数(original functions)，与标量函数和聚合函数没有关联。这样的函数有好几个，我们只讨论最重要的。

当我们在说等级划分的时候，我们指的是排序。在某些情况下我们可以在OVER里面声明我们如何选择行，我们也可以声明我们如何排序行。

这里有三个等级划分函数。在大多数情况下，它们返回相同的值。

row_number()

rank()

dense_rank()

使用一个等级划分窗口函数你必须在OVER内写一个ORDER BY条件(OVER内不能为空)。你可以结合PARTITION BY 一起使用。

例 ： 

SELECT title, country, year_released, ​

row_number() over (partition by country order by year_released desc) rn

FROM movies

返回的结果就是，电影以国家为单位划成一个个表，然后末尾加上这个电影在这个表的序号rn，然后再把这一个个表合起来。​
title                                               country  year_released  rn                    

----------------------------------------------------------------------

Sayat Nova                                           am       1969           1                     

Zama                                                      ar       2017           1    

Truman                                                   ar       2015           2   ​

OVER(partition by col1, col2, ... 

           order by col3, col4, ...)​

GROUP BY 和 ORDER BY 都可以应用于多行

如果有两行的结果是相等的，rank()产生的结果就会像

例 ： 1 2 3 3 5​​

而dense_rank()会产生

例 ： 1 2 3 3 4​

等级划分窗口函数会很容易地返回很难的几乎不可能高效完成的查询需求，下面的问题可以很好地说明：

找出每个国家最近发行的两部电影：

示例代码 : 

SELECT x.country, x.title, x.year_released FROM

(SELECT country, title, year_released, 

              row_number() over(partition by country order by year_released desc ) rn FROM movies) x​

WHERE x.rn <= 2​

​​

​

窗口函数有一个有趣的应用，就可以从​​SQL直接生成一些HTML。

 HTML只有一个可圈可点的(remarkable)的地方，就是使用一对可嵌套的标签。

 

  

​ ... ​  无序(选择)列表

 <</span>li>​  ...   ​   列表

​

问题 ： 生成一个HTML，大标题为国家，下面跟着电影名列表

 

India

 

​   

• Pather Panchali

   

• Sholay

​​

​从返回需要的信息开始:

SELECT c.country_name, m.title FROM movies m

          INNER JOIN countries c 

                        ON​ c.country_code = m.country

ORDER BY c.country_name, m.title​

需要对每一组的第一个电影做一些特殊的操作，所以需要标记出哪个是每个国家的第一个电影 :

SELECT x.rn, x.country_name, x.title

FROM (SELECT c.country_name, m.title, 

            row_number() over(partition by c.country_name order by m.title ) rn

            FROM movies m

                       INNER JOIN countries c

                                     ON c.country_code = m.country ) x

order by x.country_name, x.title, x.rn​

对于每一组的第一行，可以输出国家的名字。但还需要对最后一个做一些特殊的操作。第一行的序号永远是 1 ，但是最后一行的序号确实不确定的 ：

​

SELECT​ case x.rn when 1 then '

' || x.country_name || '

• '

                             else '

• '

                             end || x.title || ''

                             -- 计算了每个国家有多少电影，就可以知道最后一个序号是什么​

                             || case x.rn when x.cnt then ''

                                                 else ' ' end html​

FROM

 (SELECT c.country_name, m.title,

                row_number() over (partition by c.country_name order by m.title ) rn,

                count(*) over (partition by c.country_name) cnt​​

               -- 还要计算每个国家有多少电影​

   FROM movies m

               INNER JOIN countries c

                             ON c.country_code = m.country ) x ​

ORDER BY  x.country_name, x.title, x.rn​

生成HTML图表​

条形图可以通过嵌套表生成。我们需要很多数值来建立图表。我们需要条形图的"条"的大小的百分比，同样的，我们还需要知道哪个是组的第一行和最后一行。

SELECT country_name

            number_of_movies,

 round(100 * number_of_movies / max(number_of_movies ) over (), 0 )percentage,

           row_number() over (order by country_name) rn,

           count(*) over() cnt

FROM

(SELECT c.country_name, coalesce(x.number_of_movies, 0) as number_of_movies)

              FROM countries c

                       LEFT OUTER JOIN ( SELECT country as country_code,

                                                                    count(*) as number_of_movies

                                                       FROM movies GROUP BY country) x

                                              ON x.country_code = c.country_code ) y

ORDER BY country_name​​

获得了percentage, rn, cnt, country_name, number_of_movies 后，就可以组织语句生成HTML了。(Lecture 5 P 13 ，不多赘述)

SQL的微妙之处(SQL Subtleties)​

有很多人们没有掌握得很好的，但非常管用的SQL语句和技巧。所以很多人都没有设计好数据库。

​很少人真正明白，SQL并不是完全是关系型的。如果有一个只有单一限制条件的设计得较好的表，它就是一个关系。如果用WHERE关键字限制它，它还是一个关系。但如果用ORDER BY语句限制它，它就不再是一个关系。你可以收集所有你需要的结果，可以排序，但你手上的结果更像是一个数组(array)。不管怎样，你得到了一个数组后，​你还是可以将它变成一个关系 :

SELECT * FROM people -- 关系

WHERE born >= 1970 -- 关系

ORDER BY born -- 数组

SELECT * FROM (

SELECT * FROM people 

WHERE born >= 1970 

ORDER BY born 

)

WHERE         row_num <=3        -- 关系​

普通表格表达式(Common Table Expressions, CTEs )​

 ​​​​​​​​​​一种很方便的，利用WITH关键字的简单的分解查询​

问题 ：哪一个大洲有最多的电影？(用窗口函数可以很优雅地查询)

SELECT c.continent, count(m.movieid) nb_films

FROM countries c

         INNER  JOIN movies m

                        ON​ m.country = c.country_code

GROUP BY c.continent​

--返回 洲和电影数量​

在这个例子中，需要计算两次每个大洲的电影数量，一次找​最大值，一次找匹配这个最大值的大洲。

SELECT max(nb_films)

FROM(SELECT c.continent, count(m.movieid) nb_films

          FROM countries c

                    INNER  JOIN movies m

                                   ON​ m.country = c.country_code

          GROUP BY c.continent​ ) continent_films​

-- 返回最大的数量值

SELECT c.continent, count(m.movieid ) nb_films

FROM countries c

           INNER JOIN movies m

                         ON​ m.country = c.country_code

GROUP BY c.continent

having count(*) = ​

(SELECT max(nb_films)

FROM(SELECT c.continent, count(m.movieid) nb_films

          FROM countries c

                    INNER  JOIN movies m

                                   ON​ m.country = c.country_code

          GROUP BY c.continent​ ) continent_films​ )​

用一个WITH关键字，就可以把冗余的语句剔除 : 

​

WITH​ continent_films as 

(SELECT c.continent, count(m.movieid) nb_films

          FROM countries c

                    INNER  JOIN movies m

                                   ON​ m.country = c.country_code

          GROUP BY c.continent )​

SELECT * FROM continent_films

WHERE nb_films = (SELECT max(nb_films) FROM continent_films)​

​

可以定义一组CTEs ： with q1 as (), q2 as (), ...​

关于Left outer join的有趣用法

​左连接非常适合"填补空缺"，特别是时间级数(time series)

​如果我们再找非常老的电影，我们可能不可能获得每一年的数量，因为有一些年份可能没有电影发行，这样就会有"空缺"。如果我们想生成图标或计算一些诸如数量增长速度等数据(要求是在一定的连续区间)，就会有问题 :

SELECT year_released, count(*)​ as films 

FROM movies 

WHERE year_released between 1925 and 1934

GROUP BY year_released​

​

year_released  films ---------------

1925                1 

1930                1 

1931                3 

1933                2 

1934                4

-------------------------------------

可以用一个机智的填补空缺的方式 :​

select x.year_released, count(m.movieid) as films

from (select 1925 as year_released       

union all select 1926       

union all select 1927       

union all select 1928      

union all select 1929       

union all select 1930       

union all select 1931      

union all select 1932       

union all select 1933       

union all select 1934) x     

left outer join movies m    

 on m.year_released = x.year_released

 group by x.year_released 

​注意 ： 聚合函数会忽略NULL,如果没有电影,count()会返回 0 ，count()永远不返回NULL



关于min/max的有趣用法​

问题 ： 货物由多个物品组成，物品有A状态和P状态，当所有物品为A状态，就可以装船

大多数人会这样来回答 :

SELECT o.order_id, ...

FROM order o 

WHERE not exists

          (SELECT null 

           FROM order_detail d 

           WHERE d.order_id = o.order_id AND d.status <> 'A' )​

not exists ：​ 当不返回结果集时，返回；当返回结果集时，不返回。

这种方法是不高效的，因为需要扫描整个表的orders，逐个检查它的状态。

高效的方法 ：

SELECT o.order_id, ...

FROM orders o

          INNER JOIN order_detail d

                        ON​ d.order_id = o.order_id

GROUP BY o.order_id, ...

HAVING max(d.status) = 'A' ​

或者:HAVING min(...) = max(...)​

EXISTS关键字与COUNT的比较

当只需要知道count()的结果是否是0的时候，有极大多数的人会使用count(*)。假设数据有一百万行，那就需要扫描一百万次。如果使用EXISTS，在第三行就验证了条件，那就可以停止了。

找出在Howard Hawks​的电影里出演过的人 ： 

SELECT p.first_name, p.surname

FROM people p

WHERE 0 <> 

(SELECT count(*)

FROM credits ca

INNER JOIN credits cd

ON cd.movieid = ca.movieid

AND cd.credited_as = 'D'

INNER JOIN people d

ON d.peopleid = cd.peopleid

WHERE ca.peopleid = p.peopleid

AND ca.credited_as = 'A'

AND d.first_name = 'Howard'

AND d.surname = 'Hawks')​

这个方法计算了每个人在Howard Hawks的电影里出现了多少次

只需要一次就够了 ： 

SELECT p.first_name, p.surname


FROM people p


WHERE exists


(SELECT null


FROM credits ca


INNER JOIN credits cd


ON cd.movieid = ca.movieid


AND cd.credited_as = 'D'


INNER JOIN people d


ON d.peopleid = cd.peopleid


WHERE ca.peopleid = p.peopleid


AND ca.credited_as = 'A'


AND d.first_name = 'Howard'


AND d.surname = 'Hawks'


)​

GROUP BY : 同表多次自连接

SELECT d.city, a.city， . . .

​FROM flights f

          ​INNER JOIN airports d

                        ON​ d.code = f.departure

          INNER JOIN airports a

                        ON a.code = f.arrival​

也可以只连接一次，但这样的话，就会在同一列返回两行：

SELECT a.city, ...

FROM flights f

         INNER JOIN airports a

                      ON a.code in (f.departure, f.arrival)​

用case可以把结果分成两列，一个名字和一个NULL ：

​SELECT case a.code 

             when f.departure then a.city

             else null end, ...

FROM flight f

      INNER JOIN airports a 

                    ON a.code in (f.departure, f.arrival)​

用可以忽略NULL的max()函数：

SELECT max(case a.code 

             when f.departure then a.city

             else null end), . . .

FROM flights f

         INNER JOIN airports a

                       ON a.code in (f.departure, f.arrival)

GROUP BY . . .​​

​

​限制危害(Limiting damage)

​窗口函数的另一个有趣的用途就是对失控的查询进行限制，特别是在交互环境中，用户输入动态的且可能不是很有选择性的数据，这可能导致查询会返回非常多(huuuuge)的行。

​有一些人可能会这样解决 :

SELECT count(*) FROM (original query)
if count <= maxcnt : original query​

如果第一个语句​执行，同样会在内部进行痛苦地查询大量数据。如果这个判断条件成立，那么还会再查询一遍。

​一个很好的方式就是限制输出，排序，然后用窗口函数计算返回的行数。

SELECT . . . ,
            count(*) over () cnt
FROM . . .
WHERE . . .
      AND rownum <= maxcnt + 1
ORDER BY . . .​
If cnt > maxcnt, Refine your query

这一段代码做了什么？

假设我们考虑查询一般不会返回超过2000行。我们限制我们可以获得的行数最大为2001行，然后对这些行排序，计算出现在我们有多少行。如果我们计算到比2000行更少，我们就可以获得所有的行。如果我们有2001行，​排序集合可能会是错误的，但我们不会输出这个结果，所以没有关系。

​

​模糊查找(Fuzzy searches)

​在一些情况下，特别是文本数据，即使我们尝试数据标准化和格式化，还是会有一些错误的拼写，或者多元化的拼写。

这个问题并不新，且算法在一个世纪前的美国就已经用于人口普查了。核心思想就是找到一个代码，使得单词在发音相似或相同的地方会以相同的代码结束。

soundex()​

B, F, P, V                          1

C, G, J, K, Q, V, X, Z        2

D, T                                  3

L                                       4

M, N                                 5

R​                                       6

保留第一个字母，删除元音，删除听起来像元音的字母(w = oo)，还有h(通常不发音)，然后简单替换掉发音相似的辅音为相同的数，删除相同的连续的数。

这样 Gielgud和Guilgood都会变成 G423​

查找Wells可能会匹配到如下结果 ： ​

Welles，Willis， Wallach

问题是，哪一个是最佳的匹配。​

Welles : W4s Willis : W4s Wallach W4​

问题是 soundex()是完全按照英语的发音规则来写的，对其他国家的语言不大管用。即便是这样，有一些英语名字也不能模糊查找(Thompsom : T512, Thomsom : T525)​，有一些英文名是来自其他国家的语言的。

全文查找(Full-text search)通过去除一些太简单的词和标点来匹配：

2001 : a space odyssey -> 2001 space odyssey

这些单词被存储在一个特殊的表。一个全文搜索引擎"主要化"(stems)单词。它将识别复数，不定式，过去分词，使其转换为之前原来的词。

create table movie_title_ft_index
     (title_word   varchar(30) not null,
         movieid   int not null,
        primary key(title_word,movieid),
        foreign key(movieid)
            references movies(movieid))

问题是：关键词可能会有多种匹配。我们需要一个优化方法：

查找 ： 2001， a space odyssey

匹配 ：2001 space odyssey, ODYSSEY, Flying form OUTER SPACE

下面这个查询可能可以应对需求：

SELECT movieid FROM (SELECT movieid,

                                                   rank() over(order by hits desc) as rnk

                                      FROM(SELECT movieid, count(*) as hits 

                                                 FROM movie_title_ft_index

                                                 WHERE title_word in ('SPACE','ODYSSEY','2001')

                                                 GROUP BY movieid) q1) q2

WHERE rnk = 1

Ties (节点)

说到节点，我们应该能够解决他们。

查找关键词 : Casablanca 

返回 : Casablanca, The Marx Bros A NIGHT IN CASABLANCA, CASABLANCA EXPRESS

查找最佳匹配得另一个方法：在标题中除去关键词，比较剩余文本的长度

拼写错误，外文乱入：将单词与脚本系统(Scripting system)隔离，这些系统可以识别拉丁文，希腊文...

对于东亚语言(除了越南语)，一个策略就是替换掉2-3个字符的序列，序列越多，匹配得越好。

查询思想

举个例子：看看我们怎么样能够把查询问题转换成SQL表达式。如果我们要正确地做到，就需要下面5个步骤：

步骤一：观察。找到我们需要的表。

步骤二：聚合。有时会需要大量的聚合函数计算，我们必须在JOIN前完成它们。

步骤三：过滤。GROPUBYWHEREHAVING不多赘述

步骤四：连接。用JOIN关键字获得必需的数据或用于过滤。

步骤五：抛光。OREDER的使用或其他只为了让结果更易于观察的操作。

这些要点和SQL的语句和设计没有关系，但却有很高的实用价值。

数据的安全性问题就体现在数据不是只读的，我们需要做一些定期备份。

首先，我们需要了解一个与一致性(consistency)有关的概念：交易(Transaction)

在现实生活中，交易通常是货物和货币的交换。交易有几个关键步骤，否则一切都是空谈。

一个经典的数据库例子就是，如果要把你现有账户钱转到储蓄账户。系统必须修改两个数据，如果数据修改到一半卡了怎么办？

一个解决方案就是，修改两个数据必须同时失败或同时成功。

一些产品要求要有特殊的命令来开始进行交易(BEGIN有时候是START)：

BEGIN transaction

    insert

    update

    delete

当要完成交易时，就需要输入commit来提交交易，如果没有commit，就会回滚回原来的状态。也可以输入roolback来返回上一次状态。