{"id":567,"date":"2018-11-08T12:44:46","date_gmt":"2018-11-08T18:44:46","guid":{"rendered":"http:\/\/thenoyes.com\/littlenoise\/?p=567"},"modified":"2018-11-08T13:14:25","modified_gmt":"2018-11-08T19:14:25","slug":"window-functions-with-unusual-boundaries","status":"publish","type":"post","link":"https:\/\/thenoyes.com\/littlenoise\/?p=567","title":{"rendered":"Window Functions with Unusual Boundaries"},"content":{"rendered":"

Somebody on Freenode wanted this:<\/p>\n

   Source            Result\r\n+----+------+    +----+------+\r\n| id | x    |    | id | c    |\r\n+----+------+    +----+------+\r\n|  1 |    1 |    |  1 |    2 |\r\n|  2 |    1 |    |  2 |    2 |\r\n|  3 | NULL |    |  3 | NULL |\r\n|  4 | NULL | -> |  4 | NULL |\r\n|  5 |    1 |    |  5 |    1 |\r\n|  6 | NULL |    |  6 | NULL |\r\n|  7 |    1 |    |  7 |    3 |\r\n|  9 |    1 |    |  9 |    3 |\r\n| 10 |    1 |    | 10 |    3 |\r\n+----+------+    +----+------+\r\n<\/pre>\n
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<\/em> anyone wants such a thing; it is not ours to reason why…<\/p>\n
Anyway, the point is that you can use arbitrary expressions, even subqueries, to define your window partitions.<\/p>\n
SELECT \r\n    id, \r\n    -- Count of rows in windows bound by NULL values in x\r\n    IF(\r\n      x IS NULL, \r\n      NULL, \r\n      COUNT(*) OVER (PARTITION BY (\r\n        -- Partition by the number of earlier NULLs\r\n        SELECT COUNT(*) FROM t AS t1 \r\n        WHERE t1.id < t.id AND t1.x IS NULL\r\n        ), \r\n        -- Exclude the end boundary \"NULL\" from the window\r\n        x IS NULL\r\n        ORDER BY id\r\n      )\r\n    ) AS c \r\nFROM t;<\/pre>\n
How does it work?<\/p>\n
First, let's see what that subquery is all about:<\/p>\n
SELECT id, x, \r\n(SELECT COUNT(*) FROM t AS t1 WHERE t1.id < t.id AND t1.x IS NULL) AS p \r\nFROM t;\r\n+----+------+---+\r\n| id | x    | p |\r\n+----+------+---+\r\n|  1 |    1 | 0 |\r\n|  2 |    1 | 0 |\r\n|  3 | NULL | 0 |\r\n|  4 | NULL | 1 |\r\n|  5 |    1 | 2 |\r\n|  6 | NULL | 2 |\r\n|  7 |    1 | 3 |\r\n|  9 |    1 | 3 |\r\n| 10 |    1 | 3 |\r\n+----+------+---+<\/pre>\n
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.<\/p>\n
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.<\/p>\n
SELECT id, x, \r\n(SELECT COUNT(*) FROM t AS t1 WHERE t1.id < t.id AND t1.x IS NULL) AS p1, \r\nx IS NULL AS p2 \r\nFROM t;\r\n+----+------+------+----+\r\n| id | x    | p1   | p2 |\r\n+----+------+------+----+\r\n|  1 |    1 |    0 |  0 |\r\n|  2 |    1 |    0 |  0 |\r\n|  3 | NULL |    0 |  1 |\r\n|  4 | NULL |    1 |  1 |\r\n|  5 |    1 |    2 |  0 |\r\n|  6 | NULL |    2 |  1 |\r\n|  7 |    1 |    3 |  0 |\r\n|  9 |    1 |    3 |  0 |\r\n| 10 |    1 |    3 |  0 |\r\n+----+------+------+----+<\/pre>\n
The combination of (p1, p2) neatly partition the rows, so each \"NULL row\" is by itself, and non-NULL rows are together.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[4],"tags":[],"class_list":["post-567","post","type-post","status-publish","format-standard","hentry","category-mysql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p2IBF1-99","jetpack_sharing_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/567","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=567"}],"version-history":[{"count":10,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/567\/revisions"}],"predecessor-version":[{"id":577,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/567\/revisions\/577"}],"wp:attachment":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=567"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=567"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=567"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}