https://community.databricks.com/t5/technical-blog/highly-selective-sql-refined-beyond-the-where/ba-p/64459 <P class="lia-align-center"><FONT face="comic sans ms,sans-serif">Inuktitut, the language of the Inuit, has 50 words for snow and ice.</FONT></P> <P><A href="https://readable.com/blog/do-inuits-really-have-50-words-for-snow/" target="_self">That’s - as they say - fake news</A>, but the point made is metaphorical:</P> <P class="lia-align-center"><FONT face="comic sans ms,sans-serif">When something is important to a people, their language finds a way to become efficient on that topic.</FONT></P> <P>SQL is all about transforming and filtering data. So it should come as no surprise that SQL has sprouted a whole lot more ways to eliminate datapoints than the humble <STRONG><FONT face="andale mono,times">WHERE</FONT></STRONG>. In this post we broaden our palate to speak SQL a bit more … refined.</P> <P><span class="lia-inline-image-display-wrapper lia-image-align-center" image-alt="filterserge.png" style="width: 351px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/6864i711C5CDE2E6CFC53/image-size/medium?v=v2&amp;px=400" role="button" title="filterserge.png" alt="filterserge.png" /></span></P> <H1>Warm Up</H1> <P data-unlink="true"><STRONG>Note:</STRONG><SPAN>&nbsp;You can find all examples to run&nbsp;</SPAN><A href="https://github.com/srielau/notebooks/blob/main/Highly%20selective_%20SQL%20Refined%20beyond%20the%20WHERE.sql" target="_self">here</A><SPAN>.</SPAN></P> <P>The obvious and most SQL way to filter rows from a table is the <STRONG><FONT face="andale mono,times">WHERE&nbsp;</FONT></STRONG>clause.</P> <DIV> <PRE><SPAN><FONT color="#008000">DROP</FONT> <FONT color="#008000">TABLE IF EXISTS</FONT> <FONT color="#FF9900">products</FONT>;<BR /><FONT color="#008000">CREATE</FONT></SPAN><FONT color="#008000"> <SPAN>TABLE</SPAN></FONT> <FONT color="#FF9900"><SPAN>products</SPAN></FONT><SPAN>(</SPAN><FONT color="#FF9900"><SPAN>name</SPAN></FONT><SPAN><FONT color="#008000"> STRING</FONT>, <FONT color="#FF9900">category</FONT> <FONT color="#008000">STRING,</FONT> <FONT color="#FF9900">price</FONT> </SPAN><FONT color="#008000"><SPAN>INTEGER</SPAN></FONT><SPAN><FONT color="#008000">,</FONT> <FONT color="#FF9900">units</FONT> <FONT color="#008000">INTEGER,</FONT> </SPAN><FONT color="#FF9900"><SPAN>description</SPAN></FONT><SPAN> <FONT color="#008000">STRING);</FONT></SPAN><BR /><SPAN><FONT color="#008000">INSERT INTO</FONT></SPAN><SPAN> <FONT color="#FF9900">products</FONT> </SPAN><FONT color="#008000"><SPAN>VALUES</SPAN></FONT><BR /><FONT color="#000000"><SPAN>(</SPAN><SPAN>'colander'</SPAN></FONT><SPAN> , </SPAN><SPAN>'housewares'</SPAN><SPAN> , </SPAN><SPAN>10</SPAN><SPAN>, 1, </SPAN><SPAN>'Bowl with large holes to drain water from past and vegetables'</SPAN><SPAN>),</SPAN><BR /><SPAN>(</SPAN><SPAN>'coffee filter'</SPAN><SPAN> , </SPAN><SPAN>'house supplies' </SPAN><SPAN>, </SPAN><SPAN>4</SPAN><SPAN>, 20, </SPAN><SPAN>'Holds ground coffee'</SPAN><SPAN> ),</SPAN><BR /><SPAN>(</SPAN><SPAN>'skimmer'</SPAN><SPAN> , </SPAN><SPAN>'pool supplies'</SPAN><SPAN> , </SPAN><SPAN>100</SPAN><SPAN>, 1, </SPAN><SPAN>'Drain filtering leaves from the water surface'</SPAN><SPAN> ),</SPAN><BR /><SPAN>(</SPAN><SPAN>'diatomaceous earth'</SPAN><SPAN> , </SPAN><SPAN>'pool supplies'</SPAN><SPAN> , </SPAN><SPAN>30</SPAN><SPAN>, 0, </SPAN><SPAN>'Coats pool filter to filter impurities from water'</SPAN><SPAN> ),</SPAN><BR /><SPAN>(</SPAN><SPAN>'strainer'</SPAN><SPAN> , </SPAN><SPAN>'house wares'</SPAN><SPAN> , </SPAN><SPAN>15</SPAN><SPAN>, 1, </SPAN><SPAN>'Bowl with a handle and fine holes'</SPAN><SPAN> ),</SPAN><BR /><SPAN>(</SPAN><SPAN>'tea infuser'</SPAN><SPAN> , </SPAN><SPAN>'house wares'</SPAN><SPAN> , </SPAN><SPAN>5</SPAN><SPAN>, 1, </SPAN><SPAN>'Holds loose tea leaves for infusion'</SPAN><SPAN> ),</SPAN><BR /><SPAN>(</SPAN><SPAN>'electrostatic air filter'</SPAN><SPAN>, </SPAN><SPAN>'HVAC hardware'</SPAN><SPAN> , </SPAN><SPAN>10</SPAN><SPAN>, 1, </SPAN><SPAN>'Air filter for HVAC systems'</SPAN><SPAN> <FONT color="#008000">);</FONT><BR /><BR /><FONT color="#008000">SELECT</FONT> * <FONT color="#008000">FROM</FONT> <FONT color="#FF9900">products</FONT> <FONT color="#008000">WHERE</FONT> <FONT color="#FF9900">category</FONT> = 'pool supplies';<BR /><BR /><FONT color="#993366"><U>name </U> </FONT><BR /><FONT color="#993366">skimmer</FONT><BR /><FONT color="#993366">diatomaceous</FONT></SPAN></PRE> </DIV> <P>But even <FONT face="andale mono,times">WHERE</FONT> has some properties that can trip some users up. First, the <FONT face="andale mono,times">WHERE</FONT> clause applies before the <FONT face="andale mono,times">SELECT</FONT> list items are executed. That has consequences:</P> <UL> <LI>The <FONT face="andale mono,times">WHERE</FONT> clause protects expressions in the <FONT face="andale mono,times">SELECT</FONT> list from runtime errors, such as division by zero.</LI> </UL> <PRE><FONT color="#008000">SELECT</FONT> <FONT color="#FF9900">name</FONT>, <FONT color="#FF9900">price</FONT> / <FONT color="#FF9900">units</FONT> <FONT color="#008000">AS</FONT> <FONT color="#FF9900">unitprice</FONT><BR /><FONT color="#008000"> FROM</FONT> <FONT color="#FF9900">products</FONT><BR /><FONT color="#008000"> WHERE</FONT> <FONT color="#FF9900">category</FONT> = 'pool supplies' <FONT color="#008000">AND</FONT> <FONT color="#FF9900">units</FONT> != 0;<BR /><FONT color="#993366"><U>name </U> <U>unitprice</U></FONT><BR /><FONT color="#993366">skimmer 100</FONT></PRE> <UL> <LI>The <FONT face="andale mono,times">WHERE</FONT> clause cannot reference the results (i.e. the column aliases) of the <FONT face="andale mono,times">SELECT</FONT> list.<BR />Where “lateral column alias” within the select is handy, it is outright wrong when applied to the <FONT face="andale mono,times">WHERE</FONT> clause.</LI> </UL> <PRE><FONT color="#008000">SELECT</FONT> <FONT color="#FF9900">name, price</FONT> / nullif(<FONT color="#FF9900">units</FONT>, 0) <FONT color="#008000">AS</FONT> <FONT color="#FF9900">unitprice<BR /></FONT><FONT color="#008000"> FROM</FONT> <FONT color="#FF9900">products<BR /></FONT><FONT color="#008000"> WHERE</FONT> <FONT color="#FF9900">unitprice</FONT> <FONT color="#008000">IS NULL<FONT color="#000000">;</FONT></FONT><BR /><FONT color="#000000">[UNRESOLVED_COLUMN.WITH_SUGGESTION]</FONT> A column, variable, or function parameter with name `unitprice` cannot be resolved.<BR />Did you mean one of the following? [`price`, `units`, `name`, `category`, `description`]. SQLSTATE: 42703</PRE> <P>Second, the <FONT face="andale mono,times">WHERE</FONT> clause is not the first filter to be applied.The first filter to be applied is the <FONT face="andale mono,times"><STRONG>ON</STRONG></FONT> clause of any joins. In many cases this doesn’t matter ...</P> <PRE><FONT color="#008000">SELECT</FONT> <FONT color="#FF9900">name, price</FONT> * <FONT color="#FF9900">number</FONT> <FONT color="#008000">AS</FONT> <FONT color="#FF9900">total</FONT><BR /> <FONT color="#008000">FROM</FONT> <FONT color="#FF9900">products</FONT><BR /> <FONT color="#008000">NATURAL JOIN VALUES</FONT>('skimmer' , 1),<BR /> ('colander', 2) <FONT color="#008000">AS</FONT> <FONT color="#FF9900">orders</FONT>(<FONT color="#FF9900">name</FONT>, <FONT color="#FF9900">number</FONT>)<BR /> <FONT color="#008000">WHERE</FONT> <FONT color="#FF9900">category</FONT> = 'housewares';<BR /><FONT color="#993366"><U>name </U> <U>total</U></FONT><BR /><FONT color="#993366">colander 20</FONT></PRE> <P>... until it does. Most systems, including Databricks, are rather cavalier on that matter.</P> <PRE><FONT color="#008000">SELECT</FONT> <FONT color="#FF9900">name</FONT>, <FONT color="#FF9900">price</FONT> * <FONT color="#FF9900">number</FONT> <FONT color="#008000">AS</FONT> <FONT color="#FF9900">total</FONT><BR /> <FONT color="#008000">FROM</FONT> <FONT color="#FF9900">products</FONT><BR /> <FONT color="#008000">NATURAL JOIN VALUES</FONT>('skimmer' , 1),<BR /> ('colander', 2) <FONT color="#008000">AS</FONT> <FONT color="#FF9900">orders</FONT>(<FONT color="#FF9900">name</FONT>, <FONT color="#FF9900">number</FONT>)<BR /> <FONT color="#008000">WHERE</FONT> <FONT color="#FF9900">price</FONT> / <FONT color="#FF9900">units</FONT> &gt; 1;<BR />[DIVIDE_BY_ZERO] Division by zero. Use `try_divide` to tolerate divisor being 0 and return NULL instead. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error. SQLSTATE: 22012<BR />== SQL (line 5, position 7) ==<BR />WHERE price / units &gt; 1<BR /> ^^^^^^^^^^^^^</PRE> <P><BR />Third, there is no promise of order of execution within the <FONT face="andale mono,times">WHERE</FONT> clause itself. There isn’t even a promise that every expression within a boolean expression is in fact executed!</P> <P>&nbsp;</P> <H1>HAVING</H1> <P>As far as row filtering is concerned the <FONT face="andale mono,times">WHERE</FONT> clause is really all SQL needs. Thanks to nesting of subqueries we can easily answer a question like:</P> <P class="lia-align-center"><FONT face="comic sans ms,sans-serif">Give me all categories with more than one product.</FONT></P> <PRE><FONT color="#008000">SELECT</FONT> *<BR /> <FONT color="#008000">FROM (SELECT</FONT> <FONT color="#FF9900">category</FONT>, count(*) <FONT color="#008000">AS</FONT> <FONT color="#FF9900">num_items</FONT><BR /> <FONT color="#008000">FROM</FONT> <FONT color="#FF9900">products</FONT><BR /> <FONT color="#008000">GROUP</FONT> <FONT color="#008000">BY ALL</FONT>)<BR /> <FONT color="#008000">WHERE</FONT> <FONT color="#FF9900">num_items</FONT> &gt; 1;<BR /><FONT color="#993366"><U>category </U> <U>num_items</U></FONT><BR /><FONT color="#993366">housewares 2</FONT><BR /><FONT color="#993366">pool supplies 2</FONT></PRE> <P>&nbsp;This pattern is so frequent, that SQL sprouted its first extension in order to make this query more succinct:</P> <PRE><FONT color="#008000">SELECT</FONT> <FONT color="#FF9900">category</FONT>, count(*) <FONT color="#008000">AS</FONT> <FONT color="#FF9900">num_items</FONT><BR /> <FONT color="#008000">FROM</FONT> <FONT color="#FF9900">products</FONT><BR /> <FONT color="#008000">GROUP BY ALL</FONT><BR /><FONT color="#008000"> HAVING</FONT> <FONT color="#FF9900">num_items</FONT> &gt; 1;<BR /><FONT color="#993366"><U>category </U> <U>num_items</U></FONT><BR /><FONT color="#993366">housewares 2</FONT><BR /><FONT color="#993366">pool supplies 2</FONT></PRE> <P>The <FONT face="andale mono,times">HAVING</FONT> clause is nothing more than syntactic sugar for:</P> <UL> <LI>pushing a subquery</LI> <LI>selecting all of the subquery's columns, and</LI> <LI>adding a new <FONT face="andale mono,times">WHERE</FONT> clause.</LI> </UL> <P>As far as bedrock ANSI SQL 92 and row filtering is concerned: <FONT face="andale mono,times">ON</FONT>, <FONT face="andale mono,times">WHERE</FONT>, and <FONT face="andale mono,times">HAVING</FONT> are pretty much it. There is of course <FONT face="andale mono,times">LIMIT</FONT> (called&nbsp;<FONT face="andale mono,times">FETCH FIRST n ROWS</FONT>&nbsp;in ANSI)... hold that thought.</P> <P>A more interesting area to look at next is filtering on column values, or more precisely aggregates.</P> <P>&nbsp;</P> <H1>Aggregate level filtering</H1> <P>By default, aggregate functions operate on all rows within a group. For example, we can count products per category.</P> <PRE><FONT color="#008000">SELECT</FONT> <FONT color="#FF9900">category</FONT>, count(*) <FONT color="#008000">AS</FONT> <FONT color="#FF9900">num_items</FONT><BR /> <FONT color="#008000">FROM</FONT> <FONT color="#FF9900">products</FONT> <FONT color="#008000">GROUP BY ALL</FONT>;<BR /><FONT color="#993366"><U>category </U> <U>num_items</U></FONT><BR /><FONT color="#993366">HVAC hardware 1</FONT><BR /><FONT color="#993366">housewares 4</FONT><BR /><FONT color="#993366">pool supplies 2</FONT></PRE> <P>If we want to differentiate between consumables (items we need to keep buying) and non-consumables we need to add that column to the <FONT face="andale mono,times">GROUP BY</FONT>:</P> <PRE><FONT color="#008000">SELECT</FONT> <FONT color="#FF9900">category</FONT>, <FONT color="#FF9900">consumable</FONT>, count(*) <FONT color="#008000">AS</FONT> <FONT color="#FF9900">num_items</FONT><BR /> <FONT color="#008000">FROM</FONT> <FONT color="#FF9900">products</FONT> <FONT color="#008000">GROUP BY ALL</FONT>;<BR /><FONT color="#993366"><U>category </U> <U>consumable</U> <U>num_items</U></FONT><BR /><FONT color="#993366">pool supplies false 1</FONT><BR /><FONT color="#993366">housewares false 3</FONT><BR /><FONT color="#993366">pool supplies true 1</FONT><BR /><FONT color="#993366">HVAC hardware false 1</FONT><BR /><FONT color="#993366">housewares true 1</FONT></PRE> <P>We get two rows per category: One for consumables, one for non-consumables. However, this may not be what we wanted. We may want one line per category and separate columns for consumable and non-consumable.</P> <P>We can pivot the table, but that’s heavy handed. An easier way is to control the aggregation itself:</P> <PRE><FONT color="#008000">SELECT</FONT> <FONT color="#FF9900">category</FONT>,<BR /> sum(<FONT color="#008000">CASE WHEN</FONT> <FONT color="#FF9900">consumable</FONT> <FONT color="#008000">THEN</FONT> 1 <FONT color="#008000">ELSE</FONT> 0 <FONT color="#008000">END) AS</FONT> <FONT color="#FF9900">consumable</FONT>,<BR /> <FONT color="#008000"><FONT color="#000000">sum(</FONT>CASE WHEN</FONT> !<FONT color="#FF9900">consumable</FONT> <FONT color="#008000">THEN</FONT> 1 <FONT color="#008000">ELSE</FONT> 0 <FONT color="#008000">END) AS</FONT> <FONT color="#FF9900">non_consumable</FONT><BR /> <FONT color="#008000">FROM</FONT> <FONT color="#FF9900">products</FONT><BR /> <FONT color="#008000">GROUP BY ALL</FONT>;<BR /><FONT color="#993366"><U>category </U> <U>consumable</U> <U>non_consumable</U></FONT><BR /><FONT color="#993366">HVAC hardware 0 1</FONT><BR /><FONT color="#993366">housewares 1 3</FONT><BR /><FONT color="#993366">pool supplies 1 1</FONT></PRE> <P>There are any number of variations other than <FONT face="andale mono,times">CASE</FONT>.</P> <P>This pattern of conditional counting is so frequently used that a dedicated aggregation function is available to do this with less keystrokes:</P> <PRE><FONT color="#008000">SELECT</FONT> <FONT color="#FF9900">category</FONT>,<BR /> count_if(<FONT color="#FF9900">consumable</FONT>) <FONT color="#008000">AS</FONT> <FONT color="#FF9900">consumable</FONT>,<BR /> count_if(!<FONT color="#FF9900">consumable</FONT>) <FONT color="#008000">AS</FONT> <FONT color="#FF9900">non_consumable</FONT><BR /> <FONT color="#008000">FROM</FONT> <FONT color="#FF9900">products</FONT><BR /> <FONT color="#008000">GROUP BY ALL</FONT>;<BR /><FONT color="#993366"><U>category </U> <U>consumable</U> <U>non_consumable</U></FONT><BR /><FONT color="#993366">HVAC hardware 0 1</FONT><BR /><FONT color="#993366">housewares 1 3</FONT><BR /><FONT color="#993366">pool supplies 1 1</FONT></PRE> <P>Great! But we left all those other aggregations out in the cold. What if we wanted to also aggregate the price of these items?</P> <PRE><FONT color="#008000">SELECT</FONT> <FONT color="#FF9900">category</FONT>,<BR /> count_if(<FONT color="#FF9900">consumable</FONT>) <FONT color="#008000">AS</FONT> <FONT color="#FF9900">consumable</FONT>,<BR /> sum(<FONT color="#008000">CASE WHEN</FONT> <FONT color="#FF9900">consumable</FONT> <FONT color="#008000">THEN</FONT> <FONT color="#FF9900">price</FONT> <FONT color="#008000">ELSE</FONT> 0 <FONT color="#008000">END<FONT color="#000000">)</FONT> AS</FONT> <FONT color="#FF9900">consumable_price</FONT>,<BR /> count_if(!<FONT color="#FF9900">consumable</FONT>) <FONT color="#008000">AS</FONT> <FONT color="#FF9900">non_consumable</FONT>,<BR /> sum(<FONT color="#008000">CASE WHEN</FONT> !<FONT color="#FF9900">consumable</FONT> <FONT color="#008000">THEN</FONT> <FONT color="#FF9900">price</FONT> <FONT color="#008000">ELSE</FONT> 0 <FONT color="#008000">END<FONT color="#000000">)</FONT> AS</FONT> <FONT color="#FF9900">non_consumable_price</FONT><BR /> <FONT color="#008000">FROM</FONT> <FONT color="#FF9900">products</FONT><BR /> <FONT color="#008000">GROUP BY ALL</FONT>;<BR /><FONT color="#993366"><U>category </U> <U>consumable</U> <U>consumable_price</U> <U>non_consumable</U> <U>non_consumable_price</U></FONT><BR /><FONT color="#993366">HVAC hardware 0 0 1 10</FONT><BR /><FONT color="#993366">housewares 1 4 3 30</FONT><BR /><FONT color="#993366">pool supplies 1 30 1 100</FONT></PRE> <P>The language could have spawned <FONT face="andale mono,times">sum_if()</FONT>,<FONT face="andale mono,times"> avg_if()</FONT> etc… just like Databricks added&nbsp;<A href="https://docs.databricks.com/en/sql/language-manual/sql-ref-functions-builtin-alpha.html" target="_self"><FONT face="andale mono,times">try_*</FONT> functions</A>.<BR />Do we really want to introduce:&nbsp;<FONT face="andale mono,times">try_sum_if()</FONT>?</P> <P>It seems any aggregate function could potentially need some sort of postfix that controls whether a value within the group should or should not contribute to the aggregation. There are two choices to here beyond exploding the number of functions:</P> <OL> <LI>Add an optional boolean parameter to each aggregate function</LI> <LI>Add a SQL syntax clause</LI> </OL> <P>Option 2 has won out and <FONT face="andale mono,times">FILTER</FONT> was born.</P> <PRE><FONT color="#008000">SELECT</FONT> <FONT color="#FF9900">category</FONT>,<BR /> count(1) <FONT color="#008000">FILTER<FONT color="#000000">(</FONT>WHERE </FONT> <FONT color="#FF9900">consumable</FONT>) <FONT color="#008000">AS</FONT> <FONT color="#FF9900">consumable</FONT>,<BR /> sum(<FONT color="#FF9900">price</FONT>) <FONT color="#008000">FILTER<FONT color="#000000">(</FONT>WHERE </FONT> <FONT color="#FF9900">consumable</FONT>) <FONT color="#008000">AS</FONT> <FONT color="#FF9900">consumable_price</FONT>,<BR /> count(1) <FONT color="#008000">FILTER</FONT>(<FONT color="#008000">WHERE</FONT> !<FONT color="#FF9900">consumable</FONT>) <FONT color="#008000">AS</FONT> <FONT color="#FF9900">noon_consumable</FONT>,<BR /> sum(<FONT color="#FF9900">price</FONT>) <FONT color="#008000">FILTER</FONT>(<FONT color="#008000">WHERE</FONT> !<FONT color="#FF9900">consumable</FONT>) <FONT color="#008000">AS</FONT> <FONT color="#FF9900">non_consumable_price</FONT><BR /> <FONT color="#008000">FROM</FONT> <FONT color="#FF9900">products</FONT><BR /> <FONT color="#008000">GROUP</FONT> <FONT color="#008000">BY ALL</FONT>;<BR /><FONT color="#993366"><U>category </U> <U>consumable</U> <U>consumable_price</U> <U>noon_consumable</U> <U>non_consumable_price</U></FONT><BR /><FONT color="#993366">HVAC hardware 0 1 </FONT><BR /><FONT color="#993366">housewares 1 4 3 4</FONT><BR /><FONT color="#993366">pool supplies 1 30 1 30</FONT></PRE> <P>Unlike the <FONT face="andale mono,times">CASE</FONT> expression pattern though, <FONT face="andale mono,times">FILTER</FONT> does not rely on the existence of a neutral input argument. It is truly more expressive.</P> <P><STRONG>Note</STRONG>: It's easy to predict that eventually there will be a subquery level <FONT face="andale mono,times">FILTER</FONT> clause, just like the repetitive nature of the window aggregation resulted in the&nbsp;<FONT face="andale mono,times">WINDOW</FONT> clause.</P> <P>Talking of window clauses, let's return to row filtering.</P> <P>&nbsp;</P> <H1>No LIMITs</H1> <P>Earlier we glossed over <FONT face="andale mono,times">LIMIT;&nbsp;</FONT>let’s now have a closer look.</P> <P>Aside from the obvious</P> <P class="lia-align-center"><FONT face="comic sans ms,sans-serif">Give me a page's worth of results only, I’m not going to look at the rest anyway!</FONT></P> <P>use case,&nbsp; there is a common pattern to retrieve to “maximum row".</P> <P class="lia-align-center"><FONT face="comic sans ms,sans-serif">What is the priciest product?&nbsp; And give me that entire row!</FONT></P> <PRE><FONT color="#008000">SELECT</FONT> *<BR /> <FONT color="#008000">FROM</FONT> <FONT color="#FF9900">products</FONT><BR /> <FONT color="#008000">ORDER BY</FONT> <FONT color="#FF9900">price</FONT> <FONT color="#008000">DESC LIMIT</FONT> 1;<BR /><FONT color="#993366"><U>name </U> <U>category </U> <U>price</U> <U>units</U> <U>consumable</U> <U>description </U> </FONT><BR /><FONT color="#993366">skimmer pool supplies 100 1 false Drain filtering leaves from the water surface</FONT></PRE> <P>Alas, things get tricky if we want to return the priciest product by category, and we want more than just the price. The classic solution is use an<FONT face="andale mono,times"> EXISTS</FONT> and a self join:</P> <PRE><FONT color="#008000">SELECT</FONT> p.*<BR /> <FONT color="#008000">FROM</FONT> <FONT color="#FF9900">products</FONT> <FONT color="#008000">AS</FONT> <FONT color="#FF9900">p</FONT><BR /> <FONT color="#008000">WHERE EXISTS</FONT> (<FONT color="#008000">SELECT</FONT> 1 <BR /> <FONT color="#008000">FROM</FONT> <FONT color="#FF9900">products</FONT> <FONT color="#008000">AS</FONT> <FONT color="#FF9900">m</FONT><BR /> <FONT color="#008000">WHERE</FONT> <FONT color="#FF9900">m.category</FONT> = <FONT color="#FF9900">p.category</FONT><BR /> <FONT color="#008000">HAVING</FONT> max(<FONT color="#FF9900">m.price</FONT>) =<FONT color="#FF9900"> p.price</FONT>);<BR /><FONT color="#993366"><U>name </U> <U>category </U> <U>price</U> <U>units</U> <U>consumable</U> <U>description </U> </FONT><BR /><FONT color="#993366">skimmer pool supplies 100 1 false Drain filtering leaves from the water surface</FONT><BR /><FONT color="#993366">strainer housewares 15 1 false Bowl with a handle and fine holes</FONT><BR /><FONT color="#993366">electrostatic air filter HVAC hardware 10 1 false Air filter for HVAC systems</FONT></PRE> <P><BR />Another solution, without the self join, is to use the <FONT face="andale mono,times">max_by()</FONT> aggregate function which returns a value based on another value's max.</P> <PRE><FONT color="#008000">SELECT</FONT> max_by(<FONT color="#FF9900">name</FONT>, <FONT color="#FF9900">price</FONT>) <FONT color="#008000">AS</FONT> <FONT color="#FF9900">name</FONT>,<BR /> <FONT color="#FF9900">category</FONT>,<BR /> max(<FONT color="#FF9900">price</FONT>) <FONT color="#008000">AS</FONT> <FONT color="#FF9900">price</FONT>,<BR /> max_by(<FONT color="#FF9900">units</FONT>, <FONT color="#FF9900">price</FONT>) <FONT color="#008000">AS</FONT> <FONT color="#FF9900">units</FONT>,<BR /> max_by(<FONT color="#FF9900">consumable</FONT>, <FONT color="#FF9900">price</FONT>) <FONT color="#008000">AS</FONT> <FONT color="#FF9900">consumable</FONT>,<BR /> max_by(<FONT color="#FF9900">description</FONT>, <FONT color="#FF9900">price</FONT>) <FONT color="#008000">AS</FONT> <FONT color="#FF9900">description</FONT><BR /> <FONT color="#008000">FROM</FONT> <FONT color="#FF9900">products</FONT><BR /> <FONT color="#008000">GROUP BY ALL</FONT>;<BR /><FONT color="#993366"><U>name </U> <U>category </U> <U>price</U> <U>units</U> <U>consumable</U> <U>description </U></FONT><BR /><FONT color="#993366">electrostatic air filter HVAC hardware 10 1 false Air filter for HVAC systems</FONT><BR /><FONT color="#993366">strainer housewares 15 1 false Bowl with a handle and fine holes</FONT><BR /><FONT color="#993366">skimmer pool supplies 100 1 false Drain filtering leaves from the water surface</FONT></PRE> <P>There is a problem though: price can hardly be assumed to be unique by category. We are walking on thin ice assuming <FONT face="andale mono,times">max_by()</FONT> will not deliver us a chimera of a product mixing different values from products at the same price.</P> <P>Yet another, safer, way is to employ window aggregation. In this approach we rank each product within its category.<BR />Then we filter out only the winners per category, and remove the rank itself.</P> <PRE><FONT color="#008000">SELECT</FONT> * <FONT color="#008000">EXCEPT</FONT>(<FONT color="#FF9900">rank</FONT>)<BR /> <FONT color="#008000">FROM</FONT> (<FONT color="#008000">SELECT</FONT> row_number() <FONT color="#008000">OVER</FONT> (<FONT color="#008000">PARTITION BY</FONT> <FONT color="#FF9900">category</FONT> <FONT color="#008000">ORDER BY</FONT> <FONT color="#FF9900">price</FONT> <FONT color="#008000">DESC) AS</FONT> <FONT color="#FF9900">rank</FONT>,<BR /> *<BR /> <FONT color="#008000">FROM</FONT> <FONT color="#FF9900">products</FONT>)<BR /> <FONT color="#008000">WHERE</FONT> <FONT color="#FF9900">rank</FONT> = 1;<BR /><FONT color="#993366"><U>name </U> <U>category </U> <U>price</U> <U>units</U> <U>consumable</U> <U>description </U></FONT><BR /><FONT color="#993366">electrostatic air filter HVAC hardware 10 1 false Air filter for HVAC systems</FONT><BR /><FONT color="#993366">strainer housewares 15 1 false Bowl with a handle and fine holes</FONT><BR /><FONT color="#993366">skimmer pool supplies 100 1 false Drain filtering leaves from the water surface</FONT></PRE> <P>Thank goodness for the <FONT face="andale mono,times">EXCEPT</FONT> syntax, which made this manageable. But we had to push a subquery, just like we had to do at the beginning of the post when we "invented" <FONT face="andale mono,times">HAVING</FONT>.</P> <P>Again, this pattern is popular enough that SQL found a way. It's called the <FONT face="andale mono,times">QUALIFY</FONT> clause and it operates on expressions involving window functions only.</P> <PRE><FONT color="#008000">SELECT</FONT> *<BR /> <FONT color="#008000">FROM</FONT> <FONT color="#FF9900">products</FONT><BR /> <FONT color="#008000">QUALIFY</FONT> row_number() <FONT color="#008000">OVER</FONT>(<FONT color="#008000">PARTITION BY</FONT> <FONT color="#FF9900">category</FONT> <FONT color="#008000">ORDER BY</FONT> <FONT color="#FF9900">price</FONT> <FONT color="#008000">DESC)</FONT> = 1;<BR /><FONT color="#993366"><U>name </U> <U>category </U> <U>price</U> <U>units</U> <U>consumable</U> <U>description </U></FONT><BR /><FONT color="#993366">electrostatic air filter HVAC hardware 10 1 false Air filter for HVAC systems</FONT><BR /><FONT color="#993366">strainer housewares 15 1 false Bowl with a handle and fine holes</FONT><BR /><FONT color="#993366">skimmer pool supplies 100 1 false Drain filtering leaves from the water surface</FONT></PRE> <P>The trick with <FONT face="andale mono,times">QUALIFY</FONT> is not so much that it does allow the window function, but that it executes after the <FONT face="andale mono,times">SELECT</FONT> list, just like <FONT face="andale mono,times">HAVING</FONT>.</P> <P>We can make another prediction: Sooner rather than later <FONT face="andale mono,times">QUALIFY</FONT> will be generalized to allow any predicate operating on any column in the <FONT face="andale mono,times">SELECT</FONT> list.</P> <P>Wouldn't that be nice?</P> <P>&nbsp;</P> <H1>Conclusion</H1> <P>SQL provides a whole lot more than just <FONT face="andale mono,times">WHERE</FONT> to filter data:</P> <UL> <LI><FONT face="andale mono,times">HAVING</FONT> filters on groups</LI> <LI><FONT face="andale mono,times">FILTER</FONT> refines aggregation</LI> <LI><FONT face="andale mono,times">QUALIFY</FONT> sieves through selected rows.</LI> </UL> <H1 id="toc-hId-1508844637">Related</H1> <UL> <LI><A href="https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-qry-select-named-window.html" target="_self" rel="nofollow noreferrer">SQL Ref: WINDOW clause</A></LI> <LI><A href="https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-qry-select-limit.html" target="_self" rel="nofollow noreferrer">SQL Ref: LIMIT clause</A></LI> <LI><A href="https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-qry-select-where.html" target="_self" rel="nofollow noreferrer">SQL Ref: WHERE clause</A></LI> <LI><A href="https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-qry-select-having.html" target="_self" rel="nofollow noreferrer">SQL Ref: HAVING clause</A></LI> <LI><A href="https://docs.databricks.com/en/sql/language-manual/sql-ref-window-functions.html" target="_self">SQL Ref: window functions</A></LI> <LI><A href="https://docs.databricks.com/en/sql/language-manual/functions/count_if.html" target="_self">SQL Ref: count_if function</A></LI> </UL> <P>&nbsp;</P> Fri, 29 Mar 2024 15:51:36 GMT SergeRielau 2024-03-29T15:51:36Z https://community.databricks.com/t5/technical-blog/highly-selective-sql-refined-beyond-the-where/ba-p/64459 <P>It is said that the Inuktitut, the language of the Inuit has 52 words for snow and ice.<BR />That’s, as they say, fake news, but the point is metaphorical:<BR />When something is important to a people, their language finds a way to become efficient on that topic.<BR />SQL is all about transforming and filtering data. So it should come as no surprise that SQL language has sprouted a whole lot more ways to eliminate datapoints than the humble <STRONG><FONT face="andale mono,times">WHERE</FONT></STRONG>.<BR />In this time we broaden our tongue to speak SQL a bit more … refined.</P> Fri, 29 Mar 2024 15:51:36 GMT https://community.databricks.com/t5/technical-blog/highly-selective-sql-refined-beyond-the-where/ba-p/64459 SergeRielau 2024-03-29T15:51:36Z