Querying the database of Chicago employees

The database

We start with loading a sample database. Our sample database is derived from the dataset of all employees of the city of Chicago (source).

In [1]:
include("../citydb.jl")

using RBT
setdb(citydb)
Out[1]:
Department:
  name :: UTF8String # unique
  employee (inverse of Employee.department) :: Array{Employee,1} # unique, covering
Employee:
  name :: UTF8String
  surname :: UTF8String
  position :: UTF8String
  salary :: Int64
  department :: Department
  managed_by :: Nullable{Employee}
  manages (inverse of Employee.managed_by) :: Array{Employee,1} # unique

We can execute a query using @query() command:

In [2]:
@query(6*(3+4))
Out[2]:
42

Traversing the database structure

Find the names of all departments.

In [3]:
@query(department.name)
Out[3]:
35-element Array{UTF8String,1}:
 "WATER MGMNT"      
 "POLICE"           
 "GENERAL SERVICES" 
 "CITY COUNCIL"     
 "STREETS & SAN"    
 ⋮                  
 "BOARD OF ETHICS"  
 "POLICE BOARD"     
 "BUDGET & MGMT"    
 "ADMIN HEARNG"     
 "LICENSE APPL COMM"

Find the names of all employees.

In [4]:
@query(department.employee.name)
Out[4]:
32181-element Array{UTF8String,1}:
 "ELVIA"     
 "VICENTE"   
 "MUHAMMAD"  
 "GIRLEY"    
 "DILAN"     
 ⋮           
 "NANCY"     
 "DARCI"     
 "THADDEUS"  
 "RACHENETTE"
 "MICHELLE"  

We are not longer restricted by the hierarchical structure of the database, so we can query employees directly.

In [5]:
@query(employee.name)
Out[5]:
32181-element Array{UTF8String,1}:
 "ELVIA"    
 "JEFFERY"  
 "KARINA"   
 "KIMBERLEI"
 "VICENTE"  
 ⋮          
 "MICHAEL"  
 "PETER"    
 "MARK"     
 "CARLO"    
 "DARIUSZ"  

We can traverse the schema in any direction, for instance, from employees to their departments.

In [6]:
@query(employee.department.name)
Out[6]:
32181-element Array{UTF8String,1}:
 "WATER MGMNT"     
 "POLICE"          
 "POLICE"          
 "GENERAL SERVICES"
 "WATER MGMNT"     
 ⋮                 
 "GENERAL SERVICES"
 "POLICE"          
 "POLICE"          
 "POLICE"          
 "DoIT"            

Show the list of all salaries.

In [7]:
@query(employee.salary)
Out[7]:
32181-element Array{Int64,1}:
  88968
  80778
  80778
  84780
 104736
      ⋮
  97448
  86520
  83616
  86520
 110352

If the traversal ends at an entity class, an array of records is generated.

In [8]:
@query(employee)
Out[8]:
32181-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:
 ("ELVIA","A","WATER MGMNT","WATER RATE TAKER",88968)                     
 ("JEFFERY","A","POLICE","POLICE OFFICER",80778)                          
 ("KARINA","A","POLICE","POLICE OFFICER",80778)                           
 ("KIMBERLEI","A","GENERAL SERVICES","CHIEF CONTRACT EXPEDITER",84780)    
 ("VICENTE","A","WATER MGMNT","CIVIL ENGINEER IV",104736)                 
 ⋮                                                                        
 ("MICHAEL","Z","GENERAL SERVICES","FRM OF MACHINISTS - AUTOMOTIVE",97448)
 ("PETER","Z","POLICE","POLICE OFFICER",86520)                            
 ("MARK","Z","POLICE","POLICE OFFICER",83616)                             
 ("CARLO","Z","POLICE","POLICE OFFICER",86520)                            
 ("DARIUSZ","Z","DoIT","CHIEF DATA BASE ANALYST",110352)                  

Which fields are selected depends on the path to the class.

In [9]:
@query(department.employee)
Out[9]:
32181-element Array{Tuple{UTF8String,UTF8String},1}:
 ("ELVIA","A")     
 ("VICENTE","A")   
 ("MUHAMMAD","A")  
 ("GIRLEY","A")    
 ("DILAN","A")     
 ⋮                 
 ("NANCY","T")     
 ("DARCI","W")     
 ("THADDEUS","W")  
 ("RACHENETTE","W")
 ("MICHELLE","G")  

Summarizing data

Find the number of departments.

In [10]:
@query(count(department))
Out[10]:
35

Find the number of employees for each department.

In [11]:
@query(department.count(employee))
Out[11]:
35-element Array{Int64,1}:
  1848
 13570
   924
   397
  2090
     ⋮
     9
     2
    43
    39
     1

Find the total number of employees.

In [12]:
@query(count(department.employee))
Out[12]:
32181

Again, we can query employee directly.

In [13]:
@query(count(employee))
Out[13]:
32181

Find the top salary among all employees.

In [14]:
@query(max(employee.salary))
Out[14]:
Nullable(260004)

Find the maximum number of employees per department.

In [15]:
@query(max(department.count(employee)))
Out[15]:
Nullable(13570)

Selecting output columns

For each department, find the number of employees.

In [16]:
@query(department:select(name,count(employee)))
Out[16]:
35-element Array{Tuple{UTF8String,Int64},1}:
 ("WATER MGMNT",1848)    
 ("POLICE",13570)        
 ("GENERAL SERVICES",924)
 ("CITY COUNCIL",397)    
 ("STREETS & SAN",2090)  
 ⋮                       
 ("BOARD OF ETHICS",9)   
 ("POLICE BOARD",2)      
 ("BUDGET & MGMT",43)    
 ("ADMIN HEARNG",39)     
 ("LICENSE APPL COMM",1) 

The :select notation is a syntax sugar for regular function call where the first argument is placed before the function name (postfix notation).

In [17]:
@query(select(department,name,count(employee)))
Out[17]:
35-element Array{Tuple{UTF8String,Int64},1}:
 ("WATER MGMNT",1848)    
 ("POLICE",13570)        
 ("GENERAL SERVICES",924)
 ("CITY COUNCIL",397)    
 ("STREETS & SAN",2090)  
 ⋮                       
 ("BOARD OF ETHICS",9)   
 ("POLICE BOARD",2)      
 ("BUDGET & MGMT",43)    
 ("ADMIN HEARNG",39)     
 ("LICENSE APPL COMM",1) 

It is easy to add new columns to the output. Let us add the top salary per department.

In [18]:
@query(
    department
    :select(
        name,
        count(employee),
        max(employee.salary)))
Out[18]:
35-element Array{Tuple{UTF8String,Int64,Nullable{Int64}},1}:
 ("WATER MGMNT",1848,Nullable(169512))    
 ("POLICE",13570,Nullable(260004))        
 ("GENERAL SERVICES",924,Nullable(157092))
 ("CITY COUNCIL",397,Nullable(160248))    
 ("STREETS & SAN",2090,Nullable(157092))  
 ⋮                                        
 ("BOARD OF ETHICS",9,Nullable(131688))   
 ("POLICE BOARD",2,Nullable(97728))       
 ("BUDGET & MGMT",43,Nullable(169992))    
 ("ADMIN HEARNG",39,Nullable(156420))     
 ("LICENSE APPL COMM",1,Nullable(69888))  

Filtering data

Find the employees with salary greater than $200k.

In [19]:
@query(
    employee
    :filter(salary>200000)
    :select(name,surname,position,salary))
Out[19]:
3-element Array{Tuple{UTF8String,UTF8String,UTF8String,Int64},1}:
 ("RAHM","E","MAYOR",216210)                    
 ("GARRY","M","SUPERINTENDENT OF POLICE",260004)
 ("JOSE","S","FIRE COMMISSIONER",202728)        

You can apply :filter() on any selected column.

In [20]:
@query(
    employee
    :select(name,surname,position,salary)
    :filter(salary>200000))
Out[20]:
3-element Array{Tuple{UTF8String,UTF8String,UTF8String,Int64},1}:
 ("RAHM","E","MAYOR",216210)                    
 ("GARRY","M","SUPERINTENDENT OF POLICE",260004)
 ("JOSE","S","FIRE COMMISSIONER",202728)        

Find the number of employees with salary in the range from \$100k to \$200k.

In [21]:
@query(
    employee
    :filter((salary>100000)&(salary<=200000))
    :count)
Out[21]:
3916

Find the departments with more than 1000 employees.

In [22]:
@query(
    department
    :filter(count(employee)>1000)
    .name)
WARNING: deprecated syntax "filter(count(employee)>1000) ." at In[22]:4.
Use "filter(count(employee)>1000)." instead.
Out[22]:
7-element Array{UTF8String,1}:
 "WATER MGMNT"  
 "POLICE"       
 "STREETS & SAN"
 "AVIATION"     
 "FIRE"         
 "OEMC"         
 "TRANSPORTN"   

Find the number of departments with more than 1000 employees.

In [23]:
@query(
    count(
        department
        :filter(count(employee)>1000)))
Out[23]:
7

For each department, find the number of employees with salary higher than $100k.

In [24]:
@query(
    department
    :select(
        name,
        count(employee:filter(salary>100000))))
Out[24]:
35-element Array{Tuple{UTF8String,Int64},1}:
 ("WATER MGMNT",179)    
 ("POLICE",1493)        
 ("GENERAL SERVICES",79)
 ("CITY COUNCIL",54)    
 ("STREETS & SAN",39)   
 ⋮                      
 ("BOARD OF ETHICS",2)  
 ("POLICE BOARD",0)     
 ("BUDGET & MGMT",12)   
 ("ADMIN HEARNG",3)     
 ("LICENSE APPL COMM",0)

For each department with the number of employees less than 1000, find the employees with salary higher than $125k.

In [25]:
@query(
    department
    :filter(count(employee)<1000)
    :select(
        name,
        employee
            :filter(salary>125000)
            :select(name,surname,position)))
Out[25]:
28-element Array{Tuple{UTF8String,Array{Tuple{UTF8String,UTF8String,UTF8String},1}},1}:
 ("GENERAL SERVICES",[("DAVID","R","COMMISSIONER OF FLEET & FACILITY MANAGEMENT"),("PHILLIP","S","EQUIPMENT SERVICES COORD")])                                                            
 ("CITY COUNCIL",[("JAMES","C","DEPUTY CHIEF ADMINISTRATIVE OFFICER"),("MARLA","K","CHIEF ADMINISTRATIVE OFFICER")])                                                                      
 ("FAMILY & SUPPORT",[("EVELYN","D","COMMISSIONER OF FAMILY AND SUPPORT SERVICES"),("MARY","G","DEPUTY COMMISSIONER"),("JENNIFER","W","FIRST DEPUTY COMMISSIONER")])                      
 ("IPRA",[("SCOTT","A","CHIEF ADMINISTRATOR"),("STEVEN","H","DEPUTY CHIEF ADMINISTRATOR"),("STEVEN","M","FIRST DEPUTY CHIEF ADMINISTRATOR"),("WILLIAM","W","DEPUTY CHIEF ADMINISTRATOR")])
 ("PUBLIC LIBRARY",[("BRIAN","B","COMMISSIONER OF CHICAGO PUBLIC LIBRARY"),("MICHELLE","F","DIRECTOR OF LIBRARY TECHNOLOGY"),("ANDREA","S","FIRST DEPUTY COMMISSIONER")])                 
 ⋮                                                                                                                                                                                        
 ("BOARD OF ETHICS",[("STEVEN","B","EXECUTIVE DIR - BOARD OF ETHICS")])                                                                                                                   
 ("POLICE BOARD",Tuple{UTF8String,UTF8String,UTF8String}[])                                                                                                                               
 ("BUDGET & MGMT",[("ALEXANDRA","H","BUDGET DIR"),("RESHMA","S","FIRST DEPUTY BUDGET DIR")])                                                                                              
 ("ADMIN HEARNG",[("PATRICIA","J","DIR OF ADMINISTRATIVE HEARINGS"),("STEVEN","S","DEPUTY DIR OF ADMINISTRATIVE ADJUDICATION")])                                                          
 ("LICENSE APPL COMM",Tuple{UTF8String,UTF8String,UTF8String}[])                                                                                                                          

Sorting

We use the :sort combinator to sort an array of values.

List the names of departments in alphabetical order.

In [26]:
@query(department.name:sort)
Out[26]:
35-element Array{UTF8String,1}:
 "ADMIN HEARNG"     
 "ANIMAL CONTRL"    
 "AVIATION"         
 "BOARD OF ELECTION"
 "BOARD OF ETHICS"  
 ⋮                  
 "PUBLIC LIBRARY"   
 "STREETS & SAN"    
 "TRANSPORTN"       
 "TREASURER"        
 "WATER MGMNT"      

We can also specify the attribute by which the elements of the array are to be sorted.

Show the employees sorted by salary.

In [27]:
@query(employee:sort(salary))
Out[27]:
32181-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:
 ("STEVEN","K","MAYOR'S OFFICE","ADMINISTRATIVE SECRETARY",1)  
 ("BETTY","A","FAMILY & SUPPORT","FOSTER GRANDPARENT",2756)    
 ("VICTOR","A","FAMILY & SUPPORT","SENIOR COMPANION",2756)     
 ("RASHEEDAH","A","FAMILY & SUPPORT","SENIOR COMPANION",2756)  
 ("MARY","A","FAMILY & SUPPORT","FOSTER GRANDPARENT",2756)     
 ⋮                                                             
 ("CHARLES","S","FIRE","FIRST DEPUTY FIRE COMMISSIONER",197736)
 ("ALFONZA","W","POLICE","FIRST DEPUTY SUPERINTENDENT",197736) 
 ("JOSE","S","FIRE","FIRE COMMISSIONER",202728)                
 ("RAHM","E","MAYOR'S OFFICE","MAYOR",216210)                  
 ("GARRY","M","POLICE","SUPERINTENDENT OF POLICE",260004)      

Use :desc indicator to reverse the order.

In [28]:
@query(employee:sort(salary:desc))
Out[28]:
32181-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:
 ("GARRY","M","POLICE","SUPERINTENDENT OF POLICE",260004)      
 ("RAHM","E","MAYOR'S OFFICE","MAYOR",216210)                  
 ("JOSE","S","FIRE","FIRE COMMISSIONER",202728)                
 ("CHARLES","S","FIRE","FIRST DEPUTY FIRE COMMISSIONER",197736)
 ("ALFONZA","W","POLICE","FIRST DEPUTY SUPERINTENDENT",197736) 
 ⋮                                                             
 ("SONG","Y","FAMILY & SUPPORT","SENIOR COMPANION",2756)       
 ("AMANDA","Y","FAMILY & SUPPORT","FOSTER GRANDPARENT",2756)   
 ("EUNICE","Y","FAMILY & SUPPORT","SENIOR COMPANION",2756)     
 ("MING","Y","FAMILY & SUPPORT","SENIOR COMPANION",2756)       
 ("STEVEN","K","MAYOR'S OFFICE","ADMINISTRATIVE SECRETARY",1)  

It is possible to specify several sorting keys.

In [29]:
@query(
    employee
    :sort(
        salary:desc,
        surname:asc,
        name:asc))
Out[29]:
32181-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:
 ("GARRY","M","POLICE","SUPERINTENDENT OF POLICE",260004)      
 ("RAHM","E","MAYOR'S OFFICE","MAYOR",216210)                  
 ("JOSE","S","FIRE","FIRE COMMISSIONER",202728)                
 ("CHARLES","S","FIRE","FIRST DEPUTY FIRE COMMISSIONER",197736)
 ("ALFONZA","W","POLICE","FIRST DEPUTY SUPERINTENDENT",197736) 
 ⋮                                                             
 ("MING","Y","FAMILY & SUPPORT","SENIOR COMPANION",2756)       
 ("SONG","Y","FAMILY & SUPPORT","SENIOR COMPANION",2756)       
 ("SONG","Y","FAMILY & SUPPORT","SENIOR COMPANION",2756)       
 ("YELENA","Y","FAMILY & SUPPORT","SENIOR COMPANION",2756)     
 ("STEVEN","K","MAYOR'S OFFICE","ADMINISTRATIVE SECRETARY",1)  

:sort can be used together with :select and :filter.

In [30]:
@query(
    department
    :select(name, size => count(employee))
    :filter(size>1000)
    :sort(size:desc))
Out[30]:
7-element Array{Tuple{UTF8String,Int64},1}:
 ("POLICE",13570)      
 ("FIRE",4875)         
 ("STREETS & SAN",2090)
 ("WATER MGMNT",1848)  
 ("AVIATION",1344)     
 ("TRANSPORTN",1200)   
 ("OEMC",1135)         

Use :define to name a commonly used expression.

In [31]:
@query(
    department
    :define(size => count(employee))
    :select(name, size)
    :filter(size>1000)
    :sort(size:desc))
Out[31]:
7-element Array{Tuple{UTF8String,Int64},1}:
 ("POLICE",13570)      
 ("FIRE",4875)         
 ("STREETS & SAN",2090)
 ("WATER MGMNT",1848)  
 ("AVIATION",1344)     
 ("TRANSPORTN",1200)   
 ("OEMC",1135)         

Limiting

Use combinators :first, :last, :take to limit the size of the output array. Use :reverse to reverse the output array.

The first employee.

In [32]:
@query(first(employee))
Out[32]:
Nullable(("ELVIA","A","WATER MGMNT","WATER RATE TAKER",88968))

The name of the first employee.

In [33]:
@query(first(employee).name)
Out[33]:
Nullable("ELVIA")

The department with the largest number of employees.

In [34]:
@query(
    department
    :select(name, size => count(employee))
    :sort(size:desc)
    :first)
Out[34]:
Nullable(("POLICE",13570))

Same query without :sort.

The department with the largest number of employees.

In [35]:
@query(
    department
    :select(name, size => count(employee))
    :first(size))
Out[35]:
Nullable(("POLICE",13570))

Last employee.

In [36]:
@query(employee:last)
Out[36]:
Nullable(("DARIUSZ","Z","DoIT","CHIEF DATA BASE ANALYST",110352))

The department with the largest number of employees.

In [37]:
@query(
    department
    :select(name, size => count(employee))
    :sort(size)
    :last)
Out[37]:
Nullable(("POLICE",13570))

Same query could be written without :sort.

The department with the largest number of employees.

In [38]:
@query(
    department
    :select(name, size => count(employee))
    :last(size:desc))
Out[38]:
Nullable(("POLICE",13570))

Show first 5 employees.

In [39]:
@query(employee:take(5))
Out[39]:
5-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:
 ("ELVIA","A","WATER MGMNT","WATER RATE TAKER",88968)                 
 ("JEFFERY","A","POLICE","POLICE OFFICER",80778)                      
 ("KARINA","A","POLICE","POLICE OFFICER",80778)                       
 ("KIMBERLEI","A","GENERAL SERVICES","CHIEF CONTRACT EXPEDITER",84780)
 ("VICENTE","A","WATER MGMNT","CIVIL ENGINEER IV",104736)             

Skip first 10 employees, show next 5.

In [40]:
@query(employee:skip(10):take(5))
Out[40]:
5-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:
 ("BETTY","A","FAMILY & SUPPORT","FOSTER GRANDPARENT",2756)
 ("LYNISE","A","POLICE","CLERK III",43920)                 
 ("WILLIAM","A","IPRA","INVESTIGATOR - IPRA II",72468)     
 ("ZAID","A","POLICE","POLICE OFFICER",69684)              
 ("ABDALMAHD","A","POLICE","POLICE OFFICER",80778)         

Show last ten employees.

In [41]:
@query(employee:skip(-10))
Out[41]:
10-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:
 ("MATTHEW","Z","AVIATION","AIRPORT OPERATIONS SUPVSR I",69840)           
 ("JEFFREY","Z","POLICE","POLICE OFFICER",83616)                          
 ("MATTHEW","Z","FIRE","FIREFIGHTER-EMT",91764)                           
 ("BRYAN","Z","POLICE","POLICE OFFICER",80778)                            
 ("JOHN","Z","GENERAL SERVICES","MACHINIST (AUTOMOTIVE)",92248)           
 ("MICHAEL","Z","GENERAL SERVICES","FRM OF MACHINISTS - AUTOMOTIVE",97448)
 ("PETER","Z","POLICE","POLICE OFFICER",86520)                            
 ("MARK","Z","POLICE","POLICE OFFICER",83616)                             
 ("CARLO","Z","POLICE","POLICE OFFICER",86520)                            
 ("DARIUSZ","Z","DoIT","CHIEF DATA BASE ANALYST",110352)                  

Show approximately half of employees.

In [42]:
@query(employee:take(count(employee)/2))
Out[42]:
16090-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:
 ("ELVIA","A","WATER MGMNT","WATER RATE TAKER",88968)                 
 ("JEFFERY","A","POLICE","POLICE OFFICER",80778)                      
 ("KARINA","A","POLICE","POLICE OFFICER",80778)                       
 ("KIMBERLEI","A","GENERAL SERVICES","CHIEF CONTRACT EXPEDITER",84780)
 ("VICENTE","A","WATER MGMNT","CIVIL ENGINEER IV",104736)             
 ⋮                                                                    
 ("BROOKE","L","LAW","ASST CORPORATION COUNSEL",63720)                
 ("JOSEPH","L","WATER MGMNT","ELECTRICAL MECHANIC",91520)             
 ("PETER","L","POLICE","POLICE OFFICER",83616)                        
 ("ROBERT","L","STREETS & SAN","SANITATION LABORER",72384)            
 ("NICHOLAS","L","OEMC","FIRE COMMUNICATIONS OPERATOR II",78180)      

Reverse the order of departments.

In [43]:
@query(department:reverse)
Out[43]:
35-element Array{Tuple{UTF8String},1}:
 ("LICENSE APPL COMM",)
 ("ADMIN HEARNG",)     
 ("BUDGET & MGMT",)    
 ("POLICE BOARD",)     
 ("BOARD OF ETHICS",)  
 ⋮                     
 ("STREETS & SAN",)    
 ("CITY COUNCIL",)     
 ("GENERAL SERVICES",) 
 ("POLICE",)           
 ("WATER MGMNT",)      

Identity

An identity of a database record is a value that identifies the record among all the entities of the same class. Use id attribute to find the identity of the input record.

In [44]:
@query(department:select(id,name))
Out[44]:
35-element Array{Tuple{Int64,UTF8String},1}:
 (1,"WATER MGMNT")       
 (2,"POLICE")            
 (3,"GENERAL SERVICES")  
 (4,"CITY COUNCIL")      
 (5,"STREETS & SAN")     
 ⋮                       
 (31,"BOARD OF ETHICS")  
 (32,"POLICE BOARD")     
 (33,"BUDGET & MGMT")    
 (34,"ADMIN HEARNG")     
 (35,"LICENSE APPL COMM")

Use :get combinator to find the record by its identity.

In [45]:
@query(department:get(5))
Out[45]:
Nullable(("STREETS & SAN",))

If a record is not found, null value is returned.

In [46]:
@query(department:get(-1))
Out[46]:
Nullable{Tuple{UTF8String}}()

You can use brackets instead of :get.

In [47]:
@query(
    department[5]
    :select(id, name, count(employee)))
Out[47]:
Nullable((5,"STREETS & SAN",2090))

Show all employees of a selected department.

In [48]:
@query(department[5].employee)
Out[48]:
2090-element Array{Tuple{UTF8String,UTF8String},1}:
 ("EMMANUEL","A")
 ("TAHIR","A")   
 ("TIMOTHY","A") 
 ("DANIELLE","A")
 ("SAMUEL","A")  
 ⋮               
 ("LUIS","Z")    
 ("ROBERT","Z")  
 ("JONATHAN","Z")
 ("THOMAS","Z")  
 ("MAC","Z")     

Hierarchical queries

(Note: the data on organizational structure is not available, so the output is largely meaningless).

Find the employees who earn more than their manager.

In [49]:
@query(
    employee
    :filter(salary>managed_by.salary)
    :select(name, surname, position, managed_by, salary-managed_by.salary))
Out[49]:
1-element Array{Tuple{UTF8String,UTF8String,UTF8String,Nullable{Tuple{UTF8String,UTF8String,UTF8String}},Nullable{Int64}},1}:
 ("BRIAN","L","AUDITOR IV",Nullable(("ROBERT","L","DIR OF ACCOUNTING")),Nullable(4140))

For all employees in a certain department, list their seniors and the number of their subordinates.

In [50]:
@query(
    department[26].employee
    :select(
        name,
        surname,
        position,
        connect(managed_by),
        count(connect(manages))))
Out[50]:
24-element Array{Tuple{UTF8String,UTF8String,UTF8String,Array{Tuple{UTF8String,UTF8String,UTF8String},1},Int64},1}:
 ("SAEED","A","ASST CITY TREASURER",[("KURT","S","CITY TREASURER")],0)                                
 ("ELIZABETH","A","ACCOUNTANT I",[("ROBERT","L","DIR OF ACCOUNTING"),("KURT","S","CITY TREASURER")],0)
 ("KONSTANTINES","A","ASSISTANT DIRECTOR OF FINANCE",[("KURT","S","CITY TREASURER")],0)               
 ("SARA","A","STUDENT INTERN",[("KURT","S","CITY TREASURER")],0)                                      
 ("NANCY","C","EXECUTIVE SECRETARY I",[("KURT","S","CITY TREASURER")],0)                              
 ⋮                                                                                                    
 ("DANIELLE","M","ASST CITY TREASURER",[("KURT","S","CITY TREASURER")],0)                             
 ("MARK","M","PORTFOLIO MANAGER",[("KURT","S","CITY TREASURER")],0)                                   
 ("KENNETH","S","ASST CITY TREASURER",[("KURT","S","CITY TREASURER")],0)                              
 ("ALEXANDRA","S","DEPUTY CITY TREASURER",[("KURT","S","CITY TREASURER")],0)                          
 ("KURT","S","CITY TREASURER",Tuple{UTF8String,UTF8String,UTF8String}[],23)                           

List employees of a certain department in hierarchical order.

In [51]:
@query(
    department[26].employee
    :sort_connect(managed_by)
    :select(
        depth(managed_by),
        name,
        surname,
        position))
Out[51]:
24-element Array{Tuple{Int64,UTF8String,UTF8String,UTF8String},1}:
 (0,"KURT","S","CITY TREASURER")                       
 (1,"SAEED","A","ASST CITY TREASURER")                 
 (1,"KONSTANTINES","A","ASSISTANT DIRECTOR OF FINANCE")
 (1,"SARA","A","STUDENT INTERN")                       
 (1,"NANCY","C","EXECUTIVE SECRETARY I")               
 ⋮                                                     
 (2,"TERRANCE","M","ACCOUNTANT IV")                    
 (1,"DANIELLE","M","ASST CITY TREASURER")              
 (1,"MARK","M","PORTFOLIO MANAGER")                    
 (1,"KENNETH","S","ASST CITY TREASURER")               
 (1,"ALEXANDRA","S","DEPUTY CITY TREASURER")           

Grouping

Use :unique combinator to generate all unique values that appear in a sequence.

List all distinct positions.

In [52]:
@query(employee.position:unique)
Out[52]:
1094-element Array{UTF8String,1}:
 "1ST DEPUTY INSPECTOR GENERAL"
 "A/MGR COM SVC-ELECTIONS"     
 "A/MGR OF MIS-ELECTIONS"      
 "A/MGR WAREHOUSE-ELECTIONS"   
 "A/SUPRV REDISTRICTING"       
 ⋮                             
 "WINDOW WASHER"               
 "YOUTH SERVICES COORD"        
 "ZONING ADMINISTRATOR"        
 "ZONING INVESTIGATOR"         
 "ZONING PLAN EXAMINER"        

Find the number of distinct positions for each department.

In [53]:
@query(
    department
    :select(
        name,
        count(unique(employee.position)),
        count(employee)))
Out[53]:
35-element Array{Tuple{UTF8String,Int64,Int64},1}:
 ("WATER MGMNT",154,1848)    
 ("POLICE",129,13570)        
 ("GENERAL SERVICES",119,924)
 ("CITY COUNCIL",28,397)     
 ("STREETS & SAN",70,2090)   
 ⋮                           
 ("BOARD OF ETHICS",9,9)     
 ("POLICE BOARD",2,2)        
 ("BUDGET & MGMT",24,43)     
 ("ADMIN HEARNG",15,39)      
 ("LICENSE APPL COMM",1,1)   

We can also list distinct positions using :group combinator. With each position, we get a list of employees having this position.

In [54]:
@query(employee:group(position))
Out[54]:
1094-element Array{Tuple{UTF8String,Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}},1}:
 ("1ST DEPUTY INSPECTOR GENERAL",[("SHARON","F","INSPECTOR GEN","1ST DEPUTY INSPECTOR GENERAL",137052)])                                                                                                                                                                                                                                                                                                                                                                                                                        
 ("A/MGR COM SVC-ELECTIONS",[("LAURA","G","BOARD OF ELECTION","A/MGR COM SVC-ELECTIONS",99816)])                                                                                                                                                                                                                                                                                                                                                                                                                                
 ("A/MGR OF MIS-ELECTIONS",[("TIEN","T","BOARD OF ELECTION","A/MGR OF MIS-ELECTIONS",94932)])                                                                                                                                                                                                                                                                                                                                                                                                                                   
 ("A/MGR WAREHOUSE-ELECTIONS",[("DERRICK","H","BOARD OF ELECTION","A/MGR WAREHOUSE-ELECTIONS",71364)])                                                                                                                                                                                                                                                                                                                                                                                                                          
 ("A/SUPRV REDISTRICTING",[("THOMAS","M","BOARD OF ELECTION","A/SUPRV REDISTRICTING",69564),("CHRISTOPHER","T","BOARD OF ELECTION","A/SUPRV REDISTRICTING",45720)])                                                                                                                                                                                                                                                                                                                                                             
 ⋮                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
 ("WINDOW WASHER",[("GARRY","S","AVIATION","WINDOW WASHER",47320),("MARK","T","AVIATION","WINDOW WASHER",47320)])                                                                                                                                                                                                                                                                                                                                                                                                               
 ("YOUTH SERVICES COORD",[("EVELYN","B","FAMILY & SUPPORT","YOUTH SERVICES COORD",67224),("MONICA","D","FAMILY & SUPPORT","YOUTH SERVICES COORD",67224),("CESAR","G","FAMILY & SUPPORT","YOUTH SERVICES COORD",73752),("KAREN","N","FAMILY & SUPPORT","YOUTH SERVICES COORD",67224),("RASAUNA","R","FAMILY & SUPPORT","YOUTH SERVICES COORD",67224),("RICCADONNA","R","FAMILY & SUPPORT","YOUTH SERVICES COORD",67224),("LA","T","POLICE","YOUTH SERVICES COORD",80916),("TERRENCE","W","POLICE","YOUTH SERVICES COORD",73752)])
 ("ZONING ADMINISTRATOR",[("PATRICIA","S","COMMUNITY DEVELOPMENT","ZONING ADMINISTRATOR",139800)])                                                                                                                                                                                                                                                                                                                                                                                                                              
 ("ZONING INVESTIGATOR",[("CARLOS","R","COMMUNITY DEVELOPMENT","ZONING INVESTIGATOR",97596)])                                                                                                                                                                                                                                                                                                                                                                                                                                   
 ("ZONING PLAN EXAMINER",[("KYLE","B","COMMUNITY DEVELOPMENT","ZONING PLAN EXAMINER",50004),("PETER","B","COMMUNITY DEVELOPMENT","ZONING PLAN EXAMINER",50004),("SHOSHA","C","COMMUNITY DEVELOPMENT","ZONING PLAN EXAMINER",50004),("EDGAR","D","COMMUNITY DEVELOPMENT","ZONING PLAN EXAMINER",60780),("ALICIA","D","COMMUNITY DEVELOPMENT","ZONING PLAN EXAMINER",66684),("JOSE","G","COMMUNITY DEVELOPMENT","ZONING PLAN EXAMINER",69888),("JANICE","H","COMMUNITY DEVELOPMENT","ZONING PLAN EXAMINER",69888)])               

For each row generated by employee:group(position), combinator employee will give you employees that have this position.

For each position, find the number of employees.

In [55]:
@query(
    employee
    :group(position)
    :select(position, size => count(employee))
    :sort(size:desc))
Out[55]:
1094-element Array{Tuple{UTF8String,Int64},1}:
 ("POLICE OFFICER",9489)                       
 ("FIREFIGHTER-EMT",1191)                      
 ("SERGEANT",1138)                             
 ("FIREFIGHTER",970)                           
 ("POLICE OFFICER (ASSIGNED AS DETECTIVE)",808)
 ⋮                                             
 ("WEB AUTHOR",1)                              
 ("WEB DEVELOPER",1)                           
 ("WEB DEVELOPER/ADMINTR-CPL",1)               
 ("ZONING ADMINISTRATOR",1)                    
 ("ZONING INVESTIGATOR",1)                     

Find positions provided by no less than 5 departments.

In [56]:
@query(
    employee
    :group(position)
    :define(department => unique(employee.department))
    :filter(count(department)>=5)
    :select(position, department)
    :sort(count(department):desc))
Out[56]:
78-element Array{Tuple{UTF8String,Array{UTF8String,1}},1}:
 ("STAFF ASST",UTF8String["WATER MGMNT","POLICE","GENERAL SERVICES","STREETS & SAN","AVIATION","FIRE","FAMILY & SUPPORT","PUBLIC LIBRARY","DoIT","BUSINESS AFFAIRS"  …  "BUILDINGS","ANIMAL CONTRL","CITY CLERK","INSPECTOR GEN","TREASURER","DISABILITIES","PROCUREMENT","BOARD OF ETHICS","ADMIN HEARNG","LICENSE APPL COMM"])                    
 ("ADMINISTRATIVE SERVICES OFFICER II",UTF8String["WATER MGMNT","POLICE","STREETS & SAN","AVIATION","FAMILY & SUPPORT","PUBLIC LIBRARY","DoIT","BUSINESS AFFAIRS","OEMC","TRANSPORTN"  …  "LAW","COMMUNITY DEVELOPMENT","BUILDINGS","ANIMAL CONTRL","CITY CLERK","DISABILITIES","HUMAN RESOURCES","HUMAN RELATIONS","BUDGET & MGMT","ADMIN HEARNG"])
 ("ADMINISTRATIVE ASST II",UTF8String["WATER MGMNT","POLICE","GENERAL SERVICES","STREETS & SAN","AVIATION","FIRE","FAMILY & SUPPORT","PUBLIC LIBRARY","BUSINESS AFFAIRS","OEMC","TRANSPORTN","HEALTH","LAW","FINANCE","BUILDINGS","CITY CLERK","PROCUREMENT","ADMIN HEARNG"])                                                                       
 ("ADMINISTRATIVE ASST III",UTF8String["WATER MGMNT","POLICE","GENERAL SERVICES","STREETS & SAN","AVIATION","FIRE","PUBLIC LIBRARY","BUSINESS AFFAIRS","OEMC","TRANSPORTN","HEALTH","FINANCE","COMMUNITY DEVELOPMENT","BUILDINGS","CITY CLERK","HUMAN RELATIONS","ADMIN HEARNG"])                                                                   
 ("ASST TO THE COMMISSIONER",UTF8String["WATER MGMNT","POLICE","GENERAL SERVICES","STREETS & SAN","AVIATION","FIRE","FAMILY & SUPPORT","PUBLIC LIBRARY","DoIT","BUSINESS AFFAIRS","TRANSPORTN","FINANCE","CULTURAL AFFAIRS","COMMUNITY DEVELOPMENT","PROCUREMENT","HUMAN RELATIONS"])                                                               
 ⋮                                                                                                                                                                                                                                                                                                                                                  
 ("MANAGER OF FINANCE",UTF8String["STREETS & SAN","AVIATION","FAMILY & SUPPORT","TRANSPORTN","HEALTH"])                                                                                                                                                                                                                                             
 ("PAINTER",UTF8String["WATER MGMNT","GENERAL SERVICES","STREETS & SAN","AVIATION","TRANSPORTN"])                                                                                                                                                                                                                                                   
 ("POOL MOTOR TRUCK DRIVER",UTF8String["WATER MGMNT","STREETS & SAN","AVIATION","PUBLIC LIBRARY","TRANSPORTN"])                                                                                                                                                                                                                                     
 ("SPECIAL ASST",UTF8String["FAMILY & SUPPORT","BUSINESS AFFAIRS","OEMC","TRANSPORTN","CULTURAL AFFAIRS"])                                                                                                                                                                                                                                          
 ("STORES LABORER",UTF8String["GENERAL SERVICES","AVIATION","FIRE","TRANSPORTN","PROCUREMENT"])                                                                                                                                                                                                                                                     

Find the popular names of Chicago employees.

In [57]:
@query(
    employee
    :group(name)
    :select(name, size => count(employee))
    :sort(size:desc))
Out[57]:
5038-element Array{Tuple{UTF8String,Int64},1}:
 ("MICHAEL",1170)
 ("JOHN",914)    
 ("JAMES",691)   
 ("ROBERT",649)  
 ("JOSEPH",543)  
 ⋮               
 ("ZORAN",1)     
 ("ZORRAYDA",1)  
 ("ZULEMA",1)    
 ("ZUZANNA",1)   
 ("ZYNETTA",1)   

Find the top salary by the first name, but only if there are at least 10 employees having this name.

In [58]:
@query(
    employee
    :group(name)
    :filter(count(employee)>=10)
    :select(name, max_salary => max(employee.salary))
    :sort(max_salary:desc))
Out[58]:
483-element Array{Tuple{UTF8String,Int64},1}:
 ("JOSE",202728)   
 ("CHARLES",197736)
 ("ROBERT",194256) 
 ("ANTHONY",187680)
 ("JOHN",187680)   
 ⋮                 
 ("SARA",86520)    
 ("TERRI",86520)   
 ("DARIUS",85020)  
 ("ANNIE",83616)   
 ("BERTHA",83616)  

Find the number of employees for each department and salary bracket.

In [59]:
@query(
    employee
    :group(department, salary_bracket => salary/10000*10000 :desc)
    :select(department, salary_bracket, salary_bracket+9999, count(employee)))
Out[59]:
405-element Array{Tuple{UTF8String,Int64,Int64,Int64},1}:
 ("WATER MGMNT",160000,169999,1)    
 ("WATER MGMNT",150000,159999,1)    
 ("WATER MGMNT",130000,139999,2)    
 ("WATER MGMNT",120000,129999,5)    
 ("WATER MGMNT",110000,119999,32)   
 ⋮                                  
 ("ADMIN HEARNG",80000,89999,2)     
 ("ADMIN HEARNG",70000,79999,3)     
 ("ADMIN HEARNG",60000,69999,19)    
 ("ADMIN HEARNG",50000,59999,7)     
 ("LICENSE APPL COMM",60000,69999,1)

To generate totals on each dimension, use :group_cube.

Find the number of employees for each department and salary bracket, including totals.

In [60]:
@query(
    employee
    :group_cube(department, salary_bracket => salary/10000*10000 :desc)
    :select(department, salary_bracket, salary_bracket+9999, count(employee)))
Out[60]:
464-element Array{Tuple{Nullable{UTF8String},Nullable{Int64},Nullable{Int64},Int64},1}:
 (Nullable("WATER MGMNT"),Nullable(160000),Nullable(169999),1)     
 (Nullable("WATER MGMNT"),Nullable(150000),Nullable(159999),1)     
 (Nullable("WATER MGMNT"),Nullable(130000),Nullable(139999),2)     
 (Nullable("WATER MGMNT"),Nullable(120000),Nullable(129999),5)     
 (Nullable("WATER MGMNT"),Nullable(110000),Nullable(119999),32)    
 ⋮                                                                 
 (Nullable{UTF8String}(),Nullable(30000),Nullable(39999),215)      
 (Nullable{UTF8String}(),Nullable(20000),Nullable(29999),420)      
 (Nullable{UTF8String}(),Nullable(10000),Nullable(19999),1209)     
 (Nullable{UTF8String}(),Nullable(0),Nullable(9999),376)           
 (Nullable{UTF8String}(),Nullable{Int64}(),Nullable{Int64}(),32181)

Add :dataframe to present this data in tabular form.

In [61]:
@query(
    employee
    :group_cube(
        department,
        salary_bracket => salary/10000*10000 :desc)
    :select(
        department,
        low => salary_bracket,
        high => salary_bracket+9999,
        size => count(employee))
    :dataframe)
Out[61]:
departmentlowhighsize
1WATER MGMNT1600001699991
2WATER MGMNT1500001599991
3WATER MGMNT1300001399992
4WATER MGMNT1200001299995
5WATER MGMNT11000011999932
6WATER MGMNT100000109999138
7WATER MGMNT9000099999607
8WATER MGMNT8000089999187
9WATER MGMNT7000079999617
10WATER MGMNT600006999995
11WATER MGMNT500005999976
12WATER MGMNT400004999987
13WATER MGMNTNANA1848
14POLICE2600002699991
15POLICE1900001999992

You can specify dimensions separately using :partition.

Find the number of positions, the number of employees and the highest salary for the first 3 departments.

In [62]:
@query(
    employee
    :partition(department:take(3))
    :select(department.name, count(unique(employee.position)), count(employee), max(employee.salary)))
Out[62]:
3-element Array{Tuple{UTF8String,Int64,Int64,Nullable{Int64}},1}:
 ("WATER MGMNT",154,1848,Nullable(169512))    
 ("POLICE",129,13570,Nullable(260004))        
 ("GENERAL SERVICES",119,924,Nullable(157092))

Similar to :group_cube, :partition_cube adds totals.

Find the numbers of positions and employees, the highest salary and the most popular position for the first 3 departments, and include the totals.

In [63]:
@query(
    employee
    :partition_cube(department:take(3))
    :select(
        department.name,
        num_pos => count(unique(employee.position)),
        num_empl => count(employee),
        max_salary => max(employee.salary),
        pop_position => employee:group(position):first(count(employee)).position)
    :dataframe)
Out[63]:
namenum_posnum_emplmax_salarypop_position
1WATER MGMNT1541848169512CONSTRUCTION LABORER
2POLICE12913570260004POLICE OFFICER
3GENERAL SERVICES119924157092MACHINIST (AUTOMOTIVE)
4NA33416342260004POLICE OFFICER

You can use an array constructor or range() combinator to specify the dimensions.

In [64]:
@query(range(0, 60000, max(employee.salary)))
Out[64]:
5-element Array{Int64,1}:
      0
  60000
 120000
 180000
 240000

For the given departments, employee's names and salary brackets, find the number of employees, the number of different positions and the most popular position.

In [65]:
@query(
    employee
    :define(salary_bracket => salary/60000*60000)
    :partition(
        department:take(3),
        name => ["ANTHONY", "BRIAN"],
        salary_bracket => range(0, 60000, max(employee.salary)))
    :select(
        dept => department.name,
        name,
        low => salary_bracket,
        high => salary_bracket+59999,
        pop_position => employee:group(position):first(count(employee)).position,
        num_pos => count(unique(employee.position)),
        num_empl => count(employee))
    :dataframe)
Out[65]:
deptnamelowhighpop_positionnum_posnum_empl
1WATER MGMNTANTHONY059999LABORER - APPRENTICE11
2WATER MGMNTANTHONY60000119999CONSTRUCTION LABORER1532
3WATER MGMNTANTHONY120000179999NA00
4WATER MGMNTANTHONY180000239999NA00
5WATER MGMNTANTHONY240000299999NA00
6WATER MGMNTBRIAN059999LABORER - APPRENTICE22
7WATER MGMNTBRIAN60000119999HOISTING ENGINEER1118
8WATER MGMNTBRIAN120000179999NA00
9WATER MGMNTBRIAN180000239999NA00
10WATER MGMNTBRIAN240000299999NA00
11POLICEANTHONY059999POLICE OFFICER27
12POLICEANTHONY60000119999POLICE OFFICER6145
13POLICEANTHONY120000179999LIEUTENANT14
14POLICEANTHONY180000239999CHIEF11
15POLICEANTHONY240000299999NA00

Output formatting

The output can be produced in the form of a JSON value or a DataFrame object.

In [66]:
@query(department:json)
Out[66]:
35-element Array{Dict{Any,Any},1}:
 Dict{Any,Any}(:name=>"WATER MGMNT")      
 Dict{Any,Any}(:name=>"POLICE")           
 Dict{Any,Any}(:name=>"GENERAL SERVICES") 
 Dict{Any,Any}(:name=>"CITY COUNCIL")     
 Dict{Any,Any}(:name=>"STREETS & SAN")    
 ⋮                                        
 Dict{Any,Any}(:name=>"BOARD OF ETHICS")  
 Dict{Any,Any}(:name=>"POLICE BOARD")     
 Dict{Any,Any}(:name=>"BUDGET & MGMT")    
 Dict{Any,Any}(:name=>"ADMIN HEARNG")     
 Dict{Any,Any}(:name=>"LICENSE APPL COMM")

Selector items become fields of the JSON dictionary.

In [67]:
@query(
    department
    :select(
        name,
        size => count(employee),
        head => employee:first(salary))
    :json)
Out[67]:
35-element Array{Dict{Any,Any},1}:
 Dict{Any,Any}(:name=>"WATER MGMNT",:size=>1848,:head=>Dict{Any,Any}(:surname=>"P",:name=>"THOMAS"))     
 Dict{Any,Any}(:name=>"POLICE",:size=>13570,:head=>Dict{Any,Any}(:surname=>"M",:name=>"GARRY"))          
 Dict{Any,Any}(:name=>"GENERAL SERVICES",:size=>924,:head=>Dict{Any,Any}(:surname=>"R",:name=>"DAVID"))  
 Dict{Any,Any}(:name=>"CITY COUNCIL",:size=>397,:head=>Dict{Any,Any}(:surname=>"K",:name=>"MARLA"))      
 Dict{Any,Any}(:name=>"STREETS & SAN",:size=>2090,:head=>Dict{Any,Any}(:surname=>"W",:name=>"CHARLES"))  
 ⋮                                                                                                       
 Dict{Any,Any}(:name=>"BOARD OF ETHICS",:size=>9,:head=>Dict{Any,Any}(:surname=>"B",:name=>"STEVEN"))    
 Dict{Any,Any}(:name=>"POLICE BOARD",:size=>2,:head=>Dict{Any,Any}(:surname=>"C",:name=>"MAX"))          
 Dict{Any,Any}(:name=>"BUDGET & MGMT",:size=>43,:head=>Dict{Any,Any}(:surname=>"H",:name=>"ALEXANDRA"))  
 Dict{Any,Any}(:name=>"ADMIN HEARNG",:size=>39,:head=>Dict{Any,Any}(:surname=>"J",:name=>"PATRICIA"))    
 Dict{Any,Any}(:name=>"LICENSE APPL COMM",:size=>1,:head=>Dict{Any,Any}(:surname=>"G",:name=>"MICHELLE"))

You can pass a list of output fields to the json combinator.

In [68]:
@query(
    department
    :json(
        name,
        size => count(employee),
        head => employee:first(salary)))
Out[68]:
35-element Array{Dict{Any,Any},1}:
 Dict{Any,Any}(:name=>"WATER MGMNT",:size=>1848,:head=>Dict{Any,Any}(:surname=>"P",:name=>"THOMAS"))     
 Dict{Any,Any}(:name=>"POLICE",:size=>13570,:head=>Dict{Any,Any}(:surname=>"M",:name=>"GARRY"))          
 Dict{Any,Any}(:name=>"GENERAL SERVICES",:size=>924,:head=>Dict{Any,Any}(:surname=>"R",:name=>"DAVID"))  
 Dict{Any,Any}(:name=>"CITY COUNCIL",:size=>397,:head=>Dict{Any,Any}(:surname=>"K",:name=>"MARLA"))      
 Dict{Any,Any}(:name=>"STREETS & SAN",:size=>2090,:head=>Dict{Any,Any}(:surname=>"W",:name=>"CHARLES"))  
 ⋮                                                                                                       
 Dict{Any,Any}(:name=>"BOARD OF ETHICS",:size=>9,:head=>Dict{Any,Any}(:surname=>"B",:name=>"STEVEN"))    
 Dict{Any,Any}(:name=>"POLICE BOARD",:size=>2,:head=>Dict{Any,Any}(:surname=>"C",:name=>"MAX"))          
 Dict{Any,Any}(:name=>"BUDGET & MGMT",:size=>43,:head=>Dict{Any,Any}(:surname=>"H",:name=>"ALEXANDRA"))  
 Dict{Any,Any}(:name=>"ADMIN HEARNG",:size=>39,:head=>Dict{Any,Any}(:surname=>"J",:name=>"PATRICIA"))    
 Dict{Any,Any}(:name=>"LICENSE APPL COMM",:size=>1,:head=>Dict{Any,Any}(:surname=>"G",:name=>"MICHELLE"))

Use :dataframe combinator to generate DataFrame output.

In [69]:
@query(employee:dataframe)
Out[69]:
namesurnamedepartmentpositionsalary
1ELVIAAWATER MGMNTWATER RATE TAKER88968
2JEFFERYAPOLICEPOLICE OFFICER80778
3KARINAAPOLICEPOLICE OFFICER80778
4KIMBERLEIAGENERAL SERVICESCHIEF CONTRACT EXPEDITER84780
5VICENTEAWATER MGMNTCIVIL ENGINEER IV104736
6ANABELACITY COUNCILASST TO THE ALDERMAN70764
7EMMANUELASTREETS & SANGENERAL LABORER - DSS40560
8ROBERTAAVIATIONELECTRICAL MECHANIC91520
9JAMESAFIREFIRE ENGINEER90456
10TERRYAPOLICEPOLICE OFFICER86520
11BETTYAFAMILY & SUPPORTFOSTER GRANDPARENT2756
12LYNISEAPOLICECLERK III43920
13WILLIAMAIPRAINVESTIGATOR - IPRA II72468
14ZAIDAPOLICEPOLICE OFFICER69684
15ABDALMAHDAPOLICEPOLICE OFFICER80778
In [70]:
@query(
    department
    :select(
        name,
        size => count(employee),
        max_salary => max(employee.salary))
    :dataframe)
Out[70]:
namesizemax_salary
1WATER MGMNT1848169512
2POLICE13570260004
3GENERAL SERVICES924157092
4CITY COUNCIL397160248
5STREETS & SAN2090157092
6AVIATION1344161652
7FIRE4875202728
8FAMILY & SUPPORT679157092
9IPRA83161856
10PUBLIC LIBRARY951167004
11DoIT106154992
12BUSINESS AFFAIRS173157092
13OEMC1135167796
14TRANSPORTN1200169500
15HEALTH555177000

You can pass a list of output fields to the dataframe combinator.

In [71]:
@query(
    department
    :dataframe(
        name,
        size => count(employee),
        max_salary => max(employee.salary)))
Out[71]:
namesizemax_salary
1WATER MGMNT1848169512
2POLICE13570260004
3GENERAL SERVICES924157092
4CITY COUNCIL397160248
5STREETS & SAN2090157092
6AVIATION1344161652
7FIRE4875202728
8FAMILY & SUPPORT679157092
9IPRA83161856
10PUBLIC LIBRARY951167004
11DoIT106154992
12BUSINESS AFFAIRS173157092
13OEMC1135167796
14TRANSPORTN1200169500
15HEALTH555177000

Cartesian product and tagged union

Use :mix to generate a Cartesian product.

Multiplication table.

In [72]:
@query(
    mix(a => range(2,1,10),
        b => range(2,1,10),
        c => range(2,1,10))
    :filter((a <= b) & (b <= c))
    :select(a, b, c, (a*b)*c))
Out[72]:
165-element Array{Tuple{Int64,Int64,Int64,Int64},1}:
 (2,2,2,8)      
 (2,2,3,12)     
 (2,2,4,16)     
 (2,2,5,20)     
 (2,2,6,24)     
 ⋮              
 (8,10,10,800)  
 (9,9,9,729)    
 (9,9,10,810)   
 (9,10,10,900)  
 (10,10,10,1000)

All pairs of departments with approximately equal number of employees.

In [73]:
@query(
    mix(department, department)
    :filter((left.id != right.id) & (left.count(employee)/10 == right.count(employee)/10))
    :select(left.name, left.count(employee), right.name, right.count(employee)))
Out[73]:
16-element Array{Tuple{UTF8String,Int64,UTF8String,Int64},1}:
 ("IPRA",83,"CITY CLERK",82)                
 ("IPRA",83,"PROCUREMENT",81)               
 ("ANIMAL CONTRL",67,"HUMAN RESOURCES",68)  
 ("CITY CLERK",82,"IPRA",83)                
 ("CITY CLERK",82,"PROCUREMENT",81)         
 ⋮                                          
 ("BOARD OF ETHICS",9,"LICENSE APPL COMM",1)
 ("POLICE BOARD",2,"BOARD OF ETHICS",9)     
 ("POLICE BOARD",2,"LICENSE APPL COMM",1)   
 ("LICENSE APPL COMM",1,"BOARD OF ETHICS",9)
 ("LICENSE APPL COMM",1,"POLICE BOARD",2)   

Use pack() combinator to generate a tagged union.

In [74]:
@query(
    pack(
        a => range(1,1,5),
        z => range(95,1,99)))
Out[74]:
10-element Array{Int64,1}:
  1
  2
  3
  4
  5
 95
 96
 97
 98
 99

Values generated by pack() don't have to have the same type.

In [75]:
@query(pack(employee, department))
Out[75]:
32216-element Array{Tuple{UTF8String,Vararg{Any}},1}:
 ("ELVIA","A","WATER MGMNT","WATER RATE TAKER",88968)                 
 ("JEFFERY","A","POLICE","POLICE OFFICER",80778)                      
 ("KARINA","A","POLICE","POLICE OFFICER",80778)                       
 ("KIMBERLEI","A","GENERAL SERVICES","CHIEF CONTRACT EXPEDITER",84780)
 ("VICENTE","A","WATER MGMNT","CIVIL ENGINEER IV",104736)             
 ⋮                                                                    
 ("BOARD OF ETHICS",)                                                 
 ("POLICE BOARD",)                                                    
 ("BUDGET & MGMT",)                                                   
 ("ADMIN HEARNG",)                                                    
 ("LICENSE APPL COMM",)                                               

You can extract tagged values using combinators named after the tags.

In [76]:
@query(pack(employee, department).employee)
Out[76]:
32181-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:
 ("ELVIA","A","WATER MGMNT","WATER RATE TAKER",88968)                     
 ("JEFFERY","A","POLICE","POLICE OFFICER",80778)                          
 ("KARINA","A","POLICE","POLICE OFFICER",80778)                           
 ("KIMBERLEI","A","GENERAL SERVICES","CHIEF CONTRACT EXPEDITER",84780)    
 ("VICENTE","A","WATER MGMNT","CIVIL ENGINEER IV",104736)                 
 ⋮                                                                        
 ("MICHAEL","Z","GENERAL SERVICES","FRM OF MACHINISTS - AUTOMOTIVE",97448)
 ("PETER","Z","POLICE","POLICE OFFICER",86520)                            
 ("MARK","Z","POLICE","POLICE OFFICER",83616)                             
 ("CARLO","Z","POLICE","POLICE OFFICER",86520)                            
 ("DARIUSZ","Z","DoIT","CHIEF DATA BASE ANALYST",110352)                  
In [77]:
@query(pack(employee, department).department)
Out[77]:
35-element Array{Tuple{UTF8String},1}:
 ("WATER MGMNT",)      
 ("POLICE",)           
 ("GENERAL SERVICES",) 
 ("CITY COUNCIL",)     
 ("STREETS & SAN",)    
 ⋮                     
 ("BOARD OF ETHICS",)  
 ("POLICE BOARD",)     
 ("BUDGET & MGMT",)    
 ("ADMIN HEARNG",)     
 ("LICENSE APPL COMM",)
In [78]:
@query(pack(employee, department):select(employee.position, department.name))
Out[78]:
32216-element Array{Tuple{Nullable{UTF8String},Nullable{UTF8String}},1}:
 (Nullable("WATER RATE TAKER"),Nullable{UTF8String}())        
 (Nullable("POLICE OFFICER"),Nullable{UTF8String}())          
 (Nullable("POLICE OFFICER"),Nullable{UTF8String}())          
 (Nullable("CHIEF CONTRACT EXPEDITER"),Nullable{UTF8String}())
 (Nullable("CIVIL ENGINEER IV"),Nullable{UTF8String}())       
 ⋮                                                            
 (Nullable{UTF8String}(),Nullable("BOARD OF ETHICS"))         
 (Nullable{UTF8String}(),Nullable("POLICE BOARD"))            
 (Nullable{UTF8String}(),Nullable("BUDGET & MGMT"))           
 (Nullable{UTF8String}(),Nullable("ADMIN HEARNG"))            
 (Nullable{UTF8String}(),Nullable("LICENSE APPL COMM"))       

Use unlink to create an unconditional link to an entity class.

Find the employees with salary within 50% of the top salary.

In [79]:
@query(
    employee
    :take(500)
    :filter(salary > max(unlink(employee).salary)/2))
Out[79]:
5-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:
 ("BRADLEY","A","FIRE","CAPTAIN-EMT",131466)                                     
 ("DANA","A","POLICE","DEPUTY CHIEF",170112)                                     
 ("MICHAEL","A","POLICE","CAPTAIN",134820)                                       
 ("SANDRA","A","ANIMAL CONTRL","EXECUTIVE DIR OF ANIMAL CARE AND CONTROL",138420)
 ("VERDIE","A","FIRE","ASST DEPUTY CHIEF PARAMEDIC",156360)                      

Use link to create a link on an arbitrary condition.

For a given employee, find all his namesakes.

In [80]:
@query(
    employee[10]
    :define(namesake => link((left.id!=right.id)&(left.name==right.name), employee))
    :select(name, count(namesake), namesake:take(3)))
Out[80]:
Nullable(("TERRY",36,[("TERRY","A","TRANSPORTN","FOREMAN OF CONSTRUCTION LABORERS",81328),("TERRY","B","POLICE","POLICE OFFICER",83616),("TERRY","B","POLICE","POLICE OFFICER",86520)]))

Queries with parameteres

Find all employees with the given name and position.

In [81]:
@query(
    employee:filter((position==POSITION) & (name==NAME)),
    POSITION="POLICE OFFICER",
    NAME="CHARLES")
Out[81]:
36-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:
 ("CHARLES","A","POLICE","POLICE OFFICER",46206)
 ("CHARLES","A","POLICE","POLICE OFFICER",89718)
 ("CHARLES","A","POLICE","POLICE OFFICER",89718)
 ("CHARLES","B","POLICE","POLICE OFFICER",83616)
 ("CHARLES","B","POLICE","POLICE OFFICER",89718)
 ⋮                                              
 ("CHARLES","T","POLICE","POLICE OFFICER",46206)
 ("CHARLES","W","POLICE","POLICE OFFICER",92316)
 ("CHARLES","W","POLICE","POLICE OFFICER",86520)
 ("CHARLES","W","POLICE","POLICE OFFICER",86520)
 ("CHARLES","W","POLICE","POLICE OFFICER",83616)

Find all departments bigger than the given size.

In [82]:
@query(
    department
    :filter(count(employee)>SIZE)
    :select(name, count(employee)-SIZE),
    SIZE=1000)
Out[82]:
7-element Array{Tuple{UTF8String,Int64},1}:
 ("WATER MGMNT",848)   
 ("POLICE",12570)      
 ("STREETS & SAN",1090)
 ("AVIATION",344)      
 ("FIRE",3875)         
 ("OEMC",135)          
 ("TRANSPORTN",200)    

Find all employees in the given departments.

In [83]:
@query(
    employee:filter(department.name in DEPTS),
    DEPTS=["POLICE", "FIRE"])
Out[83]:
18445-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:
 ("JEFFERY","A","POLICE","POLICE OFFICER",80778)
 ("KARINA","A","POLICE","POLICE OFFICER",80778) 
 ("JAMES","A","FIRE","FIRE ENGINEER",90456)     
 ("TERRY","A","POLICE","POLICE OFFICER",86520)  
 ("LYNISE","A","POLICE","CLERK III",43920)      
 ⋮                                              
 ("MATTHEW","Z","FIRE","FIREFIGHTER-EMT",91764) 
 ("BRYAN","Z","POLICE","POLICE OFFICER",80778)  
 ("PETER","Z","POLICE","POLICE OFFICER",86520)  
 ("MARK","Z","POLICE","POLICE OFFICER",83616)   
 ("CARLO","Z","POLICE","POLICE OFFICER",86520)  

Parameters could also be calculated dynamically using combinator given.

Find the highest paid employee.

In [84]:
@query(
    employee
    :filter(salary==MAX_SALARY)
    :given(MAX_SALARY => max(employee.salary)))
Out[84]:
1-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:
 ("GARRY","M","POLICE","SUPERINTENDENT OF POLICE",260004)

Find the highest paid employee in each department.

In [85]:
@query(
    department
    :select(
        name,
        employee
            :filter(salary==MAX_SALARY)
            :given(MAX_SALARY => max(employee.salary))))
Out[85]:
35-element Array{Tuple{UTF8String,Array{Tuple{UTF8String,UTF8String},1}},1}:
 ("WATER MGMNT",[("THOMAS","P")])        
 ("POLICE",[("GARRY","M")])              
 ("GENERAL SERVICES",[("DAVID","R")])    
 ("CITY COUNCIL",[("MARLA","K")])        
 ("STREETS & SAN",[("CHARLES","W")])     
 ⋮                                       
 ("BOARD OF ETHICS",[("STEVEN","B")])    
 ("POLICE BOARD",[("MAX","C")])          
 ("BUDGET & MGMT",[("ALEXANDRA","H")])   
 ("ADMIN HEARNG",[("PATRICIA","J")])     
 ("LICENSE APPL COMM",[("MICHELLE","G")])

Sometimes you may want to refer to other values in the output. Combinators before, after and around allow you to establish a link between output values.

Combinator before refers to the set of previous values in the output.

In [86]:
@query(
    department
    :dataframe(
        name,
        past_names => before.name))
Out[86]:
namepast_names
1WATER MGMNTUTF8String[]
2POLICEUTF8String["WATER MGMNT"]
3GENERAL SERVICESUTF8String["POLICE","WATER MGMNT"]
4CITY COUNCILUTF8String["GENERAL SERVICES","POLICE","WATER MGMNT"]
5STREETS & SANUTF8String["CITY COUNCIL","GENERAL SERVICES","POLICE","WATER MGMNT"]
6AVIATIONUTF8String["STREETS & SAN","CITY COUNCIL","GENERAL SERVICES","POLICE","WATER MGMNT"]
7FIREUTF8String["AVIATION","STREETS & SAN","CITY COUNCIL","GENERAL SERVICES","POLICE","WATER MGMNT"]
8FAMILY & SUPPORTUTF8String["FIRE","AVIATION","STREETS & SAN","CITY COUNCIL","GENERAL SERVICES","POLICE","WATER MGMNT"]
9IPRAUTF8String["FAMILY & SUPPORT","FIRE","AVIATION","STREETS & SAN","CITY COUNCIL","GENERAL SERVICES","POLICE","WATER MGMNT"]
10PUBLIC LIBRARYUTF8String["IPRA","FAMILY & SUPPORT","FIRE","AVIATION","STREETS & SAN","CITY COUNCIL","GENERAL SERVICES","POLICE","WATER MGMNT"]
11DoITUTF8String["PUBLIC LIBRARY","IPRA","FAMILY & SUPPORT","FIRE","AVIATION","STREETS & SAN","CITY COUNCIL","GENERAL SERVICES","POLICE","WATER MGMNT"]
12BUSINESS AFFAIRSUTF8String["DoIT","PUBLIC LIBRARY","IPRA","FAMILY & SUPPORT","FIRE","AVIATION","STREETS & SAN","CITY COUNCIL","GENERAL SERVICES","POLICE","WATER MGMNT"]
13OEMCUTF8String["BUSINESS AFFAIRS","DoIT","PUBLIC LIBRARY","IPRA","FAMILY & SUPPORT","FIRE","AVIATION","STREETS & SAN","CITY COUNCIL","GENERAL SERVICES","POLICE","WATER MGMNT"]
14TRANSPORTNUTF8String["OEMC","BUSINESS AFFAIRS","DoIT","PUBLIC LIBRARY","IPRA","FAMILY & SUPPORT","FIRE","AVIATION","STREETS & SAN","CITY COUNCIL","GENERAL SERVICES","POLICE","WATER MGMNT"]
15HEALTHUTF8String["TRANSPORTN","OEMC","BUSINESS AFFAIRS","DoIT","PUBLIC LIBRARY","IPRA","FAMILY & SUPPORT","FIRE","AVIATION","STREETS & SAN","CITY COUNCIL","GENERAL SERVICES","POLICE","WATER MGMNT"]

Similarly, combinator after refers to all the subsequent values.

In [87]:
@query(
    department
    :dataframe(
        name,
        next_name => first(after).name))
Out[87]:
namenext_name
1WATER MGMNTPOLICE
2POLICEGENERAL SERVICES
3GENERAL SERVICESCITY COUNCIL
4CITY COUNCILSTREETS & SAN
5STREETS & SANAVIATION
6AVIATIONFIRE
7FIREFAMILY & SUPPORT
8FAMILY & SUPPORTIPRA
9IPRAPUBLIC LIBRARY
10PUBLIC LIBRARYDoIT
11DoITBUSINESS AFFAIRS
12BUSINESS AFFAIRSOEMC
13OEMCTRANSPORTN
14TRANSPORTNHEALTH
15HEALTHMAYOR'S OFFICE

You can use the before combinator to number output records.

In [88]:
@query(
    department
    :select(1+count(before), name))
Out[88]:
35-element Array{Tuple{Int64,UTF8String},1}:
 (1,"WATER MGMNT")       
 (2,"POLICE")            
 (3,"GENERAL SERVICES")  
 (4,"CITY COUNCIL")      
 (5,"STREETS & SAN")     
 ⋮                       
 (31,"BOARD OF ETHICS")  
 (32,"POLICE BOARD")     
 (33,"BUDGET & MGMT")    
 (34,"ADMIN HEARNG")     
 (35,"LICENSE APPL COMM")

A variant of before called and_before includes the current record in the set. You can also use and_before to calculate running totals.

In [89]:
@query(
    department
    :define(size => count(employee))
    :dataframe(
        name,
        size,
        total => sum(and_before.size)))
Out[89]:
namesizetotal
1WATER MGMNT18481848
2POLICE1357015418
3GENERAL SERVICES92416342
4CITY COUNCIL39716739
5STREETS & SAN209018829
6AVIATION134420173
7FIRE487525048
8FAMILY & SUPPORT67925727
9IPRA8325810
10PUBLIC LIBRARY95126761
11DoIT10626867
12BUSINESS AFFAIRS17327040
13OEMC113528175
14TRANSPORTN120029375
15HEALTH55529930

Combinator and_around let you refer to the full set of the output values.

Find the departments with the largest number of employees.

In [90]:
@query(
    department
    :define(size => count(employee))
    :filter(size == max(and_around.size))
    :select(name, size))
Out[90]:
1-element Array{Tuple{UTF8String,Int64},1}:
 ("POLICE",13570)

Combinator around can also give you output values that have the same property as the current value.

For each employee in a certain department, find how much does their salary differ from the top salary for their position.

In [91]:
@query(
    employee
    :filter(department.name == DEPT)
    :dataframe(
        name,
        surname,
        position,
        salary,
        salary_diff => max(and_around(position).salary)-salary),
    DEPT="TREASURER")
Out[91]:
namesurnamepositionsalarysalary_diff
1SAEEDAASST CITY TREASURER850200
2ELIZABETHAACCOUNTANT I728400
3KONSTANTINESAASSISTANT DIRECTOR OF FINANCE730800
4SARAASTUDENT INTERN156000
5NANCYCEXECUTIVE SECRETARY I455280
6JULIAEDEPUTY CITY TREASURER1138980
7JACQUELINEFASST TO THE CITY TREASURER765120
8MICHAELFPORTFOLIO MANAGER6415212060
9JAMESGSTAFF ASST803280
10CHARUGASST TO THE CITY TREASURER6351612996
11ALEXANDRAHSTUDENT INTERN156000
12KARENHACCOUNTANT III879120
13GEORGEHPOLICY ANALYST450000
14KATHRYNHDIR OF PUBLIC AFFAIRS902520
15NASREENKACCOUNTANT IV958800

By default, context extends to all values produced by the combinator while executing the query. You can limit the scope of the context using the frame combinator.

Find the highest paid employee in each department.

In [92]:
@query(
    department
    :select(
        name,
        employee
            :filter(salary==max(and_around.salary))
            :frame))
Out[92]:
35-element Array{Tuple{UTF8String,Array{Tuple{UTF8String,UTF8String},1}},1}:
 ("WATER MGMNT",[("THOMAS","P")])        
 ("POLICE",[("GARRY","M")])              
 ("GENERAL SERVICES",[("DAVID","R")])    
 ("CITY COUNCIL",[("MARLA","K")])        
 ("STREETS & SAN",[("CHARLES","W")])     
 ⋮                                       
 ("BOARD OF ETHICS",[("STEVEN","B")])    
 ("POLICE BOARD",[("MAX","C")])          
 ("BUDGET & MGMT",[("ALEXANDRA","H")])   
 ("ADMIN HEARNG",[("PATRICIA","J")])     
 ("LICENSE APPL COMM",[("MICHELLE","G")])

Compiling and executing queries

You can compile and execute queries separately. To compile a query, use the RBT.prepare() function.

In [93]:
q1 = RBT.@prepare(department.employee.name)
Out[93]:
department.employee.name :: Array{UTF8String,1}
In [94]:
q2 = RBT.@prepare(count(department))
Out[94]:
count(department) :: Int64
In [95]:
q3 = RBT.@prepare(department:select(name,count(employee)))
Out[95]:
department:select(name,count(employee)) :: Array{Tuple{UTF8String,Int64},1}
In [96]:
q4 = RBT.@prepare(count(employee:filter((salary>100000)&(salary<200000))))
Out[96]:
count(employee:filter(&(>(salary,100000),<(salary,200000)))) :: Int64

You can also prepare a query with parameters.

In [97]:
q5 = RBT.@prepare(X*(Y*Z), X=Int, Y=Nullable{Int}, Z=Vector{Int})
Out[97]:
*(X,*(Y,Z)) :: {Void, X => Int64, Y => Nullable{Int64}, Z => Array{Int64,1}} -> Array{Int64,1}
In [98]:
q6 = RBT.@prepare(employee:filter((name == NAME) & (salary > MIN_SALARY)), NAME=UTF8String, MIN_SALARY=Int)
Out[98]:
employee:filter(&(==(name,NAME),>(salary,MIN_SALARY))) :: {Void, MIN_SALARY => Int64, NAME => UTF8String} -> Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}

Queries know their parameters.

In [99]:
RBT.params(q1)
Out[99]:
Pair{Tuple{Vararg{Symbol}},Tuple{Vararg{Type{T}}}}((),())
In [100]:
RBT.params(q5)
Out[100]:
Pair{Tuple{Vararg{Symbol}},Tuple{Vararg{Type{T}}}}((:X,:Y,:Z),(Int64,Nullable{Int64},Array{Int64,1}))
In [101]:
RBT.params(q6)
Out[101]:
Pair{Tuple{Vararg{Symbol}},Tuple{Vararg{Type{T}}}}((:MIN_SALARY,:NAME),(Int64,UTF8String))

To execute a query, call the compiled query as a function.

In [102]:
q1()
Out[102]:
32181-element Array{UTF8String,1}:
 "ELVIA"     
 "VICENTE"   
 "MUHAMMAD"  
 "GIRLEY"    
 "DILAN"     
 ⋮           
 "NANCY"     
 "DARCI"     
 "THADDEUS"  
 "RACHENETTE"
 "MICHELLE"  
In [103]:
q2()
Out[103]:
35
In [104]:
q3()
Out[104]:
35-element Array{Tuple{UTF8String,Int64},1}:
 ("WATER MGMNT",1848)    
 ("POLICE",13570)        
 ("GENERAL SERVICES",924)
 ("CITY COUNCIL",397)    
 ("STREETS & SAN",2090)  
 ⋮                       
 ("BOARD OF ETHICS",9)   
 ("POLICE BOARD",2)      
 ("BUDGET & MGMT",43)    
 ("ADMIN HEARNG",39)     
 ("LICENSE APPL COMM",1) 
In [105]:
q4()
Out[105]:
3916
In [106]:
q5(X=5, Y=Nullable(4), Z=[3,2,1])
Out[106]:
3-element Array{Int64,1}:
 60
 40
 20
In [107]:
q6(NAME="CHARLES", MIN_SALARY=100000)
Out[107]:
28-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:
 ("CHARLES","A","POLICE","SERGEANT",103590)                                     
 ("CHARLES","B","POLICE","SERGEANT",106920)                                     
 ("CHARLES","B","POLICE","SERGEANT",103590)                                     
 ("CHARLES","B","FINANCE","AUDITOR IV",114492)                                  
 ("CHARLES","D","POLICE","SERGEANT",106920)                                     
 ⋮                                                                              
 ("CHARLES","S","FIRE","FIRST DEPUTY FIRE COMMISSIONER",197736)                 
 ("CHARLES","W","STREETS & SAN","GENERAL SUPT OF STREETS AND SANITATION",110112)
 ("CHARLES","W","FIRE","LIEUTENANT-EMT",106524)                                 
 ("CHARLES","W","FIRE","BATTALION CHIEF - EMT",143682)                          
 ("CHARLES","W","STREETS & SAN","COMMISSIONER OF STREETS AND SANITATION",157092)