{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Querying the database of Chicago employees" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## The database\n", "We start with loading a sample database. Our sample database is derived from the dataset of all employees of the city of Chicago ([source](https://data.cityofchicago.org/Administration-Finance/Current-Employee-Names-Salaries-and-Position-Title/xzkq-xp2w))." ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "Department:\n", " name :: UTF8String # unique\n", " employee (inverse of Employee.department) :: Array{Employee,1} # unique, covering\n", "Employee:\n", " name :: UTF8String\n", " surname :: UTF8String\n", " position :: UTF8String\n", " salary :: Int64\n", " department :: Department\n", " managed_by :: Nullable{Employee}\n", " manages (inverse of Employee.managed_by) :: Array{Employee,1} # unique" ] }, "execution_count": 1, "metadata": {}, "output_type": "execute_result" } ], "source": [ "include(\"../citydb.jl\")\n", "\n", "using RBT\n", "setdb(citydb)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can execute a query using `@query()` command:" ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "42" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(6*(3+4))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Traversing the database structure\n", "*Find the names of all departments.*" ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "35-element Array{UTF8String,1}:\n", " \"WATER MGMNT\" \n", " \"POLICE\" \n", " \"GENERAL SERVICES\" \n", " \"CITY COUNCIL\" \n", " \"STREETS & SAN\" \n", " ⋮ \n", " \"BOARD OF ETHICS\" \n", " \"POLICE BOARD\" \n", " \"BUDGET & MGMT\" \n", " \"ADMIN HEARNG\" \n", " \"LICENSE APPL COMM\"" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(department.name)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*Find the names of all employees.*" ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "32181-element Array{UTF8String,1}:\n", " \"ELVIA\" \n", " \"VICENTE\" \n", " \"MUHAMMAD\" \n", " \"GIRLEY\" \n", " \"DILAN\" \n", " ⋮ \n", " \"NANCY\" \n", " \"DARCI\" \n", " \"THADDEUS\" \n", " \"RACHENETTE\"\n", " \"MICHELLE\" " ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(department.employee.name)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We are not longer restricted by the hierarchical structure of the database, so we can query employees directly." ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "32181-element Array{UTF8String,1}:\n", " \"ELVIA\" \n", " \"JEFFERY\" \n", " \"KARINA\" \n", " \"KIMBERLEI\"\n", " \"VICENTE\" \n", " ⋮ \n", " \"MICHAEL\" \n", " \"PETER\" \n", " \"MARK\" \n", " \"CARLO\" \n", " \"DARIUSZ\" " ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(employee.name)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can traverse the schema in any direction, for instance, from employees to their departments." ] }, { "cell_type": "code", "execution_count": 6, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "32181-element Array{UTF8String,1}:\n", " \"WATER MGMNT\" \n", " \"POLICE\" \n", " \"POLICE\" \n", " \"GENERAL SERVICES\"\n", " \"WATER MGMNT\" \n", " ⋮ \n", " \"GENERAL SERVICES\"\n", " \"POLICE\" \n", " \"POLICE\" \n", " \"POLICE\" \n", " \"DoIT\" " ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(employee.department.name)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*Show the list of all salaries.*" ] }, { "cell_type": "code", "execution_count": 7, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "32181-element Array{Int64,1}:\n", " 88968\n", " 80778\n", " 80778\n", " 84780\n", " 104736\n", " ⋮\n", " 97448\n", " 86520\n", " 83616\n", " 86520\n", " 110352" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(employee.salary)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "If the traversal ends at an entity class, an array of records is generated." ] }, { "cell_type": "code", "execution_count": 8, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "32181-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:\n", " (\"ELVIA\",\"A\",\"WATER MGMNT\",\"WATER RATE TAKER\",88968) \n", " (\"JEFFERY\",\"A\",\"POLICE\",\"POLICE OFFICER\",80778) \n", " (\"KARINA\",\"A\",\"POLICE\",\"POLICE OFFICER\",80778) \n", " (\"KIMBERLEI\",\"A\",\"GENERAL SERVICES\",\"CHIEF CONTRACT EXPEDITER\",84780) \n", " (\"VICENTE\",\"A\",\"WATER MGMNT\",\"CIVIL ENGINEER IV\",104736) \n", " ⋮ \n", " (\"MICHAEL\",\"Z\",\"GENERAL SERVICES\",\"FRM OF MACHINISTS - AUTOMOTIVE\",97448)\n", " (\"PETER\",\"Z\",\"POLICE\",\"POLICE OFFICER\",86520) \n", " (\"MARK\",\"Z\",\"POLICE\",\"POLICE OFFICER\",83616) \n", " (\"CARLO\",\"Z\",\"POLICE\",\"POLICE OFFICER\",86520) \n", " (\"DARIUSZ\",\"Z\",\"DoIT\",\"CHIEF DATA BASE ANALYST\",110352) " ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(employee)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Which fields are selected depends on the path to the class." ] }, { "cell_type": "code", "execution_count": 9, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "32181-element Array{Tuple{UTF8String,UTF8String},1}:\n", " (\"ELVIA\",\"A\") \n", " (\"VICENTE\",\"A\") \n", " (\"MUHAMMAD\",\"A\") \n", " (\"GIRLEY\",\"A\") \n", " (\"DILAN\",\"A\") \n", " ⋮ \n", " (\"NANCY\",\"T\") \n", " (\"DARCI\",\"W\") \n", " (\"THADDEUS\",\"W\") \n", " (\"RACHENETTE\",\"W\")\n", " (\"MICHELLE\",\"G\") " ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(department.employee)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Summarizing data\n", "*Find the number of departments.*" ] }, { "cell_type": "code", "execution_count": 10, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "35" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(count(department))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*Find the number of employees for each department.*" ] }, { "cell_type": "code", "execution_count": 11, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "35-element Array{Int64,1}:\n", " 1848\n", " 13570\n", " 924\n", " 397\n", " 2090\n", " ⋮\n", " 9\n", " 2\n", " 43\n", " 39\n", " 1" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(department.count(employee))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*Find the total number of employees.*" ] }, { "cell_type": "code", "execution_count": 12, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "32181" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(count(department.employee))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Again, we can query `employee` directly." ] }, { "cell_type": "code", "execution_count": 13, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "32181" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(count(employee))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*Find the top salary among all employees.*" ] }, { "cell_type": "code", "execution_count": 14, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "Nullable(260004)" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(max(employee.salary))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*Find the maximum number of employees per department.*" ] }, { "cell_type": "code", "execution_count": 15, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "Nullable(13570)" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(max(department.count(employee)))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Selecting output columns\n", "*For each department, find the number of employees.*" ] }, { "cell_type": "code", "execution_count": 16, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "35-element Array{Tuple{UTF8String,Int64},1}:\n", " (\"WATER MGMNT\",1848) \n", " (\"POLICE\",13570) \n", " (\"GENERAL SERVICES\",924)\n", " (\"CITY COUNCIL\",397) \n", " (\"STREETS & SAN\",2090) \n", " ⋮ \n", " (\"BOARD OF ETHICS\",9) \n", " (\"POLICE BOARD\",2) \n", " (\"BUDGET & MGMT\",43) \n", " (\"ADMIN HEARNG\",39) \n", " (\"LICENSE APPL COMM\",1) " ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(department:select(name,count(employee)))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The `:select` notation is a syntax sugar for regular function call where the first argument is placed before the function name (postfix notation)." ] }, { "cell_type": "code", "execution_count": 17, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "35-element Array{Tuple{UTF8String,Int64},1}:\n", " (\"WATER MGMNT\",1848) \n", " (\"POLICE\",13570) \n", " (\"GENERAL SERVICES\",924)\n", " (\"CITY COUNCIL\",397) \n", " (\"STREETS & SAN\",2090) \n", " ⋮ \n", " (\"BOARD OF ETHICS\",9) \n", " (\"POLICE BOARD\",2) \n", " (\"BUDGET & MGMT\",43) \n", " (\"ADMIN HEARNG\",39) \n", " (\"LICENSE APPL COMM\",1) " ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(select(department,name,count(employee)))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "It is easy to add new columns to the output. Let us add *the top salary per department.*" ] }, { "cell_type": "code", "execution_count": 18, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "35-element Array{Tuple{UTF8String,Int64,Nullable{Int64}},1}:\n", " (\"WATER MGMNT\",1848,Nullable(169512)) \n", " (\"POLICE\",13570,Nullable(260004)) \n", " (\"GENERAL SERVICES\",924,Nullable(157092))\n", " (\"CITY COUNCIL\",397,Nullable(160248)) \n", " (\"STREETS & SAN\",2090,Nullable(157092)) \n", " ⋮ \n", " (\"BOARD OF ETHICS\",9,Nullable(131688)) \n", " (\"POLICE BOARD\",2,Nullable(97728)) \n", " (\"BUDGET & MGMT\",43,Nullable(169992)) \n", " (\"ADMIN HEARNG\",39,Nullable(156420)) \n", " (\"LICENSE APPL COMM\",1,Nullable(69888)) " ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " department\n", " :select(\n", " name,\n", " count(employee),\n", " max(employee.salary)))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Filtering data\n", "*Find the employees with salary greater than $200k.*" ] }, { "cell_type": "code", "execution_count": 19, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "3-element Array{Tuple{UTF8String,UTF8String,UTF8String,Int64},1}:\n", " (\"RAHM\",\"E\",\"MAYOR\",216210) \n", " (\"GARRY\",\"M\",\"SUPERINTENDENT OF POLICE\",260004)\n", " (\"JOSE\",\"S\",\"FIRE COMMISSIONER\",202728) " ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " employee\n", " :filter(salary>200000)\n", " :select(name,surname,position,salary))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "You can apply `:filter()` on any selected column." ] }, { "cell_type": "code", "execution_count": 20, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "3-element Array{Tuple{UTF8String,UTF8String,UTF8String,Int64},1}:\n", " (\"RAHM\",\"E\",\"MAYOR\",216210) \n", " (\"GARRY\",\"M\",\"SUPERINTENDENT OF POLICE\",260004)\n", " (\"JOSE\",\"S\",\"FIRE COMMISSIONER\",202728) " ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " employee\n", " :select(name,surname,position,salary)\n", " :filter(salary>200000))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*Find the number of employees with salary in the range from \\$100k to \\$200k.*" ] }, { "cell_type": "code", "execution_count": 21, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "3916" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " employee\n", " :filter((salary>100000)&(salary<=200000))\n", " :count)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*Find the departments with more than 1000 employees.*" ] }, { "cell_type": "code", "execution_count": 22, "metadata": { "collapsed": false }, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "\n", "WARNING: deprecated syntax \"filter(count(employee)>1000) .\" at In[22]:4.\n", "Use \"filter(count(employee)>1000).\" instead.\n" ] }, { "data": { "text/plain": [ "7-element Array{UTF8String,1}:\n", " \"WATER MGMNT\" \n", " \"POLICE\" \n", " \"STREETS & SAN\"\n", " \"AVIATION\" \n", " \"FIRE\" \n", " \"OEMC\" \n", " \"TRANSPORTN\" " ] }, "execution_count": 22, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " department\n", " :filter(count(employee)>1000)\n", " .name)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*Find the number of departments with more than 1000 employees.*" ] }, { "cell_type": "code", "execution_count": 23, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "7" ] }, "execution_count": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " count(\n", " department\n", " :filter(count(employee)>1000)))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*For each department, find the number of employees with salary higher than $100k.*" ] }, { "cell_type": "code", "execution_count": 24, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "35-element Array{Tuple{UTF8String,Int64},1}:\n", " (\"WATER MGMNT\",179) \n", " (\"POLICE\",1493) \n", " (\"GENERAL SERVICES\",79)\n", " (\"CITY COUNCIL\",54) \n", " (\"STREETS & SAN\",39) \n", " ⋮ \n", " (\"BOARD OF ETHICS\",2) \n", " (\"POLICE BOARD\",0) \n", " (\"BUDGET & MGMT\",12) \n", " (\"ADMIN HEARNG\",3) \n", " (\"LICENSE APPL COMM\",0)" ] }, "execution_count": 24, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " department\n", " :select(\n", " name,\n", " count(employee:filter(salary>100000))))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*For each department with the number of employees less than 1000, find the employees with salary higher than $125k.*" ] }, { "cell_type": "code", "execution_count": 25, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "28-element Array{Tuple{UTF8String,Array{Tuple{UTF8String,UTF8String,UTF8String},1}},1}:\n", " (\"GENERAL SERVICES\",[(\"DAVID\",\"R\",\"COMMISSIONER OF FLEET & FACILITY MANAGEMENT\"),(\"PHILLIP\",\"S\",\"EQUIPMENT SERVICES COORD\")]) \n", " (\"CITY COUNCIL\",[(\"JAMES\",\"C\",\"DEPUTY CHIEF ADMINISTRATIVE OFFICER\"),(\"MARLA\",\"K\",\"CHIEF ADMINISTRATIVE OFFICER\")]) \n", " (\"FAMILY & SUPPORT\",[(\"EVELYN\",\"D\",\"COMMISSIONER OF FAMILY AND SUPPORT SERVICES\"),(\"MARY\",\"G\",\"DEPUTY COMMISSIONER\"),(\"JENNIFER\",\"W\",\"FIRST DEPUTY COMMISSIONER\")]) \n", " (\"IPRA\",[(\"SCOTT\",\"A\",\"CHIEF ADMINISTRATOR\"),(\"STEVEN\",\"H\",\"DEPUTY CHIEF ADMINISTRATOR\"),(\"STEVEN\",\"M\",\"FIRST DEPUTY CHIEF ADMINISTRATOR\"),(\"WILLIAM\",\"W\",\"DEPUTY CHIEF ADMINISTRATOR\")])\n", " (\"PUBLIC LIBRARY\",[(\"BRIAN\",\"B\",\"COMMISSIONER OF CHICAGO PUBLIC LIBRARY\"),(\"MICHELLE\",\"F\",\"DIRECTOR OF LIBRARY TECHNOLOGY\"),(\"ANDREA\",\"S\",\"FIRST DEPUTY COMMISSIONER\")]) \n", " ⋮ \n", " (\"BOARD OF ETHICS\",[(\"STEVEN\",\"B\",\"EXECUTIVE DIR - BOARD OF ETHICS\")]) \n", " (\"POLICE BOARD\",Tuple{UTF8String,UTF8String,UTF8String}[]) \n", " (\"BUDGET & MGMT\",[(\"ALEXANDRA\",\"H\",\"BUDGET DIR\"),(\"RESHMA\",\"S\",\"FIRST DEPUTY BUDGET DIR\")]) \n", " (\"ADMIN HEARNG\",[(\"PATRICIA\",\"J\",\"DIR OF ADMINISTRATIVE HEARINGS\"),(\"STEVEN\",\"S\",\"DEPUTY DIR OF ADMINISTRATIVE ADJUDICATION\")]) \n", " (\"LICENSE APPL COMM\",Tuple{UTF8String,UTF8String,UTF8String}[]) " ] }, "execution_count": 25, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " department\n", " :filter(count(employee)<1000)\n", " :select(\n", " name,\n", " employee\n", " :filter(salary>125000)\n", " :select(name,surname,position)))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Sorting\n", "\n", "We use the `:sort` combinator to sort an array of values.\n", "\n", "*List the names of departments in alphabetical order.*" ] }, { "cell_type": "code", "execution_count": 26, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "35-element Array{UTF8String,1}:\n", " \"ADMIN HEARNG\" \n", " \"ANIMAL CONTRL\" \n", " \"AVIATION\" \n", " \"BOARD OF ELECTION\"\n", " \"BOARD OF ETHICS\" \n", " ⋮ \n", " \"PUBLIC LIBRARY\" \n", " \"STREETS & SAN\" \n", " \"TRANSPORTN\" \n", " \"TREASURER\" \n", " \"WATER MGMNT\" " ] }, "execution_count": 26, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(department.name:sort)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can also specify the attribute by which the elements of the array are to be sorted.\n", "\n", "*Show the employees sorted by salary.*" ] }, { "cell_type": "code", "execution_count": 27, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "32181-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:\n", " (\"STEVEN\",\"K\",\"MAYOR'S OFFICE\",\"ADMINISTRATIVE SECRETARY\",1) \n", " (\"BETTY\",\"A\",\"FAMILY & SUPPORT\",\"FOSTER GRANDPARENT\",2756) \n", " (\"VICTOR\",\"A\",\"FAMILY & SUPPORT\",\"SENIOR COMPANION\",2756) \n", " (\"RASHEEDAH\",\"A\",\"FAMILY & SUPPORT\",\"SENIOR COMPANION\",2756) \n", " (\"MARY\",\"A\",\"FAMILY & SUPPORT\",\"FOSTER GRANDPARENT\",2756) \n", " ⋮ \n", " (\"CHARLES\",\"S\",\"FIRE\",\"FIRST DEPUTY FIRE COMMISSIONER\",197736)\n", " (\"ALFONZA\",\"W\",\"POLICE\",\"FIRST DEPUTY SUPERINTENDENT\",197736) \n", " (\"JOSE\",\"S\",\"FIRE\",\"FIRE COMMISSIONER\",202728) \n", " (\"RAHM\",\"E\",\"MAYOR'S OFFICE\",\"MAYOR\",216210) \n", " (\"GARRY\",\"M\",\"POLICE\",\"SUPERINTENDENT OF POLICE\",260004) " ] }, "execution_count": 27, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(employee:sort(salary))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Use `:desc` indicator to reverse the order." ] }, { "cell_type": "code", "execution_count": 28, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "32181-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:\n", " (\"GARRY\",\"M\",\"POLICE\",\"SUPERINTENDENT OF POLICE\",260004) \n", " (\"RAHM\",\"E\",\"MAYOR'S OFFICE\",\"MAYOR\",216210) \n", " (\"JOSE\",\"S\",\"FIRE\",\"FIRE COMMISSIONER\",202728) \n", " (\"CHARLES\",\"S\",\"FIRE\",\"FIRST DEPUTY FIRE COMMISSIONER\",197736)\n", " (\"ALFONZA\",\"W\",\"POLICE\",\"FIRST DEPUTY SUPERINTENDENT\",197736) \n", " ⋮ \n", " (\"SONG\",\"Y\",\"FAMILY & SUPPORT\",\"SENIOR COMPANION\",2756) \n", " (\"AMANDA\",\"Y\",\"FAMILY & SUPPORT\",\"FOSTER GRANDPARENT\",2756) \n", " (\"EUNICE\",\"Y\",\"FAMILY & SUPPORT\",\"SENIOR COMPANION\",2756) \n", " (\"MING\",\"Y\",\"FAMILY & SUPPORT\",\"SENIOR COMPANION\",2756) \n", " (\"STEVEN\",\"K\",\"MAYOR'S OFFICE\",\"ADMINISTRATIVE SECRETARY\",1) " ] }, "execution_count": 28, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(employee:sort(salary:desc))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "It is possible to specify several sorting keys." ] }, { "cell_type": "code", "execution_count": 29, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "32181-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:\n", " (\"GARRY\",\"M\",\"POLICE\",\"SUPERINTENDENT OF POLICE\",260004) \n", " (\"RAHM\",\"E\",\"MAYOR'S OFFICE\",\"MAYOR\",216210) \n", " (\"JOSE\",\"S\",\"FIRE\",\"FIRE COMMISSIONER\",202728) \n", " (\"CHARLES\",\"S\",\"FIRE\",\"FIRST DEPUTY FIRE COMMISSIONER\",197736)\n", " (\"ALFONZA\",\"W\",\"POLICE\",\"FIRST DEPUTY SUPERINTENDENT\",197736) \n", " ⋮ \n", " (\"MING\",\"Y\",\"FAMILY & SUPPORT\",\"SENIOR COMPANION\",2756) \n", " (\"SONG\",\"Y\",\"FAMILY & SUPPORT\",\"SENIOR COMPANION\",2756) \n", " (\"SONG\",\"Y\",\"FAMILY & SUPPORT\",\"SENIOR COMPANION\",2756) \n", " (\"YELENA\",\"Y\",\"FAMILY & SUPPORT\",\"SENIOR COMPANION\",2756) \n", " (\"STEVEN\",\"K\",\"MAYOR'S OFFICE\",\"ADMINISTRATIVE SECRETARY\",1) " ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " employee\n", " :sort(\n", " salary:desc,\n", " surname:asc,\n", " name:asc))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "`:sort` can be used together with `:select` and `:filter`." ] }, { "cell_type": "code", "execution_count": 30, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "7-element Array{Tuple{UTF8String,Int64},1}:\n", " (\"POLICE\",13570) \n", " (\"FIRE\",4875) \n", " (\"STREETS & SAN\",2090)\n", " (\"WATER MGMNT\",1848) \n", " (\"AVIATION\",1344) \n", " (\"TRANSPORTN\",1200) \n", " (\"OEMC\",1135) " ] }, "execution_count": 30, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " department\n", " :select(name, size => count(employee))\n", " :filter(size>1000)\n", " :sort(size:desc))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Use `:define` to name a commonly used expression." ] }, { "cell_type": "code", "execution_count": 31, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "7-element Array{Tuple{UTF8String,Int64},1}:\n", " (\"POLICE\",13570) \n", " (\"FIRE\",4875) \n", " (\"STREETS & SAN\",2090)\n", " (\"WATER MGMNT\",1848) \n", " (\"AVIATION\",1344) \n", " (\"TRANSPORTN\",1200) \n", " (\"OEMC\",1135) " ] }, "execution_count": 31, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " department\n", " :define(size => count(employee))\n", " :select(name, size)\n", " :filter(size>1000)\n", " :sort(size:desc))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Limiting\n", "\n", "Use combinators `:first`, `:last`, `:take` to limit the size of the output array. Use `:reverse` to reverse the output array.\n", "\n", "*The first employee.*" ] }, { "cell_type": "code", "execution_count": 32, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "Nullable((\"ELVIA\",\"A\",\"WATER MGMNT\",\"WATER RATE TAKER\",88968))" ] }, "execution_count": 32, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(first(employee))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*The name of the first employee.*" ] }, { "cell_type": "code", "execution_count": 33, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "Nullable(\"ELVIA\")" ] }, "execution_count": 33, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(first(employee).name)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*The department with the largest number of employees.*" ] }, { "cell_type": "code", "execution_count": 34, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "Nullable((\"POLICE\",13570))" ] }, "execution_count": 34, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " department\n", " :select(name, size => count(employee))\n", " :sort(size:desc)\n", " :first)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Same query without `:sort`.\n", "\n", "*The department with the largest number of employees.*" ] }, { "cell_type": "code", "execution_count": 35, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "Nullable((\"POLICE\",13570))" ] }, "execution_count": 35, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " department\n", " :select(name, size => count(employee))\n", " :first(size))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*Last employee.*" ] }, { "cell_type": "code", "execution_count": 36, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "Nullable((\"DARIUSZ\",\"Z\",\"DoIT\",\"CHIEF DATA BASE ANALYST\",110352))" ] }, "execution_count": 36, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(employee:last)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*The department with the largest number of employees.*" ] }, { "cell_type": "code", "execution_count": 37, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "Nullable((\"POLICE\",13570))" ] }, "execution_count": 37, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " department\n", " :select(name, size => count(employee))\n", " :sort(size)\n", " :last)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Same query could be written without `:sort`.\n", "\n", "*The department with the largest number of employees.*" ] }, { "cell_type": "code", "execution_count": 38, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "Nullable((\"POLICE\",13570))" ] }, "execution_count": 38, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " department\n", " :select(name, size => count(employee))\n", " :last(size:desc))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*Show first 5 employees.*" ] }, { "cell_type": "code", "execution_count": 39, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "5-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:\n", " (\"ELVIA\",\"A\",\"WATER MGMNT\",\"WATER RATE TAKER\",88968) \n", " (\"JEFFERY\",\"A\",\"POLICE\",\"POLICE OFFICER\",80778) \n", " (\"KARINA\",\"A\",\"POLICE\",\"POLICE OFFICER\",80778) \n", " (\"KIMBERLEI\",\"A\",\"GENERAL SERVICES\",\"CHIEF CONTRACT EXPEDITER\",84780)\n", " (\"VICENTE\",\"A\",\"WATER MGMNT\",\"CIVIL ENGINEER IV\",104736) " ] }, "execution_count": 39, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(employee:take(5))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*Skip first 10 employees, show next 5.*" ] }, { "cell_type": "code", "execution_count": 40, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "5-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:\n", " (\"BETTY\",\"A\",\"FAMILY & SUPPORT\",\"FOSTER GRANDPARENT\",2756)\n", " (\"LYNISE\",\"A\",\"POLICE\",\"CLERK III\",43920) \n", " (\"WILLIAM\",\"A\",\"IPRA\",\"INVESTIGATOR - IPRA II\",72468) \n", " (\"ZAID\",\"A\",\"POLICE\",\"POLICE OFFICER\",69684) \n", " (\"ABDALMAHD\",\"A\",\"POLICE\",\"POLICE OFFICER\",80778) " ] }, "execution_count": 40, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(employee:skip(10):take(5))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*Show last ten employees.*" ] }, { "cell_type": "code", "execution_count": 41, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "10-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:\n", " (\"MATTHEW\",\"Z\",\"AVIATION\",\"AIRPORT OPERATIONS SUPVSR I\",69840) \n", " (\"JEFFREY\",\"Z\",\"POLICE\",\"POLICE OFFICER\",83616) \n", " (\"MATTHEW\",\"Z\",\"FIRE\",\"FIREFIGHTER-EMT\",91764) \n", " (\"BRYAN\",\"Z\",\"POLICE\",\"POLICE OFFICER\",80778) \n", " (\"JOHN\",\"Z\",\"GENERAL SERVICES\",\"MACHINIST (AUTOMOTIVE)\",92248) \n", " (\"MICHAEL\",\"Z\",\"GENERAL SERVICES\",\"FRM OF MACHINISTS - AUTOMOTIVE\",97448)\n", " (\"PETER\",\"Z\",\"POLICE\",\"POLICE OFFICER\",86520) \n", " (\"MARK\",\"Z\",\"POLICE\",\"POLICE OFFICER\",83616) \n", " (\"CARLO\",\"Z\",\"POLICE\",\"POLICE OFFICER\",86520) \n", " (\"DARIUSZ\",\"Z\",\"DoIT\",\"CHIEF DATA BASE ANALYST\",110352) " ] }, "execution_count": 41, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(employee:skip(-10))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*Show approximately half of employees.*" ] }, { "cell_type": "code", "execution_count": 42, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "16090-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:\n", " (\"ELVIA\",\"A\",\"WATER MGMNT\",\"WATER RATE TAKER\",88968) \n", " (\"JEFFERY\",\"A\",\"POLICE\",\"POLICE OFFICER\",80778) \n", " (\"KARINA\",\"A\",\"POLICE\",\"POLICE OFFICER\",80778) \n", " (\"KIMBERLEI\",\"A\",\"GENERAL SERVICES\",\"CHIEF CONTRACT EXPEDITER\",84780)\n", " (\"VICENTE\",\"A\",\"WATER MGMNT\",\"CIVIL ENGINEER IV\",104736) \n", " ⋮ \n", " (\"BROOKE\",\"L\",\"LAW\",\"ASST CORPORATION COUNSEL\",63720) \n", " (\"JOSEPH\",\"L\",\"WATER MGMNT\",\"ELECTRICAL MECHANIC\",91520) \n", " (\"PETER\",\"L\",\"POLICE\",\"POLICE OFFICER\",83616) \n", " (\"ROBERT\",\"L\",\"STREETS & SAN\",\"SANITATION LABORER\",72384) \n", " (\"NICHOLAS\",\"L\",\"OEMC\",\"FIRE COMMUNICATIONS OPERATOR II\",78180) " ] }, "execution_count": 42, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(employee:take(count(employee)/2))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*Reverse the order of departments.*" ] }, { "cell_type": "code", "execution_count": 43, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "35-element Array{Tuple{UTF8String},1}:\n", " (\"LICENSE APPL COMM\",)\n", " (\"ADMIN HEARNG\",) \n", " (\"BUDGET & MGMT\",) \n", " (\"POLICE BOARD\",) \n", " (\"BOARD OF ETHICS\",) \n", " ⋮ \n", " (\"STREETS & SAN\",) \n", " (\"CITY COUNCIL\",) \n", " (\"GENERAL SERVICES\",) \n", " (\"POLICE\",) \n", " (\"WATER MGMNT\",) " ] }, "execution_count": 43, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(department:reverse)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Identity\n", "\n", "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." ] }, { "cell_type": "code", "execution_count": 44, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "35-element Array{Tuple{Int64,UTF8String},1}:\n", " (1,\"WATER MGMNT\") \n", " (2,\"POLICE\") \n", " (3,\"GENERAL SERVICES\") \n", " (4,\"CITY COUNCIL\") \n", " (5,\"STREETS & SAN\") \n", " ⋮ \n", " (31,\"BOARD OF ETHICS\") \n", " (32,\"POLICE BOARD\") \n", " (33,\"BUDGET & MGMT\") \n", " (34,\"ADMIN HEARNG\") \n", " (35,\"LICENSE APPL COMM\")" ] }, "execution_count": 44, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(department:select(id,name))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Use `:get` combinator to find the record by its identity." ] }, { "cell_type": "code", "execution_count": 45, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "Nullable((\"STREETS & SAN\",))" ] }, "execution_count": 45, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(department:get(5))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "If a record is not found, `null` value is returned." ] }, { "cell_type": "code", "execution_count": 46, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "Nullable{Tuple{UTF8String}}()" ] }, "execution_count": 46, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(department:get(-1))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "You can use brackets instead of `:get`." ] }, { "cell_type": "code", "execution_count": 47, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "Nullable((5,\"STREETS & SAN\",2090))" ] }, "execution_count": 47, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " department[5]\n", " :select(id, name, count(employee)))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*Show all employees of a selected department.*" ] }, { "cell_type": "code", "execution_count": 48, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "2090-element Array{Tuple{UTF8String,UTF8String},1}:\n", " (\"EMMANUEL\",\"A\")\n", " (\"TAHIR\",\"A\") \n", " (\"TIMOTHY\",\"A\") \n", " (\"DANIELLE\",\"A\")\n", " (\"SAMUEL\",\"A\") \n", " ⋮ \n", " (\"LUIS\",\"Z\") \n", " (\"ROBERT\",\"Z\") \n", " (\"JONATHAN\",\"Z\")\n", " (\"THOMAS\",\"Z\") \n", " (\"MAC\",\"Z\") " ] }, "execution_count": 48, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(department[5].employee)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Hierarchical queries\n", "(Note: the data on organizational structure is not available, so the output is largely meaningless).\n", "\n", "*Find the employees who earn more than their manager.*" ] }, { "cell_type": "code", "execution_count": 49, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "1-element Array{Tuple{UTF8String,UTF8String,UTF8String,Nullable{Tuple{UTF8String,UTF8String,UTF8String}},Nullable{Int64}},1}:\n", " (\"BRIAN\",\"L\",\"AUDITOR IV\",Nullable((\"ROBERT\",\"L\",\"DIR OF ACCOUNTING\")),Nullable(4140))" ] }, "execution_count": 49, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " employee\n", " :filter(salary>managed_by.salary)\n", " :select(name, surname, position, managed_by, salary-managed_by.salary))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*For all employees in a certain department, list their seniors and the number of their subordinates.*" ] }, { "cell_type": "code", "execution_count": 50, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "24-element Array{Tuple{UTF8String,UTF8String,UTF8String,Array{Tuple{UTF8String,UTF8String,UTF8String},1},Int64},1}:\n", " (\"SAEED\",\"A\",\"ASST CITY TREASURER\",[(\"KURT\",\"S\",\"CITY TREASURER\")],0) \n", " (\"ELIZABETH\",\"A\",\"ACCOUNTANT I\",[(\"ROBERT\",\"L\",\"DIR OF ACCOUNTING\"),(\"KURT\",\"S\",\"CITY TREASURER\")],0)\n", " (\"KONSTANTINES\",\"A\",\"ASSISTANT DIRECTOR OF FINANCE\",[(\"KURT\",\"S\",\"CITY TREASURER\")],0) \n", " (\"SARA\",\"A\",\"STUDENT INTERN\",[(\"KURT\",\"S\",\"CITY TREASURER\")],0) \n", " (\"NANCY\",\"C\",\"EXECUTIVE SECRETARY I\",[(\"KURT\",\"S\",\"CITY TREASURER\")],0) \n", " ⋮ \n", " (\"DANIELLE\",\"M\",\"ASST CITY TREASURER\",[(\"KURT\",\"S\",\"CITY TREASURER\")],0) \n", " (\"MARK\",\"M\",\"PORTFOLIO MANAGER\",[(\"KURT\",\"S\",\"CITY TREASURER\")],0) \n", " (\"KENNETH\",\"S\",\"ASST CITY TREASURER\",[(\"KURT\",\"S\",\"CITY TREASURER\")],0) \n", " (\"ALEXANDRA\",\"S\",\"DEPUTY CITY TREASURER\",[(\"KURT\",\"S\",\"CITY TREASURER\")],0) \n", " (\"KURT\",\"S\",\"CITY TREASURER\",Tuple{UTF8String,UTF8String,UTF8String}[],23) " ] }, "execution_count": 50, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " department[26].employee\n", " :select(\n", " name,\n", " surname,\n", " position,\n", " connect(managed_by),\n", " count(connect(manages))))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*List employees of a certain department in hierarchical order.*" ] }, { "cell_type": "code", "execution_count": 51, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "24-element Array{Tuple{Int64,UTF8String,UTF8String,UTF8String},1}:\n", " (0,\"KURT\",\"S\",\"CITY TREASURER\") \n", " (1,\"SAEED\",\"A\",\"ASST CITY TREASURER\") \n", " (1,\"KONSTANTINES\",\"A\",\"ASSISTANT DIRECTOR OF FINANCE\")\n", " (1,\"SARA\",\"A\",\"STUDENT INTERN\") \n", " (1,\"NANCY\",\"C\",\"EXECUTIVE SECRETARY I\") \n", " ⋮ \n", " (2,\"TERRANCE\",\"M\",\"ACCOUNTANT IV\") \n", " (1,\"DANIELLE\",\"M\",\"ASST CITY TREASURER\") \n", " (1,\"MARK\",\"M\",\"PORTFOLIO MANAGER\") \n", " (1,\"KENNETH\",\"S\",\"ASST CITY TREASURER\") \n", " (1,\"ALEXANDRA\",\"S\",\"DEPUTY CITY TREASURER\") " ] }, "execution_count": 51, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " department[26].employee\n", " :sort_connect(managed_by)\n", " :select(\n", " depth(managed_by),\n", " name,\n", " surname,\n", " position))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Grouping\n", "Use `:unique` combinator to generate all unique values that appear in a sequence.\n", "\n", "*List all distinct positions.*" ] }, { "cell_type": "code", "execution_count": 52, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "1094-element Array{UTF8String,1}:\n", " \"1ST DEPUTY INSPECTOR GENERAL\"\n", " \"A/MGR COM SVC-ELECTIONS\" \n", " \"A/MGR OF MIS-ELECTIONS\" \n", " \"A/MGR WAREHOUSE-ELECTIONS\" \n", " \"A/SUPRV REDISTRICTING\" \n", " ⋮ \n", " \"WINDOW WASHER\" \n", " \"YOUTH SERVICES COORD\" \n", " \"ZONING ADMINISTRATOR\" \n", " \"ZONING INVESTIGATOR\" \n", " \"ZONING PLAN EXAMINER\" " ] }, "execution_count": 52, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(employee.position:unique)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*Find the number of distinct positions for each department.*" ] }, { "cell_type": "code", "execution_count": 53, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "35-element Array{Tuple{UTF8String,Int64,Int64},1}:\n", " (\"WATER MGMNT\",154,1848) \n", " (\"POLICE\",129,13570) \n", " (\"GENERAL SERVICES\",119,924)\n", " (\"CITY COUNCIL\",28,397) \n", " (\"STREETS & SAN\",70,2090) \n", " ⋮ \n", " (\"BOARD OF ETHICS\",9,9) \n", " (\"POLICE BOARD\",2,2) \n", " (\"BUDGET & MGMT\",24,43) \n", " (\"ADMIN HEARNG\",15,39) \n", " (\"LICENSE APPL COMM\",1,1) " ] }, "execution_count": 53, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " department\n", " :select(\n", " name,\n", " count(unique(employee.position)),\n", " count(employee)))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can also list distinct positions using `:group` combinator. With each position, we get a list of employees having this position." ] }, { "cell_type": "code", "execution_count": 54, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "1094-element Array{Tuple{UTF8String,Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}},1}:\n", " (\"1ST DEPUTY INSPECTOR GENERAL\",[(\"SHARON\",\"F\",\"INSPECTOR GEN\",\"1ST DEPUTY INSPECTOR GENERAL\",137052)]) \n", " (\"A/MGR COM SVC-ELECTIONS\",[(\"LAURA\",\"G\",\"BOARD OF ELECTION\",\"A/MGR COM SVC-ELECTIONS\",99816)]) \n", " (\"A/MGR OF MIS-ELECTIONS\",[(\"TIEN\",\"T\",\"BOARD OF ELECTION\",\"A/MGR OF MIS-ELECTIONS\",94932)]) \n", " (\"A/MGR WAREHOUSE-ELECTIONS\",[(\"DERRICK\",\"H\",\"BOARD OF ELECTION\",\"A/MGR WAREHOUSE-ELECTIONS\",71364)]) \n", " (\"A/SUPRV REDISTRICTING\",[(\"THOMAS\",\"M\",\"BOARD OF ELECTION\",\"A/SUPRV REDISTRICTING\",69564),(\"CHRISTOPHER\",\"T\",\"BOARD OF ELECTION\",\"A/SUPRV REDISTRICTING\",45720)]) \n", " ⋮ \n", " (\"WINDOW WASHER\",[(\"GARRY\",\"S\",\"AVIATION\",\"WINDOW WASHER\",47320),(\"MARK\",\"T\",\"AVIATION\",\"WINDOW WASHER\",47320)]) \n", " (\"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)])\n", " (\"ZONING ADMINISTRATOR\",[(\"PATRICIA\",\"S\",\"COMMUNITY DEVELOPMENT\",\"ZONING ADMINISTRATOR\",139800)]) \n", " (\"ZONING INVESTIGATOR\",[(\"CARLOS\",\"R\",\"COMMUNITY DEVELOPMENT\",\"ZONING INVESTIGATOR\",97596)]) \n", " (\"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)]) " ] }, "execution_count": 54, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(employee:group(position))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "For each row generated by `employee:group(position)`, combinator `employee` will give you employees that have this position.\n", "\n", "*For each position, find the number of employees.*" ] }, { "cell_type": "code", "execution_count": 55, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "1094-element Array{Tuple{UTF8String,Int64},1}:\n", " (\"POLICE OFFICER\",9489) \n", " (\"FIREFIGHTER-EMT\",1191) \n", " (\"SERGEANT\",1138) \n", " (\"FIREFIGHTER\",970) \n", " (\"POLICE OFFICER (ASSIGNED AS DETECTIVE)\",808)\n", " ⋮ \n", " (\"WEB AUTHOR\",1) \n", " (\"WEB DEVELOPER\",1) \n", " (\"WEB DEVELOPER/ADMINTR-CPL\",1) \n", " (\"ZONING ADMINISTRATOR\",1) \n", " (\"ZONING INVESTIGATOR\",1) " ] }, "execution_count": 55, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " employee\n", " :group(position)\n", " :select(position, size => count(employee))\n", " :sort(size:desc))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*Find positions provided by no less than 5 departments.*" ] }, { "cell_type": "code", "execution_count": 56, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "78-element Array{Tuple{UTF8String,Array{UTF8String,1}},1}:\n", " (\"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\"]) \n", " (\"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\"])\n", " (\"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\"]) \n", " (\"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\"]) \n", " (\"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\"]) \n", " ⋮ \n", " (\"MANAGER OF FINANCE\",UTF8String[\"STREETS & SAN\",\"AVIATION\",\"FAMILY & SUPPORT\",\"TRANSPORTN\",\"HEALTH\"]) \n", " (\"PAINTER\",UTF8String[\"WATER MGMNT\",\"GENERAL SERVICES\",\"STREETS & SAN\",\"AVIATION\",\"TRANSPORTN\"]) \n", " (\"POOL MOTOR TRUCK DRIVER\",UTF8String[\"WATER MGMNT\",\"STREETS & SAN\",\"AVIATION\",\"PUBLIC LIBRARY\",\"TRANSPORTN\"]) \n", " (\"SPECIAL ASST\",UTF8String[\"FAMILY & SUPPORT\",\"BUSINESS AFFAIRS\",\"OEMC\",\"TRANSPORTN\",\"CULTURAL AFFAIRS\"]) \n", " (\"STORES LABORER\",UTF8String[\"GENERAL SERVICES\",\"AVIATION\",\"FIRE\",\"TRANSPORTN\",\"PROCUREMENT\"]) " ] }, "execution_count": 56, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " employee\n", " :group(position)\n", " :define(department => unique(employee.department))\n", " :filter(count(department)>=5)\n", " :select(position, department)\n", " :sort(count(department):desc))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*Find the popular names of Chicago employees.*" ] }, { "cell_type": "code", "execution_count": 57, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "5038-element Array{Tuple{UTF8String,Int64},1}:\n", " (\"MICHAEL\",1170)\n", " (\"JOHN\",914) \n", " (\"JAMES\",691) \n", " (\"ROBERT\",649) \n", " (\"JOSEPH\",543) \n", " ⋮ \n", " (\"ZORAN\",1) \n", " (\"ZORRAYDA\",1) \n", " (\"ZULEMA\",1) \n", " (\"ZUZANNA\",1) \n", " (\"ZYNETTA\",1) " ] }, "execution_count": 57, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " employee\n", " :group(name)\n", " :select(name, size => count(employee))\n", " :sort(size:desc))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*Find the top salary by the first name, but only if there are at least 10 employees having this name.*" ] }, { "cell_type": "code", "execution_count": 58, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "483-element Array{Tuple{UTF8String,Int64},1}:\n", " (\"JOSE\",202728) \n", " (\"CHARLES\",197736)\n", " (\"ROBERT\",194256) \n", " (\"ANTHONY\",187680)\n", " (\"JOHN\",187680) \n", " ⋮ \n", " (\"SARA\",86520) \n", " (\"TERRI\",86520) \n", " (\"DARIUS\",85020) \n", " (\"ANNIE\",83616) \n", " (\"BERTHA\",83616) " ] }, "execution_count": 58, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " employee\n", " :group(name)\n", " :filter(count(employee)>=10)\n", " :select(name, max_salary => max(employee.salary))\n", " :sort(max_salary:desc))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*Find the number of employees for each department and salary bracket.*" ] }, { "cell_type": "code", "execution_count": 59, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "405-element Array{Tuple{UTF8String,Int64,Int64,Int64},1}:\n", " (\"WATER MGMNT\",160000,169999,1) \n", " (\"WATER MGMNT\",150000,159999,1) \n", " (\"WATER MGMNT\",130000,139999,2) \n", " (\"WATER MGMNT\",120000,129999,5) \n", " (\"WATER MGMNT\",110000,119999,32) \n", " ⋮ \n", " (\"ADMIN HEARNG\",80000,89999,2) \n", " (\"ADMIN HEARNG\",70000,79999,3) \n", " (\"ADMIN HEARNG\",60000,69999,19) \n", " (\"ADMIN HEARNG\",50000,59999,7) \n", " (\"LICENSE APPL COMM\",60000,69999,1)" ] }, "execution_count": 59, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " employee\n", " :group(department, salary_bracket => salary/10000*10000 :desc)\n", " :select(department, salary_bracket, salary_bracket+9999, count(employee)))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "To generate totals on each dimension, use `:group_cube`.\n", "\n", "*Find the number of employees for each department and salary bracket, including totals.*" ] }, { "cell_type": "code", "execution_count": 60, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "464-element Array{Tuple{Nullable{UTF8String},Nullable{Int64},Nullable{Int64},Int64},1}:\n", " (Nullable(\"WATER MGMNT\"),Nullable(160000),Nullable(169999),1) \n", " (Nullable(\"WATER MGMNT\"),Nullable(150000),Nullable(159999),1) \n", " (Nullable(\"WATER MGMNT\"),Nullable(130000),Nullable(139999),2) \n", " (Nullable(\"WATER MGMNT\"),Nullable(120000),Nullable(129999),5) \n", " (Nullable(\"WATER MGMNT\"),Nullable(110000),Nullable(119999),32) \n", " ⋮ \n", " (Nullable{UTF8String}(),Nullable(30000),Nullable(39999),215) \n", " (Nullable{UTF8String}(),Nullable(20000),Nullable(29999),420) \n", " (Nullable{UTF8String}(),Nullable(10000),Nullable(19999),1209) \n", " (Nullable{UTF8String}(),Nullable(0),Nullable(9999),376) \n", " (Nullable{UTF8String}(),Nullable{Int64}(),Nullable{Int64}(),32181)" ] }, "execution_count": 60, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " employee\n", " :group_cube(department, salary_bracket => salary/10000*10000 :desc)\n", " :select(department, salary_bracket, salary_bracket+9999, count(employee)))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Add `:dataframe` to present this data in tabular form." ] }, { "cell_type": "code", "execution_count": 61, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
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
⋮⋮⋮⋮⋮
" ], "text/plain": [ "464x4 DataFrames.DataFrame\n", "| Row | department | low | high | size |\n", "|-----|---------------|--------|--------|-------|\n", "| 1 | \"WATER MGMNT\" | 160000 | 169999 | 1 |\n", "| 2 | \"WATER MGMNT\" | 150000 | 159999 | 1 |\n", "| 3 | \"WATER MGMNT\" | 130000 | 139999 | 2 |\n", "| 4 | \"WATER MGMNT\" | 120000 | 129999 | 5 |\n", "⋮\n", "| 460 | NA | 30000 | 39999 | 215 |\n", "| 461 | NA | 20000 | 29999 | 420 |\n", "| 462 | NA | 10000 | 19999 | 1209 |\n", "| 463 | NA | 0 | 9999 | 376 |\n", "| 464 | NA | NA | NA | 32181 |" ] }, "execution_count": 61, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " employee\n", " :group_cube(\n", " department,\n", " salary_bracket => salary/10000*10000 :desc)\n", " :select(\n", " department,\n", " low => salary_bracket,\n", " high => salary_bracket+9999,\n", " size => count(employee))\n", " :dataframe)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "You can specify dimensions separately using `:partition`.\n", "\n", "*Find the number of positions, the number of employees and the highest salary for the first 3 departments.*" ] }, { "cell_type": "code", "execution_count": 62, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "3-element Array{Tuple{UTF8String,Int64,Int64,Nullable{Int64}},1}:\n", " (\"WATER MGMNT\",154,1848,Nullable(169512)) \n", " (\"POLICE\",129,13570,Nullable(260004)) \n", " (\"GENERAL SERVICES\",119,924,Nullable(157092))" ] }, "execution_count": 62, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " employee\n", " :partition(department:take(3))\n", " :select(department.name, count(unique(employee.position)), count(employee), max(employee.salary)))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Similar to `:group_cube`, `:partition_cube` adds totals.\n", "\n", "*Find the numbers of positions and employees, the highest salary and the most popular position for the first 3 departments, and include the totals.*" ] }, { "cell_type": "code", "execution_count": 63, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
namenum_posnum_emplmax_salarypop_position
1WATER MGMNT1541848169512CONSTRUCTION LABORER
2POLICE12913570260004POLICE OFFICER
3GENERAL SERVICES119924157092MACHINIST (AUTOMOTIVE)
4NA33416342260004POLICE OFFICER
" ], "text/plain": [ "4x5 DataFrames.DataFrame\n", "| Row | name | num_pos | num_empl | max_salary |\n", "|-----|--------------------|---------|----------|------------|\n", "| 1 | \"WATER MGMNT\" | 154 | 1848 | 169512 |\n", "| 2 | \"POLICE\" | 129 | 13570 | 260004 |\n", "| 3 | \"GENERAL SERVICES\" | 119 | 924 | 157092 |\n", "| 4 | NA | 334 | 16342 | 260004 |\n", "\n", "| Row | pop_position |\n", "|-----|--------------------------|\n", "| 1 | \"CONSTRUCTION LABORER\" |\n", "| 2 | \"POLICE OFFICER\" |\n", "| 3 | \"MACHINIST (AUTOMOTIVE)\" |\n", "| 4 | \"POLICE OFFICER\" |" ] }, "execution_count": 63, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " employee\n", " :partition_cube(department:take(3))\n", " :select(\n", " department.name,\n", " num_pos => count(unique(employee.position)),\n", " num_empl => count(employee),\n", " max_salary => max(employee.salary),\n", " pop_position => employee:group(position):first(count(employee)).position)\n", " :dataframe)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "You can use an array constructor or `range()` combinator to specify the dimensions." ] }, { "cell_type": "code", "execution_count": 64, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "5-element Array{Int64,1}:\n", " 0\n", " 60000\n", " 120000\n", " 180000\n", " 240000" ] }, "execution_count": 64, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(range(0, 60000, max(employee.salary)))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*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.*" ] }, { "cell_type": "code", "execution_count": 65, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
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
⋮⋮⋮⋮⋮⋮⋮⋮
" ], "text/plain": [ "30x7 DataFrames.DataFrame\n", "| Row | dept | name | low | high |\n", "|-----|--------------------|-----------|--------|--------|\n", "| 1 | \"WATER MGMNT\" | \"ANTHONY\" | 0 | 59999 |\n", "| 2 | \"WATER MGMNT\" | \"ANTHONY\" | 60000 | 119999 |\n", "| 3 | \"WATER MGMNT\" | \"ANTHONY\" | 120000 | 179999 |\n", "| 4 | \"WATER MGMNT\" | \"ANTHONY\" | 180000 | 239999 |\n", "⋮\n", "| 26 | \"GENERAL SERVICES\" | \"BRIAN\" | 0 | 59999 |\n", "| 27 | \"GENERAL SERVICES\" | \"BRIAN\" | 60000 | 119999 |\n", "| 28 | \"GENERAL SERVICES\" | \"BRIAN\" | 120000 | 179999 |\n", "| 29 | \"GENERAL SERVICES\" | \"BRIAN\" | 180000 | 239999 |\n", "| 30 | \"GENERAL SERVICES\" | \"BRIAN\" | 240000 | 299999 |\n", "\n", "| Row | pop_position | num_pos | num_empl |\n", "|-----|--------------------------|---------|----------|\n", "| 1 | \"LABORER - APPRENTICE\" | 1 | 1 |\n", "| 2 | \"CONSTRUCTION LABORER\" | 15 | 32 |\n", "| 3 | NA | 0 | 0 |\n", "| 4 | NA | 0 | 0 |\n", "⋮\n", "| 26 | \"GARAGE ATTENDANT\" | 1 | 1 |\n", "| 27 | \"MACHINIST (AUTOMOTIVE)\" | 6 | 10 |\n", "| 28 | NA | 0 | 0 |\n", "| 29 | NA | 0 | 0 |\n", "| 30 | NA | 0 | 0 |" ] }, "execution_count": 65, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " employee\n", " :define(salary_bracket => salary/60000*60000)\n", " :partition(\n", " department:take(3),\n", " name => [\"ANTHONY\", \"BRIAN\"],\n", " salary_bracket => range(0, 60000, max(employee.salary)))\n", " :select(\n", " dept => department.name,\n", " name,\n", " low => salary_bracket,\n", " high => salary_bracket+59999,\n", " pop_position => employee:group(position):first(count(employee)).position,\n", " num_pos => count(unique(employee.position)),\n", " num_empl => count(employee))\n", " :dataframe)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Output formatting\n", "\n", "The output can be produced in the form of a JSON value or a `DataFrame` object." ] }, { "cell_type": "code", "execution_count": 66, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "35-element Array{Dict{Any,Any},1}:\n", " Dict{Any,Any}(:name=>\"WATER MGMNT\") \n", " Dict{Any,Any}(:name=>\"POLICE\") \n", " Dict{Any,Any}(:name=>\"GENERAL SERVICES\") \n", " Dict{Any,Any}(:name=>\"CITY COUNCIL\") \n", " Dict{Any,Any}(:name=>\"STREETS & SAN\") \n", " ⋮ \n", " Dict{Any,Any}(:name=>\"BOARD OF ETHICS\") \n", " Dict{Any,Any}(:name=>\"POLICE BOARD\") \n", " Dict{Any,Any}(:name=>\"BUDGET & MGMT\") \n", " Dict{Any,Any}(:name=>\"ADMIN HEARNG\") \n", " Dict{Any,Any}(:name=>\"LICENSE APPL COMM\")" ] }, "execution_count": 66, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(department:json)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Selector items become fields of the JSON dictionary." ] }, { "cell_type": "code", "execution_count": 67, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "35-element Array{Dict{Any,Any},1}:\n", " Dict{Any,Any}(:name=>\"WATER MGMNT\",:size=>1848,:head=>Dict{Any,Any}(:surname=>\"P\",:name=>\"THOMAS\")) \n", " Dict{Any,Any}(:name=>\"POLICE\",:size=>13570,:head=>Dict{Any,Any}(:surname=>\"M\",:name=>\"GARRY\")) \n", " Dict{Any,Any}(:name=>\"GENERAL SERVICES\",:size=>924,:head=>Dict{Any,Any}(:surname=>\"R\",:name=>\"DAVID\")) \n", " Dict{Any,Any}(:name=>\"CITY COUNCIL\",:size=>397,:head=>Dict{Any,Any}(:surname=>\"K\",:name=>\"MARLA\")) \n", " Dict{Any,Any}(:name=>\"STREETS & SAN\",:size=>2090,:head=>Dict{Any,Any}(:surname=>\"W\",:name=>\"CHARLES\")) \n", " ⋮ \n", " Dict{Any,Any}(:name=>\"BOARD OF ETHICS\",:size=>9,:head=>Dict{Any,Any}(:surname=>\"B\",:name=>\"STEVEN\")) \n", " Dict{Any,Any}(:name=>\"POLICE BOARD\",:size=>2,:head=>Dict{Any,Any}(:surname=>\"C\",:name=>\"MAX\")) \n", " Dict{Any,Any}(:name=>\"BUDGET & MGMT\",:size=>43,:head=>Dict{Any,Any}(:surname=>\"H\",:name=>\"ALEXANDRA\")) \n", " Dict{Any,Any}(:name=>\"ADMIN HEARNG\",:size=>39,:head=>Dict{Any,Any}(:surname=>\"J\",:name=>\"PATRICIA\")) \n", " Dict{Any,Any}(:name=>\"LICENSE APPL COMM\",:size=>1,:head=>Dict{Any,Any}(:surname=>\"G\",:name=>\"MICHELLE\"))" ] }, "execution_count": 67, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " department\n", " :select(\n", " name,\n", " size => count(employee),\n", " head => employee:first(salary))\n", " :json)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "You can pass a list of output fields to the `json` combinator." ] }, { "cell_type": "code", "execution_count": 68, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "35-element Array{Dict{Any,Any},1}:\n", " Dict{Any,Any}(:name=>\"WATER MGMNT\",:size=>1848,:head=>Dict{Any,Any}(:surname=>\"P\",:name=>\"THOMAS\")) \n", " Dict{Any,Any}(:name=>\"POLICE\",:size=>13570,:head=>Dict{Any,Any}(:surname=>\"M\",:name=>\"GARRY\")) \n", " Dict{Any,Any}(:name=>\"GENERAL SERVICES\",:size=>924,:head=>Dict{Any,Any}(:surname=>\"R\",:name=>\"DAVID\")) \n", " Dict{Any,Any}(:name=>\"CITY COUNCIL\",:size=>397,:head=>Dict{Any,Any}(:surname=>\"K\",:name=>\"MARLA\")) \n", " Dict{Any,Any}(:name=>\"STREETS & SAN\",:size=>2090,:head=>Dict{Any,Any}(:surname=>\"W\",:name=>\"CHARLES\")) \n", " ⋮ \n", " Dict{Any,Any}(:name=>\"BOARD OF ETHICS\",:size=>9,:head=>Dict{Any,Any}(:surname=>\"B\",:name=>\"STEVEN\")) \n", " Dict{Any,Any}(:name=>\"POLICE BOARD\",:size=>2,:head=>Dict{Any,Any}(:surname=>\"C\",:name=>\"MAX\")) \n", " Dict{Any,Any}(:name=>\"BUDGET & MGMT\",:size=>43,:head=>Dict{Any,Any}(:surname=>\"H\",:name=>\"ALEXANDRA\")) \n", " Dict{Any,Any}(:name=>\"ADMIN HEARNG\",:size=>39,:head=>Dict{Any,Any}(:surname=>\"J\",:name=>\"PATRICIA\")) \n", " Dict{Any,Any}(:name=>\"LICENSE APPL COMM\",:size=>1,:head=>Dict{Any,Any}(:surname=>\"G\",:name=>\"MICHELLE\"))" ] }, "execution_count": 68, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " department\n", " :json(\n", " name,\n", " size => count(employee),\n", " head => employee:first(salary)))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Use `:dataframe` combinator to generate `DataFrame` output." ] }, { "cell_type": "code", "execution_count": 69, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
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
⋮⋮⋮⋮⋮⋮
" ], "text/plain": [ "32181x5 DataFrames.DataFrame\n", "| Row | name | surname | department |\n", "|-------|-------------|---------|--------------------|\n", "| 1 | \"ELVIA\" | \"A\" | \"WATER MGMNT\" |\n", "| 2 | \"JEFFERY\" | \"A\" | \"POLICE\" |\n", "| 3 | \"KARINA\" | \"A\" | \"POLICE\" |\n", "| 4 | \"KIMBERLEI\" | \"A\" | \"GENERAL SERVICES\" |\n", "⋮\n", "| 32177 | \"MICHAEL\" | \"Z\" | \"GENERAL SERVICES\" |\n", "| 32178 | \"PETER\" | \"Z\" | \"POLICE\" |\n", "| 32179 | \"MARK\" | \"Z\" | \"POLICE\" |\n", "| 32180 | \"CARLO\" | \"Z\" | \"POLICE\" |\n", "| 32181 | \"DARIUSZ\" | \"Z\" | \"DoIT\" |\n", "\n", "| Row | position | salary |\n", "|-------|----------------------------------|--------|\n", "| 1 | \"WATER RATE TAKER\" | 88968 |\n", "| 2 | \"POLICE OFFICER\" | 80778 |\n", "| 3 | \"POLICE OFFICER\" | 80778 |\n", "| 4 | \"CHIEF CONTRACT EXPEDITER\" | 84780 |\n", "⋮\n", "| 32177 | \"FRM OF MACHINISTS - AUTOMOTIVE\" | 97448 |\n", "| 32178 | \"POLICE OFFICER\" | 86520 |\n", "| 32179 | \"POLICE OFFICER\" | 83616 |\n", "| 32180 | \"POLICE OFFICER\" | 86520 |\n", "| 32181 | \"CHIEF DATA BASE ANALYST\" | 110352 |" ] }, "execution_count": 69, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(employee:dataframe)" ] }, { "cell_type": "code", "execution_count": 70, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
namesizemax_salary
1WATER MGMNT1848169512
2POLICE13570260004
3GENERAL SERVICES924157092
4CITY COUNCIL397160248
5STREETS & SAN2090157092
6AVIATION1344161652
7FIRE4875202728
8FAMILY & SUPPORT679157092
9IPRA83161856
10PUBLIC LIBRARY951167004
11DoIT106154992
12BUSINESS AFFAIRS173157092
13OEMC1135167796
14TRANSPORTN1200169500
15HEALTH555177000
⋮⋮⋮⋮
" ], "text/plain": [ "35x3 DataFrames.DataFrame\n", "| Row | name | size | max_salary |\n", "|-----|---------------------|-------|------------|\n", "| 1 | \"WATER MGMNT\" | 1848 | 169512 |\n", "| 2 | \"POLICE\" | 13570 | 260004 |\n", "| 3 | \"GENERAL SERVICES\" | 924 | 157092 |\n", "| 4 | \"CITY COUNCIL\" | 397 | 160248 |\n", "⋮\n", "| 31 | \"BOARD OF ETHICS\" | 9 | 131688 |\n", "| 32 | \"POLICE BOARD\" | 2 | 97728 |\n", "| 33 | \"BUDGET & MGMT\" | 43 | 169992 |\n", "| 34 | \"ADMIN HEARNG\" | 39 | 156420 |\n", "| 35 | \"LICENSE APPL COMM\" | 1 | 69888 |" ] }, "execution_count": 70, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " department\n", " :select(\n", " name,\n", " size => count(employee),\n", " max_salary => max(employee.salary))\n", " :dataframe)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "You can pass a list of output fields to the `dataframe` combinator." ] }, { "cell_type": "code", "execution_count": 71, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
namesizemax_salary
1WATER MGMNT1848169512
2POLICE13570260004
3GENERAL SERVICES924157092
4CITY COUNCIL397160248
5STREETS & SAN2090157092
6AVIATION1344161652
7FIRE4875202728
8FAMILY & SUPPORT679157092
9IPRA83161856
10PUBLIC LIBRARY951167004
11DoIT106154992
12BUSINESS AFFAIRS173157092
13OEMC1135167796
14TRANSPORTN1200169500
15HEALTH555177000
⋮⋮⋮⋮
" ], "text/plain": [ "35x3 DataFrames.DataFrame\n", "| Row | name | size | max_salary |\n", "|-----|---------------------|-------|------------|\n", "| 1 | \"WATER MGMNT\" | 1848 | 169512 |\n", "| 2 | \"POLICE\" | 13570 | 260004 |\n", "| 3 | \"GENERAL SERVICES\" | 924 | 157092 |\n", "| 4 | \"CITY COUNCIL\" | 397 | 160248 |\n", "⋮\n", "| 31 | \"BOARD OF ETHICS\" | 9 | 131688 |\n", "| 32 | \"POLICE BOARD\" | 2 | 97728 |\n", "| 33 | \"BUDGET & MGMT\" | 43 | 169992 |\n", "| 34 | \"ADMIN HEARNG\" | 39 | 156420 |\n", "| 35 | \"LICENSE APPL COMM\" | 1 | 69888 |" ] }, "execution_count": 71, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " department\n", " :dataframe(\n", " name,\n", " size => count(employee),\n", " max_salary => max(employee.salary)))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Cartesian product and tagged union\n", "\n", "Use `:mix` to generate a Cartesian product.\n", "\n", "*Multiplication table.*" ] }, { "cell_type": "code", "execution_count": 72, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "165-element Array{Tuple{Int64,Int64,Int64,Int64},1}:\n", " (2,2,2,8) \n", " (2,2,3,12) \n", " (2,2,4,16) \n", " (2,2,5,20) \n", " (2,2,6,24) \n", " ⋮ \n", " (8,10,10,800) \n", " (9,9,9,729) \n", " (9,9,10,810) \n", " (9,10,10,900) \n", " (10,10,10,1000)" ] }, "execution_count": 72, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " mix(a => range(2,1,10),\n", " b => range(2,1,10),\n", " c => range(2,1,10))\n", " :filter((a <= b) & (b <= c))\n", " :select(a, b, c, (a*b)*c))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*All pairs of departments with approximately equal number of employees.*" ] }, { "cell_type": "code", "execution_count": 73, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "16-element Array{Tuple{UTF8String,Int64,UTF8String,Int64},1}:\n", " (\"IPRA\",83,\"CITY CLERK\",82) \n", " (\"IPRA\",83,\"PROCUREMENT\",81) \n", " (\"ANIMAL CONTRL\",67,\"HUMAN RESOURCES\",68) \n", " (\"CITY CLERK\",82,\"IPRA\",83) \n", " (\"CITY CLERK\",82,\"PROCUREMENT\",81) \n", " ⋮ \n", " (\"BOARD OF ETHICS\",9,\"LICENSE APPL COMM\",1)\n", " (\"POLICE BOARD\",2,\"BOARD OF ETHICS\",9) \n", " (\"POLICE BOARD\",2,\"LICENSE APPL COMM\",1) \n", " (\"LICENSE APPL COMM\",1,\"BOARD OF ETHICS\",9)\n", " (\"LICENSE APPL COMM\",1,\"POLICE BOARD\",2) " ] }, "execution_count": 73, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " mix(department, department)\n", " :filter((left.id != right.id) & (left.count(employee)/10 == right.count(employee)/10))\n", " :select(left.name, left.count(employee), right.name, right.count(employee)))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Use `pack()` combinator to generate a tagged union." ] }, { "cell_type": "code", "execution_count": 74, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "10-element Array{Int64,1}:\n", " 1\n", " 2\n", " 3\n", " 4\n", " 5\n", " 95\n", " 96\n", " 97\n", " 98\n", " 99" ] }, "execution_count": 74, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " pack(\n", " a => range(1,1,5),\n", " z => range(95,1,99)))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Values generated by `pack()` don't have to have the same type." ] }, { "cell_type": "code", "execution_count": 75, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "32216-element Array{Tuple{UTF8String,Vararg{Any}},1}:\n", " (\"ELVIA\",\"A\",\"WATER MGMNT\",\"WATER RATE TAKER\",88968) \n", " (\"JEFFERY\",\"A\",\"POLICE\",\"POLICE OFFICER\",80778) \n", " (\"KARINA\",\"A\",\"POLICE\",\"POLICE OFFICER\",80778) \n", " (\"KIMBERLEI\",\"A\",\"GENERAL SERVICES\",\"CHIEF CONTRACT EXPEDITER\",84780)\n", " (\"VICENTE\",\"A\",\"WATER MGMNT\",\"CIVIL ENGINEER IV\",104736) \n", " ⋮ \n", " (\"BOARD OF ETHICS\",) \n", " (\"POLICE BOARD\",) \n", " (\"BUDGET & MGMT\",) \n", " (\"ADMIN HEARNG\",) \n", " (\"LICENSE APPL COMM\",) " ] }, "execution_count": 75, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(pack(employee, department))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "You can extract tagged values using combinators named after the tags." ] }, { "cell_type": "code", "execution_count": 76, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "32181-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:\n", " (\"ELVIA\",\"A\",\"WATER MGMNT\",\"WATER RATE TAKER\",88968) \n", " (\"JEFFERY\",\"A\",\"POLICE\",\"POLICE OFFICER\",80778) \n", " (\"KARINA\",\"A\",\"POLICE\",\"POLICE OFFICER\",80778) \n", " (\"KIMBERLEI\",\"A\",\"GENERAL SERVICES\",\"CHIEF CONTRACT EXPEDITER\",84780) \n", " (\"VICENTE\",\"A\",\"WATER MGMNT\",\"CIVIL ENGINEER IV\",104736) \n", " ⋮ \n", " (\"MICHAEL\",\"Z\",\"GENERAL SERVICES\",\"FRM OF MACHINISTS - AUTOMOTIVE\",97448)\n", " (\"PETER\",\"Z\",\"POLICE\",\"POLICE OFFICER\",86520) \n", " (\"MARK\",\"Z\",\"POLICE\",\"POLICE OFFICER\",83616) \n", " (\"CARLO\",\"Z\",\"POLICE\",\"POLICE OFFICER\",86520) \n", " (\"DARIUSZ\",\"Z\",\"DoIT\",\"CHIEF DATA BASE ANALYST\",110352) " ] }, "execution_count": 76, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(pack(employee, department).employee)" ] }, { "cell_type": "code", "execution_count": 77, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "35-element Array{Tuple{UTF8String},1}:\n", " (\"WATER MGMNT\",) \n", " (\"POLICE\",) \n", " (\"GENERAL SERVICES\",) \n", " (\"CITY COUNCIL\",) \n", " (\"STREETS & SAN\",) \n", " ⋮ \n", " (\"BOARD OF ETHICS\",) \n", " (\"POLICE BOARD\",) \n", " (\"BUDGET & MGMT\",) \n", " (\"ADMIN HEARNG\",) \n", " (\"LICENSE APPL COMM\",)" ] }, "execution_count": 77, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(pack(employee, department).department)" ] }, { "cell_type": "code", "execution_count": 78, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "32216-element Array{Tuple{Nullable{UTF8String},Nullable{UTF8String}},1}:\n", " (Nullable(\"WATER RATE TAKER\"),Nullable{UTF8String}()) \n", " (Nullable(\"POLICE OFFICER\"),Nullable{UTF8String}()) \n", " (Nullable(\"POLICE OFFICER\"),Nullable{UTF8String}()) \n", " (Nullable(\"CHIEF CONTRACT EXPEDITER\"),Nullable{UTF8String}())\n", " (Nullable(\"CIVIL ENGINEER IV\"),Nullable{UTF8String}()) \n", " ⋮ \n", " (Nullable{UTF8String}(),Nullable(\"BOARD OF ETHICS\")) \n", " (Nullable{UTF8String}(),Nullable(\"POLICE BOARD\")) \n", " (Nullable{UTF8String}(),Nullable(\"BUDGET & MGMT\")) \n", " (Nullable{UTF8String}(),Nullable(\"ADMIN HEARNG\")) \n", " (Nullable{UTF8String}(),Nullable(\"LICENSE APPL COMM\")) " ] }, "execution_count": 78, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(pack(employee, department):select(employee.position, department.name))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Use `unlink` to create an unconditional link to an entity class.\n", "\n", "*Find the employees with salary within 50% of the top salary.*" ] }, { "cell_type": "code", "execution_count": 79, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "5-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:\n", " (\"BRADLEY\",\"A\",\"FIRE\",\"CAPTAIN-EMT\",131466) \n", " (\"DANA\",\"A\",\"POLICE\",\"DEPUTY CHIEF\",170112) \n", " (\"MICHAEL\",\"A\",\"POLICE\",\"CAPTAIN\",134820) \n", " (\"SANDRA\",\"A\",\"ANIMAL CONTRL\",\"EXECUTIVE DIR OF ANIMAL CARE AND CONTROL\",138420)\n", " (\"VERDIE\",\"A\",\"FIRE\",\"ASST DEPUTY CHIEF PARAMEDIC\",156360) " ] }, "execution_count": 79, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " employee\n", " :take(500)\n", " :filter(salary > max(unlink(employee).salary)/2))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Use `link` to create a link on an arbitrary condition.\n", "\n", "*For a given employee, find all his namesakes.*" ] }, { "cell_type": "code", "execution_count": 80, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "Nullable((\"TERRY\",36,[(\"TERRY\",\"A\",\"TRANSPORTN\",\"FOREMAN OF CONSTRUCTION LABORERS\",81328),(\"TERRY\",\"B\",\"POLICE\",\"POLICE OFFICER\",83616),(\"TERRY\",\"B\",\"POLICE\",\"POLICE OFFICER\",86520)]))" ] }, "execution_count": 80, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " employee[10]\n", " :define(namesake => link((left.id!=right.id)&(left.name==right.name), employee))\n", " :select(name, count(namesake), namesake:take(3)))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Queries with parameteres\n", "\n", "*Find all employees with the given name and position.*" ] }, { "cell_type": "code", "execution_count": 81, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "36-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:\n", " (\"CHARLES\",\"A\",\"POLICE\",\"POLICE OFFICER\",46206)\n", " (\"CHARLES\",\"A\",\"POLICE\",\"POLICE OFFICER\",89718)\n", " (\"CHARLES\",\"A\",\"POLICE\",\"POLICE OFFICER\",89718)\n", " (\"CHARLES\",\"B\",\"POLICE\",\"POLICE OFFICER\",83616)\n", " (\"CHARLES\",\"B\",\"POLICE\",\"POLICE OFFICER\",89718)\n", " ⋮ \n", " (\"CHARLES\",\"T\",\"POLICE\",\"POLICE OFFICER\",46206)\n", " (\"CHARLES\",\"W\",\"POLICE\",\"POLICE OFFICER\",92316)\n", " (\"CHARLES\",\"W\",\"POLICE\",\"POLICE OFFICER\",86520)\n", " (\"CHARLES\",\"W\",\"POLICE\",\"POLICE OFFICER\",86520)\n", " (\"CHARLES\",\"W\",\"POLICE\",\"POLICE OFFICER\",83616)" ] }, "execution_count": 81, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " employee:filter((position==POSITION) & (name==NAME)),\n", " POSITION=\"POLICE OFFICER\",\n", " NAME=\"CHARLES\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*Find all departments bigger than the given size.*" ] }, { "cell_type": "code", "execution_count": 82, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "7-element Array{Tuple{UTF8String,Int64},1}:\n", " (\"WATER MGMNT\",848) \n", " (\"POLICE\",12570) \n", " (\"STREETS & SAN\",1090)\n", " (\"AVIATION\",344) \n", " (\"FIRE\",3875) \n", " (\"OEMC\",135) \n", " (\"TRANSPORTN\",200) " ] }, "execution_count": 82, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " department\n", " :filter(count(employee)>SIZE)\n", " :select(name, count(employee)-SIZE),\n", " SIZE=1000)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*Find all employees in the given departments.*" ] }, { "cell_type": "code", "execution_count": 83, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "18445-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:\n", " (\"JEFFERY\",\"A\",\"POLICE\",\"POLICE OFFICER\",80778)\n", " (\"KARINA\",\"A\",\"POLICE\",\"POLICE OFFICER\",80778) \n", " (\"JAMES\",\"A\",\"FIRE\",\"FIRE ENGINEER\",90456) \n", " (\"TERRY\",\"A\",\"POLICE\",\"POLICE OFFICER\",86520) \n", " (\"LYNISE\",\"A\",\"POLICE\",\"CLERK III\",43920) \n", " ⋮ \n", " (\"MATTHEW\",\"Z\",\"FIRE\",\"FIREFIGHTER-EMT\",91764) \n", " (\"BRYAN\",\"Z\",\"POLICE\",\"POLICE OFFICER\",80778) \n", " (\"PETER\",\"Z\",\"POLICE\",\"POLICE OFFICER\",86520) \n", " (\"MARK\",\"Z\",\"POLICE\",\"POLICE OFFICER\",83616) \n", " (\"CARLO\",\"Z\",\"POLICE\",\"POLICE OFFICER\",86520) " ] }, "execution_count": 83, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " employee:filter(department.name in DEPTS),\n", " DEPTS=[\"POLICE\", \"FIRE\"])" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Parameters could also be calculated dynamically using combinator `given`.\n", "\n", "*Find the highest paid employee.*" ] }, { "cell_type": "code", "execution_count": 84, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "1-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:\n", " (\"GARRY\",\"M\",\"POLICE\",\"SUPERINTENDENT OF POLICE\",260004)" ] }, "execution_count": 84, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " employee\n", " :filter(salary==MAX_SALARY)\n", " :given(MAX_SALARY => max(employee.salary)))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*Find the highest paid employee in each department.*" ] }, { "cell_type": "code", "execution_count": 85, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "35-element Array{Tuple{UTF8String,Array{Tuple{UTF8String,UTF8String},1}},1}:\n", " (\"WATER MGMNT\",[(\"THOMAS\",\"P\")]) \n", " (\"POLICE\",[(\"GARRY\",\"M\")]) \n", " (\"GENERAL SERVICES\",[(\"DAVID\",\"R\")]) \n", " (\"CITY COUNCIL\",[(\"MARLA\",\"K\")]) \n", " (\"STREETS & SAN\",[(\"CHARLES\",\"W\")]) \n", " ⋮ \n", " (\"BOARD OF ETHICS\",[(\"STEVEN\",\"B\")]) \n", " (\"POLICE BOARD\",[(\"MAX\",\"C\")]) \n", " (\"BUDGET & MGMT\",[(\"ALEXANDRA\",\"H\")]) \n", " (\"ADMIN HEARNG\",[(\"PATRICIA\",\"J\")]) \n", " (\"LICENSE APPL COMM\",[(\"MICHELLE\",\"G\")])" ] }, "execution_count": 85, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " department\n", " :select(\n", " name,\n", " employee\n", " :filter(salary==MAX_SALARY)\n", " :given(MAX_SALARY => max(employee.salary))))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Navigating the context\n", "\n", "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.\n", "\n", "Combinator `before` refers to the set of previous values in the output." ] }, { "cell_type": "code", "execution_count": 86, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
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\"]
⋮⋮⋮
" ], "text/plain": [ "35x2 DataFrames.DataFrame\n", "| Row | name |\n", "|-----|---------------------|\n", "| 1 | \"WATER MGMNT\" |\n", "| 2 | \"POLICE\" |\n", "| 3 | \"GENERAL SERVICES\" |\n", "| 4 | \"CITY COUNCIL\" |\n", "⋮\n", "| 31 | \"BOARD OF ETHICS\" |\n", "| 32 | \"POLICE BOARD\" |\n", "| 33 | \"BUDGET & MGMT\" |\n", "| 34 | \"ADMIN HEARNG\" |\n", "| 35 | \"LICENSE APPL COMM\" |\n", "\n", "| Row | past_names |\n", "|-----|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|\n", "| 1 | UTF8String[] |\n", "| 2 | UTF8String[\"WATER MGMNT\"] |\n", "| 3 | UTF8String[\"POLICE\",\"WATER MGMNT\"] |\n", "| 4 | UTF8String[\"GENERAL SERVICES\",\"POLICE\",\"WATER MGMNT\"] |\n", "⋮\n", "| 31 | UTF8String[\"HUMAN RELATIONS\",\"PROCUREMENT\",\"HUMAN RESOURCES\",\"DISABILITIES\",\"TREASURER\",\"INSPECTOR GEN\",\"BOARD OF ELECTION\",\"CITY CLERK\",\"ANIMAL CONTRL\",\"BUILDINGS\" … \"PUBLIC LIBRARY\",\"IPRA\",\"FAMILY & SUPPORT\",\"FIRE\",\"AVIATION\",\"STREETS & SAN\",\"CITY COUNCIL\",\"GENERAL SERVICES\",\"POLICE\",\"WATER MGMNT\"] |\n", "| 32 | UTF8String[\"BOARD OF ETHICS\",\"HUMAN RELATIONS\",\"PROCUREMENT\",\"HUMAN RESOURCES\",\"DISABILITIES\",\"TREASURER\",\"INSPECTOR GEN\",\"BOARD OF ELECTION\",\"CITY CLERK\",\"ANIMAL CONTRL\" … \"PUBLIC LIBRARY\",\"IPRA\",\"FAMILY & SUPPORT\",\"FIRE\",\"AVIATION\",\"STREETS & SAN\",\"CITY COUNCIL\",\"GENERAL SERVICES\",\"POLICE\",\"WATER MGMNT\"] |\n", "| 33 | UTF8String[\"POLICE BOARD\",\"BOARD OF ETHICS\",\"HUMAN RELATIONS\",\"PROCUREMENT\",\"HUMAN RESOURCES\",\"DISABILITIES\",\"TREASURER\",\"INSPECTOR GEN\",\"BOARD OF ELECTION\",\"CITY CLERK\" … \"PUBLIC LIBRARY\",\"IPRA\",\"FAMILY & SUPPORT\",\"FIRE\",\"AVIATION\",\"STREETS & SAN\",\"CITY COUNCIL\",\"GENERAL SERVICES\",\"POLICE\",\"WATER MGMNT\"] |\n", "| 34 | UTF8String[\"BUDGET & MGMT\",\"POLICE BOARD\",\"BOARD OF ETHICS\",\"HUMAN RELATIONS\",\"PROCUREMENT\",\"HUMAN RESOURCES\",\"DISABILITIES\",\"TREASURER\",\"INSPECTOR GEN\",\"BOARD OF ELECTION\" … \"PUBLIC LIBRARY\",\"IPRA\",\"FAMILY & SUPPORT\",\"FIRE\",\"AVIATION\",\"STREETS & SAN\",\"CITY COUNCIL\",\"GENERAL SERVICES\",\"POLICE\",\"WATER MGMNT\"] |\n", "| 35 | UTF8String[\"ADMIN HEARNG\",\"BUDGET & MGMT\",\"POLICE BOARD\",\"BOARD OF ETHICS\",\"HUMAN RELATIONS\",\"PROCUREMENT\",\"HUMAN RESOURCES\",\"DISABILITIES\",\"TREASURER\",\"INSPECTOR GEN\" … \"PUBLIC LIBRARY\",\"IPRA\",\"FAMILY & SUPPORT\",\"FIRE\",\"AVIATION\",\"STREETS & SAN\",\"CITY COUNCIL\",\"GENERAL SERVICES\",\"POLICE\",\"WATER MGMNT\"] |" ] }, "execution_count": 86, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " department\n", " :dataframe(\n", " name,\n", " past_names => before.name))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Similarly, combinator `after` refers to all the subsequent values." ] }, { "cell_type": "code", "execution_count": 87, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
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
⋮⋮⋮
" ], "text/plain": [ "35x2 DataFrames.DataFrame\n", "| Row | name | next_name |\n", "|-----|---------------------|---------------------|\n", "| 1 | \"WATER MGMNT\" | \"POLICE\" |\n", "| 2 | \"POLICE\" | \"GENERAL SERVICES\" |\n", "| 3 | \"GENERAL SERVICES\" | \"CITY COUNCIL\" |\n", "| 4 | \"CITY COUNCIL\" | \"STREETS & SAN\" |\n", "⋮\n", "| 31 | \"BOARD OF ETHICS\" | \"POLICE BOARD\" |\n", "| 32 | \"POLICE BOARD\" | \"BUDGET & MGMT\" |\n", "| 33 | \"BUDGET & MGMT\" | \"ADMIN HEARNG\" |\n", "| 34 | \"ADMIN HEARNG\" | \"LICENSE APPL COMM\" |\n", "| 35 | \"LICENSE APPL COMM\" | NA |" ] }, "execution_count": 87, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " department\n", " :dataframe(\n", " name,\n", " next_name => first(after).name))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "You can use the `before` combinator to number output records." ] }, { "cell_type": "code", "execution_count": 88, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "35-element Array{Tuple{Int64,UTF8String},1}:\n", " (1,\"WATER MGMNT\") \n", " (2,\"POLICE\") \n", " (3,\"GENERAL SERVICES\") \n", " (4,\"CITY COUNCIL\") \n", " (5,\"STREETS & SAN\") \n", " ⋮ \n", " (31,\"BOARD OF ETHICS\") \n", " (32,\"POLICE BOARD\") \n", " (33,\"BUDGET & MGMT\") \n", " (34,\"ADMIN HEARNG\") \n", " (35,\"LICENSE APPL COMM\")" ] }, "execution_count": 88, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " department\n", " :select(1+count(before), name))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "A variant of `before` called `and_before` includes the current record in the set. You can also use `and_before` to calculate running totals." ] }, { "cell_type": "code", "execution_count": 89, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
namesizetotal
1WATER MGMNT18481848
2POLICE1357015418
3GENERAL SERVICES92416342
4CITY COUNCIL39716739
5STREETS & SAN209018829
6AVIATION134420173
7FIRE487525048
8FAMILY & SUPPORT67925727
9IPRA8325810
10PUBLIC LIBRARY95126761
11DoIT10626867
12BUSINESS AFFAIRS17327040
13OEMC113528175
14TRANSPORTN120029375
15HEALTH55529930
⋮⋮⋮⋮
" ], "text/plain": [ "35x3 DataFrames.DataFrame\n", "| Row | name | size | total |\n", "|-----|---------------------|-------|-------|\n", "| 1 | \"WATER MGMNT\" | 1848 | 1848 |\n", "| 2 | \"POLICE\" | 13570 | 15418 |\n", "| 3 | \"GENERAL SERVICES\" | 924 | 16342 |\n", "| 4 | \"CITY COUNCIL\" | 397 | 16739 |\n", "⋮\n", "| 31 | \"BOARD OF ETHICS\" | 9 | 32096 |\n", "| 32 | \"POLICE BOARD\" | 2 | 32098 |\n", "| 33 | \"BUDGET & MGMT\" | 43 | 32141 |\n", "| 34 | \"ADMIN HEARNG\" | 39 | 32180 |\n", "| 35 | \"LICENSE APPL COMM\" | 1 | 32181 |" ] }, "execution_count": 89, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " department\n", " :define(size => count(employee))\n", " :dataframe(\n", " name,\n", " size,\n", " total => sum(and_before.size)))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Combinator `and_around` let you refer to the full set of the output values.\n", "\n", "*Find the departments with the largest number of employees.*" ] }, { "cell_type": "code", "execution_count": 90, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "1-element Array{Tuple{UTF8String,Int64},1}:\n", " (\"POLICE\",13570)" ] }, "execution_count": 90, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " department\n", " :define(size => count(employee))\n", " :filter(size == max(and_around.size))\n", " :select(name, size))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Combinator `around` can also give you output values that have the same property as the current value.\n", "\n", "*For each employee in a certain department, find how much does their salary differ from the top salary for their position.*" ] }, { "cell_type": "code", "execution_count": 91, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
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
⋮⋮⋮⋮⋮⋮
" ], "text/plain": [ "24x5 DataFrames.DataFrame\n", "| Row | name | surname | position | salary |\n", "|-----|----------------|---------|---------------------------------|--------|\n", "| 1 | \"SAEED\" | \"A\" | \"ASST CITY TREASURER\" | 85020 |\n", "| 2 | \"ELIZABETH\" | \"A\" | \"ACCOUNTANT I\" | 72840 |\n", "| 3 | \"KONSTANTINES\" | \"A\" | \"ASSISTANT DIRECTOR OF FINANCE\" | 73080 |\n", "| 4 | \"SARA\" | \"A\" | \"STUDENT INTERN\" | 15600 |\n", "⋮\n", "| 20 | \"DANIELLE\" | \"M\" | \"ASST CITY TREASURER\" | 85020 |\n", "| 21 | \"MARK\" | \"M\" | \"PORTFOLIO MANAGER\" | 76212 |\n", "| 22 | \"KENNETH\" | \"S\" | \"ASST CITY TREASURER\" | 75000 |\n", "| 23 | \"ALEXANDRA\" | \"S\" | \"DEPUTY CITY TREASURER\" | 90000 |\n", "| 24 | \"KURT\" | \"S\" | \"CITY TREASURER\" | 133545 |\n", "\n", "| Row | salary_diff |\n", "|-----|-------------|\n", "| 1 | 0 |\n", "| 2 | 0 |\n", "| 3 | 0 |\n", "| 4 | 0 |\n", "⋮\n", "| 20 | 0 |\n", "| 21 | 0 |\n", "| 22 | 10020 |\n", "| 23 | 23898 |\n", "| 24 | 0 |" ] }, "execution_count": 91, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " employee\n", " :filter(department.name == DEPT)\n", " :dataframe(\n", " name,\n", " surname,\n", " position,\n", " salary,\n", " salary_diff => max(and_around(position).salary)-salary),\n", " DEPT=\"TREASURER\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "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.\n", "\n", "*Find the highest paid employee in each department.*" ] }, { "cell_type": "code", "execution_count": 92, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "35-element Array{Tuple{UTF8String,Array{Tuple{UTF8String,UTF8String},1}},1}:\n", " (\"WATER MGMNT\",[(\"THOMAS\",\"P\")]) \n", " (\"POLICE\",[(\"GARRY\",\"M\")]) \n", " (\"GENERAL SERVICES\",[(\"DAVID\",\"R\")]) \n", " (\"CITY COUNCIL\",[(\"MARLA\",\"K\")]) \n", " (\"STREETS & SAN\",[(\"CHARLES\",\"W\")]) \n", " ⋮ \n", " (\"BOARD OF ETHICS\",[(\"STEVEN\",\"B\")]) \n", " (\"POLICE BOARD\",[(\"MAX\",\"C\")]) \n", " (\"BUDGET & MGMT\",[(\"ALEXANDRA\",\"H\")]) \n", " (\"ADMIN HEARNG\",[(\"PATRICIA\",\"J\")]) \n", " (\"LICENSE APPL COMM\",[(\"MICHELLE\",\"G\")])" ] }, "execution_count": 92, "metadata": {}, "output_type": "execute_result" } ], "source": [ "@query(\n", " department\n", " :select(\n", " name,\n", " employee\n", " :filter(salary==max(and_around.salary))\n", " :frame))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Compiling and executing queries\n", "You can compile and execute queries separately. To compile a query, use the `RBT.prepare()` function." ] }, { "cell_type": "code", "execution_count": 93, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "department.employee.name :: Array{UTF8String,1}" ] }, "execution_count": 93, "metadata": {}, "output_type": "execute_result" } ], "source": [ "q1 = RBT.@prepare(department.employee.name)" ] }, { "cell_type": "code", "execution_count": 94, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "count(department) :: Int64" ] }, "execution_count": 94, "metadata": {}, "output_type": "execute_result" } ], "source": [ "q2 = RBT.@prepare(count(department))" ] }, { "cell_type": "code", "execution_count": 95, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "department:select(name,count(employee)) :: Array{Tuple{UTF8String,Int64},1}" ] }, "execution_count": 95, "metadata": {}, "output_type": "execute_result" } ], "source": [ "q3 = RBT.@prepare(department:select(name,count(employee)))" ] }, { "cell_type": "code", "execution_count": 96, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "count(employee:filter(&(>(salary,100000),<(salary,200000)))) :: Int64" ] }, "execution_count": 96, "metadata": {}, "output_type": "execute_result" } ], "source": [ "q4 = RBT.@prepare(count(employee:filter((salary>100000)&(salary<200000))))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "You can also prepare a query with parameters." ] }, { "cell_type": "code", "execution_count": 97, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "*(X,*(Y,Z)) :: {Void, X => Int64, Y => Nullable{Int64}, Z => Array{Int64,1}} -> Array{Int64,1}" ] }, "execution_count": 97, "metadata": {}, "output_type": "execute_result" } ], "source": [ "q5 = RBT.@prepare(X*(Y*Z), X=Int, Y=Nullable{Int}, Z=Vector{Int})" ] }, { "cell_type": "code", "execution_count": 98, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "employee:filter(&(==(name,NAME),>(salary,MIN_SALARY))) :: {Void, MIN_SALARY => Int64, NAME => UTF8String} -> Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}" ] }, "execution_count": 98, "metadata": {}, "output_type": "execute_result" } ], "source": [ "q6 = RBT.@prepare(employee:filter((name == NAME) & (salary > MIN_SALARY)), NAME=UTF8String, MIN_SALARY=Int)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Queries know their parameters." ] }, { "cell_type": "code", "execution_count": 99, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "Pair{Tuple{Vararg{Symbol}},Tuple{Vararg{Type{T}}}}((),())" ] }, "execution_count": 99, "metadata": {}, "output_type": "execute_result" } ], "source": [ "RBT.params(q1)" ] }, { "cell_type": "code", "execution_count": 100, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "Pair{Tuple{Vararg{Symbol}},Tuple{Vararg{Type{T}}}}((:X,:Y,:Z),(Int64,Nullable{Int64},Array{Int64,1}))" ] }, "execution_count": 100, "metadata": {}, "output_type": "execute_result" } ], "source": [ "RBT.params(q5)" ] }, { "cell_type": "code", "execution_count": 101, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "Pair{Tuple{Vararg{Symbol}},Tuple{Vararg{Type{T}}}}((:MIN_SALARY,:NAME),(Int64,UTF8String))" ] }, "execution_count": 101, "metadata": {}, "output_type": "execute_result" } ], "source": [ "RBT.params(q6)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "To execute a query, call the compiled query as a function." ] }, { "cell_type": "code", "execution_count": 102, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "32181-element Array{UTF8String,1}:\n", " \"ELVIA\" \n", " \"VICENTE\" \n", " \"MUHAMMAD\" \n", " \"GIRLEY\" \n", " \"DILAN\" \n", " ⋮ \n", " \"NANCY\" \n", " \"DARCI\" \n", " \"THADDEUS\" \n", " \"RACHENETTE\"\n", " \"MICHELLE\" " ] }, "execution_count": 102, "metadata": {}, "output_type": "execute_result" } ], "source": [ "q1()" ] }, { "cell_type": "code", "execution_count": 103, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "35" ] }, "execution_count": 103, "metadata": {}, "output_type": "execute_result" } ], "source": [ "q2()" ] }, { "cell_type": "code", "execution_count": 104, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "35-element Array{Tuple{UTF8String,Int64},1}:\n", " (\"WATER MGMNT\",1848) \n", " (\"POLICE\",13570) \n", " (\"GENERAL SERVICES\",924)\n", " (\"CITY COUNCIL\",397) \n", " (\"STREETS & SAN\",2090) \n", " ⋮ \n", " (\"BOARD OF ETHICS\",9) \n", " (\"POLICE BOARD\",2) \n", " (\"BUDGET & MGMT\",43) \n", " (\"ADMIN HEARNG\",39) \n", " (\"LICENSE APPL COMM\",1) " ] }, "execution_count": 104, "metadata": {}, "output_type": "execute_result" } ], "source": [ "q3()" ] }, { "cell_type": "code", "execution_count": 105, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "3916" ] }, "execution_count": 105, "metadata": {}, "output_type": "execute_result" } ], "source": [ "q4()" ] }, { "cell_type": "code", "execution_count": 106, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "3-element Array{Int64,1}:\n", " 60\n", " 40\n", " 20" ] }, "execution_count": 106, "metadata": {}, "output_type": "execute_result" } ], "source": [ "q5(X=5, Y=Nullable(4), Z=[3,2,1])" ] }, { "cell_type": "code", "execution_count": 107, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "28-element Array{Tuple{UTF8String,UTF8String,UTF8String,UTF8String,Int64},1}:\n", " (\"CHARLES\",\"A\",\"POLICE\",\"SERGEANT\",103590) \n", " (\"CHARLES\",\"B\",\"POLICE\",\"SERGEANT\",106920) \n", " (\"CHARLES\",\"B\",\"POLICE\",\"SERGEANT\",103590) \n", " (\"CHARLES\",\"B\",\"FINANCE\",\"AUDITOR IV\",114492) \n", " (\"CHARLES\",\"D\",\"POLICE\",\"SERGEANT\",106920) \n", " ⋮ \n", " (\"CHARLES\",\"S\",\"FIRE\",\"FIRST DEPUTY FIRE COMMISSIONER\",197736) \n", " (\"CHARLES\",\"W\",\"STREETS & SAN\",\"GENERAL SUPT OF STREETS AND SANITATION\",110112)\n", " (\"CHARLES\",\"W\",\"FIRE\",\"LIEUTENANT-EMT\",106524) \n", " (\"CHARLES\",\"W\",\"FIRE\",\"BATTALION CHIEF - EMT\",143682) \n", " (\"CHARLES\",\"W\",\"STREETS & SAN\",\"COMMISSIONER OF STREETS AND SANITATION\",157092)" ] }, "execution_count": 107, "metadata": {}, "output_type": "execute_result" } ], "source": [ "q6(NAME=\"CHARLES\", MIN_SALARY=100000)" ] } ], "metadata": { "kernelspec": { "display_name": "Julia 0.5.0-dev", "language": "julia", "name": "julia-0.5" }, "language_info": { "file_extension": ".jl", "mimetype": "application/julia", "name": "julia", "version": "0.5.0" } }, "nbformat": 4, "nbformat_minor": 0 }