{"id":5743,"date":"2024-10-15T20:01:03","date_gmt":"2024-10-15T12:01:03","guid":{"rendered":"https:\/\/ixyzero.com\/blog\/?p=5743"},"modified":"2024-10-15T20:01:03","modified_gmt":"2024-10-15T12:01:03","slug":"hive-sql%e5%a6%82%e4%bd%95%e6%89%be%e5%87%ba%e6%9c%80%e5%a4%a7%e7%9a%84%e8%bf%9e%e7%bb%ad%e6%93%8d%e4%bd%9c%e5%a4%a9%e6%95%b0","status":"publish","type":"post","link":"https:\/\/ixyzero.com\/blog\/archives\/5743.html","title":{"rendered":"Hive SQL\u5982\u4f55\u627e\u51fa\u6700\u5927\u7684\u8fde\u7eed\u64cd\u4f5c\u5929\u6570"},"content":{"rendered":"\n<p>=Start=<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">\u7f18\u7531\uff1a<\/h4>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p>\u6570\u636e\u8fd0\u8425\u4eba\u5458\u5e38\u5e38\u4f1a\u9700\u8981\u67e5\u627e\u6d3b\u8dc3\u7528\u6237\u540d\u5355\uff0c\u800c\u6d3b\u8dc3\u7528\u6237\u5f88\u591a\u60c5\u51b5\u4e0b\u88ab\u5b9a\u4e49\u4e3a\u8fde\u7eed\u5728\u7ebfn\u5929\u53ca\u4ee5\u4e0a\u7684\u7528\u6237\u3002\u4e00\u65b9\u9762\u6211\u4eec\u53ef\u4ee5\u6839\u636en\u7684\u503c\u76f4\u63a5\u8fdb\u884c\u7b5b\u9009\uff1b\u66f4\u5177\u4e00\u822c\u6027\u5730\uff0c\u5c31\u8981\u6c42\u6211\u4eec\u53bb\u6c42\u53d6\u6bcf\u4e2a\u7528\u6237\u67d0\u6bb5\u65f6\u95f4\u5185\u7684\u6700\u5927\u8fde\u7eed\u5728\u7ebf\u5929\u6570\u4e86\u3002<\/p>\n\n\n\n<p>SQL\u6c42\u8fde\u7eed\u5728\u7ebf\u5929\u6570\u662f\u4e00\u4e2a\u975e\u5e38\u7ecf\u5178\u7684\u95ee\u9898\uff0c\u8be5\u95ee\u9898\u5728\u4e0d\u8003\u8651\u8ba1\u7b97\u6210\u672c\u4e0b\u6709\u975e\u5e38\u591a\u7684\u89e3\u6cd5\u3002\u8be5\u95ee\u9898\u4e5f\u662f\u6211\u5728\u9762\u8bd5\u5b9e\u4e60\u751f\u65f6\u6700\u559c\u6b22\u6df1\u5165\u95ee\u7684\u4e00\u4e2a\u95ee\u9898\uff0c\u5728\u5f15\u5bfc\u4e00\u4e2a\u5019\u9009\u4eba\u53bb\u5b8c\u6210\u8fd9\u4e2a\u95ee\u9898\u7684\u8fc7\u7a0b\u4e2d\u53ef\u4ee5\u770b\u51fa\u5176\u5bf9SQL\u7684\u7406\u89e3\u6df1\u5ea6\u4ee5\u53ca\u5176\u601d\u7ef4\u662f\u5426\u7075\u654f\u3002<\/p>\n<\/blockquote>\n\n\n\n<p><strong>\u505a\u5f02\u5e38\u5206\u6790\u65f6\u9700\u8981\u5bf9\u4e00\u4e9b\u7279\u6b8a\u4eba\u7fa4\u505a\u7b5b\u9009<\/strong>\uff0c\u5176\u4e2d\u6709\u4e00\u7c7b\u5c31\u662f\u8fde\u7eed\u64cd\u4f5c\u65e5\u671f\u5f88\u957f\uff08\u6bd4\u5982\u8d85\u8fc710\u5929\uff09\u7684\u4eba\u5458\uff0c\u8981\u4e48\u662f\u975e\u5e38\u70ed\u7231\u5de5\u4f5c\uff0c\u8981\u4e48\u662f\u53ef\u80fd\u6709\u70b9\u5f02\u5e38\u4e86\uff08\u6bd4\u5982\u5199\u7a0b\u5e8f\/\u811a\u672c\u505a\u64cd\u4f5c\u3001\u8d26\u53f7\u5171\u7528\u3001\u8d26\u53f7\u88ab\u76d7\u3001\u2026\u2026\uff09\uff0c<strong>\u901a\u8fc7Hive SQL\u627e\u51fa\u8fde\u7eed\u64cd\u4f5c\u5929\u6570\u8d85\u957f\u7684\u4e00\u6279\u8d26\u53f7\u8fdb\u884c\u62bd\u6837\u5206\u6790\u662f\u4e00\u4e2a\u5165\u624b\u70b9<\/strong>\u3002\u9664\u4e86\u8fde\u7eed\u64cd\u4f5c\u5929\u6570\u8f83\u5927\u4e4b\u5916\uff0c\u8fd8\u6709\u64cd\u4f5c\u91cf\u9661\u589e\u7684\u60c5\u51b5\u4e5f\u9700\u8981\u53ca\u65f6\u53d1\u73b0\u548c\u5173\u6ce8\uff0c\u6bd4\u5982\uff1a<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Hive SQL\u5982\u4f55\u627e\u51fa\u6700\u5927\u7684\u8fde\u7eed\u64cd\u4f5c\u5929\u6570\uff1f<\/li>\n\n\n\n<li>Hive SQL\u5982\u4f55\u627e\u51fa\u8fde\u7eed\u65e5\u671f\u6570\u636e\u4e4b\u95f4\u7684\u8f83\u5927\u6ce2\u52a8\uff1f<\/li>\n\n\n\n<li>Hive SQL\u5982\u4f55\u627e\u51fa\u6bd4\u524d7\u5929\u5e73\u5747\u503c\u8981\u9ad8\u7684\u6570\u636e\uff1f<\/li>\n<\/ol>\n\n\n\n<h4 class=\"wp-block-heading\">\u6b63\u6587\uff1a<\/h4>\n\n\n\n<h5 class=\"wp-block-heading\">\u53c2\u8003\u89e3\u7b54\uff1a<\/h5>\n\n\n\n<pre class=\"wp-block-code\"><code>-- \u539f\u59cb\u8868\u6570\u636e\u6837\u4f8b\nselect\nuser_name, date, cnt\nfrom\nt1\n;\n\nu0001    20241010    11\nu0001    20241011    21\nu0001    20241012    13\nu0001    20241014    49\nu0001    20241015    5\nu0001    20241017    26\nu0001    20241018    27\nu0001    20241019    83\nu0001    20241020    29\nu0002    20241020    1\nu0002    20241022    1<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>-- \u7edf\u8ba1\u5404\u8d26\u53f7\u7684\u8fde\u7eed\u64cd\u4f5c\u5929\u6570\nSELECT\nuser_name\n,cnt1 as consecutive_days\n,date_min as date_start\n,date_max as date_end\n,cnt_max, cnt_avg ,cnt_p90\nFROM\n(SELECT\n*\n,row_number() over(partition by user_name order by cnt1 desc) as rk\nFROM\n    (SELECT\n    user_name, day_diff\n    ,count(1) as cnt1\n    ,min(date) as date_min\n    ,max(date) as date_max\n    ,max(cnt) as cnt_max\n    ,avg(cnt) as cnt_avg\n    ,percentile_approx(cnt,0.90) as cnt_p90\n    FROM\n        (SELECT\n        user_name, date\n        --\u56e0\u4e3a\u8fd9\u91cc date \u5b57\u6bb5\u662f yyyymmdd \u683c\u5f0f\uff0c\u800c\u975e yyyy-mm-dd \u683c\u5f0f\u7684\uff0c\u56e0\u6b64\u5728\u7ed9 date_sub \u5904\u7406\u4e4b\u524d\u9700\u8981\u5148\u683c\u5f0f\u5316\n        ,date_sub(concat(substr(date,1,4),'-',substr(date,5,2),'-',substr(date,7,2)),rn) as day_diff\n        ,cnt\n        FROM\n            (SELECT\n            user_name, date\n            ,row_number() over(partition by user_name order by date) as rn\n            ,cnt\n            FROM\n            t1\n            GROUP BY user_name, date\n            )m1 --\u5148\u5bf9\u5404\u8d26\u53f7\u57fa\u4e8e\u64cd\u4f5c\u65e5\u671f\u8fdb\u884c\u6392\u5e8f\u5e76\u6dfb\u52a0\u5e8f\u53f7\n        )m2 --\u518d\u501f\u52a9 date_sub \u51fd\u6570\u7528\u5f53\u524d\u7684\u65e5\u671f\u548c\u5e8f\u53f7\u8ba1\u7b97\u51fa\u4e00\u4e2a\u201c\u57fa\u51c6\u201d\u65e5\u671f\n        GROUP BY user_name, day_diff\n    )m3 --\u5bf9\u540c\u4e00\u201c\u57fa\u51c6\u201d\u65e5\u671f\u7684\u6570\u636e(user_name,day_diff)\u7edf\u8ba1\u51fa\u6b21\u6570\u548c\u8d77\/\u6b62\u65e5\u671f\uff0c\u987a\u5e26\u7edf\u8ba1\u51fa\u4e00\u4e9b\u6570\u503c\u5217\u7684\u6700\u5927\u503c\/\u5e73\u5747\u503c\/\u767e\u5206\u4f4d\u6570\u4fe1\u606f\n)m4 --\u6309\u7167\u8fde\u7eed\u6b21\u6570\u8fdb\u884c\u6392\u5e8f\u5e76\u6dfb\u52a0\u5e8f\u53f7\uff0c\u65b9\u4fbf\u540e\u9762\u8fdb\u884c\u8fc7\u6ee4\/\u7edf\u8ba1\nWHERE\ncnt1 &gt; 8\n-- or rk = 1<\/code><\/pre>\n\n\n\n<h5 class=\"wp-block-heading\">\u53c2\u8003\u94fe\u63a5\uff1a<\/h5>\n\n\n\n<p>Hive\u8ba1\u7b97\u6700\u5927\u8fde\u7eed\u767b\u9646\u5929\u6570<br><a href=\"https:\/\/www.cnblogs.com\/data-magnifier\/p\/15506609.html\">https:\/\/www.cnblogs.com\/data-magnifier\/p\/15506609.html<\/a><\/p>\n\n\n\n<p>hive \u65f6\u95f4\u6700\u5927\u503c hive\u8fde\u7eed\u6700\u5927\u5929\u6570<br><a href=\"https:\/\/blog.51cto.com\/u_16213658\/7128503\">https:\/\/blog.51cto.com\/u_16213658\/7128503<\/a><\/p>\n\n\n\n<p>Hive_HQL_\u590d\u6742SQL_\u8fde\u7eed\u53d1\u5355\u5929\u6570<br><a href=\"https:\/\/blog.csdn.net\/u010003835\/article\/details\/106739031\">https:\/\/blog.csdn.net\/u010003835\/article\/details\/106739031<\/a><br><a href=\"https:\/\/blog.csdn.net\/Adrian_Wang\/article\/details\/89791948\">https:\/\/blog.csdn.net\/Adrian_Wang\/article\/details\/89791948<\/a><\/p>\n\n\n\n<p>HIVE_HIVE\u51fd\u6570_\u7a97\u53e3\u51fd\u6570_LAG()\/LEAD() \u8be6\u89e3<br><a href=\"https:\/\/blog.csdn.net\/u010003835\/article\/details\/106739353\">https:\/\/blog.csdn.net\/u010003835\/article\/details\/106739353<\/a><\/p>\n\n\n\n<p>mysql\/hive\u6c42\u6700\u5927\u8fde\u7eed\u5929\u6570(\u95f4\u96941\u5929\u4e5f\u7b97\u8fde\u7eed)<br><a href=\"https:\/\/blog.csdn.net\/weixin_42430074\/article\/details\/121967436\">https:\/\/blog.csdn.net\/weixin_42430074\/article\/details\/121967436<\/a><\/p>\n\n\n\n<p>Hive\u5206\u6790\u7a97\u53e3\u51fd\u6570(\u56db) LAG,LEAD,FIRST_VALUE,LAST_VALUE<br><a href=\"http:\/\/lxw1234.com\/archives\/2015\/04\/190.htm\">http:\/\/lxw1234.com\/archives\/2015\/04\/190.htm<\/a><\/p>\n\n\n\n<p>\u6807\u7b7e\uff1ahive\u5206\u6790\u51fd\u6570<br><a href=\"http:\/\/lxw1234.com\/archives\/tag\/hive-window-functions\">http:\/\/lxw1234.com\/archives\/tag\/hive-window-functions<\/a><\/p>\n\n\n\n<p>Hive \u5206\u6790\u51fd\u6570lead\u3001lag\u5b9e\u4f8b\u5e94\u7528<br><a href=\"https:\/\/developer.aliyun.com\/article\/903855\">https:\/\/developer.aliyun.com\/article\/903855<\/a><\/p>\n\n\n\n<p>Hive \u5206\u6790\u51fd\u6570lead\u3001lag\u5b9e\u4f8b\u5e94\u7528<br><a href=\"https:\/\/blog.csdn.net\/kent7306\/article\/details\/50441967\">https:\/\/blog.csdn.net\/kent7306\/article\/details\/50441967<\/a><\/p>\n\n\n\n<p>hive\u5f00\u7a97\u51fd\u6570-lag\u548clead\u51fd\u6570<br><a href=\"https:\/\/cloud.tencent.com\/developer\/article\/2377588\">https:\/\/cloud.tencent.com\/developer\/article\/2377588<\/a><\/p>\n\n\n\n<p>\u901a\u4e49\u5343\u95ee<br><a href=\"https:\/\/tongyi.aliyun.com\/qianwen\/\">https:\/\/tongyi.aliyun.com\/qianwen\/<\/a><\/p>\n\n\n\n<p>=END=<\/p>\n","protected":false},"excerpt":{"rendered":"<p>=Start= \u7f18\u7531\uff1a \u6570\u636e\u8fd0\u8425\u4eba\u5458\u5e38\u5e38\u4f1a\u9700\u8981\u67e5\u627e\u6d3b\u8dc3\u7528\u6237\u540d\u5355\uff0c\u800c\u6d3b\u8dc3\u7528\u6237\u5f88\u591a\u60c5\u51b5\u4e0b\u88ab\u5b9a\u4e49\u4e3a\u8fde\u7eed\u5728\u7ebfn\u5929\u53ca\u4ee5\u4e0a [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,23,7],"tags":[2345,1261,659,2342,2343,1262,2344,2348,2341,2347,1373,2346,2100],"class_list":["post-5743","post","type-post","status-publish","format-standard","hentry","category-database","category-knowledgebase-2","category-programing","tag-avg","tag-hive","tag-lag","tag-lead","tag-percentile","tag-sql","tag-2344","tag-2348","tag-2341","tag-2347","tag-1373","tag-2346","tag-2100"],"views":1584,"_links":{"self":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/5743","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/comments?post=5743"}],"version-history":[{"count":1,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/5743\/revisions"}],"predecessor-version":[{"id":5744,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/5743\/revisions\/5744"}],"wp:attachment":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/media?parent=5743"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/categories?post=5743"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/tags?post=5743"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}