代码之家 › 专栏 › 技术社区 › Konrad

合并具有度量值类型的长表中两个查询的结果

opendata sparql select

0

Konrad · 技术社区 · 6 年前

背景

statistics.gov.scot 门户。该门户上表示的大多数数据都可以作为计数或比率使用。我有两个查询集,我可以使用它们来推导比率或计数,使用以下公式:

比率

?x <http://statistics.gov.scot/def/measure-properties/ratio> ?val_rto .

计数

?x <http://statistics.gov.scot/def/measure-properties/count> ?val_cnt .

我想合并这些查询,以便在一个表中获得两个度量值(比率和计数)。

------------------------------------------------
| geo         | time      | measure | value   | 
===============================================
| "S01002267" | "2001-Q2" | "Ratio" | 0.989808|
| "S01002266" | "2001-Q2" | "Ratio" | 0.989878|
| "S01002267" | "2001-Q2" | "Count" | 11111111|
| "S01002266" | "2001-Q2" | "Count" | 222     | 
-----------------------------------------------

总之,我希望在一列中有一个实际值,在另一列中有值类型(度量、比率等)的标签。我在我能到达的最后一步被卡住了 任何一个 计数或比率。

端点的Web界面位于: https://statistics.gov.scot/sparql

比率

# Prefixes
# As derived from https://statistics.gov.scot/sparql
PREFIX qb: <http://purl.org/linked-data/cube#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX sdmx: <http://purl.org/linked-data/sdmx/2009/concept#>
# Added for convenience 
PREFIX dim: <http://purl.org/linked-data/sdmx/2009/dimension#>

# Query
SELECT DISTINCT ?geo ?time ?measure ?val_cnt ?val_rto
WHERE {
    ?x dim:refArea ?geoURI .
    ?geoURI rdfs:label ?geo .


    ?x qb:dataSet <http://statistics.gov.scot/data/job-seekers-allowance> .

    ?x dim:refPeriod ?periodURI .
    ?periodURI rdfs:label ?time .

    ?x qb:measureType ?measureURI .
    ?measureURI rdfs:label ?measure .

    ?x <http://statistics.gov.scot/def/measure-properties/ratio> ?val_rto .

    FILTER (?time IN ("2001-Q1", "2001-Q2"))
}
LIMIT 20

结果

----------------------------------------------------------
| geo         | time      | measure | val_cnt | val_rto  |
==========================================================
| "S01002267" | "2001-Q2" | "Ratio" | ""      | "4.1E0"  |
| "S01002266" | "2001-Q2" | "Ratio" | ""      | "1.6E0"  |
| "S01002269" | "2001-Q2" | "Ratio" | ""      | "0"      |
| ...         | ...       | ...     | ...     | ...      |
----------------------------------------------------------

计数

# Prefixes
# As derived from https://statistics.gov.scot/sparql
PREFIX qb: <http://purl.org/linked-data/cube#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX sdmx: <http://purl.org/linked-data/sdmx/2009/concept#>
# Added for convenience 
PREFIX dim: <http://purl.org/linked-data/sdmx/2009/dimension#>

# Query
SELECT DISTINCT ?geo ?time ?measure ?val_cnt ?val_rto
WHERE {
    ?x dim:refArea ?geoURI .
    ?geoURI rdfs:label ?geo .


    ?x qb:dataSet <http://statistics.gov.scot/data/job-seekers-allowance> .

    ?x dim:refPeriod ?periodURI .
    ?periodURI rdfs:label ?time .

    ?x qb:measureType ?measureURI .
    ?measureURI rdfs:label ?measure .

    ?x <http://statistics.gov.scot/def/measure-properties/count> ?val_cnt .

    FILTER (?time IN ("2001-Q1", "2001-Q2"))
}
LIMIT 20

结果

-----------------------------------------------------------------
| geo                 | time      | measure | val_cnt | val_rto |
=================================================================
| "Comrie West"       | "2001-Q2" | "Count" | "0"     | ""      |
| "Comrie West"       | "2001-Q2" | "Count" | "10"    | ""      |
| "Comrie West"       | "2001-Q2" | "Count" | "5"     | ""      |
| ...                 | ...       | ...     | ...     | ...     |
-----------------------------------------------------------------

0 回复 | 直到 6 年前