未加星标

Window Functions with Unusual Boundaries

字体大小 | |
[系统(windows) 所属分类 系统(windows) | 发布者 店小二04 | 时间 2018 | 作者 红领巾 ] 0人收藏点击收藏

Somebody on Freenode wanted this:

Source Result
+----+------+ +----+------+
| id | x | | id | c |
+----+------+ +----+------+
| 1 | 1 | | 1 | 2 |
| 2 | 1 | | 2 | 2 |
| 3 | NULL | | 3 | NULL |
| 4 | NULL | -> | 4 | NULL |
| 5 | 1 | | 5 | 1 |
| 6 | NULL | | 6 | NULL |
| 7 | 1 | | 7 | 3 |
| 9 | 1 | | 9 | 3 |
| 10 | 1 | | 10 | 3 |
+----+------+ +----+------+

The result uses the NULL values in x as boundaries of windows, and counts the number of rows within each window. I don't know why anyone wants such a thing; it is not ours to reason why...

Anyway, the point is that you can use arbitrary expressions, even subqueries, to define your window partitions.

SELECT
id,
-- Count of rows in windows bound by NULL values in x
IF(
x IS NULL,
NULL,
COUNT(*) OVER (PARTITION BY (
-- Partition by the number of earlier NULLs
SELECT COUNT(*) FROM t AS t1
WHERE t1.id < t.id AND t1.x IS NULL
),
-- Exclude the end boundary "NULL" from the window
x IS NULL
ORDER BY id
)
) AS c
FROM t;

How does it work?

First, let's see what that subquery is all about:

SELECT id, x,
(SELECT COUNT(*) FROM t AS t1 WHERE t1.id < t.id AND t1.x IS NULL) AS p
FROM t;
+----+------+---+
| id | x | p |
+----+------+---+
| 1 | 1 | 0 |
| 2 | 1 | 0 |
| 3 | NULL | 0 |
| 4 | NULL | 1 |
| 5 | 1 | 2 |
| 6 | NULL | 2 |
| 7 | 1 | 3 |
| 9 | 1 | 3 |
| 10 | 1 | 3 |
+----+------+---+

By counting the number of "NULL rows" appearing earlier in the table, we get a value we can use to find the starting point of each window.

That alone goes one row too far, though - the "NULL row" which should end each window gets included in the window. However, you can use multiple expressions to partition windows.

SELECT id, x,
(SELECT COUNT(*) FROM t AS t1 WHERE t1.id < t.id AND t1.x IS NULL) AS p1,
x IS NULL AS p2
FROM t;
+----+------+------+----+
| id | x | p1 | p2 |
+----+------+------+----+
| 1 | 1 | 0 | 0 |
| 2 | 1 | 0 | 0 |
| 3 | NULL | 0 | 1 |
| 4 | NULL | 1 | 1 |
| 5 | 1 | 2 | 0 |
| 6 | NULL | 2 | 1 |
| 7 | 1 | 3 | 0 |
| 9 | 1 | 3 | 0 |
| 10 | 1 | 3 | 0 |
+----+------+------+----+

The combination of (p1, p2) neatly partition the rows, so each "NULL row" is by itself, and non-NULL rows are together.

本文系统(windows)相关术语:三级网络技术 计算机三级网络技术 网络技术基础 计算机网络技术

代码区博客精选文章
分页:12
转载请注明
本文标题:Window Functions with Unusual Boundaries
本站链接:https://www.codesec.net/view/611524.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 系统(windows) | 评论(0) | 阅读(71)