include("../citydb.jl")
using RBT
setdb(citydb)
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:
@query(6*(3+4))
42
Find the names of all departments.
@query(department.name)
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.
@query(department.employee.name)
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.
@query(employee.name)
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.
@query(employee.department.name)
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.
@query(employee.salary)
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.
@query(employee)
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.
@query(department.employee)
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")
Find the number of departments.
@query(count(department))
35
Find the number of employees for each department.
@query(department.count(employee))
35-element Array{Int64,1}: 1848 13570 924 397 2090 ⋮ 9 2 43 39 1
Find the total number of employees.
@query(count(department.employee))
32181
Again, we can query employee
directly.
@query(count(employee))
32181
Find the top salary among all employees.
@query(max(employee.salary))
Nullable(260004)
Find the maximum number of employees per department.
@query(max(department.count(employee)))
Nullable(13570)
For each department, find the number of employees.
@query(department:select(name,count(employee)))
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).
@query(select(department,name,count(employee)))
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.
@query(
department
:select(
name,
count(employee),
max(employee.salary)))
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))
Find the employees with salary greater than $200k.
@query(
employee
:filter(salary>200000)
:select(name,surname,position,salary))
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.
@query(
employee
:select(name,surname,position,salary)
:filter(salary>200000))
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.
@query(
employee
:filter((salary>100000)&(salary<=200000))
:count)
3916
Find the departments with more than 1000 employees.
@query(
department
:filter(count(employee)>1000)
.name)
WARNING: deprecated syntax "filter(count(employee)>1000) ." at In[22]:4. Use "filter(count(employee)>1000)." instead.
7-element Array{UTF8String,1}: "WATER MGMNT" "POLICE" "STREETS & SAN" "AVIATION" "FIRE" "OEMC" "TRANSPORTN"
Find the number of departments with more than 1000 employees.
@query(
count(
department
:filter(count(employee)>1000)))
7
For each department, find the number of employees with salary higher than $100k.
@query(
department
:select(
name,
count(employee:filter(salary>100000))))
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.
@query(
department
:filter(count(employee)<1000)
:select(
name,
employee
:filter(salary>125000)
:select(name,surname,position)))
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}[])
We use the :sort
combinator to sort an array of values.
List the names of departments in alphabetical order.
@query(department.name:sort)
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.
@query(employee:sort(salary))
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.
@query(employee:sort(salary:desc))
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.
@query(
employee
:sort(
salary:desc,
surname:asc,
name:asc))
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
.
@query(
department
:select(name, size => count(employee))
:filter(size>1000)
:sort(size:desc))
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.
@query(
department
:define(size => count(employee))
:select(name, size)
:filter(size>1000)
:sort(size:desc))
7-element Array{Tuple{UTF8String,Int64},1}: ("POLICE",13570) ("FIRE",4875) ("STREETS & SAN",2090) ("WATER MGMNT",1848) ("AVIATION",1344) ("TRANSPORTN",1200) ("OEMC",1135)
Use combinators :first
, :last
, :take
to limit the size of the output array. Use :reverse
to reverse the output array.
The first employee.
@query(first(employee))
Nullable(("ELVIA","A","WATER MGMNT","WATER RATE TAKER",88968))
The name of the first employee.
@query(first(employee).name)
Nullable("ELVIA")
The department with the largest number of employees.
@query(
department
:select(name, size => count(employee))
:sort(size:desc)
:first)
Nullable(("POLICE",13570))
Same query without :sort
.
The department with the largest number of employees.
@query(
department
:select(name, size => count(employee))
:first(size))
Nullable(("POLICE",13570))
Last employee.
@query(employee:last)
Nullable(("DARIUSZ","Z","DoIT","CHIEF DATA BASE ANALYST",110352))
The department with the largest number of employees.
@query(
department
:select(name, size => count(employee))
:sort(size)
:last)
Nullable(("POLICE",13570))
Same query could be written without :sort
.
The department with the largest number of employees.
@query(
department
:select(name, size => count(employee))
:last(size:desc))
Nullable(("POLICE",13570))
Show first 5 employees.
@query(employee:take(5))
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.
@query(employee:skip(10):take(5))
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.
@query(employee:skip(-10))
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.
@query(employee:take(count(employee)/2))
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.
@query(department:reverse)
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",)
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.
@query(department:select(id,name))
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.
@query(department:get(5))
Nullable(("STREETS & SAN",))
If a record is not found, null
value is returned.
@query(department:get(-1))
Nullable{Tuple{UTF8String}}()
You can use brackets instead of :get
.
@query(
department[5]
:select(id, name, count(employee)))
Nullable((5,"STREETS & SAN",2090))
Show all employees of a selected department.
@query(department[5].employee)
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")
(Note: the data on organizational structure is not available, so the output is largely meaningless).
Find the employees who earn more than their manager.
@query(
employee
:filter(salary>managed_by.salary)
:select(name, surname, position, managed_by, salary-managed_by.salary))
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.
@query(
department[26].employee
:select(
name,
surname,
position,
connect(managed_by),
count(connect(manages))))
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.
@query(
department[26].employee
:sort_connect(managed_by)
:select(
depth(managed_by),
name,
surname,
position))
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")
Use :unique
combinator to generate all unique values that appear in a sequence.
List all distinct positions.
@query(employee.position:unique)
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.
@query(
department
:select(
name,
count(unique(employee.position)),
count(employee)))
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.
@query(employee:group(position))
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.
@query(
employee
:group(position)
:select(position, size => count(employee))
:sort(size:desc))
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.
@query(
employee
:group(position)
:define(department => unique(employee.department))
:filter(count(department)>=5)
:select(position, department)
:sort(count(department):desc))
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.
@query(
employee
:group(name)
:select(name, size => count(employee))
:sort(size:desc))
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.
@query(
employee
:group(name)
:filter(count(employee)>=10)
:select(name, max_salary => max(employee.salary))
:sort(max_salary:desc))
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.
@query(
employee
:group(department, salary_bracket => salary/10000*10000 :desc)
:select(department, salary_bracket, salary_bracket+9999, count(employee)))
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.
@query(
employee
:group_cube(department, salary_bracket => salary/10000*10000 :desc)
:select(department, salary_bracket, salary_bracket+9999, count(employee)))
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.
@query(
employee
:group_cube(
department,
salary_bracket => salary/10000*10000 :desc)
:select(
department,
low => salary_bracket,
high => salary_bracket+9999,
size => count(employee))
:dataframe)
department | low | high | size | |
---|---|---|---|---|
1 | WATER MGMNT | 160000 | 169999 | 1 |
2 | WATER MGMNT | 150000 | 159999 | 1 |
3 | WATER MGMNT | 130000 | 139999 | 2 |
4 | WATER MGMNT | 120000 | 129999 | 5 |
5 | WATER MGMNT | 110000 | 119999 | 32 |
6 | WATER MGMNT | 100000 | 109999 | 138 |
7 | WATER MGMNT | 90000 | 99999 | 607 |
8 | WATER MGMNT | 80000 | 89999 | 187 |
9 | WATER MGMNT | 70000 | 79999 | 617 |
10 | WATER MGMNT | 60000 | 69999 | 95 |
11 | WATER MGMNT | 50000 | 59999 | 76 |
12 | WATER MGMNT | 40000 | 49999 | 87 |
13 | WATER MGMNT | NA | NA | 1848 |
14 | POLICE | 260000 | 269999 | 1 |
15 | POLICE | 190000 | 199999 | 2 |
⋮ | ⋮ | ⋮ | ⋮ | ⋮ |
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.
@query(
employee
:partition(department:take(3))
:select(department.name, count(unique(employee.position)), count(employee), max(employee.salary)))
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.
@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)
name | num_pos | num_empl | max_salary | pop_position | |
---|---|---|---|---|---|
1 | WATER MGMNT | 154 | 1848 | 169512 | CONSTRUCTION LABORER |
2 | POLICE | 129 | 13570 | 260004 | POLICE OFFICER |
3 | GENERAL SERVICES | 119 | 924 | 157092 | MACHINIST (AUTOMOTIVE) |
4 | NA | 334 | 16342 | 260004 | POLICE OFFICER |
You can use an array constructor or range()
combinator to specify the dimensions.
@query(range(0, 60000, max(employee.salary)))
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.
@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)
dept | name | low | high | pop_position | num_pos | num_empl | |
---|---|---|---|---|---|---|---|
1 | WATER MGMNT | ANTHONY | 0 | 59999 | LABORER - APPRENTICE | 1 | 1 |
2 | WATER MGMNT | ANTHONY | 60000 | 119999 | CONSTRUCTION LABORER | 15 | 32 |
3 | WATER MGMNT | ANTHONY | 120000 | 179999 | NA | 0 | 0 |
4 | WATER MGMNT | ANTHONY | 180000 | 239999 | NA | 0 | 0 |
5 | WATER MGMNT | ANTHONY | 240000 | 299999 | NA | 0 | 0 |
6 | WATER MGMNT | BRIAN | 0 | 59999 | LABORER - APPRENTICE | 2 | 2 |
7 | WATER MGMNT | BRIAN | 60000 | 119999 | HOISTING ENGINEER | 11 | 18 |
8 | WATER MGMNT | BRIAN | 120000 | 179999 | NA | 0 | 0 |
9 | WATER MGMNT | BRIAN | 180000 | 239999 | NA | 0 | 0 |
10 | WATER MGMNT | BRIAN | 240000 | 299999 | NA | 0 | 0 |
11 | POLICE | ANTHONY | 0 | 59999 | POLICE OFFICER | 2 | 7 |
12 | POLICE | ANTHONY | 60000 | 119999 | POLICE OFFICER | 6 | 145 |
13 | POLICE | ANTHONY | 120000 | 179999 | LIEUTENANT | 1 | 4 |
14 | POLICE | ANTHONY | 180000 | 239999 | CHIEF | 1 | 1 |
15 | POLICE | ANTHONY | 240000 | 299999 | NA | 0 | 0 |
⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ |
The output can be produced in the form of a JSON value or a DataFrame
object.
@query(department:json)
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.
@query(
department
:select(
name,
size => count(employee),
head => employee:first(salary))
:json)
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.
@query(
department
:json(
name,
size => count(employee),
head => employee:first(salary)))
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.
@query(employee:dataframe)
name | surname | department | position | salary | |
---|---|---|---|---|---|
1 | ELVIA | A | WATER MGMNT | WATER RATE TAKER | 88968 |
2 | JEFFERY | A | POLICE | POLICE OFFICER | 80778 |
3 | KARINA | A | POLICE | POLICE OFFICER | 80778 |
4 | KIMBERLEI | A | GENERAL SERVICES | CHIEF CONTRACT EXPEDITER | 84780 |
5 | VICENTE | A | WATER MGMNT | CIVIL ENGINEER IV | 104736 |
6 | ANABEL | A | CITY COUNCIL | ASST TO THE ALDERMAN | 70764 |
7 | EMMANUEL | A | STREETS & SAN | GENERAL LABORER - DSS | 40560 |
8 | ROBERT | A | AVIATION | ELECTRICAL MECHANIC | 91520 |
9 | JAMES | A | FIRE | FIRE ENGINEER | 90456 |
10 | TERRY | A | POLICE | POLICE OFFICER | 86520 |
11 | BETTY | A | FAMILY & SUPPORT | FOSTER GRANDPARENT | 2756 |
12 | LYNISE | A | POLICE | CLERK III | 43920 |
13 | WILLIAM | A | IPRA | INVESTIGATOR - IPRA II | 72468 |
14 | ZAID | A | POLICE | POLICE OFFICER | 69684 |
15 | ABDALMAHD | A | POLICE | POLICE OFFICER | 80778 |
⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ |
@query(
department
:select(
name,
size => count(employee),
max_salary => max(employee.salary))
:dataframe)
name | size | max_salary | |
---|---|---|---|
1 | WATER MGMNT | 1848 | 169512 |
2 | POLICE | 13570 | 260004 |
3 | GENERAL SERVICES | 924 | 157092 |
4 | CITY COUNCIL | 397 | 160248 |
5 | STREETS & SAN | 2090 | 157092 |
6 | AVIATION | 1344 | 161652 |
7 | FIRE | 4875 | 202728 |
8 | FAMILY & SUPPORT | 679 | 157092 |
9 | IPRA | 83 | 161856 |
10 | PUBLIC LIBRARY | 951 | 167004 |
11 | DoIT | 106 | 154992 |
12 | BUSINESS AFFAIRS | 173 | 157092 |
13 | OEMC | 1135 | 167796 |
14 | TRANSPORTN | 1200 | 169500 |
15 | HEALTH | 555 | 177000 |
⋮ | ⋮ | ⋮ | ⋮ |
You can pass a list of output fields to the dataframe
combinator.
@query(
department
:dataframe(
name,
size => count(employee),
max_salary => max(employee.salary)))
name | size | max_salary | |
---|---|---|---|
1 | WATER MGMNT | 1848 | 169512 |
2 | POLICE | 13570 | 260004 |
3 | GENERAL SERVICES | 924 | 157092 |
4 | CITY COUNCIL | 397 | 160248 |
5 | STREETS & SAN | 2090 | 157092 |
6 | AVIATION | 1344 | 161652 |
7 | FIRE | 4875 | 202728 |
8 | FAMILY & SUPPORT | 679 | 157092 |
9 | IPRA | 83 | 161856 |
10 | PUBLIC LIBRARY | 951 | 167004 |
11 | DoIT | 106 | 154992 |
12 | BUSINESS AFFAIRS | 173 | 157092 |
13 | OEMC | 1135 | 167796 |
14 | TRANSPORTN | 1200 | 169500 |
15 | HEALTH | 555 | 177000 |
⋮ | ⋮ | ⋮ | ⋮ |
@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))
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.
@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)))
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.
@query(
pack(
a => range(1,1,5),
z => range(95,1,99)))
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.
@query(pack(employee, department))
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.
@query(pack(employee, department).employee)
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)
@query(pack(employee, department).department)
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",)
@query(pack(employee, department):select(employee.position, department.name))
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.
@query(
employee
:take(500)
:filter(salary > max(unlink(employee).salary)/2))
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.
@query(
employee[10]
:define(namesake => link((left.id!=right.id)&(left.name==right.name), employee))
:select(name, count(namesake), namesake:take(3)))
Nullable(("TERRY",36,[("TERRY","A","TRANSPORTN","FOREMAN OF CONSTRUCTION LABORERS",81328),("TERRY","B","POLICE","POLICE OFFICER",83616),("TERRY","B","POLICE","POLICE OFFICER",86520)]))
Find all employees with the given name and position.
@query(
employee:filter((position==POSITION) & (name==NAME)),
POSITION="POLICE OFFICER",
NAME="CHARLES")
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.
@query(
department
:filter(count(employee)>SIZE)
:select(name, count(employee)-SIZE),
SIZE=1000)
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.
@query(
employee:filter(department.name in DEPTS),
DEPTS=["POLICE", "FIRE"])
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.
@query(
employee
:filter(salary==MAX_SALARY)
:given(MAX_SALARY => max(employee.salary)))
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.
@query(
department
:select(
name,
employee
:filter(salary==MAX_SALARY)
:given(MAX_SALARY => max(employee.salary))))
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.
@query(
department
:dataframe(
name,
past_names => before.name))
name | past_names | |
---|---|---|
1 | WATER MGMNT | UTF8String[] |
2 | POLICE | UTF8String["WATER MGMNT"] |
3 | GENERAL SERVICES | UTF8String["POLICE","WATER MGMNT"] |
4 | CITY COUNCIL | UTF8String["GENERAL SERVICES","POLICE","WATER MGMNT"] |
5 | STREETS & SAN | UTF8String["CITY COUNCIL","GENERAL SERVICES","POLICE","WATER MGMNT"] |
6 | AVIATION | UTF8String["STREETS & SAN","CITY COUNCIL","GENERAL SERVICES","POLICE","WATER MGMNT"] |
7 | FIRE | UTF8String["AVIATION","STREETS & SAN","CITY COUNCIL","GENERAL SERVICES","POLICE","WATER MGMNT"] |
8 | FAMILY & SUPPORT | UTF8String["FIRE","AVIATION","STREETS & SAN","CITY COUNCIL","GENERAL SERVICES","POLICE","WATER MGMNT"] |
9 | IPRA | UTF8String["FAMILY & SUPPORT","FIRE","AVIATION","STREETS & SAN","CITY COUNCIL","GENERAL SERVICES","POLICE","WATER MGMNT"] |
10 | PUBLIC LIBRARY | UTF8String["IPRA","FAMILY & SUPPORT","FIRE","AVIATION","STREETS & SAN","CITY COUNCIL","GENERAL SERVICES","POLICE","WATER MGMNT"] |
11 | DoIT | UTF8String["PUBLIC LIBRARY","IPRA","FAMILY & SUPPORT","FIRE","AVIATION","STREETS & SAN","CITY COUNCIL","GENERAL SERVICES","POLICE","WATER MGMNT"] |
12 | BUSINESS AFFAIRS | UTF8String["DoIT","PUBLIC LIBRARY","IPRA","FAMILY & SUPPORT","FIRE","AVIATION","STREETS & SAN","CITY COUNCIL","GENERAL SERVICES","POLICE","WATER MGMNT"] |
13 | OEMC | UTF8String["BUSINESS AFFAIRS","DoIT","PUBLIC LIBRARY","IPRA","FAMILY & SUPPORT","FIRE","AVIATION","STREETS & SAN","CITY COUNCIL","GENERAL SERVICES","POLICE","WATER MGMNT"] |
14 | TRANSPORTN | UTF8String["OEMC","BUSINESS AFFAIRS","DoIT","PUBLIC LIBRARY","IPRA","FAMILY & SUPPORT","FIRE","AVIATION","STREETS & SAN","CITY COUNCIL","GENERAL SERVICES","POLICE","WATER MGMNT"] |
15 | HEALTH | UTF8String["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.
@query(
department
:dataframe(
name,
next_name => first(after).name))
name | next_name | |
---|---|---|
1 | WATER MGMNT | POLICE |
2 | POLICE | GENERAL SERVICES |
3 | GENERAL SERVICES | CITY COUNCIL |
4 | CITY COUNCIL | STREETS & SAN |
5 | STREETS & SAN | AVIATION |
6 | AVIATION | FIRE |
7 | FIRE | FAMILY & SUPPORT |
8 | FAMILY & SUPPORT | IPRA |
9 | IPRA | PUBLIC LIBRARY |
10 | PUBLIC LIBRARY | DoIT |
11 | DoIT | BUSINESS AFFAIRS |
12 | BUSINESS AFFAIRS | OEMC |
13 | OEMC | TRANSPORTN |
14 | TRANSPORTN | HEALTH |
15 | HEALTH | MAYOR'S OFFICE |
⋮ | ⋮ | ⋮ |
You can use the before
combinator to number output records.
@query(
department
:select(1+count(before), name))
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.
@query(
department
:define(size => count(employee))
:dataframe(
name,
size,
total => sum(and_before.size)))
name | size | total | |
---|---|---|---|
1 | WATER MGMNT | 1848 | 1848 |
2 | POLICE | 13570 | 15418 |
3 | GENERAL SERVICES | 924 | 16342 |
4 | CITY COUNCIL | 397 | 16739 |
5 | STREETS & SAN | 2090 | 18829 |
6 | AVIATION | 1344 | 20173 |
7 | FIRE | 4875 | 25048 |
8 | FAMILY & SUPPORT | 679 | 25727 |
9 | IPRA | 83 | 25810 |
10 | PUBLIC LIBRARY | 951 | 26761 |
11 | DoIT | 106 | 26867 |
12 | BUSINESS AFFAIRS | 173 | 27040 |
13 | OEMC | 1135 | 28175 |
14 | TRANSPORTN | 1200 | 29375 |
15 | HEALTH | 555 | 29930 |
⋮ | ⋮ | ⋮ | ⋮ |
Combinator and_around
let you refer to the full set of the output values.
Find the departments with the largest number of employees.
@query(
department
:define(size => count(employee))
:filter(size == max(and_around.size))
:select(name, size))
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.
@query(
employee
:filter(department.name == DEPT)
:dataframe(
name,
surname,
position,
salary,
salary_diff => max(and_around(position).salary)-salary),
DEPT="TREASURER")
name | surname | position | salary | salary_diff | |
---|---|---|---|---|---|
1 | SAEED | A | ASST CITY TREASURER | 85020 | 0 |
2 | ELIZABETH | A | ACCOUNTANT I | 72840 | 0 |
3 | KONSTANTINES | A | ASSISTANT DIRECTOR OF FINANCE | 73080 | 0 |
4 | SARA | A | STUDENT INTERN | 15600 | 0 |
5 | NANCY | C | EXECUTIVE SECRETARY I | 45528 | 0 |
6 | JULIA | E | DEPUTY CITY TREASURER | 113898 | 0 |
7 | JACQUELINE | F | ASST TO THE CITY TREASURER | 76512 | 0 |
8 | MICHAEL | F | PORTFOLIO MANAGER | 64152 | 12060 |
9 | JAMES | G | STAFF ASST | 80328 | 0 |
10 | CHARU | G | ASST TO THE CITY TREASURER | 63516 | 12996 |
11 | ALEXANDRA | H | STUDENT INTERN | 15600 | 0 |
12 | KAREN | H | ACCOUNTANT III | 87912 | 0 |
13 | GEORGE | H | POLICY ANALYST | 45000 | 0 |
14 | KATHRYN | H | DIR OF PUBLIC AFFAIRS | 90252 | 0 |
15 | NASREEN | K | ACCOUNTANT IV | 95880 | 0 |
⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ |
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.
@query(
department
:select(
name,
employee
:filter(salary==max(and_around.salary))
:frame))
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")])
You can compile and execute queries separately. To compile a query, use the RBT.prepare()
function.
q1 = RBT.@prepare(department.employee.name)
department.employee.name :: Array{UTF8String,1}
q2 = RBT.@prepare(count(department))
count(department) :: Int64
q3 = RBT.@prepare(department:select(name,count(employee)))
department:select(name,count(employee)) :: Array{Tuple{UTF8String,Int64},1}
q4 = RBT.@prepare(count(employee:filter((salary>100000)&(salary<200000))))
count(employee:filter(&(>(salary,100000),<(salary,200000)))) :: Int64
You can also prepare a query with parameters.
q5 = RBT.@prepare(X*(Y*Z), X=Int, Y=Nullable{Int}, Z=Vector{Int})
*(X,*(Y,Z)) :: {Void, X => Int64, Y => Nullable{Int64}, Z => Array{Int64,1}} -> Array{Int64,1}
q6 = RBT.@prepare(employee:filter((name == NAME) & (salary > MIN_SALARY)), NAME=UTF8String, MIN_SALARY=Int)
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.
RBT.params(q1)
Pair{Tuple{Vararg{Symbol}},Tuple{Vararg{Type{T}}}}((),())
RBT.params(q5)
Pair{Tuple{Vararg{Symbol}},Tuple{Vararg{Type{T}}}}((:X,:Y,:Z),(Int64,Nullable{Int64},Array{Int64,1}))
RBT.params(q6)
Pair{Tuple{Vararg{Symbol}},Tuple{Vararg{Type{T}}}}((:MIN_SALARY,:NAME),(Int64,UTF8String))
To execute a query, call the compiled query as a function.
q1()
32181-element Array{UTF8String,1}: "ELVIA" "VICENTE" "MUHAMMAD" "GIRLEY" "DILAN" ⋮ "NANCY" "DARCI" "THADDEUS" "RACHENETTE" "MICHELLE"
q2()
35
q3()
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)
q4()
3916
q5(X=5, Y=Nullable(4), Z=[3,2,1])
3-element Array{Int64,1}: 60 40 20
q6(NAME="CHARLES", MIN_SALARY=100000)
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)