title: Spark SQL secondary filtering and grouping
body: <p><strong>Problem</strong>: I have a data set A {filed1, field2, field3...}, and I would like to first group A by say, <code>field1</code>, then in each of the resulting groups, I would like to do bunch of subqueries, for example, count the number of rows that have <code>field2 == true</code>, or count the number of distinct <code>field3</code> that have <code>field4 == "some_value"</code> and <code>field5 == false</code>, etc.</p>
  
  <p><strong>Some alternatives I can think of:</strong> I can write a customized user defined aggregate function that takes a function that computes the condition for filtering, but this way I have to create an instance of it for every query condition. I've also looked at the <code>countDistinct</code> function can achieve some of the operations, but I can't figure out how to use it to implement the filter-distinct-count semantic.</p>
  
  <p>In Pig, I can do:</p>
  
  <pre><code>FOREACH (GROUP A by field1) {
          field_a = FILTER A by field2 == TRUE;
          field_b = FILTER A by field4 == 'some_value' AND field5 == FALSE;
          field_c = DISTINCT field_b.field3;
  
          GENERATE  FLATTEN(group),
                    COUNT(field_a) as fa,
                    COUNT(field_b) as fb,
                    COUNT(field_c) as fc,
  </code></pre>
  
  <p>Is there a way to do this in Spark SQL?</p>