Using SQL to identify grouped records with exception

user_19898296 0
Viewed 77

I am trying to use sql to identify grouped orders/records that contain an exception but instead of just out putting the record with that exception. display all lines including the one with the exception. I need to identify groups of record where only one item in that group = 0.

order number   item number   qty
12345           G123          5
12345           C123          4 
12345           I123          0 
12345           K123          6

I am using Spark SQL and have attempted using the Group By clause with having count of distinct order number > 1

apache-spark-sql apache-spark sql
1 Answers

Old classic SQL, interesting to see how it performs.

Using my own data, but demonstrating the point. Optimizer may re-write query.

val df = Seq( 
              ( "A", "Bangalore", "a*.com", 1, "cpu" ),
              ( "A", "Bangalore", "a*.com", 9, "cpu" ),
              ( "A", "Bangalore", "a*.com", 7, "desktop" ),
              ( "C", "Bangalore", "a*.com", 0, "desktop" ),
              ( "C", "Bangalore", "a*.com", 0, "desktop" ),
              ( "D", "Bangalore", "a*.com", 0, "desktop" ),
              ( "D", "Bangalore", "a*.com", 0, "desktop" ),
              ( "D", "Bangalore", "a*.com", 0, "desktop" ),
              ( "B", "Bangalore", "a*.com", 5, "desktop" ),
              ( "B", "Bangalore", "a*.com", 0, "desktop" ),
              ( "B", "Bangalore", "a*.com", 19, "monitor" ),
             ).toDF("name" ,"address", "email", "floor", "resource")

df.createOrReplaceTempView("R")

val res = spark.sql(""" 

                    select *
                      from R
                     where R.name IN ( 
                                       select X.name 
                                         from (select name, count(*) 
                                                 from R
                                                where floor = 0
                                             group by name
                                               having count(*) = 1 ) X 
                                     )   
                             
                    """)

res.show(false)

returns:

+----+---------+------+-----+--------+
|name|address  |email |floor|resource|
+----+---------+------+-----+--------+
|B   |Bangalore|a*.com|5    |desktop |
|B   |Bangalore|a*.com|0    |desktop |
|B   |Bangalore|a*.com|19   |monitor |
+----+---------+------+-----+--------+
user_69339930
Related