\u65f6\u95f4\u533a\u95f4\u805a\u5408<\/strong><\/div>\n
\u6309\u7167\u65f6\u95f4\u533a\u95f4\u805a\u5408\u5c31\u662f\u65f6\u95f4\u533a\u95f4\u7684\u4e00\u4e2a\u6ed1\u52a8\u7a97\u53e3\uff0c\u6bd4\u5982\u4e0b\u9762\u6848\u4f8b 1 \u5c0f\u65f6\u7684\u533a\u95f4\uff0c\u6700\u65b0\u8f93\u51fa\u7684\u4e00\u6761\u6570\u636e\u7684 sum \u805a\u5408\u7ed3\u679c\u5c31\u662f\u6700\u8fd1\u4e00\u5c0f\u65f6\u6570\u636e\u7684 amount \u4e4b\u548c\u3002<\/p>\n
CREATE TABLE source_table (\r\n order_id BIGINT,\r\n product BIGINT,\r\n amount BIGINT,\r\n order_time as cast(CURRENT_TIMESTAMP as TIMESTAMP(3)),\r\n WATERMARK FOR order_time AS order_time - INTERVAL '0.001' SECOND\r\n) WITH (\r\n 'connector' = 'datagen',\r\n 'rows-per-second' = '1',\r\n 'fields.order_id.min' = '1',\r\n 'fields.order_id.max' = '2',\r\n 'fields.amount.min' = '1',\r\n 'fields.amount.max' = '10',\r\n 'fields.product.min' = '1',\r\n 'fields.product.max' = '2'\r\n);\r\n\r\nCREATE TABLE sink_table (\r\n product BIGINT,\r\n order_time TIMESTAMP(3),\r\n amount BIGINT,\r\n one_hour_prod_amount_sum BIGINT\r\n) WITH (\r\n 'connector' = 'print'\r\n);\r\n\r\nINSERT INTO sink_table\r\nSELECT product, order_time, amount,\r\n SUM(amount) OVER (\r\n PARTITION BY product\r\n ORDER BY order_time\r\n -- \u6807\u8bc6\u7edf\u8ba1\u8303\u56f4\u662f\u4e00\u4e2a product \u7684\u6700\u8fd1 1 \u5c0f\u65f6\u7684\u6570\u636e\r\n RANGE BETWEEN INTERVAL '1' HOUR PRECEDING AND CURRENT ROW\r\n ) AS one_hour_prod_amount_sum\r\nFROM source_table<\/pre>\n
\u7ed3\u679c\u5982\u4e0b\uff1a<\/p>\n
+I[2, 2021-12-24T22:08:26.583, 7, 73]\r\n+I[2, 2021-12-24T22:08:27.583, 7, 80]\r\n+I[2, 2021-12-24T22:08:28.583, 4, 84]\r\n+I[2, 2021-12-24T22:08:29.584, 7, 91]\r\n+I[2, 2021-12-24T22:08:30.583, 8, 99]\r\n+I[1, 2021-12-24T22:08:31.583, 9, 138]\r\n+I[2, 2021-12-24T22:08:32.584, 6, 105]\r\n+I[1, 2021-12-24T22:08:33.584, 7, 145]<\/pre>\n
\u884c\u6570\u805a\u5408<\/strong><\/div>\n
\u6309\u7167\u884c\u6570\u805a\u5408\u5c31\u662f\u6570\u636e\u884c\u6570\u7684\u4e00\u4e2a\u6ed1\u52a8\u7a97\u53e3\uff0c\u6bd4\u5982\u4e0b\u9762\u6848\u4f8b\uff0c\u6700\u65b0\u8f93\u51fa\u7684\u4e00\u6761\u6570\u636e\u7684 sum \u805a\u5408\u7ed3\u679c\u5c31\u662f\u6700\u8fd1 5 \u884c\u6570\u636e\u7684 amount \u4e4b\u548c\u3002<\/p>\n
CREATE TABLE source_table (\r\n order_id BIGINT,\r\n product BIGINT,\r\n amount BIGINT,\r\n order_time as cast(CURRENT_TIMESTAMP as TIMESTAMP(3)),\r\n WATERMARK FOR order_time AS order_time - INTERVAL '0.001' SECOND\r\n) WITH (\r\n 'connector' = 'datagen',\r\n 'rows-per-second' = '1',\r\n 'fields.order_id.min' = '1',\r\n 'fields.order_id.max' = '2',\r\n 'fields.amount.min' = '1',\r\n 'fields.amount.max' = '2',\r\n 'fields.product.min' = '1',\r\n 'fields.product.max' = '2'\r\n);\r\n\r\nCREATE TABLE sink_table (\r\n product BIGINT,\r\n order_time TIMESTAMP(3),\r\n amount BIGINT,\r\n one_hour_prod_amount_sum BIGINT\r\n) WITH (\r\n 'connector' = 'print'\r\n);\r\n\r\nINSERT INTO sink_table\r\nSELECT product, order_time, amount,\r\n SUM(amount) OVER (\r\n PARTITION BY product\r\n ORDER BY order_time\r\n -- \u6807\u8bc6\u7edf\u8ba1\u8303\u56f4\u662f\u4e00\u4e2a product \u7684\u6700\u8fd1 5 \u884c\u6570\u636e\r\n ROWS BETWEEN 5 PRECEDING AND CURRENT ROW\r\n ) AS one_hour_prod_amount_sum\r\nFROM source_table<\/pre>\n
\u9884\u8dd1\u7ed3\u679c\u5982\u4e0b\uff1a<\/p>\n
+I[2, 2021-12-24T22:18:19.147, 1, 9]\r\n+I[1, 2021-12-24T22:18:20.147, 2, 11]\r\n+I[1, 2021-12-24T22:18:21.147, 2, 12]\r\n+I[1, 2021-12-24T22:18:22.147, 2, 12]\r\n+I[1, 2021-12-24T22:18:23.148, 2, 12]\r\n+I[1, 2021-12-24T22:18:24.147, 1, 11]\r\n+I[1, 2021-12-24T22:18:25.146, 1, 10]\r\n+I[1, 2021-12-24T22:18:26.147, 1, 9]\r\n+I[2, 2021-12-24T22:18:27.145, 2, 11]\r\n+I[2, 2021-12-24T22:18:28.148, 1, 10]\r\n+I[2, 2021-12-24T22:18:29.145, 2, 10]<\/pre>\n
\u5f53\u7136\uff0c\u5982\u679c\u4f60\u5728\u4e00\u4e2a SELECT \u4e2d\u6709\u591a\u4e2a\u805a\u5408\u7a97\u53e3\u7684\u805a\u5408\u65b9\u5f0f\uff0cFlink SQL \u652f\u6301\u4e86\u4e00\u79cd\u7b80\u5316\u5199\u6cd5\uff0c\u5982\u4e0b\u6848\u4f8b\uff1a<\/p>\n
SELECT order_id, order_time, amount,\r\n SUM(amount) OVER w AS sum_amount,\r\n AVG(amount) OVER w AS avg_amount\r\nFROM Orders\r\n-- \u4f7f\u7528\u4e0b\u9762\u5b50\u53e5\uff0c\u5b9a\u4e49 Over Window\r\nWINDOW w AS (\r\n PARTITION BY product\r\n ORDER BY order_time\r\n RANGE BETWEEN INTERVAL '1' HOUR PRECEDING AND CURRENT ROW)<\/pre>\n","protected":false},"excerpt":{"rendered":"
\u5e94\u7528\u573a\u666f\uff1a\u8ba1\u7b97\u6700\u8fd1\u4e00\u6bb5\u6ed1\u52a8\u7a97\u53e3\u7684\u805a\u5408\u7ed3\u679c\u6570\u636e\u3002 \u5b9e\u9645\u6848\u4f8b\uff1a\u67e5\u8be2\u6bcf\u4e2a\u4ea7\u54c1\u6700\u8fd1\u4e00\u5c0f\u65f6\u8ba2\u5355\u7684\u91d1\u989d\u603b\u548c\uff1a SELECT […]<\/p>\n","protected":false},"author":1920,"featured_media":200375,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[55],"tags":[],"class_list":["post-245287","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-thread"],"acf":[],"_links":{"self":[{"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/posts\/245287","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/users\/1920"}],"replies":[{"embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/comments?post=245287"}],"version-history":[{"count":2,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/posts\/245287\/revisions"}],"predecessor-version":[{"id":245289,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/posts\/245287\/revisions\/245289"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/media\/200375"}],"wp:attachment":[{"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/media?parent=245287"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/categories?post=245287"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/tags?post=245287"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}