{"id":1145,"date":"2021-01-29T13:47:38","date_gmt":"2021-01-29T05:47:38","guid":{"rendered":"https:\/\/nj.transwarp.cn:8180\/?p=1145"},"modified":"2021-01-29T13:47:37","modified_gmt":"2021-01-29T05:47:37","slug":"%e5%86%85%e8%bf%9e%e6%8e%a5%e5%a4%96%e8%bf%9e%e6%8e%a5%e7%94%b1%e4%ba%8e%e5%85%b3%e8%81%94%e5%ad%97%e6%ae%b5%e9%87%8d%e5%a4%8d%e5%80%bc%e5%af%bc%e8%87%b4%e5%b1%80%e9%83%a8%e7%ac%9b%e5%8d%a1%e5%b0%94","status":"publish","type":"post","link":"https:\/\/kbwp.transwarp.cn\/?p=1145","title":{"rendered":"\u5185\u8fde\u63a5\u5916\u8fde\u63a5\u7531\u4e8e\u5173\u8054\u5b57\u6bb5\u91cd\u590d\u503c\u5bfc\u81f4\u5c40\u90e8\u7b1b\u5361\u5c14\u79ef\u8fc7\u7a0b\u89e3\u6790"},"content":{"rendered":"<h3>\u6982\u8981\u63cf\u8ff0<\/h3>\n<hr \/>\n<p>Inceptor\u57fa\u672c\u4f18\u5316\u601d\u8def\u4e2d\uff0c\u6211\u4eec\u5728\u505ajoin\u5173\u8054\u64cd\u4f5c\u7684\u65f6\u5019\uff0c\u5c3d\u91cf\u907f\u514d\u5173\u8054\u5b57\u6bb5\u91cd\u590d\u503c\u3001\u5173\u8054\u8fc7\u7a0b\u4e2d\u51fa\u73b0\u91cd\u590d\u503c\uff0c\u4f1a\u5bfc\u81f4\u7b1b\u5361\u5c14\u79ef\uff0c\u7136\u540etask\u6267\u884c\u8fc7\u6162\u3002<\/p>\n<p>\u5173\u4e8e\u5185\u8fde\u63a5\u548c\u5916\u8fde\u63a5\uff0c\u6211\u4eec\u7684\u7b2c\u4e00\u53cd\u5e94\uff0c\u80af\u5b9a\u4e0d\u4f1a\u4ea7\u751f\u7b1b\u5361\u5c14\u79ef\uff0c\u5de6\u8fde\u63a5\u662f\u4ee5\u5de6\u8868\u4e3a\u51c6\uff0c\u5de6\u8868\u6709m\u6761\u8bb0\u5f55\uff0c\u5219\u7ed3\u679c\u96c6\u662fm\u6761\u8bb0\u5f55\uff0c\u800c\u5b9e\u9645\u60c5\u51b5\uff0c\u6bd4\u60f3\u8c61\u4e2d\u8981\u590d\u6742\u3002<\/p>\n<p>\u9996\u5148\u8bf4\u4e0b\u7ed3\u8bba\uff1a\u8fde\u63a5\u67e5\u8be2\uff0c\u5982\u679con\u6761\u4ef6\u662f\u975e\u552f\u4e00\u5b57\u6bb5\uff0c\u4f1a\u51fa\u73b0\u7b1b\u5361\u5c14\u79ef(\u5c40\u90e8\u7b1b\u5361\u5c14\u79ef)\uff1b\u5982\u679con\u6761\u4ef6\u662f\u8868\u7684\u552f\u4e00\u5b57\u6bb5\uff0c\u5219\u4e0d\u4f1a\u51fa\u73b0\u7b1b\u5361\u5c14\u79ef\u3002<\/p>\n<h3>\u8be6\u7ec6\u8bf4\u660e<\/h3>\n<hr \/>\n<h4>1. \u6d4b\u8bd5\u6570\u636e\u51c6\u5907<\/h4>\n<pre><code class=\"language-sql\">--\u88681\nCREATE TABLE USER01 (id INT ,name STRING ,job_Id INT ,valid INT ) CLUSTERED BY (id) INTO 1 BUCKETS STORED AS ORC_TRANSACTION;\nINSERT INTO USER01 SELECT 1,'\u8bf8\u845b\u4eae',1,1 FROM system.dual;\nINSERT INTO USER01 SELECT 2,'\u5173\u7fbd',2,2 FROM system.dual;\nINSERT INTO USER01 SELECT 3,'\u5f20\u98de',2,2 FROM system.dual;\nINSERT INTO USER01 SELECT 4,'\u5f20\u98de',6,6 FROM system.dual;\nINSERT INTO USER01 SELECT 5,'\u5a01\u4e25',7,7 FROM system.dual;\nINSERT INTO USER01 SELECT null,null,null,null FROM system.dual;\n--\u88682\nCREATE TABLE job (id INT ,name STRING ,valid INT ) CLUSTERED BY (id) INTO 1 BUCKETS STORED AS ORC_TRANSACTION;\nINSERT INTO job SELECT 1,'\u4ea7\u54c1\u90e8',1 FROM system.dual;\nINSERT INTO job SELECT 2,'\u6280\u672f\u90e8',1 FROM system.dual;\nINSERT INTO job SELECT 7,'\u8d22\u52a1\u90e8',7 FROM system.dual;\nINSERT INTO job SELECT 8,'\u4eba\u4e8b\u90e8',8 FROM system.dual;\nINSERT INTO job SELECT NULL,NULL,NULL  FROM system.dual;<\/code><\/pre>\n<p><img decoding=\"async\" src=\"\/\/nj.transwarp.cn:8180\/wp-content\/uploads\/2019\/10\/image-1571624711319.png\" alt=\"file\" \/><\/p>\n<p><img decoding=\"async\" src=\"\/\/nj.transwarp.cn:8180\/wp-content\/uploads\/2019\/10\/image-1571624749747.png\" alt=\"file\" \/><\/p>\n<h4>2. \u5185\u8fde\u63a5\u4ea7\u751f\u7684\u5c40\u90e8\u7b1b\u5361\u5c14\u79ef<\/h4>\n<ul>\n<li>\n<p>\u5982\u679cA\u8868\u6709m(5)\u6761\u8bb0\u5f55\uff0cm1(4)\u6761\u7b26\u5408on\u6761\u4ef6\uff0cB\u8868\u6709n(4)\u6761\u8bb0\u5f55\uff0c\u6709n1(3)\u6761\u7b26\u5408on\u6761\u4ef6\uff0c\u5185\u8fde\u63a5\u552f\u4e00\u5b57\u6bb5\u7ed3\u679c\u4e3a:Max(m1,n1)=4<\/p>\n<\/li>\n<li>\n<p>1,2,2,6,7 \u548c 1,2,7,8\u5bf9\u6bd4,\u4ee5user01\u8868\u4e3a\u4e3b\u8868,\u56e0\u4e3a\u4e3b\u8868\u4e2d\u67094\u6761\u7b26\u5408\u6761\u4ef6\u7684\u8bb0\u5f55(1,2,2,7),\u800cjob\u8868\u67093\u6761\u7b26\u5408\u6761\u4ef6\u7684\u8bb0\u5f55(1,2,7),\u53d6\u4e24\u8005\u4e2d\u7684\u6700\u5927\u7684,\u6240\u4ee5\u4e3a4\u6761<\/p>\n<\/li>\n<\/ul>\n<pre><code class=\"language-sql\">SELECT * FROM USER01 u JOIN job j ON u.JOB_ID=j.ID;<\/code><\/pre>\n<p><img decoding=\"async\" src=\"\/\/nj.transwarp.cn:8180\/wp-content\/uploads\/2019\/10\/image-1571624967812.png\" alt=\"file\" \/><\/p>\n<p><strong>\u7ed3\u8bba\uff1a\u5047\u5982\uff0con\u6761\u4ef6\u662f\u8868\u4e2d\u975e\u552f\u4e00\u5b57\u6bb5\uff0c\u5219\u7ed3\u679c\u96c6\u662f\u4e24\u8868\u5339\u914d\u5230\u7684\u7ed3\u679c\u96c6\u7684\u7b1b\u5361\u5c14\u79ef(\u5c40\u90e8\u7b1b\u5361\u5c14\u79ef) \u3002<\/strong><\/p>\n<h4>3. \u5916\u8fde\u63a5\u4ea7\u751f\u7684\u5c40\u90e8\u7b1b\u5361\u5c14\u79ef<\/h4>\n<ul>\n<li>1,2,2,6,7 \u548c 1,1,7,8\u5bf9\u6bd4,\u4ee5user01\u8868\u4e3a\u4e3b\u8868\uff0c\u56e0\u4e3a\u4e3b\u8868\u4e2d\u67092\u6761\u7b26\u5408\u6761\u4ef6\u7684\u8bb0\u5f55(1,7)\uff0c\u800cjob\u8868\u67093\u6761\u7b26\u5408\u6761\u4ef6\u7684\u8bb0\u5f55(1,1,7),\u53d6\u4e24\u8005\u4e2d\u7684\u6700\u5927\u7684,\u6240\u4ee5\u53d63\u6761,\u7136\u540e\u5728\u52a0\u4e0auser\u8868\u5728job\u8868\u4e2d\u6ca1\u6709\u5339\u914d\u7684\u8bb0\u5f55(6-2=4),\u6240\u4ee5\u6700\u7ec8\u7ed3\u679c\u4e3a3+4=7\u6761<\/li>\n<\/ul>\n<pre><code class=\"language-sql\">SELECT * FROM USER01 u LEFT JOIN job j ON u.VALID=j.VALID;<\/code><\/pre>\n<p><img decoding=\"async\" src=\"\/\/nj.transwarp.cn:8180\/wp-content\/uploads\/2019\/10\/image-1571626775340.png\" alt=\"file\" \/><\/p>\n<p><strong>\u7ed3\u8bba\uff1a\u5de6\u8fde\u63a5\u975e\u552f\u4e00\u5b57\u6bb5\uff0c\u662f\u5c40\u90e8\u7b1b\u5361\u5c14\u79ef<\/strong><\/p>\n<h3>\u6392\u67e5\u601d\u8def<\/h3>\n<hr \/>\n<pre><code class=\"language-sql\">SELECT * FROM USER01 u LEFT JOIN job j ON u.VALID=j.VALID;<\/code><\/pre>\n<ul>\n<li>\u4ee5\u8be5sql\u4e3a\u4f8b\uff0c\u4e24\u5f20\u8868\u662f\u6839\u636eVALID\u5b57\u6bb5\u5173\u8054\u7684\uff0c\u503e\u659c\u7684\u8bdd\u80af\u5b9a\u5c31\u662fVALID\u503e\u659c<\/li>\n<li>\u6839\u636ejoin key\u505a\u805a\u5408\u67e5\u8be2\u5e76\u964d\u5e8f\u6392\u5e8f\uff0c\u7edf\u8ba1<\/li>\n<\/ul>\n<pre><code class=\"language-sql\">set mapred.reduce.tasks = 5;\nselect valid,count(*) as num from USER01 group by valid distribute by valid sort by num desc limit 10;\nselect valid,count(*) as num from job group by valid distribute by valid sort by num desc limit 10;<\/code><\/pre>\n<p><strong>\u540c\u4e00\u4e2ajoin key\u7684\u6570\u636e\u91cf\u76f8\u4e58\uff0c\u5982\u679c\u6570\u636e\u91cf\u8fc7\u5927\uff0c\u8ba1\u7b97\u91cf\u5168\u90e8\u5206\u914d\u7ed9\u4e00\u4e2a\u8282\u70b9\u80af\u5b9a\u4f1a\u9020\u6210task\u6267\u884c\u65f6\u95f4\u8fc7\u957f\u3002<\/strong><\/p>\n<h3>\u89e3\u51b3\u65b9\u6848<\/h3>\n<hr \/>\n<ul>\n<li>\u5efa\u8bae\u5728\u5185\u8fde\u63a5\u3001\u5de6\u8fde\u63a5\u7684on\u6761\u4ef6\u4e2d\u52a0\u4e0a\u80fd\u552f\u4e00\u6807\u8bc6\u8868\u4e2d\u7684\u552f\u4e00\u8bb0\u5f55<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>\u6982\u8981\u63cf\u8ff0 Inceptor\u57fa\u672c\u4f18\u5316\u601d\u8def\u4e2d\uff0c\u6211\u4eec\u5728\u505ajoin\u5173\u8054\u64cd\u4f5c\u7684\u65f6\u5019\uff0c\u5c3d\u91cf\u907f\u514d\u5173\u8054\u5b57\u6bb5\u91cd\u590d\u503c\u3001\u5173\u8054\u8fc7\u7a0b\u4e2d\u51fa ..<\/p>\n<div class=\"clear-fix\"><\/div>\n<p><a href=\"https:\/\/kbwp.transwarp.cn\/?p=1145\" title=\"read more...\">Read more<\/a><\/p>\n","protected":false},"author":12,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1145","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"acf":[],"_links":{"self":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts\/1145","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/users\/12"}],"replies":[{"embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1145"}],"version-history":[{"count":3,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts\/1145\/revisions"}],"predecessor-version":[{"id":1156,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts\/1145\/revisions\/1156"}],"wp:attachment":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1145"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1145"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1145"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}