{"id":5808,"date":"2024-12-14T07:42:00","date_gmt":"2024-12-13T23:42:00","guid":{"rendered":"https:\/\/ixyzero.com\/blog\/?p=5808"},"modified":"2024-12-17T10:43:00","modified_gmt":"2024-12-17T02:43:00","slug":"%e5%a6%82%e4%bd%95%e7%94%9f%e6%88%90%e4%b8%80%e5%bc%a0%ef%bc%88%e6%9c%89%e6%b3%95%e5%ae%9a%e8%8a%82%e5%81%87%e6%97%a5%e4%bf%a1%e6%81%af%e7%9a%84%ef%bc%89%e6%97%a5%e6%9c%9f%e7%bb%b4%e5%ba%a6%e8%a1%a8","status":"publish","type":"post","link":"https:\/\/ixyzero.com\/blog\/archives\/5808.html","title":{"rendered":"\u5982\u4f55\u751f\u6210\u4e00\u5f20\uff08\u6709\u6cd5\u5b9a\u8282\u5047\u65e5\u4fe1\u606f\u7684\uff09\u65e5\u671f\u7ef4\u5ea6\u8868\uff1f"},"content":{"rendered":"\n<p>=Start=<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">\u7f18\u7531\uff1a<\/h4>\n\n\n\n<p>\u5728\u4f7f\u7528\u5185\u90e8\u7684\u65e5\u671f\u7ef4\u5ea6\u8868\u65f6\u7a81\u7136\u60f3\u5230\u7684\u4e00\u4e2a\u95ee\u9898\uff0c\u611f\u89c9\u53ef\u4ee5\u501f\u52a9\u901a\u4e49\u5343\u95ee\u8fd9\u79cd\u5bf9\u8bdd\u5f0f\u7684\u5927\u8bed\u8a00\u6a21\u578b\u6765\u5e2e\u5fd9\u5feb\u901f\u5b9e\u73b0\u3002<br>\u53e6\u5916\u60f3\u8bf4\u7684\u5c31\u662f\uff0c<strong>\u56e0\u4e3a\u56fd\u5185\u7684\u8282\u5047\u65e5\u5b89\u6392\u6d89\u53ca\u5230\u8c03\u4f11\uff08\u6bcf\u5e74\u90fd\u8fd8\u4e0d\u5b8c\u5168\u4e00\u6837\uff09\uff0c\u6240\u4ee5\u4e00\u822c\u53ea\u80fd\u7b49\u5f53\u5e74\u768411\u6708\u4efd\u5de6\u53f3\u624d\u4f1a\u51fa\u7b2c2\u5e74\u7684\u653e\u5047\u5b89\u6392\u901a\u77e5\u4e4b\u540e\u624d\u80fd\u57fa\u672c\u786e\u5b9a<\/strong>\uff08\u5982\u679c\u6709\u4efb\u4f55\u66f4\u65b0\u6216\u53d8\u5316\uff0c\u8bf7\u4ee5\u6700\u65b0\u7684\u5b98\u65b9\u516c\u544a\u4e3a\u51c6\uff09\u3002<\/p>\n\n\n\n<p>\u8fd9\u79cd\u64cd\u4f5c\u540e\u9762\u4e5f\u53ef\u4ee5\u501f\u52a9LLM agent(s)\u6765\u5b9e\u73b0\u5b8c\u6574\u7684\u81ea\u52a8\u5316\u6d41\u7a0b\u3002<\/p>\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<p>question<br>\u8bf7\u95ee\u5982\u4f55\u83b7\u53d62025\u5e74\u4e2d\u56fd\u5927\u9646\u7684\u6cd5\u5b9a\u8282\u5047\u65e5\u4fe1\u606f\uff1f<\/p>\n\n\n\n<p>answer<br>2025\u5e74\u4e2d\u56fd\u5927\u9646\u7684\u6cd5\u5b9a\u8282\u5047\u65e5\u5b89\u6392\u5982\u4e0b\uff0c\u6839\u636e\u56fd\u52a1\u9662\u529e\u516c\u5385\u53d1\u5e03\u7684\u901a\u77e5\u6574\u7406\uff1a<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>\u5143\u65e6<\/strong>\uff1a1\u67081\u65e5\uff08\u5468\u4e09\uff09\u653e\u50471\u5929\uff0c\u4e0d\u8c03\u4f11\u3002<\/li>\n\n\n\n<li><strong>\u6625\u8282<\/strong>\uff1a\u4ece1\u670828\u65e5\uff08\u519c\u5386\u9664\u5915\u3001\u5468\u4e8c\uff09\u81f32\u67084\u65e5\uff08\u519c\u5386\u6b63\u6708\u521d\u4e03\u3001\u5468\u4e8c\uff09\u653e\u5047\u8c03\u4f11\uff0c\u51718\u5929\u3002\u5176\u4e2d1\u670826\u65e5\uff08\u5468\u65e5\uff09\u30012\u67088\u65e5\uff08\u5468\u516d\uff09\u9700\u8981\u4e0a\u73ed\u3002<\/li>\n\n\n\n<li><strong>\u6e05\u660e\u8282<\/strong>\uff1a4\u67084\u65e5\uff08\u5468\u4e94\uff09\u81f36\u65e5\uff08\u5468\u65e5\uff09\u653e\u5047\uff0c\u51713\u5929\u3002<\/li>\n\n\n\n<li><strong>\u52b3\u52a8\u8282<\/strong>\uff1a5\u67081\u65e5\uff08\u5468\u56db\uff09\u81f35\u65e5\uff08\u5468\u4e00\uff09\u653e\u5047\u8c03\u4f11\uff0c\u51715\u5929\u3002\u5176\u4e2d4\u670827\u65e5\uff08\u5468\u65e5\uff09\u9700\u8981\u4e0a\u73ed\u3002<\/li>\n\n\n\n<li><strong>\u7aef\u5348\u8282<\/strong>\uff1a5\u670831\u65e5\uff08\u5468\u516d\uff09\u81f36\u67082\u65e5\uff08\u5468\u4e00\uff09\u653e\u5047\uff0c\u51713\u5929\u3002<\/li>\n\n\n\n<li><strong>\u56fd\u5e86\u8282\u4e0e\u4e2d\u79cb\u8282\u5408\u5e76<\/strong>\uff1a10\u67081\u65e5\uff08\u5468\u4e09\uff09\u81f38\u65e5\uff08\u5468\u4e09\uff09\u653e\u5047\u8c03\u4f11\uff0c\u51718\u5929\u3002\u5176\u4e2d9\u670828\u65e5\uff08\u5468\u65e5\uff09\u300110\u670811\u65e5\uff08\u5468\u516d\uff09\u9700\u8981\u4e0a\u73ed\u3002<\/li>\n<\/ol>\n\n\n\n<p>\u4ee5\u4e0a\u4fe1\u606f\u662f\u57fa\u4e8e\u5f53\u524d\u5df2\u53d1\u5e03\u7684\u5b98\u65b9\u6587\u4ef6\u3002\u5982\u679c\u6709\u4efb\u4f55\u66f4\u65b0\u6216\u53d8\u5316\uff0c\u8bf7\u4ee5\u6700\u65b0\u7684\u5b98\u65b9\u516c\u544a\u4e3a\u51c6\u3002\u5728\u8fd9\u4e9b\u8282\u5047\u65e5\u671f\u95f4\uff0c\u5404\u5730\u533a\u548c\u90e8\u95e8\u4f1a\u59a5\u5584\u5b89\u6392\u503c\u73ed\u548c\u5176\u4ed6\u5fc5\u8981\u7684\u5de5\u4f5c\uff0c\u786e\u4fdd\u516c\u5171\u5b89\u5168\u548c\u670d\u52a1\u7684\u8fde\u7eed\u6027\u3002<\/p>\n\n\n\n<p>question2<br>\u6839\u636e\u4e0a\u9762\u7684\u5185\u5bb9\uff0c\u8bf7\u95ee\u53ef\u4ee5<strong>\u7528\u4e0b\u9762\u7684\u5f62\u5f0f\u5e2e\u5fd9\u6574\u7406\u51fa<\/strong>2025\u5e74\u4e2d\u56fd\u5927\u9646\u7684\u6cd5\u5b9a\u8282\u5047\u65e5\u5217\u8868\u5417\uff1f<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>YYYY-MM-DD, YYYYMMDD, \u661f\u671f\u51e0, \u662f\u5426\u6cd5\u5b9a\u8282\u5047\u65e5\n2025-01-01, 20250101, 3, 1<\/code><\/pre>\n\n\n\n<p>answer2<br>\u6839\u636e\u4e4b\u524d\u63d0\u4f9b\u7684\u4e2d\u56fd\u5927\u96462025\u5e74\u6cd5\u5b9a\u8282\u5047\u65e5\u4fe1\u606f\uff0c\u4ee5\u4e0b\u662f<strong>\u6309\u7167\u60a8\u6307\u5b9a\u683c\u5f0f\u6574\u7406\u7684\u5217\u8868<\/strong>\u3002\u8bf7\u6ce8\u610f\uff0c\u661f\u671f\u51e0\u662f\u4ee51\u8868\u793a\u661f\u671f\u4e00\uff0c7\u8868\u793a\u661f\u671f\u65e5\u3002<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>YYYY-MM-DD, YYYYMMDD, \u661f\u671f\u51e0, \u662f\u5426\u6cd5\u5b9a\u8282\u5047\u65e5\n2025-01-01, 20250101, 3, 1  -- \u5143\u65e6\n\n2025-01-28, 20250128, 2, 1  -- \u6625\u8282\u5047\u671f\u5f00\u59cb\uff08\u9664\u5915\uff09\n2025-01-29, 20250129, 3, 1\n2025-01-30, 20250130, 4, 1\n2025-01-31, 20250131, 5, 1\n2025-02-01, 20250201, 6, 1\n2025-02-02, 20250202, 7, 1\n2025-02-03, 20250203, 1, 1\n2025-02-04, 20250204, 2, 1  -- \u6625\u8282\u5047\u671f\u7ed3\u675f\n\n2025-04-04, 20250404, 5, 1  -- \u6e05\u660e\u8282\u5047\u671f\u5f00\u59cb\n2025-04-05, 20250405, 6, 1\n2025-04-06, 20250406, 7, 1  -- \u6e05\u660e\u8282\u5047\u671f\u7ed3\u675f\n\n2025-05-01, 20250501, 4, 1  -- \u52b3\u52a8\u8282\u5047\u671f\u5f00\u59cb\n2025-05-02, 20250502, 5, 1\n2025-05-03, 20250503, 6, 1\n2025-05-04, 20250504, 7, 1  -- \u52b3\u52a8\u8282\u5047\u671f\u7ed3\u675f\n2025-05-05, 20250505, 1, 1\n\n2025-05-31, 20250531, 6, 1  -- \u7aef\u5348\u8282\u5047\u671f\u5f00\u59cb\n2025-06-01, 20250601, 7, 1\n2025-06-02, 20250602, 1, 1  -- \u7aef\u5348\u8282\u5047\u671f\u7ed3\u675f\n\n2025-10-01, 20251001, 3, 1  -- \u56fd\u5e86\u8282\u4e0e\u4e2d\u79cb\u8282\u5408\u5e76\u5047\u671f\u5f00\u59cb\n2025-10-02, 20251002, 4, 1\n2025-10-03, 20251003, 5, 1\n2025-10-04, 20251004, 6, 1\n2025-10-05, 20251005, 7, 1\n2025-10-06, 20251006, 1, 1\n2025-10-07, 20251007, 2, 1\n2025-10-08, 20251008, 3, 1  -- \u56fd\u5e86\u8282\u4e0e\u4e2d\u79cb\u8282\u5408\u5e76\u5047\u671f\u7ed3\u675f<\/code><\/pre>\n\n\n\n<p>\u8bf7\u6ce8\u610f\uff0c\u8fd9\u91cc\u7684\u661f\u671f\u51e0\u662f\u57fa\u4e8e\u5047\u8bbe2025\u5e74\u7684\u5b9e\u9645\u65e5\u671f\u548c\u661f\u671f\u5339\u914d\u6765\u586b\u5199\u7684\u3002\u5982\u679c\u9700\u8981\u786e\u5207\u7684\u661f\u671f\u51e0\uff0c\u8bf7\u6839\u636e\u5b9e\u9645\u7684\u65e5\u5386\u8fdb\u884c\u6838\u5bf9\u3002\u6b64\u5916\uff0c\u56fd\u5e86\u8282\u548c\u4e2d\u79cb\u8282\u5408\u5e76\u5047\u671f\u7684\u5177\u4f53\u5b89\u6392\u53ef\u80fd\u4f1a\u6709\u53d8\u5316\uff0c\u56e0\u6b64\u5efa\u8bae\u4ee5\u5b98\u65b9\u6700\u7ec8\u516c\u5e03\u7684\u4fe1\u606f\u4e3a\u51c6\u3002<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p>\u7136\u540e\u518d\u501f\u52a9\u4e00\u6bb5SQL\u6765\u751f\u6210\u5b8c\u6574\u76842025\u5e74\u65e5\u671f\u5217\u8868\uff0c\u518d\u548c\u4e0a\u9762\u7684\u8fd9\u90e8\u5206\u5185\u5bb9\u505a left outer join \u8865\u5168\u6cd5\u5b9a\u8282\u5047\u65e5\u7684\u4fe1\u606f\u5373\u53ef\u5b8c\u6210\u4e00\u5f00\u59cb\u7684\u76ee\u6807\u2014\u2014\u201c\u5982\u4f55\u751f\u6210\u4e00\u5f20\uff08\u6709\u6cd5\u5b9a\u8282\u5047\u65e5\u4fe1\u606f\u7684\uff09\u65e5\u671f\u7ef4\u5ea6\u8868\u201d\u3002<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- \u56e0\u4e3a\u5185\u7f6e\u7684 dayofweek \u51fd\u6570\u662f\u4ee5\u661f\u671f\u5929\u4e3a 1 \u5f00\u59cb\u8ba1\u7b97\u7684\u7b2c\u51e0\u5929\uff0c\u6240\u4ee5\u5f53\u524d\u76f4\u63a5\u5728\u5176\u7ed3\u679c\u4e0a\u51cf\u4e00\uff0c\u66f4\u7b26\u5408\u56fd\u5185\u7684\u4e60\u60ef\n-- \u6216\u8005\u4e5f\u53ef\u4ee5\u76f4\u63a5\u4f7f\u7528 weekday \u51fd\u6570\u52a0\u4e00\u7684\u7ed3\u679c\uff0c\u8fd9\u6837\u661f\u671f\u5929\u5c31\u662f 7 \u800c\u4e0d\u662f\u51cf\u4e00\u4e4b\u540e\u7684 0 \u4e86\nwith t as (\n  select '2025-01-01' as dt_start, '2025-12-31' as dt_end\n)\nselect\nidx\n,date_add(to_date(dt_start), idx) as dt\n,date_format(date_add(to_date(dt_start), idx), 'yyyyMMdd') as date\n,dayofweek(date_add(to_date(dt_start), idx))-1 as week_num\n,weekday(date_add(to_date(dt_start), idx))+1 as week_num2 --\u901f\u5ea6\u6709\u70b9\u6162\nfrom t\nlateral view posexplode(split(space(datediff(t.dt_end,t.dt_start)),' ')) pi as idx, empty\n;<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<pre class=\"wp-block-code\"><code>YYYY-MM-DD, YYYYMMDD, day_of_week, is_holiday, remark\n2025-01-01, 20250101, 3, 1, \u5143\u65e6\n\n2025-01-26, 20250126, 7, 0, \u6625\u8282\u5047\u671f\u8c03\u4f11\n2025-01-28, 20250128, 2, 1, \u6625\u8282\uff08\u9664\u5915\uff09\n2025-01-29, 20250129, 3, 1, \u6625\u8282\n2025-01-30, 20250130, 4, 1, \u6625\u8282\n2025-01-31, 20250131, 5, 1, \u6625\u8282\n2025-02-01, 20250201, 6, 1, \u6625\u8282\n2025-02-02, 20250202, 7, 1, \u6625\u8282\n2025-02-03, 20250203, 1, 1, \u6625\u8282\n2025-02-04, 20250204, 2, 1, \u6625\u8282\n2025-02-08, 20250208, 6, 0, \u6625\u8282\u5047\u671f\u8c03\u4f11\n\n2025-04-04, 20250404, 5, 1, \u6e05\u660e\u8282\n2025-04-05, 20250405, 6, 1, \u6e05\u660e\u8282\n2025-04-06, 20250406, 7, 1, \u6e05\u660e\u8282\n\n2025-04-27, 20250427, 7, 0, \u52b3\u52a8\u8282\u5047\u671f\u8c03\u4f11\n2025-05-01, 20250501, 4, 1, \u52b3\u52a8\u8282\n2025-05-02, 20250502, 5, 1, \u52b3\u52a8\u8282\n2025-05-03, 20250503, 6, 1, \u52b3\u52a8\u8282\n2025-05-04, 20250504, 7, 1, \u52b3\u52a8\u8282\n2025-05-05, 20250505, 1, 1, \u52b3\u52a8\u8282\n\n2025-05-31, 20250531, 6, 1, \u7aef\u5348\u8282\n2025-06-01, 20250601, 7, 1, \u7aef\u5348\u8282\n2025-06-02, 20250602, 1, 1, \u7aef\u5348\u8282\n\n2025-09-28, 20250928, 7, 0, \u56fd\u5e86\u8282\u4e0e\u4e2d\u79cb\u8282\u5408\u5e76\u5047\u671f\u8c03\u4f11\n2025-10-01, 20251001, 3, 1, \u56fd\u5e86\u8282\u4e0e\u4e2d\u79cb\u8282\u5408\u5e76\n2025-10-02, 20251002, 4, 1, \u56fd\u5e86\u8282\u4e0e\u4e2d\u79cb\u8282\u5408\u5e76\n2025-10-03, 20251003, 5, 1, \u56fd\u5e86\u8282\u4e0e\u4e2d\u79cb\u8282\u5408\u5e76\n2025-10-04, 20251004, 6, 1, \u56fd\u5e86\u8282\u4e0e\u4e2d\u79cb\u8282\u5408\u5e76\n2025-10-05, 20251005, 7, 1, \u56fd\u5e86\u8282\u4e0e\u4e2d\u79cb\u8282\u5408\u5e76\n2025-10-06, 20251006, 1, 1, \u56fd\u5e86\u8282\u4e0e\u4e2d\u79cb\u8282\u5408\u5e76\n2025-10-07, 20251007, 2, 1, \u56fd\u5e86\u8282\u4e0e\u4e2d\u79cb\u8282\u5408\u5e76\n2025-10-08, 20251008, 3, 1, \u56fd\u5e86\u8282\u4e0e\u4e2d\u79cb\u8282\u5408\u5e76\n2025-10-11, 20251011, 6, 0, \u56fd\u5e86\u8282\u4e0e\u4e2d\u79cb\u8282\u5408\u5e76\u5047\u671f\u8c03\u4f11<\/code><\/pre>\n\n\n\n<h5 class=\"wp-block-heading\">\u53c2\u8003\u94fe\u63a5\uff1a<\/h5>\n\n\n\n<p>datediff \u51fd\u6570<br><a href=\"https:\/\/learn.microsoft.com\/zh-cn\/azure\/databricks\/sql\/language-manual\/functions\/datediff\">https:\/\/learn.microsoft.com\/zh-cn\/azure\/databricks\/sql\/language-manual\/functions\/datediff<\/a><\/p>\n\n\n\n<p>dayofweek \u51fd\u6570<br><a href=\"https:\/\/learn.microsoft.com\/zh-cn\/azure\/databricks\/sql\/language-manual\/functions\/dayofweek\">https:\/\/learn.microsoft.com\/zh-cn\/azure\/databricks\/sql\/language-manual\/functions\/dayofweek<\/a><\/p>\n\n\n\n<p>weekday \u51fd\u6570<br><a href=\"https:\/\/learn.microsoft.com\/zh-cn\/azure\/databricks\/sql\/language-manual\/functions\/weekday\">https:\/\/learn.microsoft.com\/zh-cn\/azure\/databricks\/sql\/language-manual\/functions\/weekday<\/a><\/p>\n\n\n\n<p>\u6625\u82828\u5929\uff0c\u52b3\u52a8\u82825\u5929\u2026\u20262025\u5e74\u6cd5\u5b9a\u8282\u5047\u65e5\u5b89\u6392\u51fa\u7089<br><a href=\"https:\/\/mp.weixin.qq.com\/s?__biz=MzI3MTQ0MzM2OA==&amp;mid=2247508458&amp;idx=1&amp;sn=a3a34ea5adaff5fe741d8601f2862115\">https:\/\/mp.weixin.qq.com\/s?__biz=MzI3MTQ0MzM2OA==&amp;mid=2247508458&amp;idx=1&amp;sn=a3a34ea5adaff5fe741d8601f2862115<\/a><\/p>\n\n\n\n<p>=END=<\/p>\n","protected":false},"excerpt":{"rendered":"<p>=Start= \u7f18\u7531\uff1a \u5728\u4f7f\u7528\u5185\u90e8\u7684\u65e5\u671f\u7ef4\u5ea6\u8868\u65f6\u7a81\u7136\u60f3\u5230\u7684\u4e00\u4e2a\u95ee\u9898\uff0c\u611f\u89c9\u53ef\u4ee5\u501f\u52a9\u901a\u4e49\u5343\u95ee\u8fd9\u79cd\u5bf9\u8bdd\u5f0f\u7684\u5927\u8bed\u8a00\u6a21\u578b [&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],"tags":[2392,2394,1261,2391,1965,1262,19,2393,2395],"class_list":["post-5808","post","type-post","status-publish","format-standard","hentry","category-database","category-knowledgebase-2","tag-dayofweek","tag-dim_date","tag-hive","tag-is_holiday","tag-llm","tag-sql","tag-tips","tag-2393","tag-2395"],"views":1139,"_links":{"self":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/5808","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=5808"}],"version-history":[{"count":4,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/5808\/revisions"}],"predecessor-version":[{"id":5819,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/5808\/revisions\/5819"}],"wp:attachment":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/media?parent=5808"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/categories?post=5808"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/tags?post=5808"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}