Google Git
Sign in
apache / spark-website / 2c562896505588421cce257d111c8ba3805528c2 / . / site / docs / 3.4.3 / generated-conditional-funcs-examples.html
blob: 3c3f0d5ebe82b5e4aefb70fbf15dbffbfd5e1bfb [file] [log] [blame]
<div class="codehilite"><pre><span></span><code><span class="c1">-- coalesce</span>
<span class="k">SELECT</span><span class="w"> </span><span class="k">coalesce</span><span class="p">(</span><span class="k">NULL</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="k">NULL</span><span class="p">);</span>
<span class="o">+</span><span class="c1">-----------------------+</span>
<span class="o">|</span><span class="k">coalesce</span><span class="p">(</span><span class="k">NULL</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="k">NULL</span><span class="p">)</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------------------+</span>
<span class="c1">-- if</span>
<span class="k">SELECT</span><span class="w"> </span><span class="k">if</span><span class="p">(</span><span class="mi">1</span><span class="w"> </span><span class="o">&lt;</span><span class="w"> </span><span class="mi">2</span><span class="p">,</span><span class="w"> </span><span class="s1">&#39;a&#39;</span><span class="p">,</span><span class="w"> </span><span class="s1">&#39;b&#39;</span><span class="p">);</span>
<span class="o">+</span><span class="c1">-------------------+</span>
<span class="o">|</span><span class="p">(</span><span class="k">IF</span><span class="p">((</span><span class="mi">1</span><span class="w"> </span><span class="o">&lt;</span><span class="w"> </span><span class="mi">2</span><span class="p">),</span><span class="w"> </span><span class="n">a</span><span class="p">,</span><span class="w"> </span><span class="n">b</span><span class="p">))</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="n">a</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------------+</span>
<span class="c1">-- ifnull</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">ifnull</span><span class="p">(</span><span class="k">NULL</span><span class="p">,</span><span class="w"> </span><span class="nb">array</span><span class="p">(</span><span class="s1">&#39;2&#39;</span><span class="p">));</span>
<span class="o">+</span><span class="c1">----------------------+</span>
<span class="o">|</span><span class="n">ifnull</span><span class="p">(</span><span class="k">NULL</span><span class="p">,</span><span class="w"> </span><span class="nb">array</span><span class="p">(</span><span class="mi">2</span><span class="p">))</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="p">[</span><span class="mi">2</span><span class="p">]</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------------------+</span>
<span class="c1">-- nanvl</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">nanvl</span><span class="p">(</span><span class="k">cast</span><span class="p">(</span><span class="s1">&#39;NaN&#39;</span><span class="w"> </span><span class="k">as</span><span class="w"> </span><span class="n">double</span><span class="p">),</span><span class="w"> </span><span class="mi">123</span><span class="p">);</span>
<span class="o">+</span><span class="c1">-------------------------------+</span>
<span class="o">|</span><span class="n">nanvl</span><span class="p">(</span><span class="k">CAST</span><span class="p">(</span><span class="n">NaN</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">DOUBLE</span><span class="p">),</span><span class="w"> </span><span class="mi">123</span><span class="p">)</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="mi">123</span><span class="p">.</span><span class="mi">0</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------------------------+</span>
<span class="c1">-- nullif</span>
<span class="k">SELECT</span><span class="w"> </span><span class="k">nullif</span><span class="p">(</span><span class="mi">2</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">);</span>
<span class="o">+</span><span class="c1">------------+</span>
<span class="o">|</span><span class="k">nullif</span><span class="p">(</span><span class="mi">2</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">)</span><span class="o">|</span>
<span class="o">+</span><span class="c1">------------+</span>
<span class="o">|</span><span class="w"> </span><span class="k">null</span><span class="o">|</span>
<span class="o">+</span><span class="c1">------------+</span>
<span class="c1">-- nvl</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">nvl</span><span class="p">(</span><span class="k">NULL</span><span class="p">,</span><span class="w"> </span><span class="nb">array</span><span class="p">(</span><span class="s1">&#39;2&#39;</span><span class="p">));</span>
<span class="o">+</span><span class="c1">-------------------+</span>
<span class="o">|</span><span class="n">nvl</span><span class="p">(</span><span class="k">NULL</span><span class="p">,</span><span class="w"> </span><span class="nb">array</span><span class="p">(</span><span class="mi">2</span><span class="p">))</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="p">[</span><span class="mi">2</span><span class="p">]</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------------+</span>
<span class="c1">-- nvl2</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">nvl2</span><span class="p">(</span><span class="k">NULL</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">);</span>
<span class="o">+</span><span class="c1">----------------+</span>
<span class="o">|</span><span class="n">nvl2</span><span class="p">(</span><span class="k">NULL</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">)</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------------+</span>
<span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------------+</span>
<span class="c1">-- when</span>
<span class="k">SELECT</span><span class="w"> </span><span class="k">CASE</span><span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="o">&gt;</span><span class="w"> </span><span class="mi">0</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="mi">2</span><span class="w"> </span><span class="o">&gt;</span><span class="w"> </span><span class="mi">0</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="mi">2</span><span class="p">.</span><span class="mi">0</span><span class="w"> </span><span class="k">ELSE</span><span class="w"> </span><span class="mi">1</span><span class="p">.</span><span class="mi">2</span><span class="w"> </span><span class="k">END</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-----------------------------------------------------------+</span>
<span class="o">|</span><span class="k">CASE</span><span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="p">(</span><span class="mi">1</span><span class="w"> </span><span class="o">&gt;</span><span class="w"> </span><span class="mi">0</span><span class="p">)</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="p">(</span><span class="mi">2</span><span class="w"> </span><span class="o">&gt;</span><span class="w"> </span><span class="mi">0</span><span class="p">)</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="mi">2</span><span class="p">.</span><span class="mi">0</span><span class="w"> </span><span class="k">ELSE</span><span class="w"> </span><span class="mi">1</span><span class="p">.</span><span class="mi">2</span><span class="w"> </span><span class="k">END</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------------------------------------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="p">.</span><span class="mi">0</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------------------------------------------------------+</span>
<span class="k">SELECT</span><span class="w"> </span><span class="k">CASE</span><span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="o">&lt;</span><span class="w"> </span><span class="mi">0</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="mi">2</span><span class="w"> </span><span class="o">&gt;</span><span class="w"> </span><span class="mi">0</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="mi">2</span><span class="p">.</span><span class="mi">0</span><span class="w"> </span><span class="k">ELSE</span><span class="w"> </span><span class="mi">1</span><span class="p">.</span><span class="mi">2</span><span class="w"> </span><span class="k">END</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-----------------------------------------------------------+</span>
<span class="o">|</span><span class="k">CASE</span><span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="p">(</span><span class="mi">1</span><span class="w"> </span><span class="o">&lt;</span><span class="w"> </span><span class="mi">0</span><span class="p">)</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="p">(</span><span class="mi">2</span><span class="w"> </span><span class="o">&gt;</span><span class="w"> </span><span class="mi">0</span><span class="p">)</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="mi">2</span><span class="p">.</span><span class="mi">0</span><span class="w"> </span><span class="k">ELSE</span><span class="w"> </span><span class="mi">1</span><span class="p">.</span><span class="mi">2</span><span class="w"> </span><span class="k">END</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------------------------------------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="mi">2</span><span class="p">.</span><span class="mi">0</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------------------------------------------------------+</span>
<span class="k">SELECT</span><span class="w"> </span><span class="k">CASE</span><span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="o">&lt;</span><span class="w"> </span><span class="mi">0</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="mi">2</span><span class="w"> </span><span class="o">&lt;</span><span class="w"> </span><span class="mi">0</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="mi">2</span><span class="p">.</span><span class="mi">0</span><span class="w"> </span><span class="k">END</span><span class="p">;</span>
<span class="o">+</span><span class="c1">--------------------------------------------------+</span>
<span class="o">|</span><span class="k">CASE</span><span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="p">(</span><span class="mi">1</span><span class="w"> </span><span class="o">&lt;</span><span class="w"> </span><span class="mi">0</span><span class="p">)</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="k">WHEN</span><span class="w"> </span><span class="p">(</span><span class="mi">2</span><span class="w"> </span><span class="o">&lt;</span><span class="w"> </span><span class="mi">0</span><span class="p">)</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="mi">2</span><span class="p">.</span><span class="mi">0</span><span class="w"> </span><span class="k">END</span><span class="o">|</span>
<span class="o">+</span><span class="c1">--------------------------------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="k">null</span><span class="o">|</span>
<span class="o">+</span><span class="c1">--------------------------------------------------+</span>
</code></pre></div>