HiveQL/SparQL 常用函数

HiveQL/SparQL 常用函数一、窗口函数 1.1 基础结构 1.2 row_number/rank/dense_rank：排序函数 1.3 sum/avg/max/min：聚合函数 1.4 lag/lead/first_value/last_value：取上下行函数 1.5 ntile： 分析函数，用于百分点、n分片等 1.6 count over 二、数组类型的函数 2.1 array：生成数组格式 2.2 collect_list/collect_set：分组中某列转为数组返回 2.3 array_contains：判断数组是否包含某个元素 2.4 split：字符串切分为数组三、正则表达式函数 3.1 like：判断是否匹配 3.2 regexp/rlike：正则判断是否 3.3 regexp_extract：正则匹配 3.4 regexp_replace：正则代替 3.5 常用匹配四、字符串函数 4.1 concat：字符串拼接 4.2 concat_ws：列表格式拼接 4.3 instr：字符串查找 4.4 substr：字符串截取五、时间处理函数 5.1 from_unixtime/unix_timestamp：时间戳转换 5.2 pdate2date/date2pdate：日期格式转换 5.3 date_sub/add_months： 返回前n天/月的日期 5.4 datediff：计算日期差 5.5 trunc：返回 当月第一天/返回双月第一天 六、行转列 6.1 explode：单字段行转列 6.2 lateral view explode：行转列 6.3 lateral view posexplode：行转列，带顺序七、分位数函数 7.1 percentile & percentile_approx 八、json函数 8.1 get_json_object：获取json对象的value 8.2 json_tuple：一次获取多个对象的value 九、其他 9.1 greatest：多列 最大值 9.2 rand：随机函数 9.3 max_pt：读取表中最大分区 9.4 coalesce/nvl：返回第一个非空值十、参考
一、窗口函数

1.1 基础结构

窗口函数 = 分析函数 + over函数
分析函数：sum ( )、max ( )、row_number ( ) 等等
over函数：over (partition by user_id order by date desc/asc)
按照user_id字段分区，每个分区内根据date字段排序，asc：升序（默认升序） desc：降序
1.2 row_number/rank/dense_rank：排序函数

a） row_number ( ) over ( ) ：依次排序且不会并列重复
b） rank ( ) over ( ) ：排序相等会在名次中留下空位
c） dense_rank ( ) over ( ) ：排序相等不会在名次中留下空位
</div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 35px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>10</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation" style=""><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -34.9929px; width: 35px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    user_id<span class="cm-punctuation">,</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    <span class="cm-operator">--</span>依次排序且不会并列重复</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">4</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    row_number<span class="cm-bracket">()</span> over<span class="cm-bracket">(</span><span class="cm-keyword">partition</span> <span class="cm-keyword">by</span> user_id <span class="cm-keyword">order</span> <span class="cm-keyword">by</span> <span class="cm-type">date</span><span class="cm-bracket">)</span> <span class="cm-keyword">as</span> row_number<span class="cm-punctuation">,</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">5</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    <span class="cm-operator">--</span>排序相等会在名次中留下空位</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">6</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    rank<span class="cm-bracket">()</span> over<span class="cm-bracket">(</span><span class="cm-keyword">partition</span> <span class="cm-keyword">by</span> user_id <span class="cm-keyword">order</span> <span class="cm-keyword">by</span> <span class="cm-type">date</span><span class="cm-bracket">)</span> <span class="cm-keyword">as</span> rank<span class="cm-punctuation">,</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">7</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    <span class="cm-operator">--</span>排序相等不会在名次中留下空位</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">8</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    dense_rank<span class="cm-bracket">()</span> over<span class="cm-bracket">(</span><span class="cm-keyword">partition</span> <span class="cm-keyword">by</span> user_id <span class="cm-keyword">order</span> <span class="cm-keyword">by</span> <span class="cm-type">date</span><span class="cm-bracket">)</span> <span class="cm-keyword">as</span> dense_rank</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">9</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">FROM</span></span></pre></div><div style="position: relative;" class=""><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">10</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    app_launch</span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 230px;"/><div class="CodeMirror-gutters" style="height: 230px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 34px;"/></div></div></div></pre><figure><table><thead><tr><th><span>user_id</span></th><th><span>date</span></th><th><span>row_number</span></th><th><span>rank</span></th><th><span>dense_rank</span></th></tr></thead><tbody><tr><td><span>12345</span></td><td><span>20190101</span></td><td><span>1</span></td><td><span>1</span></td><td><span>1</span></td></tr><tr><td><span>12345</span></td><td><span>20190101</span></td><td><span>2</span></td><td><span>1</span></td><td><span>1</span></td></tr><tr><td><span>12345</span></td><td><span>20190102</span></td><td><span>3</span></td><td><span>3</span></td><td><span>2</span></td></tr></tbody></table></figure><h3 id="13-sumavgmaxmin聚合函数"><span>1.3 sum/avg/max/min：聚合函数</span></h3><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 33.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 26px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>8</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation" style=""><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -25.9929px; width: 26px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    user_id<span class="cm-punctuation">,</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    <span class="cm-keyword">sum</span><span class="cm-bracket">(</span>salary<span class="cm-bracket">)</span> over<span class="cm-bracket">(</span><span class="cm-keyword">partition</span> <span class="cm-keyword">by</span> user_id <span class="cm-keyword">order</span> <span class="cm-keyword">by</span> <span class="cm-type">date</span><span class="cm-bracket">)</span> <span class="cm-keyword">as</span> <span class="cm-keyword">sum</span><span class="cm-punctuation">,</span><span class="cm-operator">--</span>起点到当前行总和</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">4</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    <span class="cm-keyword">avg</span><span class="cm-bracket">(</span>salary<span class="cm-bracket">)</span> over<span class="cm-bracket">(</span><span class="cm-keyword">partition</span> <span class="cm-keyword">by</span> user_id <span class="cm-keyword">order</span> <span class="cm-keyword">by</span> <span class="cm-type">date</span><span class="cm-bracket">)</span> <span class="cm-keyword">as</span> <span class="cm-keyword">avg</span><span class="cm-punctuation">,</span><span class="cm-operator">--</span>起点到当前行平均值</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">5</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    <span class="cm-keyword">max</span><span class="cm-bracket">(</span>salary<span class="cm-bracket">)</span> over<span class="cm-bracket">(</span><span class="cm-keyword">partition</span> <span class="cm-keyword">by</span> user_id <span class="cm-keyword">order</span> <span class="cm-keyword">by</span> <span class="cm-type">date</span><span class="cm-bracket">)</span> <span class="cm-keyword">as</span> <span class="cm-keyword">max</span><span class="cm-punctuation">,</span><span class="cm-operator">--</span>起点到当前行最大值</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">6</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    <span class="cm-keyword">min</span><span class="cm-bracket">(</span>salary<span class="cm-bracket">)</span> over<span class="cm-bracket">(</span><span class="cm-keyword">partition</span> <span class="cm-keyword">by</span> user_id <span class="cm-keyword">order</span> <span class="cm-keyword">by</span> <span class="cm-type">date</span><span class="cm-bracket">)</span> <span class="cm-keyword">as</span> <span class="cm-keyword">min</span> <span class="cm-operator">--</span>起点到当前行最小值</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">7</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">FROM</span></span></pre></div><div style="position: relative;" class=""><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">8</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    app_launch</span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 184px;"/><div class="CodeMirror-gutters" style="height: 184px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 25px;"/></div></div></div></pre><figure><table><thead><tr><th><span>user_id</span></th><th><span>date</span></th><th><span>salary</span></th><th><span>sum</span></th><th><span>avg</span></th><th><span>max</span></th><th><span>min</span></th></tr></thead><tbody><tr><td><span>12345</span></td><td><span>201901</span></td><td><span>100</span></td><td><span>100</span></td><td><span>100</span></td><td><span>100</span></td><td><span>100</span></td></tr><tr><td><span>12345</span></td><td><span>201902</span></td><td><span>200</span></td><td><span>300</span></td><td><span>150</span></td><td><span>200</span></td><td><span>100</span></td></tr><tr><td><span>12345</span></td><td><span>201903</span></td><td><span>300</span></td><td><span>600</span></td><td><span>200</span></td><td><span>300</span></td><td><span>100</span></td></tr></tbody></table></figure><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 33.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 26px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>1</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation" style=""><div style="position: relative;" class="CodeMirror-activeline"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -25.9929px; width: 26px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    user_id<span class="cm-punctuation">,</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    <span class="cm-keyword">sum</span><span class="cm-bracket">(</span>salary<span class="cm-bracket">)</span> over<span class="cm-bracket">(</span><span class="cm-keyword">partition</span> <span class="cm-keyword">by</span> user_id<span class="cm-bracket">)</span> <span class="cm-keyword">as</span> <span class="cm-keyword">sum</span><span class="cm-punctuation">,</span> <span class="cm-operator">--</span>分组内所有行的总和</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">4</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    <span class="cm-keyword">avg</span><span class="cm-bracket">(</span>salary<span class="cm-bracket">)</span> over<span class="cm-bracket">(</span><span class="cm-keyword">partition</span> <span class="cm-keyword">by</span> user_id<span class="cm-bracket">)</span> <span class="cm-keyword">as</span> <span class="cm-keyword">avg</span><span class="cm-punctuation">,</span> <span class="cm-operator">--</span>分组内所有行的平均值</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">5</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    <span class="cm-keyword">max</span><span class="cm-bracket">(</span>salary<span class="cm-bracket">)</span> over<span class="cm-bracket">(</span><span class="cm-keyword">partition</span> <span class="cm-keyword">by</span> user_id<span class="cm-bracket">)</span> <span class="cm-keyword">as</span> <span class="cm-keyword">max</span><span class="cm-punctuation">,</span> <span class="cm-operator">--</span>分组内所有行的最大值</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">6</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    <span class="cm-keyword">min</span><span class="cm-bracket">(</span>salary<span class="cm-bracket">)</span> over<span class="cm-bracket">(</span><span class="cm-keyword">partition</span> <span class="cm-keyword">by</span> user_id<span class="cm-bracket">)</span> <span class="cm-keyword">as</span> <span class="cm-keyword">min</span>  <span class="cm-operator">--</span>分组内所有行的最小值</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">7</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">FROM</span></span></pre></div><div class="" style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">8</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    app_launch</span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 184px;"/><div class="CodeMirror-gutters" style="height: 184px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 25px;"/></div></div></div></pre><figure><table><thead><tr><th><span>user_id</span></th><th><span>date</span></th><th><span>salary</span></th><th><span>sum</span></th><th><span>avg</span></th><th><span>max</span></th><th><span>min</span></th></tr></thead><tbody><tr><td><span>12345</span></td><td><span>201901</span></td><td><span>100</span></td><td><span>600</span></td><td><span>200</span></td><td><span>300</span></td><td><span>100</span></td></tr><tr><td><span>12345</span></td><td><span>201902</span></td><td><span>200</span></td><td><span>600</span></td><td><span>200</span></td><td><span>300</span></td><td><span>100</span></td></tr><tr><td><span>12345</span></td><td><span>201903</span></td><td><span>300</span></td><td><span>600</span></td><td><span>200</span></td><td><span>300</span></td><td><span>100</span></td></tr></tbody></table></figure><h3 id="14-lagleadfirstvaluelastvalue取上下行函数"><span>1.4 lag/lead/first_value/last_value：取上下行函数</span></h3><ul><li><code>lag (col, n,  default) over ( )</code><span>：用于统计窗口内往上第n行值</span></li></ul><ul><li><p><code>lead (col, n,  default) over ( )</code><span>：用于统计窗口内往下第n行值</span></p><ul><li><mark><span>第1个参数</span></mark><span>：列名</span></li></ul><ul><li><mark><span>第2个参数</span></mark><span>：往上/往下第n行（默认为1）</span></li></ul><ul><li><mark><span>第3个参数</span></mark><span>：当往上/往下第n行为NULL时候，取默认值，不写为NULL</span></li></ul></li></ul><ul><li><span>first_value (col) over ( )：取分组内排序后，截止到当前行，第一个值</span></li></ul><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 33.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 26px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>1</div></div><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>15</div></div><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>1</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation" style=""><div style="position: relative;" class="CodeMirror-activeline"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -25.9929px; width: 26px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    user_id<span class="cm-punctuation">,</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    lag<span class="cm-bracket">(</span><span class="cm-type">date</span><span class="cm-punctuation">,</span> <span class="cm-number">1</span><span class="cm-punctuation">,</span> <span class="cm-string">""</span><span class="cm-bracket">)</span> over<span class="cm-bracket">(</span><span class="cm-keyword">partition</span> <span class="cm-keyword">by</span> user_id <span class="cm-keyword">order</span> <span class="cm-keyword">by</span> <span class="cm-type">date</span><span class="cm-bracket">)</span> <span class="cm-keyword">as</span> lag<span class="cm-punctuation">,</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">4</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    lead<span class="cm-bracket">(</span><span class="cm-type">date</span><span class="cm-punctuation">,</span> <span class="cm-number">1</span><span class="cm-bracket">)</span> over<span class="cm-bracket">(</span><span class="cm-keyword">partition</span> <span class="cm-keyword">by</span> user_id <span class="cm-keyword">order</span> <span class="cm-keyword">by</span> <span class="cm-type">date</span><span class="cm-bracket">)</span> <span class="cm-keyword">as</span> lead<span class="cm-punctuation">,</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">5</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    first_value<span class="cm-bracket">(</span><span class="cm-type">date</span><span class="cm-bracket">)</span> over<span class="cm-bracket">(</span><span class="cm-keyword">partition</span> <span class="cm-keyword">by</span> user_id <span class="cm-keyword">order</span> <span class="cm-keyword">by</span> <span class="cm-type">date</span><span class="cm-bracket">)</span> <span class="cm-keyword">as</span> first_value<span class="cm-punctuation">,</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">6</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    first_value<span class="cm-bracket">(</span><span class="cm-type">date</span><span class="cm-bracket">)</span> over<span class="cm-bracket">(</span><span class="cm-keyword">partition</span> <span class="cm-keyword">by</span> user_id <span class="cm-keyword">order</span> <span class="cm-keyword">by</span> <span class="cm-type">date</span> <span class="cm-keyword">desc</span><span class="cm-bracket">)</span> <span class="cm-keyword">as</span> last_value</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">7</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">FROM</span></span></pre></div><div class="" style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">8</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    app_launch</span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 184px;"/><div class="CodeMirror-gutters" style="height: 184px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 25px;"/></div></div></div></pre><figure><table><thead><tr><th><span>user_id</span></th><th><span>date</span></th><th><span>lag</span></th><th><span>lead</span></th><th><span>first_value</span></th><th><span>first_value_desc</span></th></tr></thead><tbody><tr><td><span>12345</span></td><td><span>20190101</span></td><td><span>""</span></td><td><span>20190102</span></td><td><span>20190101</span></td><td><span>20190103</span></td></tr><tr><td><span>12345</span></td><td><span>20190102</span></td><td><span>20190101</span></td><td><span>20190103</span></td><td><span>20190101</span></td><td><span>20190103</span></td></tr><tr><td><span>12345</span></td><td><span>20190103</span></td><td><span>20190102</span></td><td><span>NULL</span></td><td><span>20190101</span></td><td><span>20190103</span></td></tr></tbody></table></figure><ul><li><span>last_value (col) over ( )：取分组内排序后，截止到当前行，最后一个值</span></li></ul><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 33.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 26px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>1</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation" style=""><div style="position: relative;" class="CodeMirror-activeline"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -25.9929px; width: 26px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    user_id<span class="cm-punctuation">,</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    last_value<span class="cm-bracket">(</span><span class="cm-type">date</span><span class="cm-bracket">)</span> over<span class="cm-bracket">(</span><span class="cm-keyword">partition</span> <span class="cm-keyword">by</span> user_id <span class="cm-keyword">order</span> <span class="cm-keyword">by</span> <span class="cm-type">date</span><span class="cm-bracket">)</span> <span class="cm-keyword">as</span> last_value<span class="cm-punctuation">,</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">4</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    last_value<span class="cm-bracket">(</span><span class="cm-type">date</span><span class="cm-bracket">)</span> over<span class="cm-bracket">(</span><span class="cm-keyword">partition</span> <span class="cm-keyword">by</span> user_id <span class="cm-keyword">order</span> <span class="cm-keyword">by</span> <span class="cm-type">date</span> <span class="cm-keyword">desc</span><span class="cm-bracket">)</span> <span class="cm-keyword">as</span> last_value2</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">5</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">FROM</span></span></pre></div><div class="" style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">6</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    app_launch</span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 138px;"/><div class="CodeMirror-gutters" style="height: 138px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 25px;"/></div></div></div></pre><figure><table><thead><tr><th><span>user_id</span></th><th><span>date</span></th><th><span>last_value</span></th><th><span>last_value2</span></th></tr></thead><tbody><tr><td><span>12345</span></td><td><span>20190101</span></td><td><span>20190101</span></td><td><span>20190103</span></td></tr><tr><td><span>12345</span></td><td><span>20190102</span></td><td><span>20190102</span></td><td><span>20190102</span></td></tr><tr><td><span>12345</span></td><td><span>20190103</span></td><td><span>20190103</span></td><td><span>20190101</span></td></tr></tbody></table></figure><h3 id="15-ntile分析函数用于百分点n分片等"><span>1.5 ntile：</span><strong><span>分析函数，用于百分点、n分片等</span></strong></h3><p><code>NTILE()</code><span>把有序的数据集合平均分配到指定的数量（num）个桶中, 将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。</span></p><blockquote><p><code>NTILE()</code><span>函数将排序分区中的行划分为特定数量的组。从每个组分配一个从一开始的桶号。对于每一行，</span><code>NTILE()</code><span>函数返回一个桶号，表示行所属的组。</span></p><ul><li><span> </span><code>n</code><span>是一个字面正整数。桶号的范围是1到</span><code>n</code><span>。</span></li><li><span>在</span><code>PARTITION BY</code><span>从返回的结果集划分</span><code>FROM</code><span>子句为分区到的</span><code>NTILE()</code><span>函数被应用。</span></li><li><code>ORDER BY</code><span>子句指定将</span><code>NTILE()</code><span>值分配给分区中的行的顺序。</span></li></ul><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 33.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 26px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>4</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation"><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -25.9929px; width: 26px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">NTILE<span class="cm-bracket">(</span>n<span class="cm-bracket">)</span> OVER <span class="cm-bracket">(</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    <span class="cm-keyword">PARTITION</span> <span class="cm-keyword">BY</span> <span class="cm-operator"><</span>expression<span class="cm-operator">></span><span class="cm-bracket">[{</span><span class="cm-punctuation">,</span><span class="cm-operator"><</span>expression<span class="cm-operator">></span>...<span class="cm-bracket">}]</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    <span class="cm-keyword">ORDER</span> <span class="cm-keyword">BY</span> <span class="cm-operator"><</span>expression<span class="cm-operator">></span> <span class="cm-bracket">[</span><span class="cm-keyword">ASC</span><span class="cm-operator">|</span><span class="cm-keyword">DESC</span><span class="cm-bracket">]</span><span class="cm-punctuation">,</span> <span class="cm-bracket">[{</span><span class="cm-punctuation">,</span><span class="cm-operator"><</span>expression<span class="cm-operator">></span>...<span class="cm-bracket">}]</span></span></pre></div><div style="position: relative;" class=""><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">4</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-bracket">)</span> </span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 92px;"/><div class="CodeMirror-gutters" style="height: 92px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 25px;"/></div></div></div></pre></blockquote><p><span>然后可以根据桶号，选取前或后n分之几的数据。</span></p><p><span>用户收入表：user_salary</span></p><figure><table><thead><tr><th><span>user_id</span></th><th><span>salary</span></th></tr></thead><tbody><tr><td><span>1</span></td><td><span>100</span></td></tr><tr><td><span>2</span></td><td><span>200</span></td></tr><tr><td><span>3</span></td><td><span>300</span></td></tr></tbody></table></figure><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 33.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 26px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>1</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation" style=""><div style="position: relative;" class="CodeMirror-activeline"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -25.9929px; width: 26px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    user_id<span class="cm-punctuation">,</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    salary<span class="cm-punctuation">,</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">4</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    ntile<span class="cm-bracket">(</span><span class="cm-number">2</span><span class="cm-bracket">)</span> over<span class="cm-bracket">(</span><span class="cm-keyword">partition</span> <span class="cm-keyword">by</span> user_id <span class="cm-keyword">order</span> <span class="cm-keyword">by</span> salary<span class="cm-bracket">)</span> <span class="cm-keyword">as</span> rn</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">5</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">FROM</span></span></pre></div><div class="" style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">6</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    user_salary</span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 138px;"/><div class="CodeMirror-gutters" style="height: 138px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 25px;"/></div></div></div></pre><figure><table><thead><tr><th><span>user_id</span></th><th><span>salary</span></th><th><span>rn</span></th></tr></thead><tbody><tr><td><span>1</span></td><td><span>100</span></td><td><span>1</span></td></tr><tr><td><span>2</span></td><td><span>200</span></td><td><span>1</span></td></tr><tr><td><span>3</span></td><td><span>300</span></td><td><span>2</span></td></tr></tbody></table></figure><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="mysql"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="mysql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 42.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 35px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>13</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation" style=""><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -34.9929px; width: 35px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-comment">-- 收入前50%用户的平均收入</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    <span class="cm-keyword">avg</span><span class="cm-bracket">(</span>salary<span class="cm-bracket">)</span> <span class="cm-keyword">as</span> avg_salary</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">4</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">FROM</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">5</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-bracket">(</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">6</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    <span class="cm-keyword">SELECT</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">7</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">        user_id<span class="cm-punctuation">,</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">8</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">        salary<span class="cm-punctuation">,</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">9</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">        ntile<span class="cm-bracket">(</span><span class="cm-number">2</span><span class="cm-bracket">)</span> over<span class="cm-bracket">(</span><span class="cm-keyword">partition</span> <span class="cm-keyword">by</span> user_id <span class="cm-keyword">order</span> <span class="cm-keyword">by</span> salary<span class="cm-bracket">)</span> <span class="cm-keyword">as</span> rn</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">10</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    <span class="cm-keyword">FROM</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">11</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">        user_salary</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">12</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-bracket">)</span> a</span></pre></div><div style="position: relative;" class=""><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">13</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">where</span> rn <span class="cm-operator">=</span> <span class="cm-number">1</span></span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 299px;"/><div class="CodeMirror-gutters" style="height: 299px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 34px;"/></div></div></div></pre><h3 id="16-count-over"><span>1.6 count over</span></h3><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 33.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 26px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>1</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation" style=""><div style="position: relative;" class="CodeMirror-activeline"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -25.9929px; width: 26px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    <span class="cm-operator">*</span><span class="cm-punctuation">,</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    <span class="cm-keyword">count</span><span class="cm-bracket">(</span><span class="cm-number">1</span><span class="cm-bracket">)</span> over<span class="cm-bracket">(</span><span class="cm-keyword">partition</span> <span class="cm-keyword">by</span> department<span class="cm-bracket">)</span> <span class="cm-keyword">as</span> cnt1<span class="cm-punctuation">,</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">4</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    <span class="cm-keyword">count</span><span class="cm-bracket">(</span><span class="cm-number">1</span><span class="cm-bracket">)</span> over<span class="cm-bracket">(</span><span class="cm-keyword">order</span> <span class="cm-keyword">by</span> department<span class="cm-bracket">)</span> <span class="cm-keyword">as</span> cnt2<span class="cm-punctuation">,</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">5</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    <span class="cm-keyword">count</span><span class="cm-bracket">(</span><span class="cm-number">1</span><span class="cm-bracket">)</span> over<span class="cm-bracket">(</span><span class="cm-keyword">partition</span> <span class="cm-keyword">by</span> department <span class="cm-keyword">order</span> <span class="cm-keyword">by</span> name<span class="cm-bracket">)</span> <span class="cm-keyword">as</span> cnt3</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">6</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">FROM</span></span></pre></div><div class="" style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">7</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    dm_homed<span class="cm-variable-2">.test_count_over</span></span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 161px;"/><div class="CodeMirror-gutters" style="height: 161px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 25px;"/></div></div></div></pre><figure><table><thead><tr><th><span>name</span></th><th><span>department</span></th><th><span>salary</span></th><th><span>cn1</span></th><th><span>cn2</span></th><th><span>cn3</span></th></tr></thead><tbody><tr><td><span>王五</span></td><td><span>人事</span></td><td><span>4000</span></td><td><span>1</span></td><td><span>1</span></td><td><span>1</span></td></tr><tr><td><span>刘二</span></td><td><span>研发</span></td><td><span>6500</span></td><td><span>3</span></td><td><span>4</span></td><td><span>1</span></td></tr><tr><td><span>赵六</span></td><td><span>研发</span></td><td><span>7000</span></td><td><span>3</span></td><td><span>4</span></td><td><span>2</span></td></tr><tr><td><span>陈一</span></td><td><span>研发</span></td><td><span>6500</span></td><td><span>3</span></td><td><span>4</span></td><td><span>3</span></td></tr><tr><td><span>张三</span></td><td><span>财务</span></td><td><span>5000</span></td><td><span>2</span></td><td><span>6</span></td><td><span>1</span></td></tr><tr><td><span>李四</span></td><td><span>财务</span></td><td><span>5200</span></td><td><span>2</span></td><td><span>6</span></td><td><span>2</span></td></tr></tbody></table></figure><h2 id="二数组类型的函数"><span>二、数组类型的函数</span></h2><h3 id="21-array生成数组格式"><span>2.1 array：生成数组格式</span></h3><p><span>array (val1, val2, val3, …)：将多个数据生成数组格式</span></p><p><span>array类型的数据可以通过</span><code>'数组名[index]'</code><span>的方式访问，index从0开始:</span></p><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 33.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 26px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>5</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation" style=""><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -25.9929px; width: 26px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> ARRAY<span class="cm-bracket">(</span><span class="cm-number">0</span><span class="cm-punctuation">,</span><span class="cm-number">1</span><span class="cm-punctuation">,</span><span class="cm-number">2</span><span class="cm-bracket">)</span><span class="cm-punctuation">;</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">returns</span> <span class="cm-bracket">[</span><span class="cm-number">0</span><span class="cm-punctuation">,</span><span class="cm-number">1</span><span class="cm-punctuation">,</span><span class="cm-number">2</span><span class="cm-bracket">]</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">4</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> ARRAY<span class="cm-bracket">(</span><span class="cm-number">0</span><span class="cm-punctuation">,</span><span class="cm-number">1</span><span class="cm-punctuation">,</span><span class="cm-number">2</span><span class="cm-bracket">)[</span><span class="cm-number">1</span><span class="cm-bracket">]</span></span></pre></div><div style="position: relative;" class=""><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">5</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">returns</span> <span class="cm-number">1</span></span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 115px;"/><div class="CodeMirror-gutters" style="height: 115px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 25px;"/></div></div></div></pre><h3 id="22-collectlistcollectset分组中某列转为数组返回"><span>2.2 collect_list/collect_set：分组中某列转为数组返回</span></h3><p><span>将分组中某列转为一个数组返回，不同的是collect_list不去重而collect_set去重</span></p><p><strong><span>collect_set的参数必须是string类型</span></strong></p><figure><table><thead><tr><th><span>user_id</span></th><th><span>date</span></th></tr></thead><tbody><tr><td><span>12345</span></td><td><span>1001</span></td></tr><tr><td><span>12345</span></td><td><span>1001</span></td></tr><tr><td><span>12345</span></td><td><span>1002</span></td></tr></tbody></table></figure><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 33.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 26px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre>x</pre></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation" style=""><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -25.9929px; width: 26px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    user_id<span class="cm-punctuation">,</span></span></pre></div><div style="position: relative;" class=""><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    concat_ws<span class="cm-bracket">(</span><span class="cm-string">','</span><span class="cm-punctuation">,</span> collect_list/collect_set<span class="cm-bracket">(</span>cast<span class="cm-bracket">(</span><span class="cm-type">date</span> <span class="cm-keyword">as</span> string<span class="cm-bracket">)))</span> <span class="cm-keyword">as</span> <span class="cm-type">date</span></span></pre></div><div style="position: relative;" class=""><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">4</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">FROM</span></span></pre></div><div style="position: relative;" class=""><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">5</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    app_launch</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">6</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">GROUP</span> <span class="cm-keyword">BY</span> </span></pre></div><div style="position: relative;" class=""><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">7</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    user_id</span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 161px;"/><div class="CodeMirror-gutters" style="height: 161px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 25px;"/></div></div></div></pre><figure><table><thead><tr><th><span>函数</span></th><th><span>collect_list</span></th><th><span>collect_set</span></th></tr></thead><tbody><tr><td><span>user_id</span></td><td><span>date</span></td><td><span>date</span></td></tr><tr><td><span>12345</span></td><td><span>1001,1001,1002</span></td><td><span>1001,1002</span></td></tr></tbody></table></figure><h3 id="23-arraycontains判断数组是否包含某个元素"><span>2.3 array_contains：判断数组是否包含某个元素</span></h3><p><span>判断数组是否包含某个元素，返回true/false</span></p><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 33.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 26px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>2</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation"><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -25.9929px; width: 26px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> array_contains<span class="cm-bracket">(</span>ARRAY<span class="cm-bracket">(</span><span class="cm-number">0</span><span class="cm-punctuation">,</span><span class="cm-number">1</span><span class="cm-bracket">)</span><span class="cm-punctuation">,</span> <span class="cm-number">0</span><span class="cm-bracket">)</span><span class="cm-punctuation">;</span></span></pre></div><div style="position: relative;" class=""><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">returns</span> <span class="cm-atom">true</span></span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 46px;"/><div class="CodeMirror-gutters" style="height: 46px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 25px;"/></div></div></div></pre><h3 id="24-split字符串切分为数组"><span>2.4 split：字符串切分为数组</span></h3><p><span>将字符串切分为数组</span></p><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 33.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 26px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>2</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation"><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -25.9929px; width: 26px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> split<span class="cm-bracket">(</span><span class="cm-string">'装修避坑,选材经验,设计案例'</span><span class="cm-punctuation">,</span> <span class="cm-string">','</span><span class="cm-bracket">)</span><span class="cm-punctuation">;</span></span></pre></div><div style="position: relative;" class=""><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">returns</span> <span class="cm-bracket">[</span><span class="cm-string">"装修避坑"</span><span class="cm-punctuation">,</span> <span class="cm-string">"选材经验"</span><span class="cm-punctuation">,</span> <span class="cm-string">"设计案例"</span><span class="cm-bracket">]</span></span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 46px;"/><div class="CodeMirror-gutters" style="height: 46px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 25px;"/></div></div></div></pre><h2 id="三正则表达式函数"><span>三、正则表达式函数</span></h2><h3 id="31-like判断是否匹配"><span>3.1 like：判断是否匹配</span></h3><p><span>主要用在where子句中，用来筛选内容，%是通配符，返回true/false</span></p><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 33.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 26px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>4</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation"><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -25.9929px; width: 26px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> <span class="cm-string">"news_home/electric, news_home"</span> <span class="cm-keyword">like</span> <span class="cm-string">'%news_home%'</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">return</span> <span class="cm-atom">true</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> <span class="cm-string">"news_house/electric"</span> <span class="cm-keyword">like</span> <span class="cm-string">'%news_home%'</span></span></pre></div><div style="position: relative;" class=""><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">4</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">return</span> <span class="cm-atom">false</span></span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 92px;"/><div class="CodeMirror-gutters" style="height: 92px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 25px;"/></div></div></div></pre><h3 id="32-regexprlike正则判断是否"><span>3.2 regexp/rlike：正则判断是否</span></h3><p><span>主要用在where子句中，用来筛选内容，返回true/false</span></p><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="SQL" style="break-inside: unset;"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 42.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 35px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>24</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation" style=""><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -34.9929px; width: 35px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">category <span class="cm-operator">=</span> <span class="cm-string">"news_home/electric, news_home"</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">category1 <span class="cm-operator">=</span> <span class="cm-string">"news_home, news_home/electric"</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> category <span class="cm-keyword">regexp</span> <span class="cm-string">'news_home$'</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">4</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">return</span> <span class="cm-atom">true</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">5</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> category1 <span class="cm-keyword">regexp</span> <span class="cm-string">'news_home$'</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">6</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">return</span> <span class="cm-atom">false</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">7</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">8</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-comment">-- 判断是否为纯数字  "^[0-9]+$"</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">9</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> <span class="cm-string">"123123123"</span> <span class="cm-keyword">RLIKE</span> <span class="cm-string">"^[0-9]+$"</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">10</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">return</span> <span class="cm-atom">true</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">11</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> <span class="cm-string">"123123123a"</span> <span class="cm-keyword">RLIKE</span> <span class="cm-string">"^[0-9]+$"</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">12</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">return</span> <span class="cm-atom">false</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">13</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">14</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-comment">-- 判断是否为纯字母   "^[A-Za-z]+$"</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">15</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> <span class="cm-string">"sdkfjsdfa"</span> <span class="cm-keyword">RLIKE</span> <span class="cm-string">"^[A-Za-z]+$"</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">16</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">return</span> <span class="cm-atom">true</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">17</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> <span class="cm-string">"sdkfjsdfa1231231"</span> <span class="cm-keyword">RLIKE</span> <span class="cm-string">"^[0-9]+$"</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">18</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">return</span> <span class="cm-atom">false</span><span class="cm-operator">--</span>判断是否为纯字母   <span class="cm-string">"^[A-Za-z]+$"</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">19</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">20</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-comment">-- 判断是否为有中文   "[\u4E00-\u9FFF]+"</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">21</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> <span class="cm-string">'news_house我们electric'</span> <span class="cm-keyword">RLIKE</span> <span class="cm-string">'[\u4E00-\u9FFF]+'</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">22</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">return</span> <span class="cm-atom">true</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">23</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> <span class="cm-string">'news_house/electric'</span> <span class="cm-keyword">RLIKE</span> <span class="cm-string">'[\u4E00-\u9FFF]+'</span></span></pre></div><div style="position: relative;" class=""><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">24</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">return</span> <span class="cm-atom">false</span></span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 553px;"/><div class="CodeMirror-gutters" style="height: 553px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 34px;"/></div></div></div></pre><h3 id="33-regexpextract正则匹配"><span>3.3 regexp_extract：正则匹配</span></h3><p><span>用来从内容中</span><strong><span>匹配指定样式的内容</span></strong><span>，</span><mark><span>缺点是不能提取满足条件的所有内容</span></mark></p><p><code>regexp_extract ( date,  pattern,  index )</code></p><ul><li><span>第1个参数：待匹配字符串</span></li></ul><ul><li><span>第2个参数：匹配规则</span></li></ul><ul><li><p><span>第3个参数：返回正则完成的第几个结果</span></p><ul><li><span>0：返回正则匹配的整个结果</span></li><li><span>1：返回正则表达式中匹配的第一个括号中的的结果，以此类推</span></li></ul></li></ul><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql" style="break-inside: unset;"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 42.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 35px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>1</div></div><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>23</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation" style=""><div style="position: relative;" class="CodeMirror-activeline"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -34.9929px; width: 35px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">data</span> <span class="cm-operator">=</span> <span class="cm-string">"news_home/electric, news_home, news_home/electric, news_home"</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> regexp_extract<span class="cm-bracket">(</span><span class="cm-keyword">data</span><span class="cm-punctuation">,</span> <span class="cm-string">'(news_home(?!/))'</span><span class="cm-punctuation">,</span> <span class="cm-number">0</span><span class="cm-bracket">)</span><span class="cm-punctuation">;</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">4</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-comment">-- (?!/)表示后一位没有'/'</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">5</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">return</span> news_home</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">6</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> regexp_extract<span class="cm-bracket">(</span><span class="cm-keyword">data</span><span class="cm-punctuation">,</span> <span class="cm-string">'.*(news_home),||.*,(news_home)$'</span><span class="cm-bracket">)</span><span class="cm-punctuation">;</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">7</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-comment">-- 匹配在中间和在末尾的news_home</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">8</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">return</span> news_home</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">9</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">10</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> regexp_extract<span class="cm-bracket">(</span><span class="cm-string">'id=1,num=1,id=2,num=2'</span><span class="cm-punctuation">,</span> <span class="cm-string">'id=(.*?),.*id=(.*?),.*'</span><span class="cm-punctuation">,</span> <span class="cm-number">1</span><span class="cm-bracket">)</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">11</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-number">1</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">12</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> regexp_extract<span class="cm-bracket">(</span><span class="cm-string">'id=1,num=1,id=2,num=2'</span><span class="cm-punctuation">,</span> <span class="cm-string">'id=(.*?),.*id=(.*?),.*'</span><span class="cm-punctuation">,</span> <span class="cm-number">2</span><span class="cm-bracket">)</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">13</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-number">2</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">14</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> regexp_extract<span class="cm-bracket">(</span><span class="cm-string">'id=1,num=1'</span><span class="cm-punctuation">,</span> <span class="cm-string">'id=(.*?),.*id=(.*?),.*'</span><span class="cm-punctuation">,</span> <span class="cm-number">2</span><span class="cm-bracket">)</span> <span class="cm-operator">--</span>匹配不上为空</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">15</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-string">""</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">16</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">17</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">--</span>匹配满足条件的</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">18</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> regexp_extract<span class="cm-bracket">(</span><span class="cm-string">'insert overwrite table ecom.dim_shop_df partition'</span><span class="cm-punctuation">,</span> <span class="cm-string">'.*overwrite table (.*?) partition.*'</span><span class="cm-punctuation">,</span> <span class="cm-number">1</span><span class="cm-bracket">)</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">19</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">ecom<span class="cm-variable-2">.dim_shop_df</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">20</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">21</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">--</span>或的使用</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">22</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> regexp_extract<span class="cm-bracket">(</span><span class="cm-string">'insert overwrite table ecom. dim_shop_df partition'</span><span class="cm-punctuation">,</span> <span class="cm-string">'.*(overwrite table|OVERWRITE TABLE).*?\\.(.*?) (partition|PARTITION).*'</span><span class="cm-punctuation">,</span> <span class="cm-number">2</span><span class="cm-bracket">)</span></span></pre></div><div class="" style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">23</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">ecom<span class="cm-variable-2">.dim_shop_df</span></span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 576px;"/><div class="CodeMirror-gutters" style="height: 576px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 34px;"/></div></div></div></pre><h3 id="34-regexpreplace正则代替"><span>3.4 regexp_replace：正则代替</span></h3><p><span>用在select 子句中，用来将目标内容的特定部分</span><strong><span>替换</span></strong><span>成指定的内容</span></p><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 33.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 26px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>9</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation" style=""><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -25.9929px; width: 26px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">data</span> <span class="cm-operator">=</span> <span class="cm-string">"装修避坑: 0.358001,选材经验: 0.828523,设计案例: 0.329146"</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> regexp_replace<span class="cm-bracket">(</span><span class="cm-keyword">data</span><span class="cm-punctuation">,</span> <span class="cm-string">':(.*?),'</span><span class="cm-punctuation">,</span> <span class="cm-string">','</span><span class="cm-bracket">)</span><span class="cm-punctuation">;</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">4</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-comment">-- 替换": 0.828523,"为","</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">5</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">return</span> <span class="cm-string">"装修避坑,选材经验,设计案例"</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">6</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">7</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> regexp_replace<span class="cm-bracket">([</span><span class="cm-number">1645357130369064</span><span class="cm-punctuation">,</span><span class="cm-number">1645005576984579</span><span class="cm-bracket">]</span><span class="cm-punctuation">,</span> <span class="cm-string">'\\[|\\]'</span><span class="cm-punctuation">,</span> <span class="cm-string">''</span><span class="cm-bracket">)</span><span class="cm-punctuation">;</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">8</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-comment">-- 替换[或]为"",[前需要加两个斜杠</span></span></pre></div><div style="position: relative;" class=""><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">9</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">return</span> <span class="cm-string">"1645357130369064,1645005576984579"</span></span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 207px;"/><div class="CodeMirror-gutters" style="height: 207px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 25px;"/></div></div></div></pre><h3 id="35-常用匹配"><span>3.5 常用匹配</span></h3><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql" style="break-inside: unset;"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 42.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 35px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>1</div></div><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>15</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation" style=""><div style="position: relative;" class="CodeMirror-activeline"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -34.9929px; width: 35px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-comment">-- 匹配第一个出现的数字结果</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">select</span> regexp_extract<span class="cm-bracket">(</span><span class="cm-string">'耐克123/Nike'</span><span class="cm-punctuation">,</span><span class="cm-string">'([0-9]+)'</span><span class="cm-punctuation">,</span><span class="cm-number">1</span><span class="cm-bracket">)</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-number">123</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">4</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;" class=""><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">5</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-comment">-- 匹配第一个出现的字母结果</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">6</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">select</span> regexp_extract<span class="cm-bracket">(</span><span class="cm-string">'abc耐克/Nike'</span><span class="cm-punctuation">,</span><span class="cm-string">'([a-zA-Z]+)'</span><span class="cm-punctuation">,</span><span class="cm-number">1</span><span class="cm-bracket">)</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">7</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">abc</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">8</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;" class=""><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">9</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-comment">-- 匹配第一个出现的中文结果</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">10</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">select</span> regexp_extract<span class="cm-bracket">(</span><span class="cm-string">'耐克/Nike'</span><span class="cm-punctuation">,</span><span class="cm-string">'([\u4E00-\u9FFF]+)'</span><span class="cm-punctuation">,</span> <span class="cm-number">1</span><span class="cm-bracket">)</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">11</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">耐克</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">12</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;" class=""><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">13</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-comment">-- 匹配所有的字母：将非字母的替换为空</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">14</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">select</span> REGEXP_REPLACE<span class="cm-bracket">(</span><span class="cm-string">'abc耐克/Nike'</span><span class="cm-punctuation">,</span><span class="cm-string">'([^a-z]+)'</span><span class="cm-punctuation">,</span><span class="cm-string">''</span><span class="cm-bracket">)</span></span></pre></div><div class="" style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">15</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">abcike</span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 346px;"/><div class="CodeMirror-gutters" style="height: 346px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 34px;"/></div></div></div></pre><p> </p><h2 id="四字符串函数"><span>四、字符串函数</span></h2><h3 id="41-concat字符串拼接"><span>4.1 concat：字符串拼接</span></h3><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 33.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 26px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre>x</pre></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation"><div style="position: relative;" class="CodeMirror-activeline"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -25.9929px; width: 26px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> concat<span class="cm-bracket">(</span><span class="cm-string">"welcome"</span><span class="cm-punctuation">,</span> <span class="cm-string">" "</span><span class="cm-punctuation">,</span> <span class="cm-string">"to"</span><span class="cm-punctuation">,</span> <span class="cm-string">" "</span><span class="cm-punctuation">,</span> <span class="cm-string">"beijing"</span><span class="cm-bracket">)</span><span class="cm-punctuation">;</span></span></pre></div><div class="" style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">return</span> <span class="cm-string">"welcome to beijing"</span></span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 46px;"/><div class="CodeMirror-gutters" style="height: 46px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 25px;"/></div></div></div></pre><h3 id="42-concatws列表格式拼接"><span>4.2 concat_ws：列表格式拼接</span></h3><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 33.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 26px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>1</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation" style=""><div style="position: relative;" class="CodeMirror-activeline"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -25.9929px; width: 26px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">data_list <span class="cm-operator">=</span> <span class="cm-bracket">[</span><span class="cm-string">"装修避坑"</span><span class="cm-punctuation">,</span> <span class="cm-string">"选材经验"</span><span class="cm-punctuation">,</span> <span class="cm-string">"设计案例"</span><span class="cm-bracket">]</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> concat_ws<span class="cm-bracket">(</span><span class="cm-string">','</span> <span class="cm-punctuation">,</span> data_list<span class="cm-bracket">)</span><span class="cm-punctuation">;</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">return</span> <span class="cm-string">"装修避坑,选材经验,设计案例"</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">4</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">5</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> concat_ws<span class="cm-bracket">(</span><span class="cm-string">','</span> <span class="cm-punctuation">,</span> <span class="cm-string">"装修避坑"</span><span class="cm-punctuation">,</span> <span class="cm-string">"选材经验"</span><span class="cm-punctuation">,</span> <span class="cm-string">"设计案例"</span><span class="cm-bracket">)</span><span class="cm-punctuation">;</span></span></pre></div><div class="" style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">6</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">return</span> <span class="cm-string">"装修避坑,选材经验,设计案例"</span></span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 138px;"/><div class="CodeMirror-gutters" style="height: 138px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 25px;"/></div></div></div></pre><h3 id="43-instr字符串查找"><span>4.3 instr：字符串查找</span></h3><p><code>instr(str1, str2, start_index, nth_appearance)</code><span>：返回查找到的位置，未找到返回为0</span></p><ul><li><span>str1：源字符串，要在此字符串中查找</span></li><li><span>str2：要在str1中查找的字符串</span></li><li><span>start_index：代表从str1的哪个位置开始查找，默认为1。此参数为正，从左到右开始检索，如果此参数为负，从右到左检索</span></li><li><span>nth_appearance：查找第几次出现的str2，默认为1</span></li></ul><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 33.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 26px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>3</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation"><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -25.9929px; width: 26px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> instr<span class="cm-bracket">(</span><span class="cm-string">'syranmo'</span><span class="cm-punctuation">,</span><span class="cm-string">'syra'</span><span class="cm-bracket">)</span>       <span class="cm-comment">-- 返回 1</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> instr<span class="cm-bracket">(</span><span class="cm-string">'syranmo'</span><span class="cm-punctuation">,</span><span class="cm-string">'ranm'</span><span class="cm-bracket">)</span>       <span class="cm-comment">-- 返回 3</span></span></pre></div><div style="position: relative;" class=""><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> instr<span class="cm-bracket">(</span><span class="cm-string">'syranmo'</span><span class="cm-punctuation">,</span><span class="cm-string">'abcd'</span><span class="cm-bracket">)</span>       <span class="cm-comment">-- 返回 0</span></span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 69px;"/><div class="CodeMirror-gutters" style="height: 69px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 25px;"/></div></div></div></pre><h3 id="44-substr字符串截取"><span>4.4 substr：字符串截取</span></h3><p><code>substr(string str1, int start, int len)</code><span>：返回字符串str1从start位置开始，长度为len的字符串；len不传，默认从start位置到结尾的字符串</span></p><ul><li><span>str1：要截取的字符串</span></li><li><span>start：开始的位置</span></li><li><span>len：截取的长度</span></li></ul><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 33.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 26px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>8</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation" style=""><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -25.9929px; width: 26px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> substr<span class="cm-bracket">(</span><span class="cm-string">'12345'</span><span class="cm-punctuation">,</span><span class="cm-number">3</span><span class="cm-bracket">)</span> </span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-number">345</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">4</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> substr<span class="cm-bracket">(</span><span class="cm-string">'12345'</span><span class="cm-punctuation">,</span><span class="cm-number">1</span><span class="cm-punctuation">,</span><span class="cm-number">4</span><span class="cm-bracket">)</span> </span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">5</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-number">1234</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">6</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">7</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> substr<span class="cm-bracket">(</span><span class="cm-string">'12345'</span><span class="cm-punctuation">,</span><span class="cm-operator">-</span><span class="cm-number">2</span><span class="cm-punctuation">,</span><span class="cm-number">2</span><span class="cm-bracket">)</span> </span></pre></div><div style="position: relative;" class=""><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">8</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-number">45</span></span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 184px;"/><div class="CodeMirror-gutters" style="height: 184px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 25px;"/></div></div></div></pre><h2 id="五时间处理函数"><span>五、时间处理函数</span></h2><h3 id="51-fromunixtimeunixtimestamp时间戳转换"><span>5.1 from_unixtime/unix_timestamp：时间戳转换</span></h3><p><code>from_unixtime(ts, date_fomat)</code><span>：时间戳转换为日期格式</span></p><p><code>unix_timestamp(date_fomat, ts)</code><span>：日期格式转换为时间戳</span></p><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 42.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 35px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>13</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation" style=""><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -34.9929px; width: 35px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">--</span>from_unixtime</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> from_unixtime<span class="cm-bracket">(</span><span class="cm-number">1562160194</span><span class="cm-punctuation">,</span> <span class="cm-string">'yyyyMMdd HH:mm:ss'</span><span class="cm-bracket">)</span><span class="cm-punctuation">;</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-number">20190703</span> <span class="cm-number">21</span><span class="cm-punctuation">:</span><span class="cm-number">23</span><span class="cm-punctuation">:</span><span class="cm-number">14</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">4</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">5</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> from_unixtime<span class="cm-bracket">(</span><span class="cm-number">1562160194</span><span class="cm-punctuation">,</span> <span class="cm-string">'yyyy-MM-dd'</span><span class="cm-bracket">)</span><span class="cm-punctuation">;</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">6</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-number">2019</span><span class="cm-operator">-</span><span class="cm-number">07</span><span class="cm-operator">-</span><span class="cm-number">03</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">7</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">8</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> from_unixtime<span class="cm-bracket">(</span><span class="cm-number">1562160194</span><span class="cm-punctuation">,</span> <span class="cm-string">'yyyyMMdd'</span><span class="cm-bracket">)</span><span class="cm-punctuation">;</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">9</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-number">20190703</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">10</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">11</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">--</span>unix_timestamp</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">12</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> unix_timestamp<span class="cm-bracket">(</span><span class="cm-string">'2019-07-03 21:23:14'</span><span class="cm-punctuation">,</span> <span class="cm-string">'yyyy-MM-dd HH:mm:ss'</span><span class="cm-bracket">)</span><span class="cm-punctuation">;</span></span></pre></div><div style="position: relative;" class=""><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">13</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-number">1562160194</span></span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 299px;"/><div class="CodeMirror-gutters" style="height: 299px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 34px;"/></div></div></div></pre><h3 id="52-pdate2datedate2pdate日期格式转换"><span>5.2 pdate2date/date2pdate：日期格式转换</span></h3><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 33.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 26px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>5</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation" style=""><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -25.9929px; width: 26px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> pdate2date<span class="cm-bracket">(</span><span class="cm-string">'2020-07-23'</span><span class="cm-bracket">)</span><span class="cm-punctuation">;</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-number">20200723</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">4</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> date2pdate<span class="cm-bracket">(</span><span class="cm-string">'20200723'</span><span class="cm-bracket">)</span><span class="cm-punctuation">;</span></span></pre></div><div style="position: relative;" class=""><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">5</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-number">2020</span><span class="cm-operator">-</span><span class="cm-number">07</span><span class="cm-operator">-</span><span class="cm-number">23</span></span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 115px;"/><div class="CodeMirror-gutters" style="height: 115px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 25px;"/></div></div></div></pre><h3 id="53-datesubaddmonths返回前n天月的日期"><span>5.3 date_sub/add_months：</span><strong><span>返回前n天/月的日期</span></strong></h3><p><code>date_sub ('${DATE}', n)</code><span>：返回DATE的前n天的日期，n：正数是减，负数是加</span></p><p><code>add_months('${DATE}', n)</code><span>：返回DATE的后n月的日期，n：正数是加，负数是减</span></p><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 42.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 35px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>13</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation" style=""><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -34.9929px; width: 35px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">--</span>date_sub</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">select</span> date_sub<span class="cm-bracket">(</span><span class="cm-string">'2020-07-09'</span><span class="cm-punctuation">,</span> <span class="cm-number">4</span><span class="cm-bracket">)</span><span class="cm-punctuation">;</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-number">2020</span><span class="cm-operator">-</span><span class="cm-number">07</span><span class="cm-operator">-</span><span class="cm-number">05</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">4</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">5</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">select</span> date_sub<span class="cm-bracket">(</span><span class="cm-string">'2020-07-09'</span><span class="cm-punctuation">,</span> <span class="cm-operator">-</span><span class="cm-number">4</span><span class="cm-bracket">)</span><span class="cm-punctuation">;</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">6</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-number">2020</span><span class="cm-operator">-</span><span class="cm-number">07</span><span class="cm-operator">-</span><span class="cm-number">13</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">7</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">8</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">--</span>add_months</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">9</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">select</span> add_months<span class="cm-bracket">(</span><span class="cm-string">'2020-07-09'</span><span class="cm-punctuation">,</span> <span class="cm-operator">-</span><span class="cm-number">1</span><span class="cm-bracket">)</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">10</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-number">2020</span><span class="cm-operator">-</span><span class="cm-number">06</span><span class="cm-operator">-</span><span class="cm-number">09</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">11</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">12</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">select</span> add_months<span class="cm-bracket">(</span><span class="cm-string">'2020-03-31'</span><span class="cm-punctuation">,</span> <span class="cm-operator">-</span><span class="cm-number">1</span><span class="cm-bracket">)</span></span></pre></div><div style="position: relative;" class=""><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">13</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-number">2020</span><span class="cm-operator">-</span><span class="cm-number">02</span><span class="cm-operator">-</span><span class="cm-number">29</span></span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 299px;"/><div class="CodeMirror-gutters" style="height: 299px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 34px;"/></div></div></div></pre><h3 id="54-datediff计算日期差"><span>5.4 datediff：计算日期差</span></h3><p><code>datediff (end_date, start_date)</code><span> 求两个指定日期的差，end_date - start_date</span></p><p><strong><span>end_date/start_date的日期格式必须是'yyyy-MM-dd'格式</span></strong></p><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="Apache"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="apache"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 33.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 26px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"/><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation"><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -25.9929px; width: 26px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-def">SELECT</span><span class="cm-variable"> datediff('2019-07-23', '2019-07-01');</span></span></pre></div><div style="position: relative;" class=""><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-def">22</span></span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 46px;"/><div class="CodeMirror-gutters" style="height: 46px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 25px;"/></div></div></div></pre><h3 id="55-trunc返回当月第一天返回双月第一天"><span>5.5 trunc：返回</span><strong><span>当月第一天/返回双月第一天</span></strong></h3><p><strong><span>trunc('${DATE}', 'MM')</span></strong></p><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="SQL" style="break-inside: unset;"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 42.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 35px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>15</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation" style=""><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -34.9929px; width: 35px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">返回当月第一天</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">select</span> trunc<span class="cm-bracket">(</span><span class="cm-string">'2020-07-09'</span><span class="cm-punctuation">,</span> <span class="cm-string">'MM'</span><span class="cm-bracket">)</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-number">2020</span><span class="cm-operator">-</span><span class="cm-number">07</span><span class="cm-operator">-</span><span class="cm-number">01</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">4</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">5</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">返回上个月第一天</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">6</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">select</span> trunc<span class="cm-bracket">(</span>add_months<span class="cm-bracket">(</span><span class="cm-string">'2020-07-09'</span><span class="cm-punctuation">,</span> <span class="cm-operator">-</span><span class="cm-number">1</span><span class="cm-bracket">)</span><span class="cm-punctuation">,</span> <span class="cm-string">'MM'</span><span class="cm-bracket">)</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">7</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-number">2020</span><span class="cm-operator">-</span><span class="cm-number">06</span><span class="cm-operator">-</span><span class="cm-number">01</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">8</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">9</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">返回上个双月第一天</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">10</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">select</span> trunc<span class="cm-bracket">(</span><span class="cm-keyword">if</span><span class="cm-bracket">(</span>month<span class="cm-bracket">(</span><span class="cm-string">'${DATE}'</span><span class="cm-bracket">)</span><span class="cm-operator">%</span><span class="cm-number">2</span> <span class="cm-operator">=</span> <span class="cm-number">0</span><span class="cm-punctuation">,</span> add_months<span class="cm-bracket">(</span><span class="cm-string">'${DATE}'</span><span class="cm-punctuation">,</span> <span class="cm-operator">-</span><span class="cm-number">3</span><span class="cm-bracket">)</span><span class="cm-punctuation">,</span> add_months<span class="cm-bracket">(</span><span class="cm-string">'${DATE}'</span><span class="cm-punctuation">,</span> <span class="cm-operator">-</span><span class="cm-number">2</span><span class="cm-bracket">))</span><span class="cm-punctuation">,</span> <span class="cm-string">'MM'</span><span class="cm-bracket">))</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">11</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-number">2022</span><span class="cm-operator">-</span><span class="cm-number">01</span><span class="cm-operator">-</span><span class="cm-number">01</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">12</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">13</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">返回上个双月最后一天</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">14</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">select</span> date_sub<span class="cm-bracket">(</span>trunc<span class="cm-bracket">(</span><span class="cm-keyword">if</span><span class="cm-bracket">(</span>month<span class="cm-bracket">(</span><span class="cm-string">'${DATE}'</span><span class="cm-bracket">)</span><span class="cm-operator">%</span><span class="cm-number">2</span> <span class="cm-operator">=</span> <span class="cm-number">0</span><span class="cm-punctuation">,</span> add_months<span class="cm-bracket">(</span><span class="cm-string">'${DATE}'</span><span class="cm-punctuation">,</span> <span class="cm-operator">-</span><span class="cm-number">1</span><span class="cm-bracket">)</span><span class="cm-punctuation">,</span> add_months<span class="cm-bracket">(</span><span class="cm-string">'${DATE}'</span><span class="cm-punctuation">,</span> <span class="cm-number">0</span><span class="cm-bracket">))</span><span class="cm-punctuation">,</span> <span class="cm-string">'MM'</span><span class="cm-bracket">)</span><span class="cm-punctuation">,</span> <span class="cm-number">1</span><span class="cm-bracket">))</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">15</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-number">2022</span><span class="cm-operator">-</span><span class="cm-number">02</span><span class="cm-operator">-</span><span class="cm-number">28</span></span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 369px;"/><div class="CodeMirror-gutters" style="height: 369px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 34px;"/></div></div></div></pre><p> </p><h2 id="六行转列"><span>六、行转列</span></h2><h3 id="61-explode单字段行转列"><span>6.1 explode：单字段行转列</span></h3><p><code>explode(array)</code><span>：将一行数组转成多列，只支持单个字段的行转列</span></p><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 33.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 26px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>1</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation"><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -25.9929px; width: 26px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> explode<span class="cm-bracket">(</span>array<span class="cm-bracket">(</span><span class="cm-number">0</span><span class="cm-punctuation">,</span><span class="cm-number">1</span><span class="cm-punctuation">,</span><span class="cm-number">2</span><span class="cm-bracket">))</span> <span class="cm-keyword">as</span> num</span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 23px;"/><div class="CodeMirror-gutters" style="height: 23px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 25px;"/></div></div></div></pre><figure><table><thead><tr><th><span>num</span></th></tr></thead><tbody><tr><td><span>0</span></td></tr><tr><td><span>1</span></td></tr><tr><td><span>2</span></td></tr></tbody></table></figure><h3 id="62-lateral-view-explode行转列"><span>6.2 lateral view explode：行转列</span></h3><p><code>lateral view explode(array(0,1,2))</code><span>：将某列字段切分成多列，其他字段均不变</span></p><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 33.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 26px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>1</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation" style=""><div style="position: relative;" class="CodeMirror-activeline"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -25.9929px; width: 26px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    user_id<span class="cm-punctuation">,</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    vid2</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">4</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">FROM</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">5</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    dm_homed<span class="cm-variable-2">.ods_server_impressions_log_hourly</span> </span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">6</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    lateral <span class="cm-keyword">view</span> explode<span class="cm-bracket">(</span>split<span class="cm-bracket">(</span>version_id<span class="cm-punctuation">,</span> <span class="cm-string">','</span><span class="cm-bracket">))</span> vid_table <span class="cm-keyword">as</span> vid2</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">7</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">WHERE</span></span></pre></div><div class="" style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">8</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    <span class="cm-type">date</span> <span class="cm-operator">=</span> <span class="cm-string">'${date}'</span></span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 184px;"/><div class="CodeMirror-gutters" style="height: 184px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 25px;"/></div></div></div></pre><figure><table><thead><tr><th><span>user_id</span></th><th><span>vid2</span></th></tr></thead><tbody><tr><td><span>111</span></td><td><span>1201</span></td></tr><tr><td><span>111</span></td><td><span>1202</span></td></tr><tr><td><span>111</span></td><td><span>1203</span></td></tr><tr><td><span>111</span></td><td><span>1204</span></td></tr></tbody></table></figure><h3 id="63-lateral-view-posexplode行转列带顺序"><span>6.3 lateral view posexplode：行转列，带顺序</span></h3><p><mark><strong><span>场景1：行转列，并带上每个元素在数组中的顺序</span></strong></mark></p><figure><table><thead><tr><th><span>rule_id</span></th><th><span>prod_list</span></th></tr></thead><tbody><tr><td><span>111</span></td><td><span>1203, 1202, 1201,1204</span></td></tr></tbody></table></figure><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="SQL"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 42.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 35px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"/><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation" style=""><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -34.9929px; width: 35px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">WITH</span> prod_table <span class="cm-keyword">AS</span> <span class="cm-bracket">(</span></span></pre></div><div style="position: relative;" class=""><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    <span class="cm-keyword">SELECT</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">        <span class="cm-string">'111'</span> <span class="cm-keyword">as</span> rule_id<span class="cm-punctuation">,</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">4</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">        <span class="cm-string">'1203,1202,1201,1204'</span> <span class="cm-keyword">as</span> prod_list</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">5</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-bracket">)</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">6</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">7</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">8</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    rule_id<span class="cm-punctuation">,</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">9</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    product_id<span class="cm-punctuation">,</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">10</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    rnk</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">11</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">FROM</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">12</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    prod_table</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">13</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    lateral <span class="cm-keyword">view</span> posexplode<span class="cm-bracket">(</span>split<span class="cm-bracket">(</span>prod_list<span class="cm-punctuation">,</span> <span class="cm-string">','</span><span class="cm-bracket">))</span> p <span class="cm-keyword">as</span> rnk<span class="cm-punctuation">,</span> product_id</span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 299px;"/><div class="CodeMirror-gutters" style="height: 299px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 34px;"/></div></div></div></pre><figure><table><thead><tr><th><span>rule_id</span></th><th><span>product_id</span></th><th><span>rnk</span></th></tr></thead><tbody><tr><td><span>111</span></td><td><span>1203</span></td><td><span>0</span></td></tr><tr><td><span>111</span></td><td><span>1202</span></td><td><span>1</span></td></tr><tr><td><span>111</span></td><td><span>1201</span></td><td><span>2</span></td></tr><tr><td><span>111</span></td><td><span>1204</span></td><td><span>3</span></td></tr></tbody></table></figure><p><strong><mark><span>场景2：两行同时行转列，并按数组中相同的顺序放置为1行</span></mark></strong></p><figure><table><thead><tr><th><span>rule_id</span></th><th><span>prod_list</span></th><th><span>gmv_list</span></th></tr></thead><tbody><tr><td><span>111</span></td><td><span>1203, 1202, 1201,1204</span></td><td><span>300,200,100,400</span></td></tr></tbody></table></figure><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="SQL" style="break-inside: unset;"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 42.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 35px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"/><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation" style=""><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -34.9929px; width: 35px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">WITH</span> prod_table <span class="cm-keyword">AS</span> <span class="cm-bracket">(</span></span></pre></div><div style="position: relative;" class=""><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    <span class="cm-keyword">SELECT</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">        <span class="cm-string">'111'</span> <span class="cm-keyword">as</span> rule_id<span class="cm-punctuation">,</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">4</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">        <span class="cm-string">'1203,1202,1201,1204'</span> <span class="cm-keyword">as</span> prod_list<span class="cm-punctuation">,</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">5</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">        <span class="cm-string">'300,200,100,400'</span> <span class="cm-keyword">as</span> gmv_list</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">6</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-bracket">)</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">7</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">8</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">9</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    rule_id<span class="cm-punctuation">,</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">10</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    product_id<span class="cm-punctuation">,</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">11</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    gmv</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">12</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">FROM</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">13</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    prod_table</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">14</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    lateral <span class="cm-keyword">view</span> posexplode<span class="cm-bracket">(</span>split<span class="cm-bracket">(</span>prod_list<span class="cm-punctuation">,</span> <span class="cm-string">','</span><span class="cm-bracket">))</span> p <span class="cm-keyword">as</span> rnk1<span class="cm-punctuation">,</span> product_id</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">15</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    lateral <span class="cm-keyword">view</span> posexplode<span class="cm-bracket">(</span>split<span class="cm-bracket">(</span>gmv_list<span class="cm-punctuation">,</span> <span class="cm-string">','</span><span class="cm-bracket">))</span> p <span class="cm-keyword">as</span> rnk2<span class="cm-punctuation">,</span> gmv</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">16</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">WHERE</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">17</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    rnk1 <span class="cm-operator">=</span> rnk2</span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 392px;"/><div class="CodeMirror-gutters" style="height: 392px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 34px;"/></div></div></div></pre><figure><table><thead><tr><th><span>rule_id</span></th><th><span>product_id</span></th><th><span>gmv</span></th></tr></thead><tbody><tr><td><span>111</span></td><td><span>1203</span></td><td><span>300</span></td></tr><tr><td><span>111</span></td><td><span>1202</span></td><td><span>200</span></td></tr><tr><td><span>111</span></td><td><span>1201</span></td><td><span>100</span></td></tr><tr><td><span>111</span></td><td><span>1204</span></td><td><span>400</span></td></tr></tbody></table></figure><h2 id="七分位数函数"><span>七、分位数函数</span></h2><h3 id="71-percentile--percentileapprox"><span>7.1 percentile & percentile_approx</span></h3><p><code>percentile(col, pth)</code><span>：求准确的第pth个百分位数</span></p><p><code>percentile_approx(col, pth, acc)</code><span>：求近似的第pth个百分位数，acc默认10000（万分之一）</span></p><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="SQL"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 33.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 26px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>5</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation" style=""><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -25.9929px; width: 26px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">--</span>score精确的25分位数</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> percentile<span class="cm-bracket">(</span>score<span class="cm-punctuation">,</span> <span class="cm-number">0.25</span><span class="cm-bracket">)</span><span class="cm-punctuation">;</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">4</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">--</span>score近似的25分位数，精度默认10000（万分之一），在数据量较大的情况下近似计算更快</span></pre></div><div style="position: relative;" class=""><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">5</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> percentile_approx<span class="cm-bracket">(</span>score<span class="cm-punctuation">,</span> <span class="cm-number">0.25</span><span class="cm-punctuation">,</span> <span class="cm-number">10000</span><span class="cm-bracket">)</span><span class="cm-punctuation">;</span></span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 115px;"/><div class="CodeMirror-gutters" style="height: 115px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 25px;"/></div></div></div></pre><h2 id="八json函数"><span>八、json函数</span></h2><h3 id="81-getjsonobject获取json对象的value"><span>8.1 get_json_object：获取json对象的value</span></h3><p><code>get_json_object (json_str, '$.key1')</code></p><ul><li><span>json_str：填写json对象变量</span></li></ul><ul><li><span>key1：填写要获取json的key值</span></li></ul><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 33.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 26px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>5</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation" style=""><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -25.9929px; width: 26px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> get_json_object<span class="cm-bracket">(</span><span class="cm-string">'{"sku_id":712557421,"stock_num":100}'</span><span class="cm-punctuation">,</span> <span class="cm-string">'$.sku_id'</span><span class="cm-bracket">)</span><span class="cm-punctuation">;</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-comment"># 712557421</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">4</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> get_json_object<span class="cm-bracket">(</span><span class="cm-string">'{"sku_id":712557421,"stock_num":100}'</span><span class="cm-punctuation">,</span> <span class="cm-string">'$.stock_num'</span><span class="cm-bracket">)</span><span class="cm-punctuation">;</span></span></pre></div><div style="position: relative;" class=""><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">5</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-comment"># 100</span></span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 115px;"/><div class="CodeMirror-gutters" style="height: 115px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 25px;"/></div></div></div></pre><h3 id="82-jsontuple一次获取多个对象的value"><span>8.2 json_tuple：一次获取多个对象的value</span></h3><p><code>json_tuple(json_str, key1, key2, ...)</code></p><ul><li><span>json_str：填写json对象变量</span></li></ul><ul><li><span>key1：填写要获取json的key值</span></li></ul><ul><li><span>key2：填写要获取json的key值</span></li></ul><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 33.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 26px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>9</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation" style=""><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -25.9929px; width: 26px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">--</span>json</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    sku_id<span class="cm-punctuation">,</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">4</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    stock_num</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">5</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">FROM</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">6</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-bracket">(</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">7</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    <span class="cm-keyword">SELECT</span> <span class="cm-string">'{"sku_id":7125,"stock_num":0}'</span> <span class="cm-keyword">as</span> sku</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">8</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-bracket">)</span> a</span></pre></div><div style="position: relative;" class=""><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">9</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">lateral <span class="cm-keyword">view</span> json_tuple<span class="cm-bracket">(</span>sku<span class="cm-punctuation">,</span> <span class="cm-string">'sku_id'</span><span class="cm-punctuation">,</span> <span class="cm-string">'stock_num'</span><span class="cm-bracket">)</span> a <span class="cm-keyword">as</span> sku_id<span class="cm-punctuation">,</span> stock_num</span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 207px;"/><div class="CodeMirror-gutters" style="height: 207px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 25px;"/></div></div></div></pre><figure><table><thead><tr><th><span>sku_id</span></th><th><span>stock_num</span></th></tr></thead><tbody><tr><td><span>7125</span></td><td><span>0</span></td></tr></tbody></table></figure><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql" style="break-inside: unset;"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 42.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 35px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>1</div></div><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>19</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation" style=""><div style="position: relative;" class="CodeMirror-activeline"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -34.9929px; width: 35px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-operator">--</span>解析数组格式的json</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    sku_id<span class="cm-punctuation">,</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">4</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    stock_num</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">5</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">FROM</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">6</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-bracket">(</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">7</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    <span class="cm-keyword">SELECT</span> </span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">8</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">        regexp_replace<span class="cm-bracket">(</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">9</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">            regexp_replace<span class="cm-bracket">(</span>sku<span class="cm-punctuation">,</span> <span class="cm-string">'\\[|\\]'</span><span class="cm-punctuation">,</span> <span class="cm-string">''</span><span class="cm-bracket">)</span><span class="cm-punctuation">,</span>   <span class="cm-operator">--</span>替换括号 </span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">10</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">            <span class="cm-string">'\\},\\{'</span><span class="cm-punctuation">,</span>  <span class="cm-string">'\\}###\\{'</span>               <span class="cm-operator">--</span>替换逗号，为了切分准备</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">11</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">        <span class="cm-bracket">)</span> <span class="cm-keyword">as</span> sku_list</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">12</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    <span class="cm-keyword">FROM</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">13</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    <span class="cm-bracket">(</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">14</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">        <span class="cm-keyword">SELECT</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">15</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">            <span class="cm-string">'[{"sku_id":7125,"stock_num":0},{"sku_id":7126,"stock_num":10}]'</span> <span class="cm-keyword">as</span> sku</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">16</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">    <span class="cm-bracket">)</span> a</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">17</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-bracket">)</span> a</span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">18</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">lateral <span class="cm-keyword">view</span> explode<span class="cm-bracket">(</span>split<span class="cm-bracket">(</span>sku_list<span class="cm-punctuation">,</span> <span class="cm-string">'###'</span><span class="cm-bracket">))</span> sku_infos <span class="cm-keyword">as</span> sku   <span class="cm-operator">--</span>通过<span class="cm-comment">###切分</span></span></pre></div><div class="" style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">19</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;">lateral <span class="cm-keyword">view</span> json_tuple<span class="cm-bracket">(</span>sku<span class="cm-punctuation">,</span> <span class="cm-string">'sku_id'</span><span class="cm-punctuation">,</span> <span class="cm-string">'stock_num'</span><span class="cm-bracket">)</span> a <span class="cm-keyword">as</span> sku_id<span class="cm-punctuation">,</span> stock_num</span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 438px;"/><div class="CodeMirror-gutters" style="height: 438px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 34px;"/></div></div></div></pre><figure><table><thead><tr><th><span>sku_id</span></th><th><span>stock_num</span></th></tr></thead><tbody><tr><td><span>7125</span></td><td><span>0</span></td></tr><tr><td><span>7126</span></td><td><span>10</span></td></tr></tbody></table></figure><h2 id="九其他"><span>九、其他 </span></h2><h3 id="91-greatest多列最大值"><span>9.1 greatest：多列</span><strong><span>最大值</span></strong></h3><p><span>greatest (col1, col2, ...)：求多列的最大值</span></p><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 33.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 26px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>2</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation"><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -25.9929px; width: 26px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> greatest<span class="cm-bracket">(</span><span class="cm-number">1</span><span class="cm-punctuation">,</span> <span class="cm-number">2</span><span class="cm-punctuation">,</span> <span class="cm-number">3</span><span class="cm-punctuation">,</span> <span class="cm-number">4</span><span class="cm-bracket">)</span><span class="cm-punctuation">;</span></span></pre></div><div style="position: relative;" class=""><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-comment"># 4</span></span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 46px;"/><div class="CodeMirror-gutters" style="height: 46px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 25px;"/></div></div></div></pre><h3 id="92-rand随机函数"><span>9.2 rand：随机函数</span></h3><ul><li><code>rand ()</code><span>：返回一个0到1范围内的随机数，返回值: double</span></li></ul><ul><li><code>rand(int seed)</code><span>：如果指定种子seed，则会等到一个稳定的随机数序列</span></li></ul><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 42.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 35px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>11</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation" style=""><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -34.9929px; width: 35px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> rand<span class="cm-bracket">()</span><span class="cm-punctuation">;</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-number">0.590256460382232</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">4</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> rand<span class="cm-bracket">()</span><span class="cm-punctuation">;</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">5</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-number">0.699686875752123</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">6</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">7</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> rand<span class="cm-bracket">(</span><span class="cm-number">100</span><span class="cm-bracket">)</span><span class="cm-punctuation">;</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">8</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-number">0.6841403791584381</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 27px;">9</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">10</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">SELECT</span> rand<span class="cm-bracket">(</span><span class="cm-number">100</span><span class="cm-bracket">)</span><span class="cm-punctuation">;</span></span></pre></div><div style="position: relative;" class=""><div class="CodeMirror-gutter-wrapper" style="left: -34.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 27px;">11</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-number">0.6841403791584381</span></span></pre></div></div></div></div></div></div><div style="position: absolute; ; width: 1px; border-bottom: 0px solid transparent; top: 253px;"/><div class="CodeMirror-gutters" style="height: 253px;"><div class="CodeMirror-gutter CodeMirror-linenumbers" style="width: 34px;"/></div></div></div></pre><h3 id="93-maxpt读取表中最大分区"><span>9.3 max_pt：读取表中最大分区</span></h3><p><span>max_pt('库名.表名', '分区名', '分区格式')：返回表中最大分区</span></p><ul><li><p><span>优势：函数会读取hive表最大分区，重跑时可有效避免空分区</span></p></li><li><p><span>劣势：数据回溯时，不能保证取当时单天的，取的最新数据</span></p></li><li><p><span>注意：分区名需为string格式，不能为bigint，所以尽量把分区格式设成 string</span></p></li><li><p><span>使用场景：</span></p><ul><li><span>分区数据不会缺失的表，建议还是用date  = '${date}'</span></li><li><span>对于分区不全的表，可能在某些时间段没有分区的，为了避免数据为空，建议用max_pt</span></li></ul></li></ul><pre class="md-fences md-end-block md-fences-with-lineno ty-contain-cm modeLoaded" spellcheck="false" lang="sql"><div class="CodeMirror cm-s-inner cm-s-null-scroll CodeMirror-wrap" lang="sql"><div style="overflow: hidden; position: relative; width: 3px; ; top: 9.51786px; left: 33.9857px;"><textarea autocorrect="off" autocapitalize="off" spellcheck="false" tabindex="0" style="position: absolute; bottom: -1em; padding: 0px; width: 1000px; height: 1em; outline: none;"/></div><div class="CodeMirror-scrollbar-filler" cm-not-content="true"/><div class="CodeMirror-gutter-filler" cm-not-content="true"/><div class="CodeMirror-scroll" tabindex="-1"><div class="CodeMirror-sizer" style="margin-left: 26px; margin-bottom: 0px; border-right-width: 0px; padding-right: 0px; padding-bottom: 0px;"><div style="position: relative; top: 0px;"><div class="CodeMirror-lines" role="presentation"><div role="presentation" style="position: relative; outline: none;"><div class="CodeMirror-measure"><pre><span>xxxxxxxxxx</span></pre><div class="CodeMirror-linenumber CodeMirror-gutter-elt"><div>8</div></div></div><div class="CodeMirror-measure"/><div style="position: relative; z-index: 1;"/><div class="CodeMirror-code" role="presentation" style=""><div class="CodeMirror-activeline" style="position: relative;"><div class="CodeMirror-activeline-background CodeMirror-linebackground"/><div class="CodeMirror-gutter-background CodeMirror-activeline-gutter" style="left: -25.9929px; width: 26px;"/><div class="CodeMirror-gutter-wrapper CodeMirror-activeline-gutter" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt CodeMirror-linenumber-show" style="left: 0px; width: 18px;">1</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-keyword">show</span> <span class="cm-keyword">partitions</span> dm_temai<span class="cm-variable-2">.repeat_product_records_dict</span><span class="cm-punctuation">;</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">2</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-type">date</span><span class="cm-operator">=</span><span class="cm-number">20200712</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">3</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-type">date</span><span class="cm-operator">=</span><span class="cm-number">20200713</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">4</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-type">date</span><span class="cm-operator">=</span><span class="cm-number">20200714</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">5</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span class="cm-type">date</span><span class="cm-operator">=</span><span class="cm-number">20200715</span></span></pre></div><div style="position: relative;"><div class="CodeMirror-gutter-wrapper" style="left: -25.9929px;"><div class="CodeMirror-linenumber CodeMirror-gutter-elt" style="left: 0px; width: 18px;">6</div></div><pre class=" CodeMirror-line " role="presentation"><span role="presentation" style="padding-right: 0.1px;"><span cm-text="" cm-zwsp="">
HiveQL/SparQL 常用函数 (adsbygoogle = window.adsbygoogle || []).push({});

一、窗口函数

1.1 基础结构

1.2 row_number/rank/dense_rank：排序函数

HiveQL/SparQL 常用函数
