<\/span><\/h3>\n\n\n\n\u5148\u770b\u770b\u4e0d\u5206\u533a\u7684\u6574\u4f53\u79fb\u52a8\u6c47\u603b\u503c\u7684\u60c5\u51b5\u3002\u6211\u4eec\u4f8b\u5b50\u91cc\u6307\u5b9a\u6c47\u603b\u524d1\u4e2a\u6570\u503c\u548c\u5f53\u524d\u503c\uff0c\u800c\u4e0d\u662f\u9ed8\u8ba4\u7684\u79fb\u52a8\u6c47\u603b\u503c\u7684\uff0c\u6240\u6709\u524d\u9762\u7684\u3002\u5982\u4e0b\u8d34\u4e862\u4e2a\u7ed3\u679c\uff0c\u65b9\u4fbf\u5bf9\u6bd4\u3002<\/p>\n\n\n\n
\u53ef\u4ee5\u770b\u5230\u666e\u901a\u79fb\u52a8\u6c47\u603b\uff0c\u6570\u503c\u4e00\u76f4\u5728\u7d2f\u52a0\u4e4b\u524d\u7684\uff0c\u800c\u6307\u5b9a\u4e86rows\u7684\uff0c\u53ea\u6c47\u603b\u4e86\u6307\u5b9a\u8303\u56f4\u5185\u7684\u3002<\/p>\n\n\n\n| select t2.*,sum(val) over(order by type,val) v from t2;<\/td> | select t2.*,sum(val) over(order by type,val rows between 1 preceding and current row) v from t2;<\/td><\/tr> |
+------+------+------+------+
| id | type | val | v |
+------+------+------+------+
| 1 | 1 | 111 | 111 |
| 2 | 1 | 122 | 233 |
| 3 | 1 | 133 | 366 |
| 4 | 2 | 222 | 588 |
| 5 | 2 | 233 | 821 |
| 6 | 2 | 244 | 1065 |
| 7 | 3 | 333 | 1398 |
+------+------+------+------+
7 rows in set (Elapsed: 00:00:00.02)<\/code><\/td>+------+------+------+------+
| id | type | val | v |
+------+------+------+------+
| 1 | 1 | 111 | 111 |
| 2 | 1 | 122 | 233 |
| 3 | 1 | 133 | 255 |
| 4 | 2 | 222 | 355 |
| 5 | 2 | 233 | 455 |
| 6 | 2 | 244 | 477 |
| 7 | 3 | 333 | 577 |
+------+------+------+------+
7 rows in set (Elapsed: 00:00:00.02)<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<\/span>\u6307\u5b9arows\u7684\u6574\u4f53\u79fb\u52a8\u6c47\u603b\u503cfollowing<\/span><\/h3>\n\n\n\n\u672c\u4f8b\u6307\u5b9a\u524d1\u4e2a\u548c\u540e1\u4e2a\uff0c\u51713\u4e2a(\u5982\u679c\u6709\u7684\u8bdd)\u7684\u79fb\u52a8\u6c47\u603b\u503c\u3002<\/p>\n\n\n\n gbase> select t2.*,sum(val) over(order by type,val rows between 1 preceding and 1 following) v from t2;\n+------+------+------+------+\n| id | type | val | v |\n+------+------+------+------+\n| 1 | 1 | 111 | 233 |\n| 2 | 1 | 122 | 366 |\n| 3 | 1 | 133 | 477 |\n| 4 | 2 | 222 | 588 |\n| 5 | 2 | 233 | 699 |\n| 6 | 2 | 244 | 810 |\n| 7 | 3 | 333 | 577 |\n+------+------+------+------+\n7 rows in set (Elapsed: 00:00:00.02)<\/code><\/pre>\n\n\n\n<\/span>\u6307\u5b9arows\u7684\u5206\u533a\u79fb\u52a8\u6c47\u603b\u503c<\/span><\/h2>\n\n\n\n| select t2.*,sum(val) over(partition by type order by type,val) v from t2;<\/td> | select t2.*,sum(val) over(partition by type order by type,val rows between 1 preceding and current row) v from t2;<\/td><\/tr> | +------+------+------+------+
| id | type | val | v |
+------+------+------+------+
| 1 | 1 | 111 | 111 |
| 2 | 1 | 122 | 233 |
| 3 | 1 | 133 | 366 |
| 7 | 3 | 333 | 333 |
| 4 | 2 | 222 | 222 |
| 5 | 2 | 233 | 455 |
| 6 | 2 | 244 | 699 |
+------+------+------+------+
7 rows in set (Elapsed: 00:00:00.05)<\/code><\/td>+------+------+------+------+
| id | type | val | v |
+------+------+------+------+
| 1 | 1 | 111 | 111 |
| 2 | 1 | 122 | 233 |
| 3 | 1 | 133 | 255 |
| 7 | 3 | 333 | 333 |
| 4 | 2 | 222 | 222 |
| 5 | 2 | 233 | 455 |
| 6 | 2 | 244 | 477 |
+------+------+------+------+
7 rows in set (Elapsed: 00:00:00.05)<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<\/span>RANGE\u7684\u4f8b\u5b50<\/span><\/h2>\n\n\n\n\u540c\u6837\u4ee5SUM OVER\u4e3a\u4f8b\u5b50\uff0c\u5206\u522b\u8ba1\u7b97\u524d\u9762\u5dee\u8ddd\u5c0f\u4e8e100\u7684\uff1b\u524d\u9762\u5dee\u8ddd\u5c0f\u4e8e100\u548c\u540e\u9762\u5dee\u8ddd\u5c0f\u4e8e100\u7684\u3002<\/p>\n\n\n\n| gbase> select t2.*,sum(val) over(order by type,val) v from t2;<\/td> | select t2.*,sum(val) over(order by val range between 100 preceding<\/strong><\/span><\/code> and 1 following) v from t2;<\/td>select t2.*,sum(val) over(order by val range between 100 preceding and 100 following<\/strong><\/span>) v from t2;<\/td><\/tr>+------+------+------+------+
| id | type | val | v |
+------+------+------+------+
| 1 | 1 | 111 | 111 |
| 2 | 1 | 122 | 233 |
| 3 | 1 | 133 | 366 |
| 4 | 2 | 222 | 588 |
| 5 | 2 | 233 | 821 |
| 6 | 2 | 244 | 1065 |
| 7 | 3 | 333 | 1398 |
+------+------+------+------+
7 rows in set (Elapsed: 00:00:00.02)<\/code><\/td>+------+------+------+------+
| id | type | val | v |
+------+------+------+------+
| 1 | 1 | 111 | 111 |
| 2 | 1 | 122 | 233 |
| 3 | 1 | 133 | 366 |
| 4 | 2 | 222 | 477 |
| 5 | 2 | 233 | 588 |
| 6 | 2 | 244 | 699 |
| 7 | 3 | 333 | 810 |
+------+------+------+------+
7 rows in set (Elapsed: 00:00:00.03)<\/code><\/td>+------+------+------+------+
| id | type | val | v |
+------+------+------+------+
| 1 | 1 | 111 | 366 |
| 2 | 1 | 122 | 588 |
| 3 | 1 | 133 | 821 |
| 4 | 2 | 222 | 954 |
| 5 | 2 | 233 | 1165 |
| 6 | 2 | 244 | 1032 |
| 7 | 3 | 333 | 810 |
+------+------+------+------+
7 rows in set (Elapsed: 00:00:00.02)<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<\/span>\u65f6\u95f4\u7c7b\u578b\u7684RANGE\u4f8b\u5b50<\/span><\/h2>\n\n\n\n\u6362\u4e2a\u6570\u636e\u8868<\/p>\n\n\n\n gbase> select * from t3;\n+------+--------+------------+\n| id | name | birth |\n+------+--------+------------+\n| 1 | \u5f20\u4e09 | 1960-01-01 |\n| 2 | \u674e\u56db | 1970-01-01 |\n| 3 | \u738b\u4e94 | 1980-01-01 |\n| 4 | \u8d75\u516d | 1990-01-01 |\n+------+--------+------------+\n4 rows in set (Elapsed: 00:00:00.01)\n<\/code><\/pre>\n\n\n\n\u5206\u522b\u8ba1\u7b97\uff1a\u5411\u524d\u65e0\u8fb9\u754c\uff0c\u5411\u540e5\u300110\u300120\u5e74\u751f\u65e5\u5dee\u8ddd\u7684\u7684\u6570\u636e\u3002<\/p>\n\n\n\n gbase> select t3.*,sum(id)over(order by birth range between unbounded preceding and interval 5 year following) v from t3;\n+------+--------+------------+------+\n| id | name | birth | v |\n+------+--------+------------+------+\n| 1 | \u5f20\u4e09 | 1960-01-01 | 1 |\n| 2 | \u674e\u56db | 1970-01-01 | 3 |\n| 3 | \u738b\u4e94 | 1980-01-01 | 6 |\n| 4 | \u8d75\u516d | 1990-01-01 | 10 |\n+------+--------+------------+------+\n4 rows in set (Elapsed: 00:00:00.02)\n\ngbase> select t3.*,sum(id)over(order by birth range between unbounded preceding and interval 10 year following) v from t3;\n+------+--------+------------+------+\n| id | name | birth | v |\n+------+--------+------------+------+\n| 1 | \u5f20\u4e09 | 1960-01-01 | 3 |\n| 2 | \u674e\u56db | 1970-01-01 | 6 |\n| 3 | \u738b\u4e94 | 1980-01-01 | 10 |\n| 4 | \u8d75\u516d | 1990-01-01 | 10 |\n+------+--------+------------+------+\n4 rows in set (Elapsed: 00:00:00.02)\n\ngbase> select t3.*,sum(id)over(order by birth range between unbounded preceding and interval 20 year following) v from t3;\n+------+--------+------------+------+\n| id | name | birth | v |\n+------+--------+------------+------+\n| 1 | \u5f20\u4e09 | 1960-01-01 | 6 |\n| 2 | \u674e\u56db | 1970-01-01 | 10 |\n| 3 | \u738b\u4e94 | 1980-01-01 | 10 |\n| 4 | \u8d75\u516d | 1990-01-01 | 10 |\n+------+--------+------------+------+\n4 rows in set (Elapsed: 00:00:00.05)\n<\/code><\/pre>","protected":false},"excerpt":{"rendered":"\u5357\u5927\u901a\u7528GBase 8a \u652f\u6301\u5f00\u7a97\u5b50\u53e5rows\u548crange, \u672c\u6587\u4ecb\u7ecd\u5f00\u7a97\u5b50\u53e5\u7684\u4f7f\u7528\u65b9\u6cd5\u3002<\/p>","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[36,77],"class_list":["post-6667","post","type-post","status-publish","format-standard","hentry","category-gbase8a","tag-36","tag-olap"],"_links":{"self":[{"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/posts\/6667","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/comments?post=6667"}],"version-history":[{"count":50,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/posts\/6667\/revisions"}],"predecessor-version":[{"id":11802,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/posts\/6667\/revisions\/11802"}],"wp:attachment":[{"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/media?parent=6667"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/categories?post=6667"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/tags?post=6667"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
| | | | |
| |
| |