>我有一个包含这些值的表;
user_id ts val
uid1 19.05.2019 01:49:50 0
uid1 19.05.2019 01:50:15 0
uid1 19.05.2019 01:50:20 0
uid1 19.05.2019 01:59:50 1
uid1 19.05.2019 02:20:10 1
uid1 19.05.2019 02:20:15 0
uid1 19.05.2019 02:20:19 0
uid1 19.05.2019 02:30:53 1
uid1 19.05.2019 11:10:25 1
uid1 19.05.2019 11:13:40 0
uid1 19.05.2019 11:13:50 0
uid1 19.05.2019 11:20:19 1
uid2 19.05.2019 15:01:44 0
uid2 19.05.2019 15:05:55 0
uid2 19.05.2019 17:19:35 1
uid2 19.05.2019 17:20:01 0
uid2 19.05.2019 17:20:35 0
uid2 19.05.2019 19:15:50 1
当我仅按子句分区查询此表时,结果如下所示;
查询 :select *, sum(val) over (partition by user_id) as res from example_table;
user_id ts val res
uid1 19.05.2019 01:49:50 0 5
uid1 19.05.2019 01:50:15 0 5
uid1 19.05.2019 01:50:20 0 5
uid1 19.05.2019 01:59:50 1 5
uid1 19.05.2019 02:20:10 1 5
uid1 19.05.2019 02:20:15 0 5
uid1 19.05.2019 02:20:19 0 5
uid1 19.05.2019 02:30:53 1 5
uid1 19.05.2019 11:10:25 1 5
uid1 19.05.2019 11:13:40 0 5
uid1 19.05.2019 11:13:50 0 5
uid1 19.05.2019 11:20:19 1 5
uid2 19.05.2019 15:01:44 0 2
uid2 19.05.2019 15:05:55 0 2
uid2 19.05.2019 17:19:35 1 2
uid2 19.05.2019 17:20:01 0 2
uid2 19.05.2019 17:20:35 0 2
uid2 19.05.2019 19:15:50 1 2
在上面的结果中,res列具有每个分区的val列的总和值。但是,如果我查询分区依据和排序依据的表,我会得到这些结果;
查询:select *, sum(val) over (partition by user_id order by ts) as res from example_table;
user_id ts val res
uid1 19.05.2019 01:49:50 0 0
uid1 19.05.2019 01:50:15 0 0
uid1 19.05.2019 01:50:20 0 0
uid1 19.05.2019 01:59:50 1 1
uid1 19.05.2019 02:20:10 1 2
uid1 19.05.2019 02:20:15 0 2
uid1 19.05.2019 02:20:19 0 2
uid1 19.05.2019 02:30:53 1 3
uid1 19.05.2019 11:10:25 1 4
uid1 19.05.2019 11:13:40 0 4
uid1 19.05.2019 11:13:50 0 4
uid1 19.05.2019 11:20:19 1 5
uid2 19.05.2019 15:01:44 0 0
uid2 19.05.2019 15:05:55 0 0
uid2 19.05.2019 17:19:35 1 1
uid2 19.05.2019 17:20:01 0 1
uid2 19.05.2019 17:20:35 0 1
uid2 19.05.2019 19:15:50 1 2
但是对于按子句排序,res列具有每个分区的每一行的值列的累积总和。
为什么?我无法理解这一点。
更新
此处记录了此行为:
4.2.8. 窗口函数调用
[..] 默认成帧选项为
RANGE UNBOUNDED PRECEDING
,即 与RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
相同。跟ORDER BY
,这会将帧设置为分区中的所有行通过当前行的最后ORDER BY
对等体启动。没有ORDER BY
,这意味着分区的所有行都包含在 窗口框架,因为所有行都成为当前行的对等行。
这意味着:
在没有frame_clause的情况下 – 默认使用RANGE UNBOUNDED PRECEDING
。这包括:
- 根据
ORDER BY
子句"位于"当前行之前的所有行 - 当前行
ORDER BY
列中与当前行具有相同值的所有行
在没有ORDER BY
条款的情况下 - 假设ORDER BY NULL
(尽管我再次猜测)。因此,框架将包括分区中的所有行,因为ORDER BY
列中的值在每一行中都是相同的(始终NULL
)。
原答案:
免责声明:以下与其说是限定答案,不如说是猜测。我没有找到任何可以确认我写的内容的文档。同时,我认为目前给出的答案不能正确解释这种行为。
结果差异的原因不是直接的 ORDER BY 子句,因为a + b + c
与c + b + a
相同。原因是(这是我的猜测)ORDER BY 子句隐式地将frame_clause定义为
rows between unbounded preceding and current row
请尝试以下查询:
select *
, sum(val) over (partition by user_id) as res
, sum(val) over (partition by user_id order by ts) as res_order_by
, sum(val) over (
partition by user_id
order by ts
rows between unbounded preceding and current row
) as res_order_by_unbounded_preceding
, sum(val) over (
partition by user_id
-- order by ts
rows between unbounded preceding and current row
) as res_preceding
, sum(val) over (
partition by user_id
-- order by ts
rows between current row and unbounded following
) as res_following
, sum(val) over (
partition by user_id
order by ts
rows between unbounded preceding and unbounded following
) as res_orderby_preceding_following
from example_table;
数据库<>小提琴
您将看到,您可以在没有 ORDER BY 子句的情况下获得累积总和,也可以使用 ORDER BY 子句获得"全部"总和。
这就是order by
与窗口函数一起工作的方式。
当它不存在时,该函数的作用类似于窗口框架定义的聚合函数。 也就是说,它为窗口框架中的所有内容返回相同的值。
当它存在时,该函数以累积方式起作用,结果"最多"当前行。
当然,这也受到窗框规格的影响。 但是,您的示例查询不包括rows
或range
以及order by
。
从 3.5 开始。窗口功能:
。您还可以控制行的处理顺序 窗口函数在 OVER 中使用 ORDER BY..
这是over (partition by user_id)
的差异,其中没有顺序来处理每个组内的行,它们被划分,over (partition by user_id order by ts)
在按ts
对行进行排序后处理行。
这意味着对于每一行,将根据该行在排序行中的位置计算新sum(val)
。
对于rank()
窗口函数的情况,也许更容易理解这一点,因此请访问本答案开头的链接,其中有一个非常好的例子以及有关该主题的更多信息。
让我们创建一个简单的示例来正确理解它。
我们考虑了一个带有每日贷方和借方的银行表。 以下查询将计算客户的每日余额和总余额(partition by
用于除以单个客户的结果),如列名称使用带和不带ORDER BY
子句的分析函数所建议SUM
:
SQL> WITH BANK_TABLE (CUST_ID, DT, AMOUNT_CR_DR)
2 AS
3 (
4 SELECT 1, DATE '2019-01-01', 1000 FROM DUAL UNION ALL
5 SELECT 1, DATE '2019-01-02', 2000 FROM DUAL UNION ALL
6 SELECT 1, DATE '2019-01-03', -1000 FROM DUAL UNION ALL
7 SELECT 1, DATE '2019-01-04', -500 FROM DUAL UNION ALL
8 SELECT 1, DATE '2019-01-05', 2000 FROM DUAL
9 )
10 SELECT DT, AMOUNT_CR_DR,
11 SUM(AMOUNT_CR_DR) OVER (PARTITION BY CUST_ID) AS TOTAL_BALANCE_LIFE_TIME,
12 SUM(AMOUNT_CR_DR) OVER (PARTITION BY CUST_ID ORDER BY DT) AS TOTAL_BALANCE_TILL_DATE
13 FROM BANK_TABLE
14 ORDER BY CUST_ID, DT;
DT AMOUNT_CR_DR TOTAL_BALANCE_LIFE_TIME TOTAL_BALANCE_TILL_DATE
--------- ------------ ----------------------- -----------------------
01-JAN-19 1000 3500 1000
02-JAN-19 2000 3500 3000
03-JAN-19 -1000 3500 2000
04-JAN-19 -500 3500 1500
05-JAN-19 2000 3500 3500
Partition by
子句用于划分组中的行order by
子句用于按该顺序计算值。
所以对于按顺序排列的行,
对于第一行,将仅返回第一行的总和。
对于第二行,总和将是第一行加第二行。
同样的方式直到分区的最后一行。
干杯!!