{"id":5826,"date":"2024-12-19T07:11:00","date_gmt":"2024-12-18T23:11:00","guid":{"rendered":"https:\/\/ixyzero.com\/blog\/?p=5826"},"modified":"2024-12-18T16:11:57","modified_gmt":"2024-12-18T08:11:57","slug":"%e5%a6%82%e4%bd%95%e6%8f%90%e5%8f%96sql%e8%af%ad%e5%8f%a5%e4%b8%ad%e6%9c%89%e6%9f%a5%e8%af%a2%e6%84%8f%e4%b9%89%e7%9a%84%e6%95%b0%e5%ad%97id%e5%ad%97%e7%ac%a6%e4%b8%b2%ef%bc%9f","status":"publish","type":"post","link":"https:\/\/ixyzero.com\/blog\/archives\/5826.html","title":{"rendered":"\u5982\u4f55\u63d0\u53d6SQL\u8bed\u53e5\u4e2d\u6709\u67e5\u8be2\u610f\u4e49\u7684\u6570\u5b57ID\u5b57\u7b26\u4e32\uff1f"},"content":{"rendered":"\n

=Start=<\/p>\n\n\n\n

\u7f18\u7531\uff1a<\/h4>\n\n\n\n

\u5728\u5206\u6790\u65e5\u5fd7\u7684\u65f6\u5019\u53d1\u73b0\u4e4b\u524d\u8bb0\u5f55\u7684\u90a3\u7bc7\u6587\u7ae0\u7684\u5185\u5bb9\u597d\u50cf\u4e0d\u591f\u4e25\u8c28\uff0c\u53ef\u80fd\u4f1a\u5339\u914d\u5230\u4e00\u4e9b\u975e\u9884\u671f\u7684\u6570\u5b57ID\u5b57\u7b26\u4e32\uff0c\u5bf9\u540e\u9762\u7684\u5206\u6790\u4ea7\u751f\u5e72\u6270\uff0c\u800c\u4e14\u6392\u67e5\u6210\u672c\u8fd8\u633a\u9ad8\uff0c\u6240\u4ee5\u518d\u770b\u770b\u600e\u4e48\u80fd\u66f4\u597d\u7684\u5728\u524d\u671f\u505a\u597d\u8fc7\u6ee4\uff0c\u964d\u4f4e\u540e\u671f\u5904\u7406\u6210\u672c\u3002<\/p>\n\n\n\n

\u6b63\u6587\uff1a<\/h4>\n\n\n\n

\u53c2\u8003\u89e3\u7b54\uff1a<\/h5>\n\n\n\n

\u5148\u8bf4\u7ed3\u8bba\u2014\u2014\u60f3\u5bc4\u5e0c\u671b\u4e8e\u5355\u6b65\u5b8c\u6210\u8fc7\u6ee4\u592a\u8fc7\u56f0\u96be\uff0c\u4e14\u4e0d\u5229\u4e8e\u4f18\u5316\u548c\u66f4\u65b0\uff0c\u6700\u597d\u653e\u5728\u591a\u6b65\u4e2d\u5904\u7406<\/strong>\uff1a<\/p>\n\n\n\n

    \n
  1. \u5148\u505a\u4e00\u4e9b\u7edf\u4e00\u7684\u683c\u5f0f\u5316\u64cd\u4f5c\u2014\u2014\u79fb\u9664\u6ce8\u91ca\uff0c\u5c06\u8fde\u7eed\u7684\u591a\u4e2a\u7a7a\u767d\u7b26\u53f7\u66ff\u6362\u6210\u4e00\u4e2a\u7a7a\u683c\u7b26\u53f7<\/strong>\uff0c\u4f7f\u7528\u7684\u662f regexp_replace \u51fd\u6570\uff1b<\/li>\n\n\n\n
  2. \u5c06\u4e00\u4e9b\u53ef\u80fd\u5f15\u8d77\u9519\u8bef\u63d0\u53d6\u7684\u60c5\u51b5\u8fdb\u884c\u603b\u7ed3\uff0c\u7136\u540e\u5c3d\u53ef\u80fd\u591a\u7684\u5c06\u5927\u90e8\u5206\u4fe1\u606f\u90fd\u63d0\u53d6\u51fa\u6765<\/strong>\uff08\u5728\u5339\u914d\u6570\u5b57\u7684\u6b63\u5219\u8868\u8fbe\u5f0f\u524d\u9762\u6dfb\u52a0 (c1|c2|c3)?<\/code> \uff09\u65b9\u4fbf\u540e\u9762\u505a\u8fc7\u6ee4\uff1b<\/li>\n\n\n\n
  3. \u7528 explode \u51fd\u6570\u5c06\u5339\u914d\u5230\u7684\u5185\u5bb9\u8fdb\u884c\u5207\u5206\uff0c\u65b9\u4fbf\u5bf9\u4e0d\u7b26\u5408\u6761\u4ef6\u7684\u5185\u5bb9\u505a\u8fc7\u6ee4\uff1b<\/li>\n\n\n\n
  4. \uff08\u53ef\u9009\uff09\u7528 collect_set \u51fd\u6570\u5bf9\u8fc7\u6ee4\u7684\u7ed3\u679c\u505a\u805a\u5408\uff0c\u5c06\u7ed3\u679c\u653e\u57281\u884c\u91cc\u9762\uff1b<\/li>\n<\/ol>\n\n\n\n
    with t1 as (\nselect \"select\n1234567 as id_int ,'2345678' as id_str\n,advertiser_name AS _1700007340280\n,(toString((`adID`))) AS _r1700066608896\n,cash_cost \/  1000001 as div_test\n,(sum(coalesce(round(cash_cost\/100000, 2), 0))) AS _sum_1700007340301\nfrom db1.tbl1\nwhere\ndate between '20240101' and '20241201'\nor dt between '2024-01-01' and '2024-12-01'\n-- and comment_id = 9999999\nand condition0 = 123456789\nand condition1 > 3456789\nand condition2 < 9876543\nand condition3 - 222222 > 6666\nand condition4 * 333333 > 666666\nlimit 100001\" as multi_line_var\n)\n\nSELECT\nsize(num_array) as num_cnt\n,num_array\n,num_array2\n,num_array3\n-- ,num_array4\n,array_except(num_array,yyyymmdd_array) as filted_num --\u6392\u9664\u6389\u5339\u914d\u51fa\u6765\u7684yyyymmdd\u65e5\u671f\u683c\u5f0f\u7684\u6570\u5b57\u5b57\u7b26\u4e32\n,num1\n-- ,yyyy_mm_dd_array\n-- ,yyyymmdd_array\n,limit_num\nFROM\n(select multi_line_var\n,array_distinct(regexp_extract_all(regexp_replace(regexp_replace(multi_line_var,'--.+?\\\\n{1,}',''),'\\\\s{1,}',' '),'(\\\\+|-|\\\\*|\/|>|>=|<|<=|limit)? ?\\\\b[1-9]\\\\d{4,}\\\\b',0)) as num_array\n,regexp_extract_all(multi_line_var,'\\\\b[1-9]\\\\d{4,}\\\\b',0) as num_array2\n,regexp_extract_all(regexp_replace(multi_line_var,'\\\\s+limit\\\\s+(\\\\d+)\\\\s*','') ,'[1-9]\\\\d{4,}',0) as num_array3\n-- ,regexp_extract_all(regexp_replace(multi_line_var,'\\\\s+limit\\\\s+(\\\\d+)\\\\s*','') ,'(?<!\\\\+|-|\\\\*|\/|>|>=|<|<=)\\\\s{0,}([1-9]\\\\d{4,})\\\\b',1) as num_array4\n,regexp_extract_all(multi_line_var,'20[1-2][0-9]\\\\-(0[1-9]|1[0-2])\\\\-(0[1-9]|1[0-9]|2[0-9]|3[0-1])',0) as yyyy_mm_dd_array\n,regexp_extract_all(multi_line_var,'20[1-2][0-9](0[1-9]|1[0-2])(0[1-9]|1[0-9]|2[0-9]|3[0-1])',0) as yyyymmdd_array\n,regexp_extract(multi_line_var, '\\\\s+limit\\\\s+(\\\\d+)\\\\s*', 1) as limit_num\nfrom t1\n)x1\nlateral view explode(array_except(num_array,yyyymmdd_array)) num as num1\nWHERE\ntrim(num1) rlike '^\\\\d'<\/code><\/pre>\n\n\n\n
    -- \u4e0b\u9762\u4e3a\u4e86\u770b\u8d77\u6765\u65b9\u4fbf\uff0c\u5c063\u884c\u7ed3\u679c\u6309\u5757\u7684\u65b9\u5f0f\u8fdb\u884c\u5c55\u793a\nnum_cnt\nnum_array\nnum_array2\nnum_array3\nfilted_num\nnum1\nlimit_num\n\n15\n[\" 1234567\",\"2345678\",\"_1700007340280\",\"\/ 1000001\",\"\/100000\",\"_1700007340301\",\"20240101\",\"20241201\",\" 123456789\",\"> 3456789\",\"< 9876543\",\"- 222222\",\"* 333333\",\"> 666666\",\"limit 100001\"]\n[\"1234567\",\"2345678\",\"1000001\",\"100000\",\"20240101\",\"20241201\",\"9999999\",\"123456789\",\"3456789\",\"9876543\",\"222222\",\"333333\",\"666666\",\"100001\"]\n[\"1234567\",\"2345678\",\"1700007340280\",\"1000001\",\"100000\",\"1700007340301\",\"20240101\",\"20241201\",\"9999999\",\"123456789\",\"3456789\",\"9876543\",\"222222\",\"333333\",\"666666\"]\n[\" 1234567\",\"2345678\",\"_1700007340280\",\"\/ 1000001\",\"\/100000\",\"_1700007340301\",\" 123456789\",\"> 3456789\",\"< 9876543\",\"- 222222\",\"* 333333\",\"> 666666\",\"limit 100001\"]\n1234567\n100001\n\n15\n[\" 1234567\",\"2345678\",\"_1700007340280\",\"\/ 1000001\",\"\/100000\",\"_1700007340301\",\"20240101\",\"20241201\",\" 123456789\",\"> 3456789\",\"< 9876543\",\"- 222222\",\"* 333333\",\"> 666666\",\"limit 100001\"]\n[\"1234567\",\"2345678\",\"1000001\",\"100000\",\"20240101\",\"20241201\",\"9999999\",\"123456789\",\"3456789\",\"9876543\",\"222222\",\"333333\",\"666666\",\"100001\"]\n[\"1234567\",\"2345678\",\"1700007340280\",\"1000001\",\"100000\",\"1700007340301\",\"20240101\",\"20241201\",\"9999999\",\"123456789\",\"3456789\",\"9876543\",\"222222\",\"333333\",\"666666\"]\n[\" 1234567\",\"2345678\",\"_1700007340280\",\"\/ 1000001\",\"\/100000\",\"_1700007340301\",\" 123456789\",\"> 3456789\",\"< 9876543\",\"- 222222\",\"* 333333\",\"> 666666\",\"limit 100001\"]\n2345678\n100001\n\n15\n[\" 1234567\",\"2345678\",\"_1700007340280\",\"\/ 1000001\",\"\/100000\",\"_1700007340301\",\"20240101\",\"20241201\",\" 123456789\",\"> 3456789\",\"< 9876543\",\"- 222222\",\"* 333333\",\"> 666666\",\"limit 100001\"]\n[\"1234567\",\"2345678\",\"1000001\",\"100000\",\"20240101\",\"20241201\",\"9999999\",\"123456789\",\"3456789\",\"9876543\",\"222222\",\"333333\",\"666666\",\"100001\"]\n[\"1234567\",\"2345678\",\"1700007340280\",\"1000001\",\"100000\",\"1700007340301\",\"20240101\",\"20241201\",\"9999999\",\"123456789\",\"3456789\",\"9876543\",\"222222\",\"333333\",\"666666\"]\n[\" 1234567\",\"2345678\",\"_1700007340280\",\"\/ 1000001\",\"\/100000\",\"_1700007340301\",\" 123456789\",\"> 3456789\",\"< 9876543\",\"- 222222\",\"* 333333\",\"> 666666\",\"limit 100001\"]\n123456789\n100001<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    ,(toString((`adID`))) AS _r1700066608896 --\u672c\u6765\u4ee5\u4e3a\u5feb\u8981\u5199\u5b8c\u4e86\uff0c\u53d1\u73b0\u8fd9\u91cc\u9762\u7684 1700066608896 \u8fd9\u4e2a\u6570\u5b57\u4f1a\u88ab\u63d0\u53d6\u51fa\u6765\uff0c\u53c8\u53d1\u73b0\u4e86\u4e00\u4e2abug\n\n\u89e3\u51b3\u65b9\u6cd5\u4e5f\u5f88\u7b80\u5355\uff0c\u5728\u6570\u5b57\u5339\u914d\u90a3\u91cc\u4f7f\u7528\u6b63\u5219\u8868\u8fbe\u5f0f\u7684\u5355\u8bcd\u8fb9\u754c\u951a\u5b9a\u7b26 \\b\u3002\u8fd9\u4e2a\u7279\u6b8a\u5b57\u7b26\u7528\u6765\u5339\u914d\u4e00\u4e2a\u5355\u8bcd\u8fb9\u754c\uff0c\u5373\u5b83\u786e\u4fdd\u5339\u914d\u7684\u662f\u4e00\u4e2a\u5b8c\u6574\u7684\u5355\u8bcd\uff0c\u800c\u4e0d\u4f1a\u662f\u53e6\u4e00\u4e2a\u5355\u8bcd\u7684\u4e00\u90e8\u5206\u3002\n\n,array_distinct(regexp_extract_all(regexp_replace(regexp_replace(multi_line_var,'--.+?\\\\n{1,}',''),'\\\\s{1,}',' '),'(\\\\+|-|\\\\*|\/|>|>=|<|<=|limit)? ?\\\\b[1-9]\\\\d{4,}\\\\b',0)) as num_array<\/code><\/pre>\n\n\n\n
    \u53c2\u8003\u94fe\u63a5\uff1a<\/h5>\n\n\n\n
    array_except \u51fd\u6570
    https:\/\/learn.microsoft.com\/zh-cn\/azure\/databricks\/sql\/language-manual\/functions\/array_except<\/a><\/p>\n\n\n\n
    array_distinct \u51fd\u6570
    https:\/\/learn.microsoft.com\/zh-cn\/azure\/databricks\/sql\/language-manual\/functions\/array_distinct<\/a><\/p>\n\n\n\n
    =END=<\/p>\n","protected":false},"excerpt":{"rendered":"
    =Start= \u7f18\u7531\uff1a \u5728\u5206\u6790\u65e5\u5fd7\u7684\u65f6\u5019\u53d1\u73b0\u4e4b\u524d\u8bb0\u5f55\u7684\u90a3\u7bc7\u6587\u7ae0\u7684\u5185\u5bb9\u597d\u50cf\u4e0d\u591f\u4e25\u8c28\uff0c\u53ef\u80fd\u4f1a\u5339\u914d\u5230\u4e00\u4e9b\u975e\u9884\u671f\u7684\u6570\u5b57 […]<\/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":[2409,2115,1261,2388,2040,1262,19,1191],"class_list":["post-5826","post","type-post","status-publish","format-standard","hentry","category-database","category-knowledgebase-2","tag-array_distinct","tag-array_except","tag-hive","tag-regexp_extract_all","tag-regexp_replace","tag-sql","tag-tips","tag-1191"],"views":1206,"_links":{"self":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/5826","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=5826"}],"version-history":[{"count":1,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/5826\/revisions"}],"predecessor-version":[{"id":5827,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/5826\/revisions\/5827"}],"wp:attachment":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/media?parent=5826"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/categories?post=5826"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/tags?post=5826"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}