按组选择出现频率最高的值 [英] select value that occurs most frequently by group
本文介绍了按组选择出现频率最高的值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有关于住院病人的RDF数据，包括他们的出生日期。关于出生日期的三元组通常多个，其中一些可能是错误的。我的团队已决定使用此规则：出现频率最高的日期将暂时被视为正确。很清楚如何使用我们选择的任何编程语言(在SPARQL外部)来实现这一点。
在SPARQL中可以聚合聚合吗？
我已经阅读了类似的问题SPARQL selecting MAX value of a counter，但我还没有读到。
给定这三个数：
@prefix turbo: <http://example.org/ontologies/> .
@prefix xsd: <http://www.w3.org/2001/XMLSchema#> .
<http://example.org/ontologies/b6be95364ec943af2ef4ab161c11c855>
a <http://example.org/ontologies/StudyPartWithBBDonation> ;
turbo:hasBirthDateO turbo:3950b2b6-f575-4074-b0e8-f9fa3378f3be, turbo:4250aafa-4b0c-4f73-92b6-7639f427b61d, turbo:a3e6676e-a214-4af4-b8ef-34a8e20170bf .
turbo:3950b2b6-f575-4074-b0e8-f9fa3378f3be turbo:hasDateValue "1971-12-30"^^xsd:date .
turbo:4250aafa-4b0c-4f73-92b6-7639f427b61d turbo:hasDateValue "1971-12-30"^^xsd:date .
turbo:a3e6676e-a214-4af4-b8ef-34a8e20170bf turbo:hasDateValue "1971-12-30"^^xsd:date .
turbo:6e200ca0d5150282787464a2bda55814
a turbo:StudyPartWithBBDonation ;
turbo:hasBirthDateO turbo:b09519f5-b123-40d5-bb4a-737ec9f8b9a8, turbo:06c56881-a6c7-4d1d-993b-add8862dffd7, turbo:12ef184d-c8d6-4d93-a558-a3ba47bb56ca .
turbo:b09519f5-b123-40d5-bb4a-737ec9f8b9a8 turbo:hasDateValue "2000-04-04"^^xsd:date .
turbo:06c56881-a6c7-4d1d-993b-add8862dffd7 turbo:hasDateValue "2000-04-04"^^xsd:date .
turbo:12ef184d-c8d6-4d93-a558-a3ba47bb56ca turbo:hasDateValue "2000-04-05"^^xsd:date .
此查询
PREFIX turbo: <http://example.org/ontologies/>
SELECT ?part ?xsddate (COUNT(?xsddate) AS ?datecount)
{ ?part rdf:type turbo:StudyPartWithBBDonation ;
turbo:hasBirthDateO ?dob .
?dob turbo:hasDateValue ?xsddate
}
GROUP BY ?part ?xsddate
提供以下内容：
+----------------------------------------+------------------------+------------------+
| part | xsddate | datecount |
+----------------------------------------+------------------------+------------------+
| turbo:6e200ca0d5150282787464a2bda55814 | "2000-04-05"^^xsd:date | "1"^^xsd:integer |
| turbo:b6be95364ec943af2ef4ab161c11c855 | "1971-12-30"^^xsd:date | "3"^^xsd:integer |
| turbo:6e200ca0d5150282787464a2bda55814 | "2000-04-04"^^xsd:date | "2"^^xsd:integer |
+----------------------------------------+------------------------+------------------+
我只想查看参与研究的每个患者计数最高的日期：
+----------------------------------------+------------------------+------------------+
| part | xsddate | datecount |
+----------------------------------------+------------------------+------------------+
| turbo:b6be95364ec943af2ef4ab161c11c855 | "1971-12-30"^^xsd:date | "3"^^xsd:integer |
| turbo:6e200ca0d5150282787464a2bda55814 | "2000-04-04"^^xsd:date | "2"^^xsd:integer |
+----------------------------------------+------------------------+------------------+
我想我快到了。现在我需要获取同一行的计数和最大计数！
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX turbo: <http://example.org/ontologies/>
SELECT ?part ?xsddate ?datecount ?countmax
WHERE
{ { SELECT ?part ?xsddate (COUNT(?xsddate) AS ?datecount)
WHERE
{ ?part rdf:type turbo:StudyPartWithBBDonation ;
turbo:hasBirthDateO ?dob .
?dob turbo:hasDateValue ?xsddate
}
GROUP BY ?part ?xsddate
}
UNION
{ SELECT ?part (MAX(?datecount) AS ?countmax)
WHERE
{ SELECT ?part ?xsddate (COUNT(?xsddate) AS ?datecount)
WHERE
{ ?part rdf:type turbo:StudyPartWithBBDonation ;
turbo:hasBirthDateO ?dob .
?dob turbo:hasDateValue ?xsddate
}
GROUP BY ?part ?xsddate
}
GROUP BY ?part
}
}
赠送
+----------------------------------------+------------------------+------------------+------------------+
| part | xsddate | datecount | countmax |
+----------------------------------------+------------------------+------------------+------------------+
| turbo:6e200ca0d5150282787464a2bda55814 | "2000-04-05"^^xsd:date | "1"^^xsd:integer | |
| turbo:b6be95364ec943af2ef4ab161c11c855 | "1971-12-30"^^xsd:date | "3"^^xsd:integer | |
| turbo:6e200ca0d5150282787464a2bda55814 | "2000-04-04"^^xsd:date | "2"^^xsd:integer | |
| turbo:6e200ca0d5150282787464a2bda55814 | | | "2"^^xsd:integer |
| turbo:b6be95364ec943af2ef4ab161c11c855 | | | "3"^^xsd:integer |
+----------------------------------------+------------------------+------------------+------------------+
推荐答案
基本上，您只需在查询中将UNION替换为.(或者，您也可以像@aksw在下面的评论中指出的那样，删除这个UNION)。
但是，在GraphDB中，您将收到一个错误：
变量
?datecount已在以前的投影中使用。装订 从SESAME 2.8开始不会通过投影进行传播，因此这可能 导致查询中出现逻辑错误。
因此，按如下方式更改您的查询：
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX turbo: <http://example.org/ontologies/>
SELECT ?part ?xsddate ?datecount_ ?countmax
WHERE
{ { SELECT ?part ?xsddate (COUNT(?xsddate) AS ?datecount_)
WHERE
{ ?part rdf:type turbo:StudyPartWithBBDonation ;
turbo:hasBirthDateO ?dob .
?dob turbo:hasDateValue ?xsddate
}
GROUP BY ?part ?xsddate
}
.
{ SELECT ?part (MAX(?datecount) AS ?countmax)
WHERE
{ SELECT ?part ?xsddate (COUNT(?xsddate) AS ?datecount)
WHERE
{ ?part rdf:type turbo:StudyPartWithBBDonation ;
turbo:hasBirthDateO ?dob .
?dob turbo:hasDateValue ?xsddate
}
GROUP BY ?part ?xsddate
}
GROUP BY ?part
}
}
在Blazegraph中，您可以使用named subqueries：
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX turbo: <http://example.org/ontologies/>
SELECT ?part ?xsddate ?datecount ?countmax
WITH
{ SELECT ?part ?xsddate (COUNT(?xsddate) AS ?datecount)
WHERE
{ ?part rdf:type turbo:StudyPartWithBBDonation ;
turbo:hasBirthDateO ?dob .
?dob turbo:hasDateValue ?xsddate
}
GROUP BY ?part ?xsddate
} AS %sub
WHERE
{ { SELECT ?part (MAX(?datecount) AS ?countmax)
WHERE { INCLUDE %sub } GROUP BY ?part
}
INCLUDE %sub
}
这篇关于按组选择出现频率最高的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
