SPARQL Part III Jan Pettersen Nytun, UiA 1 S Agenda O P - PowerPoint PPT Presentation

SPARQL Part III Jan Pettersen Nytun, UiA 1

S Agenda O P  Example with: - ORDER BY - SUM  Example continues with: - GROUP BY - GROUP BY together with SUM  Example continues with: - HAVING - BIND - CONCAT  New example with: - CONSTRUCT - INSERT - DELETE - UNION - COUNT page 2

Example: Explore salaries of the S O P employees of a company… Jan Pettersen Nytun, UIA, page 3

4

S O P Jan Pettersen Nytun, UIA, page 5

S List Salaries, O P i.e., list employee, year and amount Step 1: What to list PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> PREFIX owl: <http://www.w3.org/2002/07/owl#> PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#> PREFIX xsd: <http://www.w3.org/2001/XMLSchema#> PREFIX : <http://www.uia.no/ikt437/janpettersennytun/ontologies/salary#> SELECT ?employee ?year ?amount WHERE { … } Jan Pettersen Nytun, UIA, page 6

S O P Step 2: Get all resources with salary SELECT ?employee ?year ?amount WHERE { ?employee :hasSalary :salary } ?salary :hasSalary ?employee Jan Pettersen Nytun, UIA, page 7

S O P ?salary Totally 6 matches: :hasSalary :bob :hasSalary :bobSalaray2013 . :bob :hasSalary :bobSalaray2014 . … ?employee Jan Pettersen Nytun, UIA, page 8

S What if some other types of resources O P have salaries? SELECT ?employee ?year ?amount WHERE { ?employee :hasSalary ?salary . ?employee rdf:type :Employee } ?salary Employee rdf:type :hasSalary ?employee Jan Pettersen Nytun, UIA, page 9

S Again there are 6 matches. ?salary Employee O P Showing one of them: (composed of 2 triples) : rdf:type :bob :hasSalary :bobSalaray2014 . :hasSalary :bob rdf:type :Employee . ?employee Totally 6 matches, but showing only one match: Jan Pettersen Nytun, UIA, page 10

S Step 3: Get year and amount also [4] O P PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> PREFIX owl: <http://www.w3.org/2002/07/owl#> PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#> PREFIX xsd: <http://www.w3.org/2001/XMLSchema#> PREFIX : <http://www.uia.no/ikt437/janpettersennytun/ontologies/salary#> SELECT ?employee ?year ?amount WHERE { ?employee :hasSalary ?salary . ?salary :hasYear ?year . ?salary :hasAmount ?amount} Jan Pettersen Nytun, UIA, page 11

List Salaries Ordered By Year [4] S O P PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> PREFIX owl: <http://www.w3.org/2002/07/owl#> PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#> PREFIX xsd: <http://www.w3.org/2001/XMLSchema#> PREFIX : <http://www.uia.no/ikt437/janpettersennytun/ontologies/salary#> SELECT ?employee ?year ?amount WHERE { ?employee :hasSalary ?salary . ?salary :hasYear ?year . ?salary :hasAmount ?amount} ORDER BY ?year Jan Pettersen Nytun, UIA, page 12

S Agenda O P  Example with: - ORDER BY - SUM  Example continues with: - GROUP BY - GROUP BY together with SUM  Example continues with: - HAVING - BIND - CONCAT  New example with: - CONSTRUCT - INSERT - DELETE - UNION - COUNT page 13

S Total Sum Of All Salaries [4] O P PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> PREFIX owl: <http://www.w3.org/2002/07/owl#> PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#> PREFIX xsd: <http://www.w3.org/2001/XMLSchema#> PREFIX : <http://www.uia.no/ikt437/janpettersennytun/ontologies/salary#> SELECT (SUM (?amount) AS ?total) WHERE { ?employee :hasSalary ?salary . ?salary :hasYear ?year . ?salary :hasAmount ?amount} Jan Pettersen Nytun, UIA, page 14

S Agenda O P  Example with: - ORDER BY - SUM  Example continues with: - GROUP BY - GROUP BY together with SUM  Example continues with: - HAVING - BIND - CONCAT  New example with: - CONSTRUCT - INSERT - DELETE - UNION - COUNT page 15

S O P How to get: Salaries Per Year Jan Pettersen Nytun, UIA, page 16

S O P [3] By default a solution set consists of a single group, containing all solutions. WHERE { ?employee :hasSalary ?salary . ?salary :hasYear ?year . ?salary :hasAmount ?amount} Assume this gives the following 4 matches: :e1 :hasSalary :s1 . :s1 :hasYear “2001”. :s1 : hasAmount “100” . :e2 :hasSalary :s2 . :s2 :hasYear “2003”. :s2 : hasAmount “200” . :e3 :hasSalary :s3 . :s3 :hasYear “2003”. :s3 : hasAmount “1300” . :e4 :hasSalary :s4 . :s4 :hasYear “2001”. :s4 : hasAmount “400” . Jan Pettersen Nytun, UIA, page 17

S O P Grouping may be specified using GROUP BY. SELECT … WHERE { ?employee :hasSalary ?salary . ?salary :hasYear ?year . ?salary :hasAmount ?amount} GROUP BY ?year Jan Pettersen Nytun, UIA, page 18

S :e1 :hasSalary :s1 . :s1 :hasYear “2001”. :s1 : hasAmount “100” . O P :e2 :hasSalary :s2 . :s2 :hasYear “2003”. :s2 : hasAmount “200” . :e3 :hasSalary :s3 . :s3 :hasYear “2003”. :s3 : hasAmount “1300” . :e4 :hasSalary :s4 . :s4 :hasYear “2001”. :s4 : hasAmount “400” . WHERE { … } GROUP BY ?year Gives 2 groups: :e1 :hasSalary :s1 . :s1 :hasYear “2001”. :s1 : hasAmount “100” . :e4 :hasSalary :s4 . :s4 :hasYear “2001”. :s4 : hasAmount “400” . :e2 :hasSalary :s2 . :s2 :hasYear “2003”. :s2 : hasAmount “200” . :e3 :hasSalary :s3 . :s3 :hasYear “2003”. :s3 : hasAmount “1300” . page 19

S Aggregates O P Aggregates apply expressions over groups of solutions. Aggregates defined in version 1.1 of SPARQL are COUNT, SUM, MIN, MAX, AVG, ... SELECT ?year ( SUM (?amount) AS ?total ) WHERE { ?employee :hasSalary ?salary . ?salary :hasYear ?year . ?salary :hasAmount ?amount} GROUP BY ?year Jan Pettersen Nytun, UIA, page 20

S :e1 :hasSalary :s1 . :s1 :hasYear “2001”. :s1 : hasAmount “100” . O P :e4 :hasSalary :s4 . :s4 :hasYear “2001”. :s4 : hasAmount “400” . :e2 :hasSalary :s2 . :s2 :hasYear “2003”. :s2 : hasAmount “200” . :e3 :hasSalary :s3 . :s3 :hasYear “2003”. :s3 : hasAmount “1300” . SELECT ?year (SUM (?amount) AS ?total) …. Gives 2 results: year total 2001 500 2003 1500 page 21

S Salaries Per Year [4] O P PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> PREFIX owl: <http://www.w3.org/2002/07/owl#> PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#> PREFIX xsd: <http://www.w3.org/2001/XMLSchema#> PREFIX : <http://www.uia.no/ikt437/janpettersennytun/ontologies/salary#> SELECT ?year (SUM (?amount) AS ?total) WHERE { ?employee :hasSalary ?salary . ?salary :hasYear ?year . ?salary :hasAmount ?amount} GROUP BY ?year page 22

Adding several salaries per year S O P for some employees : Jan Pettersen Nytun, UIA, page 23

Ontology used: @prefix : <http://www.uia.no/ikt437/janpettersennytun/ontologies/salary#> . @prefix owl: <http://www.w3.org/2002/07/owl#> . @prefix rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> . @prefix xml: <http://www.w3.org/XML/1998/namespace> . @prefix xsd: <http://www.w3.org/2001/XMLSchema#> . @prefix rdfs: <http://www.w3.org/2000/01/rdf-schema#> . @base <http://www.uia.no/ikt437/janpettersennytun/ontologies/salary> . <http://www.uia.no/ikt437/janpettersennytun/ontologies/salary> rdf:type owl:Ontology . :hasSalary rdf:type owl:ObjectProperty ; rdfs:domain :Employee ; rdfs:range :Salary . :hasAmount rdf:type owl:DatatypeProperty ; rdfs:range xsd:int . :hasName rdf:type owl:DatatypeProperty . :hasYear rdf:type owl:DatatypeProperty ; rdfs:range xsd:int . 24

How to see the salaries summed up S O P for each employees by year [4] SELECT ?year ?employee (SUM (?amount) AS ?total) WHERE { ?employee :hasSalary ?salary . ?salary :hasYear ?year . ?salary :hasAmount ?amount } GROUP BY ?year ?employee Jan Pettersen Nytun, UIA, page 25

S [4] O P SELECT ?year ?employee (SUM (?amount) AS ?total) WHERE { ?employee :hasSalary ?salary . ?salary :hasYear ?year . ?salary :hasAmount ?amount } GROUP BY ?year ?employee ORDER BY ?year ?employee

S Agenda O P  Example with: - ORDER BY - SUM  Example continues with: - GROUP BY - GROUP BY together with SUM  Example continues with: - HAVING - BIND - CONCAT  New example with: - CONSTRUCT - INSERT - DELETE - UNION - COUNT page 27

Only showing yearly salaries S O P higher than 250000 [4] SELECT ?year ?employee (SUM (?amount) AS ?total) WHERE { ?employee :hasSalary ?salary . ?salary :hasYear ?year . ?salary :hasAmount ?amount } GROUP BY ?year ?employee HAVING (?total > 250000) ORDER BY ?year ?employee Old query result: Jan Pettersen Nytun, UIA, page 28

S O P [4]: HAVING and FILTER are very similar ... FILTER refers to variables bound within a particular graph pattern … always appears in the pattern (between “{“ and ”}”), while HAVING refers to variables defined by aggregations in the SELECT clause, and hence always appears outside a graph pattern. Jan Pettersen Nytun, UIA, page 29

S Agenda O P  Example with: - ORDER BY - SUM  Example continues with: - GROUP BY - GROUP BY together with SUM  Example continues with: - HAVING - BIND - CONCAT  New example with: - CONSTRUCT - INSERT - DELETE - UNION - COUNT page 30