网站开发时如何设计英文版本,设计网站的素材,安徽整站优化,我的网站建设介 绍为了理解这 10 个 SQL 技巧的价值#xff0c;首先需要了解下 SQL 语言的上下文。为什么我要在 Java 会议上讨论 SQL 呢#xff1f;(我可能是唯一一个在 Java 会议上讨论 SQL 的了)下面讲下为什么#xff1a;从早期开始#xff0c;编程语言设计者就有这种的愿望#x…介 绍为了理解这 10 个 SQL 技巧的价值首先需要了解下 SQL 语言的上下文。为什么我要在 Java 会议上讨论 SQL 呢(我可能是唯一一个在 Java 会议上讨论 SQL 的了)下面讲下为什么从早期开始编程语言设计者就有这种的愿望设计一种语言在这种语言中告诉机器我们想要的结果是什么(WHAT)而不是如何(HOW)获得结果。例如在 SQL 中我们告诉计算机我们要“连接”(联接)用户表和地址表并查找居住在瑞士的用户。我们不关心数据库将如何检索这些信息(比如是先加载用户表呢还是先加载地址表这两个表是在嵌套循环中联接呢还是使用 hashmap 联接是先将所有数据加载到内存中然后再过滤出瑞士用户呢还是先加载瑞士地址等等。)与每个抽象一样我们仍然需要了解数据库背后的基本原理以帮助数据库在查询时做出正确的决策。例如做如下事情是有必要在表之间建立合适的外键(这能告诉数据库每个地址都有一个对应的用户)在搜索字段上添加索引国家(这能告诉数据库可以在 O(log N) 而不是 O(N) 的复杂度内查找到特定的国家 )。但是一旦数据库和应用程序变得成熟之后我们就可以把所有重要的元数据放在适当的位置上了并且只需专注于业务逻辑即可。下面的 10 个技巧展示了仅用几行声明式 SQL 就能编写强大惊人功能的能力它不仅可以生成简单的输出也可以生成复杂的输出。1. 一切都是表这是一条最微不足道的技巧甚至不能说是真正的技巧但它是全面理解 SQL 的基础一切都是表当我们看到这样的 SQL 语句时SELECT *FROM person……我们很快就能在 FROM 子句中找到 person 表。很好那是一张表。但我们能意识到整个语句也是一张表吗例如我们可以这样写SELECT *FROM ( SELECT * FROM person) t现在我们已经创建了一张所谓的“派生表”即 FROM 子句中的嵌套 SELECT 语句。这是微不足道的但是如果仔细想想它是相当优雅的。我们还可以在某些数据库(比如 PostgreSQL、SQL Server)中使用 VALUES() 构造函数来创建临时内存表SELECT *FROM ( VALUES(1),(2),(3)) t(a)临时表就这样产生了a—123如果对应的数据库不支持该子句则可以回到使用派生表上比如在 Oracle 中SELECT *FROM ( SELECT 1 AS a FROM DUAL UNION ALL SELECT 2 AS a FROM DUAL UNION ALL SELECT 3 AS a FROM DUAL) t既然我们已经看到了 VALUES() 和派生表实际上是相同的那么从概念上我们回顾一下 INSERT 语句它有两种类型-- SQL Server, PostgreSQL, some others:INSERT INTO my_table(a)VALUES(1),(2),(3);-- Oracle, many others:INSERT INTO my_table(a)SELECT 1 AS a FROM DUAL UNION ALLSELECT 2 AS a FROM DUAL UNION ALLSELECT 3 AS a FROM DUAL在 SQL 中一切都是表。当您在表中插入行时实际上并不是插入单独的行。是插入整张表。在大多数情况下大部分人只是碰巧插入了一张单行表因此没有意识到 INSERT 真正做了什么。一切都是表。在 PostgreSQL 中甚至函数都是表SELECT *FROM substring(abcde, 2, 3)上面语句的结果是substring———bcd如果你正在使用 Java 编程那么可以使用 Java 8 Stream API 来做进一步的类比。考虑如下等价概念TABLE : StreamSELECT : map()DISTINCT : distinct()JOIN : flatMap()WHERE / HAVING : filter()GROUP BY : collect()ORDER BY : sorted()UNION ALL : concat()在 Java 8 中“一切都是流”(至少在你开始使用流时是这样)。无论如何转换流例如使用 map() 或 filter() 转换结果类型始终都是流。我们写了一篇完整的文章来更深入地解释这一点并将 Stream API 与 SQL 进行了对比Common SQL Clauses and Their Equivalents in Java 8 Streams如果你正在寻找“更好的流”(即具有更多 SQL 语义的流)请查看 jOOλ它一个将 SQL 窗口函数引入到 Java 中的开源库。2. 使用递归 SQL 生成数据公共表表达式(Common Table Expressions CTE在 Oracle 中也叫做子查询分解)它是在 SQL 中声明变量的唯一方法(除了模糊 WINDOW 子句之外WINDOW 子句也只有在 PostgreSQL 和 Sybase SQL 中可用)。这是一个功能强大的概念。非常强大。考虑如下声明-- 表变量WITH t1(v1, v2) AS (SELECT 1, 2), t2(w1, w2) AS ( SELECT v1 * 2, v2 * 2 FROM t1 )SELECT *FROM t1, t2它的结果是v1 v2 w1 w2----------------- 1 2 2 4使用简单的 WITH 子句我们可以指定一系列表变量(请记住一切都是表)这些变量甚至可以是相互依赖的。这很容易理解。它已经使得 CTE(Common Table Expressions)非常有用了但是真正了不起的是它们还允许递归考虑如下 PostgreSQL 示例WITH RECURSIVE t(v) AS ( SELECT 1 -- 种子行 UNION ALL SELECT v 1 -- 递归 FROM t)SELECT vFROM tLIMIT 5它的结果是v—12345它是如何工作的呢一旦你看懂了一些关键词它就相对容易了。我们定义了一个公共表表达式它恰好有两个 UNION ALL 子查询。第一个 UNION ALL 子查询是我们通常所说的“种子行”。它“播种”(初始化)递归。它可以生成一行或多行稍后我们将在这些行上递归。记住一切都是表所以递归将发生在整张表上而不是单个行 / 值上。第二个 UNION ALL 子查询在发生递归的地方。如果你仔细观察会发现它从 t 中选择。也就是说允许第二个子查询从我们即将声明的 CTE 中递归地选择。因此它还可以访问使用它的 CTE 声明的列 v。在我们的示例中我们使用行 (1) 对递归进行种子处理然后通过添加 v 1 来进行递归。最后通过设置 LIMIT 5 来终止递归(需要谨防潜在的无限递归 就像使用 Java 8 的流一样)。附注图灵完备递归 CTE 使得 SQL:1999 图灵完备这意味着任何程序都可以用 SQL 编写(如果你够疯狂的话)一个经常出现在博客上的令人印象深刻的例子是Mandelbrot 集如 http://explainextended.com/2013/12/31/happy-new-year-5/ 所示。WITH RECURSIVE q(r, i, rx, ix, g) AS ( SELECT r::DOUBLE PRECISION * 0.02, i::DOUBLE PRECISION * 0.02, .0::DOUBLE PRECISION , .0::DOUBLE PRECISION, 0 FROM generate_series(-60, 20) r, generate_series(-50, 50) i UNION ALL SELECT r, i, CASE WHEN abs(rx * rx ix * ix) 2 THEN rx * rx - ix * ix END r, CASE WHEN abs(rx * rx ix * ix) 2 THEN 2 * rx * ix END i, g 1 FROM q WHERE rx IS NOT NULL AND g 99)SELECT array_to_string(array_agg(s ORDER BY r), )FROM ( SELECT i, r, substring( .:-*#%, max(g) / 10 1, 1) s FROM q GROUP BY i, r) qGROUP BY iORDER BY i在 PostgreSQL 上运行上面的代码我们将得到如下结果 .-.:-.........*..::-:::.:...*-. . .........::%.:*#.:-. ..- .:.:::*.........::...:. ...*.:.....:...::. .:::-:..-:*:::. .--...: ...::.. ....:-*:: .....-.. .....-:... .--:.... .-.. ..-#. ..... -.-..: .*%::- . ..:... ..-.............. ....-%.--.-.....-.:..........::....:-...............:..:::-*:%:.......:.::--:.....::.::.....%.:-...::-:-..%.%-..-.:::..--.印象是不是非常深刻3. 累计计算这个博客 有很多累计计算的示例。它们是学习高级 SQL 最有教育意义的示例之一因为至少有十几种方法可以实现累计计算。在概念上累计计算很容易理解。在 Microsoft Excel 中我们只需计算前两个(或后两个)值的和(或差)然后使用可用的十字光标将该公式拉过整个电子表格。我们在电子表格中“运行”这个总数。即一个“累计”。在 SQL 中最好的方法是使用 窗口函数这也是该博客多次讨论的另一主题。窗口函数是一个功能强大的概念一开始可能不太容易理解但事实上它们非常非常简单窗口函数是在相对当前行而言的一个子集上的聚合排序当前行由 SELECT 转换。就是这样简单它本质上的意思是一个窗口函数可以对当前行的“上”或“下”行执行计算。然而与普通的聚合和 GROUP BY 不同它们不转换行这使得它们非常有用。语法总结如下个别部分是可选的function(...) OVER ( PARTITION BY ... ORDER BY ... ROWS BETWEEN ... AND ...)因此我们可以使用任何类型的函数(稍后我们将介绍此类函数的示例)后面紧跟其后的是 OVER() 子句该子句指定窗口。即这个 OVER() 子句定义如下 PARTITION 窗口只考虑与当前行在同一分区中的行ORDER窗口排序可以独立于我们选择的内容ROWS(或 RANGE )框架定义窗口可以被限制在固定数量的行的“前面”和“后面”。这就是窗口函数的全部功能。那么它又是如何帮助我们累计计算的呢考虑以下数据| ID | VALUE_DATE | AMOUNT | BALANCE ||------|------------|--------|------------|| 9997 | 2014-03-18 | 99.17 | 19985.81 || 9981 | 2014-03-16 | 71.44 | 19886.64 || 9979 | 2014-03-16 | -94.60 | 19815.20 || 9977 | 2014-03-16 | -6.96 | 19909.80 || 9971 | 2014-03-15 | -65.95 | 19916.76 |假设 BALANCE 是我们想从 AMOUNT 中计算出来的直观视觉上我们可以立即看出以下情况是成立的因此使用简单的英语任何余额都可以用以下伪 SQL 表示TOP_BALANCE – SUM(AMOUNT) OVER (“all the rows on top of the current row”)在真正的 SQL 中可以这样写SUM(t.amount) OVER ( PARTITION BY t.account_id ORDER BY t.value_date DESC, t.id DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)说明分区计算每个银行帐户的总和而不是整个数据集的总和排序将确保事务在求和之前(在分区内)是有序的在求和之前rows 子句只考虑前面的行(在分区内给定顺序)所有这些都发生在内存中的数据集上该数据集由我们通过 FROM .. WHERE 子句选择出来因此速度非常快。插 曲在我们开始讨论其他精彩技巧之前先考虑一下我们已经学习了(递归)公共表表达式(CTE)窗口函数这两个功能都是非常棒功能极其强大声明式SQL 标准的一部分适用于大多数流行的 RDBMS(除了 MySQL)非常重要的构建块如果能从本文中得出什么结论那就是我们应该完全了解现代 SQL 的这两个构建块。为什么呢因为4. 查找最大无间隔序列Stack Overflow 有一个非常好的功能徽章它可以激励人们尽可能长时间地呆在他们的网站上。就规模而言你可以看到我有多少徽章。你要怎么计算这些徽章呢让我们看看“爱好者”和“狂热者”。这些徽章是颁发给那些在他们平台上连续停留一定时间的人。无论结婚纪念日或是妻子生日你都必须登录否则计数器将再次从零开始。当我们进行声明式编程时是不需要维护任何状态和内存计数器的。现在我们想用在线分析 SQL 的形式来表达这一点。即考虑如下数据| LOGIN_TIME ||---------------------|| 2014-03-18 05:37:13 || 2014-03-16 08:31:47 || 2014-03-16 06:11:17 || 2014-03-16 05:59:33 || 2014-03-15 11:17:28 || 2014-03-15 10:00:11 || 2014-03-15 07:45:27 || 2014-03-15 07:42:19 || 2014-03-14 09:38:12 |那没什么用。我们从时间戳中删除小时。这很简单SELECT DISTINCT cast(login_time AS DATE) AS login_dateFROM loginsWHERE user_id :user_id得到的结果是| LOGIN_DATE ||------------|| 2014-03-18 || 2014-03-16 || 2014-03-15 || 2014-03-14 |现在我们已经学习了窗口函数我们只需为每个日期添加一个简单的行数即可SELECT login_date, row_number() OVER (ORDER BY login_date)FROM login_dates结果如下| LOGIN_DATE | RN ||------------|----|| 2014-03-18 | 4 || 2014-03-16 | 3 || 2014-03-15 | 2 || 2014-03-14 | 1 |还是很容易的吧。现在如果我们不单独选择这些值而是减去它们会发生什么SELECT login_date - row_number() OVER (ORDER BY login_date)FROM login_dates将会得到如下结果| LOGIN_DATE | RN | GRP ||------------|----|------------|| 2014-03-18 | 4 | 2014-03-14 || 2014-03-16 | 3 | 2014-03-13 || 2014-03-15 | 2 | 2014-03-13 || 2014-03-14 | 1 | 2014-03-13 |真的。很有趣。所以14–11315–21316–313但是 18–414。没有人能比 Doge 说得更好了有一个关于这种行为的简单示例ROW_NUMBER() 没有间隔这就是它的定义但是我们的数据有间隔所以当我们从一个非连续日期的“gapful”序列中减去一个连续整数的“gapless”序列时我们将得到连续日期中每个“gapless”子序列的相同日期并且它是一个新的日期其中日期序列是有间隔的。嗯。这意味着我们现在可以简单地 GROUP BY 该任意日期值了SELECT min(login_date), max(login_date), max(login_date) - min(login_date) 1 AS lengthFROM login_date_groupsGROUP BY grpORDER BY length DESC我们做到了。最大的连续无间隔序列被找到了| MIN | MAX | LENGTH ||------------|------------|--------|| 2014-03-14 | 2014-03-16 | 3 || 2014-03-18 | 2014-03-18 | 1 |完整的查询如下ITH login_dates AS ( SELECT DISTINCT cast(login_time AS DATE) login_date FROM logins WHERE user_id :user_id ), login_date_groups AS ( SELECT login_date, login_date - row_number() OVER (ORDER BY login_date) AS grp FROM login_dates )SELECT min(login_date), max(login_date), max(login_date) - min(login_date) 1 AS lengthFROM login_date_groupsGROUP BY grpORDER BY length DESC最后没那么难吧当然最主要的是有了这个想法但是查询本身真的非常简单优雅。没有比这更简洁的方法来实现一些命令式算法了。5. 求序列的长度在前面我们看到了一系列连续的值。这很容易处理因为我们可以滥用整数的连续性。如果一个“序列”的定义不那么直观而且除此之外几个序列包含相同的值呢考虑以下数据其中 LENGTH 是要计算的每个序列的长度| ID | VALUE_DATE | AMOUNT | LENGTH ||------|------------|--------|------------|| 9997 | 2014-03-18 | 99.17 | 2 || 9981 | 2014-03-16 | 71.44 | 2 || 9979 | 2014-03-16 | -94.60 | 3 || 9977 | 2014-03-16 | -6.96 | 3 || 9971 | 2014-03-15 | -65.95 | 3 || 9964 | 2014-03-15 | 15.13 | 2 || 9962 | 2014-03-15 | 17.47 | 2 || 9960 | 2014-03-15 | -3.55 | 1 || 9959 | 2014-03-14 | 32.00 | 1 |是的你猜对了。“序列”是由连续(按 ID 排序)行且具有相同的 SIGN(AMOUNT) 这一事实来定义的。再次检查如下的数据格式| ID | VALUE_DATE | AMOUNT | LENGTH ||------|------------|--------|------------|| 9997 | 2014-03-18 | 99.17 | 2 || 9981 | 2014-03-16 | 71.44 | 2 || 9979 | 2014-03-16 | -94.60 | 3 || 9977 | 2014-03-16 | - 6.96 | 3 || 9971 | 2014-03-15 | -65.95 | 3 || 9964 | 2014-03-15 | 15.13 | 2 || 9962 | 2014-03-15 | 17.47 | 2 || 9960 | 2014-03-15 | - 3.55 | 1 || 9959 | 2014-03-14 | 32.00 | 1 |我们怎么做呢很“简单”首先我们去掉所有的噪音并添加另一个行号SELECT id, amount, sign(amount) AS sign, row_number() OVER (ORDER BY id DESC) AS rnFROM trx它的结果是| ID | AMOUNT | SIGN | RN ||------|--------|------|----|| 9997 | 99.17 | 1 | 1 || 9981 | 71.44 | 1 | 2 || 9979 | -94.60 | -1 | 3 || 9977 | -6.96 | -1 | 4 || 9971 | -65.95 | -1 | 5 || 9964 | 15.13 | 1 | 6 || 9962 | 17.47 | 1 | 7 || 9960 | -3.55 | -1 | 8 || 9959 | 32.00 | 1 | 9 |现在接下来的目标是生成如下表| ID | AMOUNT | SIGN | RN | LO | HI ||------|--------|------|----|----|----|| 9997 | 99.17 | 1 | 1 | 1 | || 9981 | 71.44 | 1 | 2 | | 2 || 9979 | -94.60 | -1 | 3 | 3 | || 9977 | -6.96 | -1 | 4 | | || 9971 | -65.95 | -1 | 5 | | 5 || 9964 | 15.13 | 1 | 6 | 6 | || 9962 | 17.47 | 1 | 7 | | 7 || 9960 | -3.55 | -1 | 8 | 8 | 8 || 9959 | 32.00 | 1 | 9 | 9 | 9 |在该表中我们希望将行号值复制到序列“末”端的“LO”和序列“顶”端的“HI”中。为此我们将使用神奇的 LEAD() 和 LAG()。LEAD() 可以访问当前行向下的第 n 行而 LAG() 可以访问当前行向上的第 n 行。例如SELECT lag(v) OVER (ORDER BY v), v, lead(v) OVER (ORDER BY v)FROM ( VALUES (1), (2), (3), (4)) t(v)上述查询生成的结果太棒了记住使用窗口函数你可以对相对于当前行的子集执行排序或聚合。在 LEAD() 和 LAG() 的情况下只要给定当前行的偏移量就可以访问相对于当前行的单个行。这在很多情况下都很有用。继续我们的“LO”和“HI”示例我们可以简单地这样写SELECT trx.*, CASE WHEN lag(sign) OVER (ORDER BY id DESC) ! sign THEN rn END AS lo, CASE WHEN lead(sign) OVER (ORDER BY id DESC) ! sign THEN rn END AS hi,FROM trx……我们将“前一个”sign (lag(sign)) 与“当前”sign (sign) 进行比较。如果它们不同我们将行号放到“LO”中因为它是序列的下界。然后我们比较“下一个”sign (lead(sign)) 和“当前”sign (sign)。如果它们不同我们将行号放到“HI”中因为它是序列的上界。最后进行一些无聊的 NULL 处理来以确保一切正常这样就完成了:SELECT -- With NULL handling... trx.*, CASE WHEN coalesce(lag(sign) OVER (ORDER BY id DESC), 0) ! sign THEN rn END AS lo, CASE WHEN coalesce(lead(sign) OVER (ORDER BY id DESC), 0) ! sign THEN rn END AS hi,FROM trx下一步。我们希望“LO”和“HI”出现在所有行中而不仅仅是在序列的“下界”和“上界”上。例如像这样| ID | AMOUNT | SIGN | RN | LO | HI ||------|--------|------|----|----|----|| 9997 | 99.17 | 1 | 1 | 1 | 2 || 9981 | 71.44 | 1 | 2 | 1 | 2 || 9979 | -94.60 | -1 | 3 | 3 | 5 || 9977 | -6.96 | -1 | 4 | 3 | 5 || 9971 | -65.95 | -1 | 5 | 3 | 5 || 9964 | 15.13 | 1 | 6 | 6 | 7 || 9962 | 17.47 | 1 | 7 | 6 | 7 || 9960 | -3.55 | -1 | 8 | 8 | 8 || 9959 | 32.00 | 1 | 9 | 9 | 9 |我们使用的特性至少在 Redshift、Sybase SQL Anywhere、DB2、Oracle 中是可用的。我们使用的是“IGNORE NULLS”子句它可以传递给某些窗口函数SELECT trx.*, last_value (lo) IGNORE NULLS OVER ( ORDER BY id DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS lo, first_value(hi) IGNORE NULLS OVER ( ORDER BY id DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS hiFROM trx有很多关键词但本质都是一样的。从任何给定的“当前”行中我们查看所有“之前的值”(ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)但忽略所有空值。从之前的值中我们取最后一个值这就是我们的新“LO”值。换句话说我们取“最近的前一个”“LO”值。“HI”也是一样的。从任何给定的“当前”行中我们查看所有“后续值”(ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)但忽略所有空值。从随后的值中我们取第一个值这是我们的新“HI”值。换言之我们取“最近的下一个”“HI”值。使用幻灯片解释如下100% 正确加上点无聊的 NULL 调整SELECT -- With NULL handling... trx.*, coalesce(last_value (lo) IGNORE NULLS OVER ( ORDER BY id DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), rn) AS lo, coalesce(first_value(hi) IGNORE NULLS OVER ( ORDER BY id DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), rn) AS hiFROM trx我们做最后一个步骤记住清除一个个的错误SELECT trx.*, 1 hi - lo AS lengthFROM trx我们做到了结果如下| ID | AMOUNT | SIGN | RN | LO | HI | LENGTH||------|--------|------|----|----|----|-------|| 9997 | 99.17 | 1 | 1 | 1 | 2 | 2 || 9981 | 71.44 | 1 | 2 | 1 | 2 | 2 || 9979 | -94.60 | -1 | 3 | 3 | 5 | 3 || 9977 | -6.96 | -1 | 4 | 3 | 5 | 3 || 9971 | -65.95 | -1 | 5 | 3 | 5 | 3 || 9964 | 15.13 | 1 | 6 | 6 | 7 | 2 || 9962 | 17.47 | 1 | 7 | 6 | 7 | 2 || 9960 | -3.55 | -1 | 8 | 8 | 8 | 1 || 9959 | 32.00 | 1 | 9 | 9 | 9 | 1 |完整的查询语句如下WITH trx1(id, amount, sign, rn) AS ( SELECT id, amount, sign(amount), row_number() OVER (ORDER BY id DESC) FROM trx ), trx2(id, amount, sign, rn, lo, hi) AS ( SELECT trx1.*, CASE WHEN coalesce(lag(sign) OVER (ORDER BY id DESC), 0) ! sign THEN rn END, CASE WHEN coalesce(lead(sign) OVER (ORDER BY id DESC), 0) ! sign THEN rn END FROM trx1 )SELECT trx2.*, 1 - last_value (lo) IGNORE NULLS OVER (ORDER BY id DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) first_value(hi) IGNORE NULLS OVER (ORDER BY id DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)FROM trx2嗯。这个 SQL 开始变得有趣了准备好接下来的学习了吗6. SQL 的子集求和问题这是我最喜欢的部分什么是子集求和问题呢在这里找到一个有趣的解释https://xkcd.com/287还有一个比较无聊的解释https://en.wikipedia.org/wiki/Subset_sum_problem基本上对于每一个总数......| ID | TOTAL ||----|-------|| 1 | 25150 || 2 | 19800 || 3 | 27511 |…我们希望找到尽可能“最佳”(即最接近的)的求和它包括以下各项的任意组合| ID | ITEM ||------|-------|| 1 | 7120 || 2 | 8150 || 3 | 8255 || 4 | 9051 || 5 | 1220 || 6 | 12515 || 7 | 13555 || 8 | 5221 || 9 | 812 || 10 | 6562 |由于我们内心的数学处理速度很快所以我们可以立即计算出这些是最好的求和| TOTAL | BEST | CALCULATION|-------|-------|--------------------------------| 25150 | 25133 | 7120 8150 9051 812| 19800 | 19768 | 1220 12515 5221 812| 27511 | 27488 | 8150 8255 9051 1220 812如何用 SQL 要做到这一点呢很简单。只需要创建一个包含所有 2npossiblesums 的 CTE然后为每个 TOTAL 找到最接近的一个即可-- All the possible 2N sumsWITH sums(sum, max_id, calc) AS (...)-- Find the best sum per “TOTAL”SELECT totals.total, something_something(total - sum) AS best, something_something(total - sum) AS calcFROM draw_the_rest_of_the_*bleep*_owl当你在读这篇文章的时候你可能会像我的朋友一样:不过别担心解决方案也不是那么难(尽管由于算法的性质它无法执行)WITH sums(sum, id, calc) AS ( SELECT item, id, to_char(item) FROM items UNION ALL SELECT item sum, items.id, calc || || item FROM sums JOIN items ON sums.id items.id)SELECT totals.id, totals.total, min (sum) KEEP ( DENSE_RANK FIRST ORDER BY abs(total - sum) ) AS best, min (calc) KEEP ( DENSE_RANK FIRST ORDER BY abs(total - sum) ) AS calc,FROM totalsCROSS JOIN sumsGROUP BY totals.id, totals.total在本文中我将不解释此解决方案的详细信息因为这个例子是从上一篇文章中选取的你可以在这里查看:如何使用 SQL 查找最接近的子集和希望你能愉悦地阅读的相应细节但一定要回来查看其余 4 个技巧7. 设限的累计计算到目前为止我们已经学习了如何使用窗口函数用 SQL 进行“普通”的累计计算。那很容易。现在如果我们把累计计算限制在永远不低于零的情况下会怎么样呢实际上我们是想要得到如下的计算| DATE | AMOUNT | TOTAL ||------------|--------|-------|| 2012-01-01 | 800 | 800 || 2012-02-01 | 1900 | 2700 || 2012-03-01 | 1750 | 4450 || 2012-04-01 | -20000 | 0 || 2012-05-01 | 900 | 900 || 2012-06-01 | 3900 | 4800 || 2012-07-01 | -2600 | 2200 || 2012-08-01 | -2600 | 0 || 2012-09-01 | 2100 | 2100 || 2012-10-01 | -2400 | 0 || 2012-11-01 | 1100 | 1100 || 2012-12-01 | 1300 | 2400 |因此当减去 AMOUNT -20000 这个大的负数是我们没有显示 -15550 这个实际的 TOTAL而是显示的 0。换句话说用数据集表示如下| DATE | AMOUNT | TOTAL ||------------|--------|-------|| 2012-01-01 | 800 | 800 | GREATEST(0, 800)| 2012-02-01 | 1900 | 2700 | GREATEST(0, 2700)| 2012-03-01 | 1750 | 4450 | GREATEST(0, 4450)| 2012-04-01 | -20000 | 0 | GREATEST(0, -15550)| 2012-05-01 | 900 | 900 | GREATEST(0, 900)| 2012-06-01 | 3900 | 4800 | GREATEST(0, 4800)| 2012-07-01 | -2600 | 2200 | GREATEST(0, 2200)| 2012-08-01 | -2600 | 0 | GREATEST(0, -400)| 2012-09-01 | 2100 | 2100 | GREATEST(0, 2100)| 2012-10-01 | -2400 | 0 | GREATEST(0, -300)| 2012-11-01 | 1100 | 1100 | GREATEST(0, 1100)| 2012-12-01 | 1300 | 2400 | GREATEST(0, 2400)我们要怎么做呢确切地说。使用模糊的、特定于供应商的 SQL。在本例中我们使用的是 Oracle SQL它是如何工作的出奇的简单只要在任何表后添加 MODEL 就可以打开一个很棒的 SQL “蠕虫罐头”SELECT ... FROM some_table-- 将此放在任意 table 的后面MODEL ...一旦我们把 MODEL 放在那里就可以像 Microsoft Excel 一样在 SQL 语句中直接实现电子表格逻辑了。以下三个条款是最有用也是使用最广泛的(即每年地球上的任何人使用 1-2 次)MODEL -- The spreadsheet dimensions DIMENSION BY ... -- The spreadsheet cell type MEASURES ... -- The spreadsheet formulas RULES ...这三个附加条款的含义最好再看下幻灯片的解释。DIMENSION BY 子句指定电子表格的维度。与 MS Excel 不同Oracle 中可以包含任意数量的维度MEASURES 子句指定电子表格中每个单元格的可用值。与 MS Excel 不同在 Oracle 中每个单元格可以有一个完整的元组而不仅仅是单个值。RULES 子句指定应用于电子表格中每个单元格的公式。与 MS Excel 不同这些规则 / 公式集中在一个地方而不是放在每个单元格中这种设计使得 MODEL 比 MS Excel 更难使用但如果你敢用的话它的功能会更强大。整个查询语句比较“琐碎”如下所示SELECT *FROM ( SELECT date, amount, 0 AS total FROM amounts)MODEL DIMENSION BY (row_number() OVER (ORDER BY date) AS rn) MEASURES (date, amount, total) RULES ( total[any] greatest(0, coalesce(total[cv(rn) - 1], 0) amount[cv(rn)]) )在整个过程中它的功能非常强大并附带了 Oracle 自己的白皮书所以请不要在本文中寻求进一步解释请阅读优秀的白皮书http://www.oracle.com/technetwork/middleware/bi-foundation/10gr1-twp-bi-dw-sqlmodel-131067.pdf8. 时间序列模式识别如果你是从事欺诈检测或在大型数据集上运行实时分析的任何其他领域时间序列模式识别对你来说肯定不是一个新术语。如果我们回看下“序列长度”的数据集我们可能希望在时间序列上为复杂事件生成一个触发器如下所示| ID | VALUE_DATE | AMOUNT | LEN | TRIGGER|------|------------|---------|-----|--------| 9997 | 2014-03-18 | 99.17 | 1 || 9981 | 2014-03-16 | - 71.44 | 4 || 9979 | 2014-03-16 | - 94.60 | 4 | x| 9977 | 2014-03-16 | - 6.96 | 4 || 9971 | 2014-03-15 | - 65.95 | 4 || 9964 | 2014-03-15 | 15.13 | 3 || 9962 | 2014-03-15 | 17.47 | 3 || 9960 | 2014-03-15 | 3.55 | 3 || 9959 | 2014-03-14 | - 32.00 | 1 |上述触发器的规则是如果事件发生超过 3 次则在第 3 次重复时触发。与前面的 MODEL 子句类似我们可以使用添加到 Oracle 12c 中的 Oracle 特定的子句来执行该操作SELECT ... FROM some_table-- 将此放在任何 table 之后尽心模式匹配-- table 的内容MATCH_RECOGNIZE (...)MATCH_RECOGNIZE 最简单的应用程序包括以下子条款SELECT *FROM seriesMATCH_RECOGNIZE ( -- 模式匹配按此顺序执行 ORDER BY ... -- 这些时模式匹配产生的列 MEASURES ... -- 对行的简短说明 -- 返回匹配结果 ALL ROWS PER MATCH -- 要匹配的事件的“正则表达式” PATTERN (...) -- “什么是事件”的定义 DEFINE ...)这有些不可思议。让我们看一些子句的实现示例SELECT *FROM seriesMATCH_RECOGNIZE ( ORDER BY id MEASURES classifier() AS trg ALL ROWS PER MATCH PATTERN (S (R X R)?) DEFINE R AS sign(R.amount) prev(sign(R.amount)), X AS sign(X.amount) prev(sign(X.amount)))在此我们做了什么按照我们想要匹配事件的顺序根据 ID 对表进行排序比较容易。然后指定我们所需的值作为结果。我们需要“MEASURE” trg它被定义为分类器也就是我们随后将在 PATTERN 中使用的文本。另外我们希望匹配所有行。然后我们指定一个类似于正则表达式的模式。在该模式中以“S”代表开始事件随后是可选事件“R”它代表重复事件“X”代表特殊事件 X随后的一个或多个“R”代表再次重复。如果整个模式匹配我们得到 SRXR 或 SRXRR 或 SRXRRR即 X 将位于序列长度4 的第三位上。最后我们将 R 和 X 定义为相同的东西当前行的 SIGN(AMOUNT) 事件与前一行的 SIGN(AMOUNT) 事件相同时。我们不必定义“S”。“S”可以是任何其他行。该查询的结果输出如下| ID | VALUE_DATE | AMOUNT | TRG ||------|------------|---------|-----|| 9997 | 2014-03-18 | 99.17 | S || 9981 | 2014-03-16 | - 71.44 | R || 9979 | 2014-03-16 | - 94.60 | X || 9977 | 2014-03-16 | - 6.96 | R || 9971 | 2014-03-15 | - 65.95 | S || 9964 | 2014-03-15 | 15.13 | S || 9962 | 2014-03-15 | 17.47 | S || 9960 | 2014-03-15 | 3.55 | S || 9959 | 2014-03-14 | - 32.00 | S |我们可以在事件流中看到一个“X”。这正是我们所期望的。在序列长度 3 的事件(同一符号)中的第三次重复。太棒了因为我们并不真正关心“S”和“R”事件所以我们可以删除它们SELECT id, value_date, amount, CASE trg WHEN X THEN X END trgFROM seriesMATCH_RECOGNIZE ( ORDER BY id MEASURES classifier() AS trg ALL ROWS PER MATCH PATTERN (S (R X R)?) DEFINE R AS sign(R.amount) prev(sign(R.amount)), X AS sign(X.amount) prev(sign(X.amount)))结果如下| ID | VALUE_DATE | AMOUNT | TRG ||------|------------|---------|-----|| 9997 | 2014-03-18 | 99.17 | || 9981 | 2014-03-16 | - 71.44 | || 9979 | 2014-03-16 | - 94.60 | X || 9977 | 2014-03-16 | - 6.96 | || 9971 | 2014-03-15 | - 65.95 | || 9964 | 2014-03-15 | 15.13 | || 9962 | 2014-03-15 | 17.47 | || 9960 | 2014-03-15 | 3.55 | || 9959 | 2014-03-14 | - 32.00 | |多亏了 Oracle再说一次不要指望我能比优秀的 Oracle 白皮书更好地解释这一点如果你使用的是 Oracle 12c我强烈建议你阅读该白皮书http://www.oracle.com/ocom/groups/public/otn/documents/webcontent/1965433.pdf9. 旋转和非旋转如果你已经读过这篇文章了那么下面的内容将非常简单以下是我们的数据即演员、电影片名和电影评级| NAME | TITLE | RATING ||-----------|-----------------|--------|| A. GRANT | ANNIE IDENTITY | G || A. GRANT | DISCIPLE MOTHER | PG || A. GRANT | GLORY TRACY | PG-13 || A. HUDSON | LEGEND JEDI | PG || A. CRONYN | IRON MOON | PG || A. CRONYN | LADY STAGE | PG || B. WALKEN | SIEGE MADRE | R |这就是我们所说的旋转| NAME | NC-17 | PG | G | PG-13 | R ||-----------|-------|-----|-----|-------|-----|| A. GRANT | 3 | 6 | 5 | 3 | 1 || A. HUDSON | 12 | 4 | 7 | 9 | 2 || A. CRONYN | 6 | 9 | 2 | 6 | 4 || B. WALKEN | 8 | 8 | 4 | 7 | 3 || B. WILLIS | 5 | 5 | 14 | 3 | 6 || C. DENCH | 6 | 4 | 5 | 4 | 5 || C. NEESON | 3 | 8 | 4 | 7 | 3 |观察我们是如何按演员分组的然后根据每个演员所演电影的评级来“旋转”电影的数量。我们不是以“关系”的方式来显示它(即每个组是一行)而是将整体旋转为每个组生成一列。我们可以这样做是因为我们事先知道所有可能的组合。非旋转与此相反从开始时如果我们想要回到用“每个组一行”的形式表示即| NAME | RATING | COUNT ||-----------|--------|-------|| A. GRANT | NC-17 | 3 || A. GRANT | PG | 6 || A. GRANT | G | 5 || A. GRANT | PG-13 | 3 || A. GRANT | R | 6 || A. HUDSON | NC-17 | 12 || A. HUDSON | PG | 4 |其实很简单。在 PostgreSQL 中可以这样做SELECT first_name, last_name, count(*) FILTER (WHERE rating NC-17) AS NC-17
