你认为不可能的10个SQL技巧
以下10个技巧展示了仅用几行声明性SQL编写的惊人功能，从而产生简单且复杂的输出。
1.一切都是一张桌子
这是最琐碎的技巧，甚至不是真正的技巧，但这是全面理解SQL的基础：一切都是表格！当您看到这样的SQL语句时：
SELECT *
FROM person
…你会很快发现表person中坐在那里FROM条款。太酷了，那是一张桌子。但是您是否意识到整个语句也是一个表？例如，您可以编写：
SELECT *
FROM (
SELECT *
FROM person
) t
现在，您已经创建了所谓的“派生表” –即子句中的嵌套SELECT语句FROM。
2.使用递归SQL生成数据
公用表表达式（也称为：CTE，在Oracle中也称为子查询因式分解）是在SQL中声明变量的唯一方法（除了WINDOW只有PostgreSQL和Sybase SQL Anywhere才知道的晦涩的子句）。
这是一个强大的概念。极其强大。考虑以下语句：
– Table variables
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
使用simple WITH子句，您可以指定表变量的列表（请记住：所有内容都是一个表），它们甚至可能相互依赖。
3.运行总计计算
从概念上讲，运行总计很容易理解。
在Microsoft Excel中，您只需计算两个先前（或后续）值的和（或差），然后使用有用的十字光标将该公式拉入整个电子表格。您通过电子表格“运行”总计。“总计”。
窗口函数是一个强大的概念–起初并不容易理解，但实际上，它们确实非常简单：
窗口函数是相对于由SELECT转换的当前行的行的子集的聚合/排名
而已。????
本质上，这意味着窗口函数可以对当前行“上方”或“下方”的行执行计算。与普通的和不同GROUP BY，它们不会转换行，这使它们非常有用。
语法可以总结如下，其中各个部分是可选的
1个
2
3
4
5 function(…) OVER (
PARTITION BY …
ORDER BY …
ROWS BETWEEN … AND …
)
因此，我们有任何类型的函数（稍后将看到此类函数的示例），后跟此OVER()子句，该子句指定了窗口。即该OVER()子句定义：
本PARTITION：是的，只有行相同的分区作为当前行会被认为是窗口
该ORDER：该窗口可以订购独立的正是我们选择
的ROWS（或RANGE）帧定义：窗口可以限制到行“超前”的固定量和“后面”
4.找到最大的无间隙系列
当我们进行声明式编程时，我们不再关心维护任何状态计数器和内存中计数器。我们想以在线分析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 |
那没有太大帮助。让我们从时间戳中删除小时。这很容易：
1个
2
3
4 SELECT DISTINCT
cast(login_time AS DATE) AS login_date
FROM logins
WHERE user_id = :user_id
产生：
| LOGIN_DATE |
|---|
| 2014-03-18 |
| 2014-03-16 |
| 2014-03-15 |
| 2014-03-14 |
现在，我们已经了解了窗口函数，让我们向每个日期添加一个简单的行号：
1个
2
3
4 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 |
还是容易。现在，如果不是将这些值分开选择，而是减去它们，会发生什么呢？
1个
2
3
4 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 |
5.查找系列的长度
以前，我们已经看到了一系列连续的值。这很容易处理，因为我们可以滥用整数的连续性。如果“系列”的定义不太直观，又有几个系列包含相同的值，该怎么办？考虑以下数据，其中LENGTH我们要计算的每个序列的长度是：
| ID | VALUE_DATE | 金额 | 长度 |
|---|---|---|---|
| 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 | 金额 | 长度 |
|---|---|---|---|
| 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 |
我们该怎么做呢？“简单”;）首先，让我们消除所有杂音，然后添加另一个行号：
1个
2
3
4
5
6 SELECT
id, amount,
sign(amount) AS sign,
row_number()
OVER (ORDER BY id DESC) AS rn
FROM trx
这将给我们：
| ID | 金额 | 招牌 | 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 | 金额 | 招牌 | 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 |
6. SQL的子集总和问题
本质上，对于所有这些总数……
| ID | 总计 |
|---|---|
| 1 | 25150 |
| 2 | 19800 |
| 3 | 27511 |
…我们希望找到可能的“最佳”（即最接近）总和，包括以下各项的任意组合：
| ID | 项目 |
|---|---|
| 1 | 7120 |
| 2 | 8150 |
| 3 | 8255 |
| 4 | 9051 |
| 5 | 1220 |
| 6 | 12515 |
| 7 | 13555 |
| 8 | 5221 |
| 9 | 812 |
| 10 | 6562 |
由于您很快就可以进行脑力数学运算，因此您立即计算出这是最好的总和：
| 总计 | 最佳 计算方式 |
|---|---|
| 25150 | 25133 |
| 19800 | 19768 |
| 27511 | 27488 |
如何使用SQL？简单。只需创建一个包含所有2 n个 可能和的CTE，然后为每个找到最接近的一个TOTAL：
1个
2
3
4
5
6
7
8
9 – All the possible 2N sums
WITH 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 calc
FROM draw_the_rest_of_the_bleep_owl
7.封顶总计
到目前为止，我们已经看到了如何使用窗口函数使用SQL计算“常规”运行总计。那很简单。现在，如果我们将运行总计设置为永远不会低于零，那又如何呢？本质上，我们要计算此：
| DATE | 金额 | 总计 |
|---|---|---|
| 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减去，而不是显示真实TOTAL的-15550，我们简单地展示0。换句话说（或数据集）：
| DATE | 金额 | 总计 |
|---|---|---|
| 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 |
8.时间序列模式识别
如果您涉及欺诈检测或对大型数据集进行实时分析的任何其他领域，那么时间序列模式识别对于您而言当然不是一个新名词。
如果我们查看“系列长度”数据集，则可能需要在时间序列上针对复杂事件生成触发器，如下所示：
| ID | VALUE_DATE | 金额| LEN | 触发
| ------ | ------------ || --------- | ----- | --------
| 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 |
9.枢纽和取消枢纽
如果您已经读了那么多内容，那么以下内容将非常简单：
这是我们的数据，即演员，电影名称和电影等级：
| NAME | 标题 | 评分 |
|---|---|---|
| A.格兰特 | 匿名身份 | G |
| A.格兰特 | 尊敬的母亲 | PG |
| A.格兰特 | 荣耀追踪 | PG-13 |
| A. HUDSON | **绝地武士 | PG |
| A. CRONYN | 铁月亮 | PG |
| A. CRONYN | 女士舞台 | PG |
| B. WALKEN | SIEGE MADRE | R |
这就是我们所说的透视：
| NAME | NC-17 | PG | G | PG-13 | R |
|---|---|---|---|---|---|
| A.格兰特 | 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 | 评分 | COUNT |
|---|---|---|
| A.格兰特 | NC-17 | 3 |
| A.格兰特 | PG | 6 |
| A.格兰特 | G | 5 |
| A.格兰特 | PG-13 | 3 |
| A.格兰特 | R | 6 |
| A. HUDSON | NC-17 | 12 |
| A. HUDSON | PG | 4 |
10.滥用XML和JSON
首先JSON只是具有较少功能和较少语法的XML
现在，每个人都知道XML很棒。因此，推论是：JSON不太出色，不要使用JSON。
现在，我们已经解决了这一点，我们可以放心地忽略正在进行的JSON-in-the-the-base-hype炒作（无论如何，你们大多数人都会在五年内后悔），然后转到最后一个示例。如何在数据库中执行XML。
最后，开发这么多年我也总结了一套学习Java的资料与面试题，如果你在技术上面想提升自己的话，可以关注我，私信发送领取资料或者在评论区留下自己的联系方式，有时间记得帮我点下转发让跟多的人看到哦。