SQL通用表表达式和窗口功能-DEV365 开发者社区

有时在为报告目的编写查询时，操纵数据所需的查询可能会变得复杂。

如果您能够在运行查询后使用PHP或Python来操纵数据，则获得所需结果所需的查询要简单得多。

，但有时您只需要找到一种仅使用SQL查询所需的数据的方法。

出于本文的目的，查询的期望结果是每天获取每天交易的总金额，并在7天期间显示每日数量和运行总额。

日期	金额	tally
2023-01-01	28.50	28.50
2023-01-02	40.50	69.00
2023-01-03	15.50	84.50
2023-01-04	48.00	132.50
2023-01-05	0.00	132.50
2023-01-06	0.00	132.50
2023-01-07	30.00	162.50

查询以获取此输出的数据是

transaction_date	金额
2023-01-01 12:55:19	18.00
2023-01-01 23:05:43	10.50
2023-01-02 10:29:23	10.50
2023-01-02 11:40:19	30.00
2023-01-03 11:08:33	10.50
2023-01-03 11:38:20	5.00
2023-01-04 09:32:00	18.00
2023-01-04 10:52:01	30.00
2023-01-07 16:14:27	30.00

入门

有了任何复杂的查询，诀窍是将其分解成其部分并在每个部分上工作，直到您准备将它们放在一起。

在这里，我将运行查询以获取日期范围的数据，以便我可以看到我需要做的事情。

SELECT transaction_date, amount
FROM subscriptions
WHERE transaction_date 
BETWEEN '2023-01-01 00:00:00' 
AND '2023-01-07 23:59:59'

transaction_date	金额
2023-01-01 12:55:19	18.00
2023-01-01 23:05:43	10.50
2023-01-02 10:29:23	10.50
2023-01-02 11:40:19	30.00
2023-01-03 11:08:33	10.50
2023-01-03 11:38:20	5.00
2023-01-04 09:32:00	18.00
2023-01-04 10:52:01	30.00
2023-01-07 16:14:27	30.00

现在，我需要按白天对数据进行分组，并总和每天交易的数量。

SELECT DATE(transaction_date) transaction_date, 
SUM(amount) amount
FROM subscriptions
WHERE transaction_date 
BETWEEN '2023-01-01 00:00:00' 
AND '2023-01-07 23:59:59'
GROUP BY DATE(transaction_date)

transaction_date	金额
2023-01-01	28.50
2023-01-02	40.50
2023-01-03	15.50
2023-01-04	48.00
2023-01-07	30.00

日期的格式是正确的，金额是正确的，但我们丢失了天数。

SQL中有很多方法可以填补空白，有些则相当简单，而另一些则过于复杂。

对我来说，我首选的方法是使用常见的表格表达式。

什么是常见的表格表达式（CTE）

CTE是可重复使用的临时结果集。以最简单的形式，他们可以替换子查询或视图，但是它们具有一些独特的特征，从而使它们变得强大。

ctes是可重复使用的，这意味着查询一次是运行一次，但是可以多次使用/查询所得的数据集。

定义一个CTE

使用
定义

WITH counter AS (...query...)

这创建了一个临时结果集，然后可以在查询中使用

WITH counter AS (SELECT 1)
SELECT * FROM counter

如果需要，可以定义从CTE返回的字段名称

WITH counter (number) AS (SELECT 1)
SELECT * FROM counter

也可以定义多个CTE

WITH counter (number) AS (SELECT 1),
WITH total (total) AS (SELECT 2)
SELECT * FROM counter

一个更复杂的CTE看起来像

WITH counter (number) AS (
SELECT 1
UNION ALL
SELECT 2
)
SELECT * FROM counter

编号
1
2

CTE变得强大的地方是他们递归引用自己的能力

如果我想返回数字1到6，我可以使用递归CTE生成数字

WITH RECURSIVE counter (number) AS (
SELECT 1
UNION ALL
SELECT number + 1 FROM counter WHERE number < 6
)
SELECT * FROM counter

这将产生

编号
1
2
3
4
5
6

使用CTE生成日期范围

对于我们的原始请求，即使没有交易的日期，也需要在日期范围内的所有日期。

为此，可以创建一个CTE来返回所需的日期范围。

WITH RECURSIVE dates (day) AS (
SELECT '2023-01-01'
UNION ALL
SELECT day + INTERVAL 1 day FROM dates WHERE day < '2023-01-07'
)
SELECT * FROM dates

天
2023-01-01
2023-01-02
2023-01-03
2023-01-04
2023-01-05
2023-01-06
2023-01-07

加入查询中的CTE

出于原始要求的目的，我添加了将交易获取到其自己的CTE中的查询

WITH RECURSIVE dates (day) AS (
SELECT '2023-01-01'
UNION ALL
SELECT day + INTERVAL 1 day FROM dates WHERE day < '2023-01-07'
),
transactions AS (
SELECT DATE(transaction_date)
 transaction_date, 
SUM(amount) amount
FROM subscriptions
WHERE transaction_date 
BETWEEN '2023-01-01 00:00:00' 
AND '2023-01-07 23:59:59'
GROUP BY DATE(transaction_date))

SELECT * FROM transactions

CTE的功能是能够在将所有CTE的结果放在一起之前检查每个CTE的结果

从交易中选择 *或从日期选择 *可以帮助我验证查询产生的数据是否正确。

虽然此查询工作原样，但如果需要更改日期范围，则需要更新两个CTE。

CTE可以引用另一个CTE。要更改查询，因此只需要更新一个CTE才能更改日期范围，我们可以从交易中引用日期CTE。

WHERE transaction_date 
BETWEEN (SELECT MIN(day) FROM dates)
AND (SELECT MAX(day) + INTERVAL 1 DAY FROM dates)

现在将两个ctes加在一起以查看所有数据

WITH RECURSIVE dates (day) AS (
SELECT '2023-01-01'
UNION ALL
SELECT day + INTERVAL 1 day FROM dates WHERE day < '2023-01-07'
),

transactions AS (
SELECT DATE(transaction_date)
 transaction_date, 
SUM(amount) amount
FROM subscriptions
WHERE transaction_date 
BETWEEN (SELECT MIN(day) FROM dates)
AND (SELECT MAX(day) + INTERVAL 1 DAY FROM dates)
GROUP BY DATE(transaction_date))

SELECT day,amount FROM dates d
LEFT JOIN transactions t ON t.transaction_date = d.day

天	金额
2023-01-01	28.50
2023-01-02	40.50
2023-01-03	15.50
2023-01-04	48.00
2023-01-05	null
2023-01-06	null
2023-01-07	30.00

除零以外，到目前为止，数据看起来不错。为了删除零值，我们可以使用cocece函数。

CoaleSce做什么？

colesce在值列表中用第一个非零值代替零值

coaleasce（value，0）如果值为null，则将替换为0。同样合并（value，null，null，0）也将替换为0作为其值列表中的第一个非零值。

SELECT day,COALESCE(amount,0) amount FROM dates d
LEFT JOIN transactions t ON t.transaction_date = d.day

天	金额
2023-01-01	28.50
2023-01-02	40.50
2023-01-03	15.50
2023-01-04	48.00
2023-01-05	0.00
2023-01-06	0.00
2023-01-07	30.00

该请求的最后一部分是运行tally。要返回运行的TALLY，需要SQL窗口功能。

什么是窗口功能？

窗口函数是整个结果集运行的函数。它们允许您获取运行的计数，或从上一行或从当前行前方的行中获取数据。

有很多窗口功能，其中一些是铅，滞后，ntile

LEAD-从当前行之前的一排返回值
滞后 - 从上一行返回值
ntile-将行分为几节，这将使您只能返回结果的前25％。

您也可以在窗口中使用汇总功能（sum，avg etc）

我们将使用总和函数获取运行的tally。

窗口函数的格式为

function(field) OVER (clause)

子句的分区类似于群体，但不相同。

在窗口函数中，函数在每个行上运行，不会将行分组在一起，但会产生将值分组在一起的结果。

订购以特定方式订购结果，然后用来计算结果。

取得跑步的计数

要获取所需的运行tally，我们需要在查询中添加一个窗口函数字段

SUM(amount) OVER (ORDER BY day) tally

预定窗口

虽然可以使用上述方法。如果您发现自己处于需要多个窗口功能的情况下，则可以预先定义窗口并引用它。

在我们当前的查询中

SELECT day,COALESCE(amount,0) amount,
SUM(amount) OVER (ORDER BY day) tally
FROM dates d
LEFT JOIN transactions t ON t.transaction_date = d.day

这可以更改为

SELECT day,COALESCE(amount,0) amount,
SUM(amount) OVER w tally
FROM dates d
LEFT JOIN transactions t ON t.transaction_date = d.day
WINDOW w AS (ORDER BY day)

最终查询

使用窗口函数的最终查询和CTE看起来像

WITH RECURSIVE dates (day) AS (
SELECT '2023-01-01'
UNION ALL
SELECT day + INTERVAL 1 day FROM dates WHERE day < '2023-01-07'
),

transactions AS (
SELECT DATE(transaction_date) 
transaction_date, 
SUM(amount) amount
FROM subscriptions
WHERE transaction_date 
BETWEEN (SELECT MIN(day) FROM dates)
AND (SELECT MAX(day) + INTERVAL 1 DAY FROM dates)
GROUP BY DATE(transaction_date)
)

SELECT day,COALESCE(amount,0) amount,
SUM(amount) OVER w tally
FROM dates d
LEFT JOIN transactions t ON t.transaction_date = d.day
WINDOW w AS (ORDER BY day)

天	金额	tally
2023-01-01	28.50	28.50
2023-01-02	40.50	69.00
2023-01-03	15.50	84.50
2023-01-04	48.00	132.50
2023-01-05	0.00	132.50
2023-01-06	0.00	132.50
2023-01-07	30.00	162.50

通用表表达式和窗口打开SQL，以更好地数据处理，尤其是用于报告和数据分析。

CTE包含简化任务的强大功能，还可以更轻松地增加可读性或调试复杂查询。

可重复使用意味着您可能必须在查询中不止一次运行子查询，CTE只能运行一次查询并使用结果集而不是运行多个子查询，这可以在某些中增加查询的效率案例。

窗口功能是提供结果集分析的好方法，这在会计类型报告中很明显，但不限于仅限会计，它具有很多用例，其中使用窗口功能允许您收集所需的其他信息产生所需的结果。